Re: Questions relating to "modified while in use" messages

Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> While working with alter table add constraint, I realized we 
> get these messages if a second session blocks on the lock the 
> alter table is getting.

It's coming from the relcache code, which sees that the table
definition has been altered when the ref count on the relcache
entry is > 0.  This is unfortunately the wrong thing, because
if the definition update is seen when first trying to access
the table during a given transaction, the ref count is guaranteed
to be > 0 (we bump the count and then check for SI update messages).
But there's no reason to fail in that situation, we should just
accept the update and forge ahead.

The correct fix will involve giving the relcache more context
information about whether it's safe to accept the cache update without
throwing an error.  I think the behavior we really want is that a change
is accepted silently if refcount = 0 (ie, we're not really using the
table now, we just have a leftover cache entry for it), *or* if we are
just now locking the table for the first access to it in the current
transaction.  But relcache can't now determine whether that second
statement is true.  Adding a relcache entry field like "xact ID at time
of last unlock of this cache entry" might do the trick, but I'm too
tired to think it through carefully right now.

A related issue is that we should probably grab some kind of lock
on a table when it is first touched by the parser within a statement;
right now we grab the lock and then release it, meaning someone could
alter information that is already getting factored into parse/plan.
Throwing an error as relcache now does protects us from trying to
actually execute such an obsoleted plan, but if we change relcache
to be more permissive we can't get away with such sloppiness at the
higher levels.  This has been discussed before (I think Hiroshi pointed
it out first).  See the archives...
        regards, tom lane