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?

Dmitriy Grankin
  • 239
  • 4
  • 12
  • 1
    Determine who is holding the locks on the table in question before you attempt to drop it. – mustaccio Sep 03 '20 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 03 '20 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. –  Sep 03 '20 at 11:46
  • Well, there you go. You'll need to figure out the source of those locks. – mustaccio Sep 03 '20 at 11:49
  • See if this helps Database "frozen" on ALTER TABLE – mustaccio Sep 03 '20 at 11:49
  • @mustaccio, how to apply this approch ALTER TABLE tblah ADD COLUMN cblah; ALTER TABLE tblah ALTER COLUMN cblah DEFAULT dblah; UPDATE tblah SET cblah = dblah WHERE cblah IS NULL; ALTER TABLE tblah ALTER COLUM cblah NOT NULL; to queries like drop table blah or alter table tblah drop column cblah? – Dmitriy Grankin Sep 03 '20 at 12:04

0 Answers0