From: Noah Misch <noah@leadboat.com>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Nikolas Everett <nik9000@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Exhaustive list of what takes what locks
Date: Tue, 22 Feb 2011 23:21:08 -0500
Message-ID: <20110223042108.GA27781@tornado.gateway.2wire.net> (raw)
In-Reply-To: <AANLkTikNzBfCQxsfed6qYTv0GrYZ8vM1wjwStjd8PwT=@mail.gmail.com>
References: <AANLkTiksaoSZ03m_jALCBwhfogZxNA5_wKo_3J6ydBfF@mail.gmail.com>
<20110202052004.GA26098@tornado.gateway.2wire.net>
<AANLkTikNzBfCQxsfed6qYTv0GrYZ8vM1wjwStjd8PwT=@mail.gmail.com>
On Tue, Feb 22, 2011 at 10:18:36PM -0500, Robert Haas wrote:
> On Wed, Feb 2, 2011 at 12:20 AM, Noah Misch <noah@leadboat.com> wrote:
> >> 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 problem is that constraints can affect the query plan. If a
> transaction sees the constraint in the system catalogs (under
> SnapshotNow) but the table data doesn't conform (under some earlier
> snapshot) and if the chosen plan depends on the validity of the
> constraint, then we've got trouble. At least when running at READ
> COMMITTED, taking an AccessExclusiveLock protects us against that
> hazard (I'm not exactly sure what if anything protects us at higher
> isolation levels... but I hope there is something).
AccessExclusiveLock does not prevent that problem. We're already on thin ice in
this regard:
-- session 1
CREATE TABLE t (x) AS SELECT NULL::int;
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT 1;
-- session 2
DELETE FROM t;
ALTER TABLE t ALTER x SET NOT NULL;
-- session 1
TABLE t;
With contortions, we can coax the same from READ COMMITTED:
-- session 1
CREATE TABLE t (x) AS SELECT NULL::int;
CREATE FUNCTION pg_temp.f() RETURNS int LANGUAGE sql
STABLE -- reuse snapshot
AS 'SELECT 1; TABLE t'; -- extra statement to avoid inlining
VALUES (pg_sleep(15), pg_temp.f());
-- session 2
DELETE FROM t;
ALTER TABLE t ALTER x SET NOT NULL;
The catalogs say x is NOT NULL, but we read a NULL value just the same. I'm not
sure what anomalies this permits today, if any, but it's in the same vein.
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, robertmhaas@gmail.com, nik9000@gmail.com
Subject: Re: Exhaustive list of what takes what locks
In-Reply-To: <20110223042108.GA27781@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