Re: locks in CREATE TRIGGER, ADD FK

Tom Lane wrote:
> I agree that we aren't MVCC with respect to DDL operations (and for this
> purpose CLUSTER is DDL). Trying to become so would open a can of worms
> far larger than it's worth, though, IMHO.

I think if we can come up with a reasonable way to handle all the
consequences, it's worth doing. And yes, I realize there are a lot of
consequences, so it may well not be possible.

> Transaction 1 Transaction 2
>
> BEGIN;
>
> ... BEGIN;
>
> ... INSERT INTO a;
>
> CLUSTER a; ...
>
> Currently, because T2 continues to hold a write lock on A until it
> commits, T1's CLUSTER will block until T2 commits; therefore CLUSTER's
> use of SnapshotNow is sufficient to not lose any live tuples. Were T1
> to use a transaction-aware snapshot to scan A, this would not be so.

I think this is somewhat tangential: we're discussing changing the
snapshot used to scan system catalogs, not user relations like A. The
only reason that CLUSTER's use of SnapshotNow is a problem at the moment
is the same reason that TRUNCATE is a problem -- a concurrent
serializable transaction will use the new relfilenode, not the old one.

> Transaction 1 Transaction 2
>
> BEGIN;
>
> ... CLUSTER a;
>
> INSERT INTO a;
>
> Were T1 using a transaction-aware snapshot to read pg_class, it would
> insert its new tuple into the wrong relfilenode for A, causing either
> immediate failure or eventual loss of a live tuple.

Yes, definitely a problem :( The same applies to TRUNCATE, naturally.
The only somewhat reasonable behavior I can think of is to cause
modifications to the oldrelfilenode to fail in a concurrent serializable
transaction. The behavior would be somewhat analogous to an UPDATE in a
serializable transaction failing because of a concurrent data
modification, although in this case we would error out on any
modification (e.g. INSERT).

-Neil