Why is ACCESS EXCLUSIVE LOCK necessary in PostgreSQL?

Question

I know PostgreSQL provides multiple lock modes for concurrency control. When I read through their documents, I cannot see why the ACCESS EXCLUSIVE lock is necessary in any case. The only difference between an EXCLUSIVE lock and ACCESS EXCLUSIVE lock seems to be that ACCESS EXCLUSIVE lock disallows reads from the table.

But isn't it a good thing that people are able to read? In what case should you prevent users from reading? In the documentation I see that usually DROP TABLE, etc. will use ACCESS EXCLUSIVE lock, but why? I was thinking that maybe when you drop a table the data is already partially gone when you are trying to read so the result will be corrupted. But then I realize since everything in Postgres is transactional, a DROP TABLE should not take effect until commit. So my guess should be wrong.

I am still confused. Why is ACCESS EXCLUSIVE lock needed again when you compared it to EXCLUSIVE lock? My best guess is that replacing ACCESS EXCLUSIVE lock with EXCLUSIVE lock is not going to break PostgreSQL. It is just sometimes people do not want others to read. Did I miss something?

Answer 1

If you have a look at the documentation, you see the list of operations that obtains this type of lock:

Acquired by the DROP TABLE, TRUNCATE, REINDEX, CLUSTER, VACUUM FULL, and REFRESH MATERIALIZED VIEW (without CONCURRENTLY) commands. Many forms of ALTER TABLE also acquire a lock at this level.

All those are physically (re)moving rows, so reading would be at least very unpredictable during these.

Talking about transactions, when you start reading from a table and another process decides slightly later to drop it, the latter must wait until the read finishes - the earlier will still get the data requested, and when that transaction commits, the second one takes the lock and drops the table. The other way around, the read will just fail after the wait from the dropping transaction to commit, as there is nothing to read from anymore.

If PostgreSQL allowed to read from a table being dropped, that would mean two things. One is, if I am not mistaken, that there existed a READ UNCOMMITTED isolation level. While you can choose that, it is in fact READ COMMITTED, meaning that no transaction is able to see intermediary states of other transactions. The other one is, as you already guessed, that possibly only a portion of the desired data is returned. This would lead to unpredictable behaviour, and that's the last thing you want from a database system.

Moreover, CLUSTER and VACUUM FULL are moving data between two files - how could one decide in which file to chase the rows?

All in all, in some cases reading data is not so urgent. Or in the case of DROP TABLE and TRUNCATE it is not so urgent anymore.