PostgreSQL – Alter Table Add Column Without Default Blocking

alter-tablepostgresqlpostgresql-10

tl;dr

The following query blocks the postgres server when executed

ALTER TABLE "image_data" ADD COLUMN "user_status" varchar(30) NULL

engine version 10.6

# of rows 1.5 mils

Hi,

I have a somewhat odd problem with my production database server (I am unable to reproduce on my local server). As part of the deployment process I wanted to add a new column with a default value of null. When I ran the above query (which was produced by dajngo's migration process), It failed and in the process locked the database and blocked other queries (non-locking selects) from executing. Since it was a production environment, I rolled back after a few minutes (approximately 10). What I find most unusual is that this same query executes within less than a second on my local db replicate (which has the exact same data as the production).

Edit:
When I run this transaction, it blocks the access to the database for all other select queries – which I think (please correct me if I'm wrong) implies that the database is in fact blocked by this transaction.

Best Answer

The problem is not your ALTER TABLE, but other transactions in the database.

You must have some long running transactions that hold any lock on the table (the ACCESS SHARE lock from a SELECT is sufficient to bloch the ACCESS EXCLUSIVE lock of an ALTER TABLE).

Your ALTER TABLE has to wait until this long running transactions are gone, and any later transactions that are trying to access the table have to queue behind the ALTER TABLE.

Close the long running transactions, and the statement will complete in a jiffy.

Related Question