> 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:
I don't think so. Seems the problem is specific to PL/pgSQL (or SPI?).
> 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.
If your theory is like that, I could see same effect without using
PL/pgSQL. But I see following in a session using psql (original row's
ctid = (0,2))
[T1] begin;
[T2] begin;
[T1] update t1 set i = 1 where i = 1;
[T2] update t1 set i = 1 where i = 1; <-- waiting for T1 committed/aborted
[T1] end;
[T2] select ctid, i from t1;
test=# select ctid,i from t1;ctid | i
-------+---(0,4) | 1
(1 row)
So I only see one row from the last select in T2?
--
Tatsuo Ishii