On Tue, Feb 01, 2011 at 02:18:37PM -0500, Nikolas Everett wrote: > Is there an exhaustive list of what takes what locks and how long they last? This documents which commands take each lock type, but it is not exhaustive: http://www.postgresql.org/docs/current/interactive/explicit-locking.html All locks on user-created database objects last until the transaction ends. This does not apply to advisory locks. Also, many commands internally take locks on system catalogs and release those locks as soon as possible. > 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); > DROP TABLE foo; > 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. PostgreSQL 9.1 will contain changes to make similar operations, though not that one, take ShareRowExclusiveLock instead of AccessExclusiveLock. Offhand, the same optimization probably could be arranged for it with minimal fuss. If "account" is heavily queried but seldom changed, that might be enough for you. The internal implementation of a FOREIGN KEY constraint takes the form of triggers on both tables. Each INSERT or UPDATE needs to know definitively whether to fire a given trigger, so adding or removing an arbitrary trigger will continue to require at least ShareRowExclusiveLock. In the abstract, the special case of a FOREIGN KEY constraint could be looser still, but that would be tricky to implement. nm -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
