3

I do experience the following situation on a PostgreSQL 11:

  1. A transaction with unknown statements creates an ACCESS SHARE lock on pg_class.
  2. Another connection wants to execute DROP TABLE which causes an ACCESS EXCLUSIVE lock on pg_class. This operation is blocked because operation #1 still holds the lock.
  3. Following that any new connection to the DB is waiting forever, because it needs to access pg_class as well.

The only way to proceed is to either kill operation #1 or #2.

While I can reproduce the situation with manual locking the tables, I do not find an explanation what causes the pg_class lock on operation #1. That is, pg_stats_activity only shows a SELECT as current query which is idle in transaction. I suspect this not being the statement creating the lock, however.

Thus, any input on what could cause ACCESS SHARE locks on pg_class except DROP TABLE is highly appreciated.

Update atm it is also unclear why DROP TABLE wants to lock pg_class.

  • What table is being dropped? – jjanes Feb 10 at 1:54
  • Some temporary Tableau table. Unfortunately I don’t have any details about it’s definition. – Sebastian Dressler Feb 10 at 7:05
0

The situation is caused by a long-running transaction which does a SELECT on pg_class first. This causes the ACCESS SHARE lock.

Typically, DROP TABLE does not cause an ACCESS EXCLUSIVE lock, but in that case there is a PG extension loaded which uses more restrictive locking.

Solution for the current situation is to move the SELECT on pg_class out of the long-running transaction.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.