Re: relation ### modified while in use

Philip Warner <pjw(at)rhyme(dot)com(dot)au> writes:
> At 01:01 23/10/00 -0400, Tom Lane wrote:
>> (It's barely possible that we could get away with allowing
>> triggers to be added or deleted mid-transaction, but that doesn't feel
>> right to me.)

> A little OT, but the above is a useful feature for managing data; it's not
> common, but the following sequence is essential to managing a database safely:

> - Start TX
> - Drop a few triggers, constraints etc
> - Add/change data to fix erroneous/no longer accurate business rules
> (audited, of course)
> - Reapply the triggers, constraints
> - Make sure it looks right
> - Commit/Rollback based on the above check

There is nothing wrong with the above as long as you hold exclusive
lock on the tables being modified for the duration of the transaction.

The scenario I'm worried about is on the other side, ie, a transaction
that has already done some things to a table is notified of a change to
that table's triggers/constraints/etc being committed by another
transaction. Can it deal with that consistently? I don't think it can
in general. What I'm proposing is that once an xact has touched a
table, other xacts should not be able to apply schema updates to that
table until the first xact commits.

regards, tom lane