Actually cutting down my mail to something more readable..
Lets consider two transactions
BEGIN; BEGIN;
DELETE FROM sbtest WHERE id=500815;
INSERT INTO sbtest
values(500815,0,'','aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy'); DELETE FROM sbtest WHERE id=500815;
< ------- hangs/waits
END;
COMMIT DELETE 0 – returns success but doesn’t delete any rows . It
doesn't roll back the transaction INSERT INTO sbtest
values(500815,0,'','aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy'); ERROR: duplicate key value violates unique
constraint"sbtest_pkey" END; ROLLBACK
The question is should the delete fail if it doesn't exist and cause a
rollback or succeed with DELETE 0 ?
The other way I tried is I tried doing the DELETE before insert in the
first one and same results.. So irrespective of how it does things if
DELETE starts before the first transaction commits, then the answer is
"ERROR: duplicate key"
What happening is while DELETE tries to delete the old row and INSERT
then tries to compete against the new row..
Not sure which way should be right in read-committed.
What’s the official READ-COMMITTED semantics for this?
Thanks.
Regards,
Jignesh