Re: PostgreSQL transaction locking problem

Tom Lane wrote:
>
> "Jeff Martin" <jeff@dgjc.org> writes:
> >> As written, he gets a delay (because of the LOCK) *and* duplicate IDs
> >> (because with the serializable isolation level, the second xact can't
>
> > i get the same error whether using "read commited" or "serializable".
>
> [ thinks about that... ]  Yeah, probably so, because SetQuerySnapshot
> is presently executed only in the outer command loop; there won't be
> one between the LOCK and the SELECT inside your function.  So the SELECT
> still doesn't think that the other xact has committed.  You could make
> it work (in read-committed mode) if you issued the LOCK from the
> application before calling the function.
>
> There's been some discussion about whether SetQuerySnapshot should occur
> between statements in plpgsql functions or not --- AFAIR, there were
> arguments on both sides, and we haven't come to a consensus yet.

I've always been on the side that the Snapshot should be changed
in read-committed mode except the sequence of SELECT statements.

1) AFAIR no one was able to understand the current behavior.
   For whom the current spec is ?
2) IIRC the current spec comes from a demand to keep a
   consistency of queries with functions. But where was
   the proof of the possibility to keep a consistency ?
   IMHO it's impossible to guarantee a consistency of
   queries with functions which have side effects and
   the result would be implementation-dependent.

regards,
Hiroshi Inoue