Is there an exhaustive list of what takes what locks and how long they last? I'm asking because we just had some trouble doing a hot db change to an 8.3.6 system. I know it is an old version but it is what I have to work with. You can reproduce it like so:
First:
DROP TABLE IF EXISTS foo;
DROP TABLE IF EXISTS account;
CREATE TABLE account (account_id SERIAL PRIMARY KEY, name CHARACTER VARYING NOT NULL);
CREATE TABLE foo (account_id INTEGER NOT NULL REFERENCES account (account_id), stuff CHARACTER VARYING);
In one connection:
INSERT INTO account (name) SELECT generate_series FROM GENERATE_SERIES(0, 10000000);
In another connection while that last one is running:
DROP TABLE foo;
And in another connection if you are feeling frisky:
select
pg_stat_activity.datname,pg_class.relname,pg_locks.transactionid, pg_locks.mode, pg_locks.granted,
pg_stat_activity.usename,pg_stat_activity.current_query, pg_stat_activity.query_start,
age(now(),pg_stat_activity.query_start) as "age", pg_stat_activity.procpid
from pg_stat_activity,pg_locks left
outer join pg_class on (pg_locks.relation = pg_class.oid)
where pg_locks.pid=pg_stat_activity.procpid order by query_start;
That query shows that the DROP takes an AccessExclusiveLock on account. This isn't totally unexpected but it is unfortunate because it means we have to wait for a downtime window to maintain constraints even if they are not really in use.
This isn't exactly how our workload actually works. Ours is more deadlock prone. We have many connections all querying account and we do the migration in a transaction. It looks as though the AccessExclusiveLock is held until the transaction terminates.
Nik Everett
