"D. Dante Lorenso" <dante@lorenso.com> writes:
> Can someone please explain what is happening to the PL/PGSQL function
> in regards to commit visibility and transactions?
Since the function is executed as part of a single client SELECT
command, the whole thing runs with a single snapshot; its view of
other process' actions on the database is frozen at the start of that
outer SELECT, even in READ COMMITTED mode.
There have been past discussions about whether this is a good idea
or not, but so far no consensus to change it, IIRC. (Look for
"SetQuerySnapshot" in the archives, if you care.)
But at any rate, because of that behavior it's really impossible to
encapsulate the locking behavior entirely in a plpgsql function; the
wait-and-retry loop *must* be on the client side. That leaves me
doubting that there's much value in trying to encapsulate any of it.
As Manfred noted, you may eventually want to add additional conditions
to the SELECT, and that's just lots easier to do if the logic is all
in one place rather than split between client and server code.
I still like my serializable-mode solution better than any of the
alternatives proposed so far. Manfred's latest try doesn't solve
the race-condition problem, because the second SELECT will never
find anything the first one didn't find. (It could if it were on
the client side... but you don't need two selects at all with the
serializable-mode solution.)
regards, tom lane