Re: relation ### modified while in use

Tom Lane wrote:

> 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.
>

I agree with you.
I've wondered why AccessShareLock is a short term lock.

If we have a mechanism to acquire a share lock on a tuple,we
could use it for managing system info generally. However the
only allowed lock on a tuple is exclusive. Access(Share/Exclusive)
Lock on tables would give us a restricted solution about pg_class
tuples.

Thers'a possibility of deadlock in any case but there are few
cases when AccessExclusiveLock is really needed and we could
acquire an AccessExclusiveLock manually from the first if
necessary.

I'm not sure about the use of AccessShareLock in parse-analyze-
optimize phase however.

Regards.
Hiroshi Inoue