Re: relation ### modified while in use

Alex Pilosov <alex@pilosoft.com> writes:
> I think this happens after I create/modify tables which reference this
> table. This is spontaneous, and doesn't _always_ happen...

Um.  I was hoping it was something more easily fixable :-(.  What's
causing the relcache to decide that the rel has been modified is the
addition or removal of foreign-key triggers on the rel.  Which seems
legitimate.  (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.)

There are two distinct known bugs that allow the error to be reported.
These have been discussed before, but to recap:

1. relcache will complain if the notification of cache invalidation
arrives after transaction start and before first use of the referenced
rel (when there was already a relcache entry left over from a prior
transaction).  In this situation we should allow the change to occur
without complaint, ISTM.  But the relcache doesn't currently have any
concept of first reference versus later references.

2. Even with #1 fixed, you could still get this error, because we are
way too willing to release locks on rels that have been referenced.
Therefore you can get this sequence:

Session 1            Session 2

begin;

select * from foo; -- LockRelation(AccessShareLock); -- UnLockRelation(AccessShareLock);
            ALTER foo ADD CONSTRAINT;              -- LockRelation(AccessExclusiveLock);              -- lock released
atcommit
 

select * from foo; -- LockRelation(AccessShareLock); -- table schema update is detected, error must be reported


I think that we should hold at least AccessShareLock on any relation
that a transaction has touched, all the way to end of transaction.
This creates the potential for deadlocks that did not use to happen;
for example, if we have two transactions that concurrently both do
begin;select * from foo;  -- gets AccessShareLockLOCK TABLE foo;        -- gets AccessExclusiveLock...end;

this will work currently because the SELECT releases AccessShareLock
when done, but it will deadlock if SELECT does not release that lock.

That's annoying but I see no way around it, if we are to allow
concurrent transactions to do schema modifications of tables that other
transactions are using.

Comments anyone?
        regards, tom lane