SELECT FOR UPDATE breaks unique constraint - Mailing list pgsql-hackers

From Hiroshi Inoue
Subject SELECT FOR UPDATE breaks unique constraint
Date
Msg-id 000a01bfdf04$f841fa80$2801007e@tpf.co.jp
Whole thread Raw
List pgsql-hackers
Hi all,
The following phenomenon has just been reported by
Mikage Sawartari in Japan.

mikage=# CREATE TABLE test (id INTEGER);
CREATE
mikage=# CREATE UNIQUE INDEX test_id_ub ON test (id);
CREATE
mikage=# INSERT INTO test VALUES (1);
INSERT 18828 1
mikage=# INSERT INTO test VALUES (1);
ERROR:  Cannot insert a duplicate key into unique index test_id_ub
mikage=# begin;
BEGIN
mikage=# SELECT * FROM test FOR UPDATE;id
---- 1
(1 row)

mikage=# INSERT INTO test VALUES (1);
INSERT 18831 1
mikage=# commit;
COMMIT
mikage=# SELECT * FROM test;id
---- 1 1
(2 rows)


HeapTupleSatisfiesDirty() seems to neglect the check about HEAP_MARKED_
FOR_UPDATE in a place. After applying the following patch,unique constraint
works well in my environment,

Comments ?

Index: utils/time/tqual.c
===================================================================
RCS file: /home/cvs/pgcurrent/src/backend/utils/time/tqual.c,v
retrieving revision 1.5
diff -c -r1.5 tqual.c
*** utils/time/tqual.c    2000/01/26 09:59:05    1.5
--- utils/time/tqual.c    2000/06/26 00:13:01
***************
*** 441,447 ****
--- 441,451 ----     }      if (TransactionIdIsCurrentTransactionId(tuple->t_xmax))
+     {
+         if (tuple->t_infomask & HEAP_MARKED_FOR_UPDATE)
+             return true;         return false;
+     }      if (!TransactionIdDidCommit(tuple->t_xmax))     {

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp


pgsql-hackers by date:

Previous
From: "Oliver Elphick"
Date:
Subject: TRUNCATE violates Referential Integrity
Next
From: Peter Eisentraut
Date:
Subject: .exe extension on Windows