🛑 Experiencing AccessExclusiveLock Stuck on Add Column with Default in Postgres
Hey folks, running into a weird issue on my Supabase postgres
- It acquires an AccessExclusiveLock but doesn’t complete.
- No other queries seem to be blocking it.
- Adding the column without a default works fine.
- Dev DB works fine (even larger table).
2 Replies
This is documented on the web as it is a Postgres thing.
Try altering to just add the column first then add back in the default in batches is one suggestion I see.
Vacuum the table before and after is also a suggestion.
But best to just search.
There is a similar question answered here https://stackoverflow.com/a/11938782 and Craig's comment explains below the answer about table locking up if you have data in it already and trying to make that alter in one go.
Stack Overflow
postgresql - add boolean column to table set default
Is this proper postgresql syntax to add a column to a table with a default value of false
ALTER TABLE users
ADD "priv_user" BIT
ALTER priv_user SET DEFAULT '0'
Thanks!