![]() Even if the operation itself is very fast or can run without holding a lock for the whole operation, ALTER TABLE still needs to briefly acquire an exclusive lock on the table before it can start its work, and while it's waiting for that lock, all other statements that touch the table - even reads! - are blocked. It seems that most or all ALTER TABLE commands behave like this. Observe that this also hangs it's now blocked waiting for the ALTER TABLE command to complete, which is in turn blocked waiting for the first transaction to complete. Open a third terminal, and try to run SELECT * FROM users WHERE id = 2 Observe that this query hangs, waiting for the transaction in the first shell to be committed. Leave the first shell open, then open a second one and try to alter the table: ALTER TABLE users ADD COLUMN credits bigint NOT NULL DEFAULT 0 INSERT INTO users (name) VALUES ('bob'), ('fred') In one psql shell, create a table, then start a transaction, do a read from the table, and don't commit: CREATE TABLE users (id SERIAL, name TEXT) Some simple steps to reproduce this (tested in Postgres 13.3): Worse, while it's waiting for that lock, it blocks all writes and reads involving the table. Still needs to wait for an exclusive lock on the users table before it can run, even though it'll execute instantly once the lock is acquired. ![]() A command like this one from the linked blog post ALTER TABLE users ADD COLUMN credits bigint NOT NULL DEFAULT 0 Unfortunately, that final caveat is important. Lots of PostgreSQL ALTER TABLE commands, like adding a new column with a default value, have clever optimisations in the latest versions of PostgreSQL that allow them to execute basically instantly, even on large tables, once Postgres has briefly acquired a lock on the table. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |