Re: Table Lock issue

On Fri, 2004-04-16 at 00:17, Tom Lane wrote:
> "Uwe C. Schroeder" <uwe(at)oss4u(dot)com> writes:
> > On Thursday 15 April 2004 04:20 pm, Tom Lane wrote:
<snip most excellent summary of how this problem manifests itself>
>
> If we were to advance the QuerySnaphot between statements of a plpgsql
> function, the problem would go away because the SELECT would see
> transaction A as already committed, making the original row not-good.
>
> 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.

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

Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL