Обсуждение: Questions relating to "modified while in use" messages

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.  I'm not sure what (if any) way there
is around this in the code, but I figure I should remove this
problem before sending the next patch for it so I thought I'd
throw it out too see if anyone can give me a pointer as
to what to do.

Stephan Szabo
sszabo@bigpanda.com

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

On Thu, 13 Jul 2000, Tom Lane wrote:

> 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

Okay... I found the code that was giving the message, but wasn't
sure if there was a way around it that one was expected to use.
It had worried me since that meant that using an alter on a
table that might be in use would do bad things, and I didn't want
to let it through if there was some local thing in my routine
that would easily fix it.
Of course, I also really only noticed it when I ran the two really
close together or the alter table inside a transaction.

Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> Of course, I also really only noticed it when I ran the two really
> close together or the alter table inside a transaction.

Right, the problem only comes up if the report of the table change
arrives at the other backend just as it's preparing to start a new
transaction on that same table.  If the other backend's first table
lock after the ALTER commits is on some other table, no problem.

We do need to fix it but I think there are higher-priority problems...
        regards, tom lane