Why does ALTER TABLE DROP CONSTRAINT take so long on an empty table?

Question

I am trying to load several million rows of data into a table (the "follow" table, which contains two foreign keys for the user table and the associated indices of these keys) in one transaction. My initial attempt caused the script to crash because the system memory was exhausted.

In some studies, it was concluded that the accident was caused by foreign key constraints, so I checked that the table is empty (that is, the transaction that led to the process being killed did not end) and changed my script to remove the constraints and foreign key indices to insert data. My intention was to subsequently recreate the constraints and indexes.

However, the ALTER TABLE DROP CONSTRAINT command to reset the first foreign key constraint in a table takes a very long time (tens of minutes), despite the fact that the table is completely empty.

The only thing I can think of is that it is associated with a large amount of data that I wrote to the table and then did not commit, because the script failed. But, of course, since the transaction was not completed, I cannot find any trace of this data in the database.

What can lead to the fact that this request will be slow (or perhaps not launched at all, at the time of writing this article is still ongoing) and how to avoid it?

Other transactions open in the database (transactions transfer other very large tables within a few hours), but none of these transactions relate to the following table.

Edit: pg locks look like this:

db=#  select relation::regclass, * from pg_locks where not granted; -[ RECORD 1 ]------+-------------------- relation      | auth_user locktype      | relation database      | 53664 relation      | 54195 page        | tuple        | virtualxid     | transactionid    | classid       | objid        | objsubid      | virtualtransaction | 5/343 pid         | 17300 mode        | AccessExclusiveLock granted       | f 

The pid above (17300) is just an ALTER TABLE request. There are no other locks or processes waiting for locks.

Answer 1

Check pg_locks and make sure that no other transaction has a lock in the table. Even a read lock will prevent ALTER TABLE .

 \x select pg_class.relname, pg_locks.* from pg_locks left outer join pg_class ON (pg_locks.relation = pg_class.oid) where pg_locks.relation = 'auth_user'::regclass; 

By filtering where not granted in the original request, you only see outstanding locks, not the locks that block them.

The fact that this lock was not provided tells me that this is a lock problem. Another transaction contains a lock on this table, which prevents the ALTER TABLE AccessExclusiveLock from being received that needs to be executed. It may just be a transaction that SELECT ed from the table at some point.

You can find it by joining pg_stat_activity :

 select c.relname, l.*, psa.* from pg_locks l inner join pg_stat_activity psa ON (psa.pid = l.pid) left outer join pg_class c ON (l.relation = c.oid) where l.relation = 'test'::regclass; 

which will show you the transaction (s) holding or waiting for locks in this table, which locks, which statement is currently being executed by these transactions, etc.

(For those in older versions: pg_stat_activity.pid , pg_stat_activity.pid used. Therefore, modify the query accordingly if you are using old PostgreSQL or upgrading.)