0

After issuing DROP TABLE or ALTER TABLE command pg_stat_activity table becomes full of Locks and the query hangs forever. Killing PIDs and reissuing query gives the same result. The problem disappears after the system gets restarted (I restart docker container for that).

How to investigate this issue?

Improve this question
6
  • 1
    Determine who is holding the locks on the table in question before you attempt to drop it.
    – mustaccio
    Sep 3, 2020 at 11:37
  • @mustaccio, I always see multiple locks of unknown nature in pg_stat_activity table. I can not give a better report at time, as I have just reset the docker container
    – Dmitriy Grankin
    Sep 3, 2020 at 11:41
  • 1
    You could try lock the_table in exclusive mode nowait before attempting the ALTER or DROP. That way you would get an error immediately if some other transaction still uses that table. Make sure you do that in a transaction to keep the lock if it succeeds.
    – user1822
    Sep 3, 2020 at 11:46
  • Well, there you go. You'll need to figure out the source of those locks.
    – mustaccio
    Sep 3, 2020 at 11:49
  • See if this helps Database "frozen" on ALTER TABLE
    – mustaccio
    Sep 3, 2020 at 11:49

0

Reset to default

Browse other questions tagged or ask your own question.