Re: ALTER TABLE SET STATISTICS requires AccessExclusiveLock - Mailing list pgsql-hackers

From Simon Riggs
Subject Re: ALTER TABLE SET STATISTICS requires AccessExclusiveLock
Date
Msg-id 1279353703.1735.50231.camel@ebony
Whole thread Raw
In response to Re: ALTER TABLE SET STATISTICS requires AccessExclusiveLock  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-hackers
On Fri, 2010-07-16 at 20:45 -0400, Tom Lane wrote:
> Andres Freund <andres@anarazel.de> writes:
> > Just to help me: The primary reasons for using SnapshotNow is speed and in 
> > some cases correctness (referential integrity). Right? Any other reasons?
> 
> Well, the main point for system catalog accesses is that you *must* have
> an up-to-date view of the table schemas.  As an example, if someone just
> added an index to an existing table, it would not do for an INSERT to
> fail to update that index --- no matter whether it's from a serializable
> transaction or not.  So the DDL-executing transaction must hold a lock
> that would block any operation that had better be able to see what it
> did, and once another transaction has acquired the lock that lets it go
> ahead with another operation, it had better see the results of the DDL
> transaction.
> 
> However that argument mostly applies to what the executor does.  A plan
> could still be usable despite having been made against a now-obsolete
> version of the table schema.
> 
> In the case at hand, I think most constraint-adding situations would
> require at least ShareLock, because they had better block execution of
> INSERT/UPDATE/DELETE operations that could fail to honor the constraint
> if they didn't see it in the catalogs.  But AFAICS, addition of a
> constraint need not block SELECT, and it need not invalidate existing
> plans.
> 
> CREATE INDEX uses ShareLock because it's okay to run multiple CREATE
> INDEXes in parallel (thanks to some rather dodgy coding of the catalog
> updates).  For other cases of constraint additions, it might not be
> practical to run two constraint additions in parallel.  In that case we
> could use ShareRowExclusive instead, which is self-exclusive but is not
> any stronger than Share from the perspective of DML commands.  Since
> it's not, I'm unconvinced that it's worth taking any great pains to try
> to make constraint additions run in parallel.

The patch follows all of the above exactly.

-- Simon Riggs           www.2ndQuadrant.comPostgreSQL Development, 24x7 Support, Training and Services



pgsql-hackers by date:

Previous
From: Alex Hunsaker
Date:
Subject: Re: pg_dump(all) --quote-all-identifiers
Next
From: Peter Eisentraut
Date:
Subject: Re: Functional dependencies and GROUP BY