Re: PostgreSQL transaction locking problem

"Jeff Martin" <jeff(at)dgjc(dot)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. But
the bottom line is that in the present implementation, a function cannot
see the effects of transactions that commit while it's running.

regards, tom lane