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
Date: Wed, 2 Feb 2011 00:20:04 -0500
Message-ID: <20110202052004.GA26098@tornado.gateway.2wire.net> (raw)
In-Reply-To: <AANLkTiksaoSZ03m_jALCBwhfogZxNA5_wKo_3J6ydBfF@mail.gmail.com>
References: <AANLkTiksaoSZ03m_jALCBwhfogZxNA5_wKo_3J6ydBfF@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: noah@leadboat.com, nik9000@gmail.com
Subject: Re: Exhaustive list of what takes what locks
In-Reply-To: <20110202052004.GA26098@tornado.gateway.2wire.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox