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:
DROP TABLE IF EXISTS foo;
DROP TABLE IF EXISTS account;
CREATE TABLE account (account_id SERIAL PRIMARY KEY, name CHARACTER VARYING
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,
In another connection while that last one is running:
DROP TABLE foo;
And in another connection if you are feeling frisky:
age(now(),pg_stat_activity.query_start) as "age",
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.
pgsql-performance by date
|Next:||From: Greg Smith||Date: 2011-02-01 19:24:44|
|Subject: Re: Any experience using "shake" defragmenter?|
|Previous:||From: Benjamin Krajmalnik||Date: 2011-02-01 17:22:21|
|Subject: Re: Configuration for a new server.|