Re: odd behavior: function not atomic/not seeing it's own updates/not acting serializable nor read committed

Robert Treat <xzilla(at)users(dot)sourceforge(dot)net> writes:
> "this is broken on so many levels..."

The issue here is that the second transaction has already set its
snapshot when it enters the function body; so even though its LOCK
command faithfully waits for the first transaction to commit, the
second transaction can't see (and so can't update) the two rows
inserted by the first transaction. It doesn't really see the update
of the original row either, except for purposes of its own UPDATE.

It would work more like you're expecting if you'd issued the LOCK
before calling the function. I realize that's not convenient in
many cases.

> OTOH if functions ran as if they we're in serializable mode, the second
> function would, upon attempt to update the first record, see that the
> record was already updated, and throw a "ERROR: Can't serialize access
> due to concurrent update", which could then be dealt with accordingly.

It would do that, if you had run it in serializable mode. I get:

regression=# begin;
BEGIN
regression=# set TRANSACTION ISOLATION LEVEL SERIALIZABLE ;
SET
regression=# select locktest1(20);
NOTICE: original entry in column a is 0
WARNING: Error occurred while executing PL/pgSQL function locktest1
WARNING: line 15 at SQL statement
ERROR: Can't serialize access due to concurrent update
regression=#

In the read-committed case, there has been some talk of executing
SetQuerySnapshot between statements of a function; search the archives
for "SetQuerySnapshot" to find past threads. I'm leaning in favor
of that myself, but no one's yet given a convincing analysis of what
this would change and what the downside might be.

(A compromise less likely to break existing code might be to do
SetQuerySnapshot only after a function issues LOCK, but still the real
effects of this would need to be clearly understood before such a
proposal is likely to pass.)

regards, tom lane