Re: Exhaustive list of what takes what locks

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