Re: Table Lock issue

Robert Treat <xzilla@users.sourceforge.net> writes:
> On Fri, 2004-04-16 at 00:17, Tom Lane wrote:
>> Now in this situation it is good to recognize the effects of other
>> transactions between statements of a plpgsql function, but it's not hard
>> to think up cases in which plpgsql functions would break if the visible
>> database state changes between statements.  So it's a bit of a tough
>> choice what to do.  I'm personally starting to think that we *should*
>> advance the QuerySnapshot, but as I said there's not yet a consensus
>> about it.

> The problem is that, while the people for changing this behavior keep
> stacking up, theres no way to quantify how many people it would cause
> trouble for... really we're only going on the theory that it could cause
> trouble for people; i don't recall anyone posting a real world example
> that requires the current semantics.

Well, that's because the people who need the current behavior (if any)
haven't had reason to complain ;-).  In general however it seems like
one would expect a series of statements issued inside a plpgsql function
to behave the same as if they were issued interactively (inside a
transaction block of course).  Right now that is true for SERIALIZABLE
mode but not true for READ COMMITTED mode.  So I'm starting to lean to
the idea that we should change it and document it as a potential
incompatibility.

>> Oh, one other point: SELECT FOR UPDATE fixes this because it has
>> different visibility rules.  Like UPDATE, it will *never* consider good
>> a row version that is marked as deleted by any committed transaction.

> The proposal to update the query snapshot inside plpgsql whenever a lock
> table in exclusive access is issued follows along this line and would
> seem like one way to help most people get around this problem since it's
> hard to imagine any real world scenario where one would want to lock a
> table exclusively and still see rows that are modified by other
> transactions.

Hm, I don't recall having heard that proposal in this context, and I
can't say that I like it.  In the first place we don't really want to be
encouraging people to lock tables exclusively, and in the second place
I'm not sure plpgsql can detect all such cases (what if the LOCK is
issued inside another function, or via EXECUTE?).  In the third place
it would create yet another subtle discrepancy in execution behavior
between functions and interactive commands, which seems like the wrong
direction to be going in.

            regards, tom lane