Re: BUG #13723: "duplicate key" error occurs when update delete and insert the same row concurrently

Hi,Jeff

> That is true only if the UPDATE actually updates the row. If you log the query completion tags, I think you will
> find the error is preceded by an UPDATE of zero rows. Now you could argue that this itself is a violation:
> that if a single statement sees that a row was deleted it is obliged to also see the row that was inserted
> in the same transaction as the deletion. But this is documented, and is unlikely to change.

My test result is just as you said, as the following:

test9.sql:
---------------------------------------
begin;
select * from tb1 where id=1;
update tb1 set c=2 where id=1;
delete from tb1 where id=1;
insert into tb1 values(1,2);
commit;
begin;
select * from tb1 where id=1;
update tb1 set c=2 where id=1;
delete from tb1 where id=1;
insert into tb1 values(1,2);
commit;
...
---------------------------------------

[postgres(at)localhost ~]$ psql -f test9.sql >a1.log 2>&1 &
[postgres(at)localhost ~]$ psql -f test9.sql >a2.log 2>&1 &

a2.log:
---------------------------------------
...
BEGIN
id | c
----+---
1 | 2
(1 row)

UPDATE 0
DELETE 0
psql:test9.sql:17: ERROR: duplicate key value violates unique constraint "tb1_pkey"
DETAIL: Key (id)=(1) already exists.
ROLLBACK
...
---------------------------------------

Even "EXPLICITLY" add "select ... for update" at first, "duplicate key " error still ocurrs.

test10.sql:
---------------------------------------
begin;
select * from tb1 where id=1 for update;
update tb1 set c=2 where id=1;
delete from tb1 where id=1;
insert into tb1 values(1,2);
commit;
begin;
select * from tb1 where id=1 for update;
update tb1 set c=2 where id=1;
delete from tb1 where id=1;
insert into tb1 values(1,2);
commit;
...
---------------------------------------

[postgres(at)localhost ~]$ psql -f test10.sql >b1.log 2>&1 &
[postgres(at)localhost ~]$ psql -f test10.sql >b2.log 2>&1 &

b1.log:
---------------------------------------
...
BEGIN
id | c
----+---
(0 rows)

UPDATE 0
DELETE 0
psql:test10.sql:29: ERROR: duplicate key value violates unique constraint "tb1_pkey"
DETAIL: Key (id)=(1) already exists.
ROLLBACK
...
---------------------------------------

So, "select * from tb1 where id=1 for update" also could see an inconsistent snapshot (sort of "Dirty Read").

Best Regard,
Chen Huajun