I see in Postgres docs:
Two transactions cannot hold locks of conflicting modes on the same table at the same time
During maintenance (schema migration) we saw cases when we can't ALTER TABLE because of hanging SELECT in transaction (pg_locks + pg_stat_activity).
In order to proceed migration we revoke CONNECT from "rogue" users and kicked all their sessions.
Is that correct that:
BEGIN;
SELECT ...;
will block ALTER TABLE until COMMIT or ROLLBACK?
Best Answer
You got it right.
This is how PostgreSQL prevents conflicts: you cannot drop or modify a table while someone is using it.