Okay, I understand Tatsuo's original complaint, and I don't think it's
a bug exactly --- it's MVCC/Read Committed operating as designed. Using
the variant script I just posted and two *freshly started* backends, do:
Backend 1:
regression=# begin;
BEGIN
regression=# SELECT myftest(1);
NOTICE: i 1 ctid (0,42) xmin 5701 xmax 0 cmin 3 cmax 0
NOTICE: i 2 ctid (0,43) xmin 5701 xmax 0 cmin 5 cmax 0myftest
--------- 0
(1 row)
Backend 2:
regression=# SELECT myftest(1);
[ backend 2 hangs; now go back and commit backend 1 ]
NOTICE: i 1 ctid (0,40) xmin 5696 xmax 5701 cmin 1 cmax 3
NOTICE: i 1 ctid (0,44) xmin 5702 xmax 0 cmin 2 cmax 0
NOTICE: i 2 ctid (0,45) xmin 5702 xmax 0 cmin 4 cmax 0
NOTICE: Error occurred while executing PL/pgSQL function myftest
NOTICE: line 10 at select into variables
ERROR: More than one tuple returned by a subselect used as an expression.
regression=#
The second backend finds that it wants to update the same row backend 1
did, so it waits to see if 1 commits. After the commit, it decides it
can do the update. Now, what will we see later in that same
transaction? SELECT will consider the original row (ctid 40, here)
to be still good --- it was deleted, sure enough, but by a transaction
that has not committed as far as the current transaction is concerned.
And the row inserted earlier in our own transaction is good too. So
you see two rows with i=1. The only way to avoid this is to use
Serializable mode, which would mean that backend 2 would've gotten an
error on its UPDATE.
However, if you do the same experiment a second time in the same
backends, you get different results. This I think is a SPI bug:
SPI is doing CommandCounterIncrements at bizarre times, and in
particular you get fewer CommandCounterIncrements while planning
and executing a plpgsql function than you do while re-executing
an already-planned one. Not sure yet exactly how it should be
changed.
regards, tom lane