16 January 2023
Previously, I wrote that you should never lock tables. And you usually shouldn’t! But sometimes, there’s a good reason to. Here’s one.
When you are doing a schema-modifying operation, like adding a column to a table, PostgreSQL needs to take an
ACCESS EXCLUSIVE lock on the table while it is modifying the system catalogs. Unless it needs to rewrite the table, this lock isn’t held for very long.
However, locks in PostgreSQL are first-come, first-served. If the system is busy, there may be conflicting locks on the table that you are attempting to modify. (Even just a
SELECT statement takes lock on the tables it is operating on; it just doesn’t conflict with much.) If the
ALTER TABLE statement can’t get the lock right away, it enters a queue, waiting to get to the front and get the lock.
However, now, every lock after that enters the queue, too, behind that
ALTER TABLE. This can create the result of a long-running
ACCESS EXCLUSIVE lock, even though it’s not granted. On a busy table on a busy system, this can shut things down.
So, what to do?
You can do this:
FOR i IN 1 .. 1000 LOOP
LOCK TABLE t NOWAIT;
ALTER TABLE t ADD COLUMN i BIGINT;
EXCEPTION WHEN lock_not_available THEN
This loops until it can acquire the lock, but doesn’t sit in the queue if it can’t. Once it acquires the lock, it does the modification and exits. If it can’t acquire the lock after a certain number of cycles, it exits with an error (you can set the number of cycles to anything, and you can adjust time it sleeps after failing to get the lock).