Why would ALTER TABLE DROP CONSTRAINT on an empty table take a long time?

Question

I am trying to load several million rows of data into a table (a "follow" table that contains two foreign keys to the user table, and the associated indexes on those keys) in a single transaction. My initial attempt caused my script to crash because system memory was exhausted.

Some research resulted in the conclusion that the crash was because of the foreign key constraints, so I verified that the table was empty (that is, the transaction that resulted in the process being killed did not complete) and modified my script to drop the foreign key constraints and indexes in order to insert the data. My intention was to recreate the constraints and indexes afterwards.

However, the ALTER TABLE DROP CONSTRAINT command to drop the first foreign key constraint on the table is taking a very long time (tens of minutes), despite the table being completely empty.

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

What could cause this query to be slow (or possibly not run at all; at the time of this writing it is still ongoing) and how can I avoid it?

There are other transactions open in the database (several-hour-long transactions migrating other very large tables) but none of those transactions are touching the follow table.

Edit: pg locks are as below:

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 simply the ALTER TABLE query itself. There are no other locks and no processes waiting for locks.

Check `pg_locks` and *verify* that no other transaction has a lock on the table. Even a read lock will prevent `ALTER TABLE`. — Craig Ringer, Jul 12 '13 at 00:55
@CraigRinger Edited to add the locks table info: the long and short is that the only lock is the one created by ALTER TABLE itself. — Andrew Gorcester, Jul 12 '13 at 01:12

Craig Ringer · Accepted Answer · 2014-04-07T07:30:20.593

Check pg_locks and verify that no other transaction has a lock on 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 for where not granted in your original query you're only showing outstanding locks, not the locks that're blocking them.

The fact that this lock has not been granted tells me that this is a locking problem. Another transaction holds a lock on this table that prevents the ALTER TABLE from acquiring the AccessExclusiveLock it needs to proceed. This might just be a transaction that SELECTed from the table at some point.

You can find it by joining on 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 on locks in this table, what the locks are, what statement is currently being run by those transactions, etc.

(For those on old versions: pg_stat_activity.pid used to be procpid. So change the query appropriately if you're using an old PostgreSQL. Or update.)

It turned out that for some reason, a separate process which was locking the user table (which the follow table has a foreign key relationship to) was blocking the ALTER TABLE query. I don't understand why locking the user table would block that, but the mystery is solved. Thank you! — Andrew Gorcester, Jul 12 '13 at 06:51
@JohnBarça tried to edit with: "In versions of Postgres prior to 9.2, pg_stat_activity pid was called procpid". — Craig Ringer, Apr 07 '14 at 07:29

Why would ALTER TABLE DROP CONSTRAINT on an empty table take a long time?

1 Answers1