Date: Wed, 2 Feb 2011 00:20:04 -0500
From: Noah Misch <noah@leadboat.com>
To: Nikolas Everett <nik9000@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Exhaustive list of what takes what locks
Message-ID: <20110202052004.GA26098@tornado.gateway.2wire.net>
References: <AANLkTiksaoSZ03m_jALCBwhfogZxNA5_wKo_3J6ydBfF@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <AANLkTiksaoSZ03m_jALCBwhfogZxNA5_wKo_3J6ydBfF@mail.gmail.com>
User-Agent: Mutt/1.5.18 (2008-05-17)

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