CODEWITHSUNDEEP
databasepostgresql
NSF
NSF

Reputation: 2549

Why does Postgres start enforcing ACCESS EXCLUSIVE lock for ALTER TABLE ADD COLUMN statements starting 9.4?

My original understanding for ALTER TABLE ADD COLUMN in postgres is that if you just add a simple new column (i.e. no default value, no indexing, no non-null validation etc.) then there's no locking.

However starting from 9.4 this no longer seems to be true:

https://www.postgresql.org/docs/9.3/static/sql-altertable.html

https://www.postgresql.org/docs/9.4/static/sql-altertable.html

In the 9.4 documentation it says

An ACCESS EXCLUSIVE lock is held unless explicitly noted. When multiple subcommands are listed, the lock held will be the strictest one required from any subcommand.

while there was no such thing in 9.3.

In practice, 9.4 does seem more likely to trigger deadlock when the table has heavy traffic - not exactly sure why this is the case though. Theory is the transaction is implicitly holding a ROW EXCLUSIVE lock on the table and then it gets escalated to ACCESS EXCLUSIVE when the statement gets executed but again it's just a theory.

Question is: what changed the design and what's the correct way to add a column today for tables with high traffic?

Upvotes: 4

Views: 1089

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246238

I just tested with PostgreSQL 9.2, and it takes an ACCESS EXCLUSIVE lock as it should.

The lock will only be held for a short duration, but if there is a lot of concurrent activity (in particular long operations), there could be a noticable hang with transactions piling up behind the ACCESS EXCLUSIVE lock request that is waiting for a long transaction to finish.

The difference in the documentation is due to PostgreSQL commit e5550d5fec66aa74caad1f79b79826ec64898688 which improved the documentation while, ironically, reducing the lock level required by certain ALTER TABLE subcommands.

It is an ongoing effort to reduce the number and severity of locks in PostgreSQL wherever possible.

Upvotes: 2

Related Questions