Would a row-level lock affect a DDL ALTER TABLE statement on Postgres?

Question

Postgres version: 13.8

A week or so ago we ran a migration to safely drop a column on one of our tables. This column is not referenced by any other table and there was no index on the column. As far as I'm aware of, dropping a column acquires an exclusive lock on the table but only for a brief moment while it removes the column from the catalog. However this migration took around a minute to run and locked the table during the entire time. This table is heavily used and has around 17 million rows.

I've been trying to figure out why the drop column migration took a lot of time. This table also has a very commonly used SELECT FOR UPDATE query ran against it. Would a row-level lock like this affect a drop column statement or am I looking down the wrong path?

Answer 1

Short answer is yes.

Row-level locks taken with SELECT FOR UPDATE also take a table-level ROW SHARE lock, that conflicts with the exclusive lock for ALTER TABLE.

See for yourself. Start a transaction in one session:

BEGIN;
SELECT FROM tbl WHERE id = 1 FOR UPDATE;

-- ROLLBACK;  -- later

Then run in a separate session:

BEGIN;
ALTER TABLE tbl DROP COLUMN some_column;

-- ROLLBACK;  -- later

You'll see the 2nd transaction waiting until the first commits or rolls back.
(Be sure to close the 2nd transactions with ROLLBACK; unless you actually want drop the column.)

The manual:

ROW SHARE (RowShareLock)

Conflicts with the EXCLUSIVE and ACCESS EXCLUSIVE lock modes.

The SELECT command acquires a lock of this mode on all tables on which one of the FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE, or FOR KEY SHARE options is specified (in addition to ACCESS SHARE locks on any other tables that are referenced without any explicit FOR ... locking option).