SELECT FOR UPDATE breaks unique constraint

Started by Hiroshi Inoueover 25 years ago1 messages
#1Hiroshi Inoue
Inoue@tpf.co.jp

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