11/6/2023 0 Comments Postgresql alter table schemaFor this reason, teams need to have internal docs on how to do it correctly, and be extra careful during reviews. In short, PostgreSQL offers good ways to control locking and avoid keeping locks for a long time, but it’s fair to say that it is a minefield it’s easy to make mistakes, and every mistake can be costly. Then you can run VALIDATE later, which doesn’t need the ACCESS EXCLUSIVE lock because it assumes new rows are respecting the constraint. You can work around it by adding a CHECK CONSTRAINT with NOT VALID, which means that the constraint is applied to new rows but not to old ones. ![]() When adding a constraint like NOT NULL, Postgres needs to first check that there are no NULLs in the table, and it has to do that while holding the lock. There are other gotchas as well, like using the magic keyword CONCURRENTLY when adding indexes, but that doesn’t work inside transactions. If the timeout hits, you keep retrying until the lock succeeds, and only then perform the ALTER TABLE. The trick to avoid the above is to explicitly take the lock before running the ALTER TABLE and set a lock_timeout to avoid queueing queries for a long period of time. This gives your table the appearance of being unavailable. While Postgres is waiting to run your schema change, all of the queries that you would've assumed would run instantly are now being queued up. ![]() Here's the issue: if your table has a running query or a running transaction, your schema change cannot begin. To achieve this, all queries issued after the ALTER TABLE are put in a queue to run after the ALTER TABLE completes. However, when an ALTER TABLE query requiring ACCESS EXCLUSIVE runs, Postgres needs to create an opening where no other queries or transactions are running. During normal operations, reads and writes run on your tables concurrently. ![]() This means that the table is essentially inaccessible and it’s important to keep this lock for the minimum possible duration.Įven when the lock is taken for a short time, it can still make the table seem unavailable. PostgreSQL has many different types of locks and most ALTER TABLE statements (but not all) take the table ACCESS EXCLUSIVE lock, which conflicts with all other lock types.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |