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