Skip site navigation (1) Skip section navigation (2)

Re: Exhaustive list of what takes what locks

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Noah Misch <noah(at)leadboat(dot)com>
Cc: Nikolas Everett <nik9000(at)gmail(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Exhaustive list of what takes what locks
Date: 2011-02-23 17:21:12
Message-ID: AANLkTinf=fC9T8JJXsGMHDEggDNiXvj87opYfjO5huEq@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Tue, Feb 22, 2011 at 11:21 PM, Noah Misch <noah(at)leadboat(dot)com> wrote:
> 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(at)leadboat(dot)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.

Ugh.  Well, I guess if we want to fix that we need the conxmin bit Tom
was just musing about.  That sucks.

I wonder if it'd be safe to reduce the locking strength for *dropping*
a constraint, though.  The comment just says:

                        case AT_DropConstraint:         /* as DROP INDEX */

...but that begs the question of why DROP INDEX needs an
AccessExclusiveLock.  It probably needs such a lock *on the index* but
I don't see why we'd need it on the table.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

In response to

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2011-02-23 17:31:19
Subject: Re: Exhaustive list of what takes what locks
Previous:From: Noah MischDate: 2011-02-23 04:21:08
Subject: Re: Exhaustive list of what takes what locks

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group