Re: ALTER TABLE SET STATISTICS requires AccessExclusiveLock

Andres Freund <andres(at)anarazel(dot)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.

regards, tom lane