8000 Fix incorrect checking of deferred exclusion constraint after a HOT u… · lansz/postgres@46f9acd · GitHub
[go: up one dir, main page]
Skip to content

Commit 46f9acd

Browse files
tglsfdctglsfdc
committed
Fix incorrect checking of deferred exclusion constraint after a HOT update.
If a row that potentially violates a deferred exclusion constraint is HOT-updated later in the same transaction, the exclusion constraint would be reported as violated when the check finally occurs, even if the row(s) the new row originally conflicted with have since been removed. This happened because the wrong TID was passed to check_exclusion_constraint(), causing the live HOT-updated row to be seen as a conflicting row rather than recognized as the row-under-test. Per bug #13148 from Evan Martin. It's been broken since exclusion constraints were invented, so back-patch to all supported branches.
1 parent 21cb21d commit 46f9acd

File tree

3 files changed

+35
-6
lines changed

3 files changed

+35
-6
lines changed

‎src/backend/commands/constraint.c

Lines changed: 11 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -89,9 +89,10 @@ unique_key_recheck(PG_FUNCTION_ARGS)
8989
* because this trigger gets queued only in response to index insertions;
9090 8000
* which means it does not get queued for HOT updates. The row we are
9191
* called for might now be dead, but have a live HOT child, in which case
92-
* we still need to make the check. Therefore we have to use
93-
* heap_hot_search, not just HeapTupleSatisfiesVisibility as is done in
94-
* the comparable test in RI_FKey_check.
92+
* we still need to make the check --- effectively, we're applying the
93+
* check against the live child row, although we can use the values from
94+
* this row since by definition all columns of interest to us are the
95+
* same.
9596
*
9697
* This might look like just an optimization, because the index AM will
9798
* make this identical test before throwing an error. But it's actually
@@ -159,7 +160,9 @@ unique_key_recheck(PG_FUNCTION_ARGS)
159160
{
160161
/*
161162
* Note: this is not a real insert; it is a check that the index entry
162-
* that has already been inserted is unique.
163+
* that has already been inserted is unique. Passing t_self is
164+
* correct even if t_self is now dead, because that is the TID the
165+
* index will know about.
163166
*/
164167
index_insert(indexRel, values, isnull, &(new_row->t_self),
165168
trigdata->tg_relation, UNIQUE_CHECK_EXISTING);
@@ -168,10 +171,12 @@ unique_key_recheck(PG_FUNCTION_ARGS)
168171
{
169172
/*
170173
* For exclusion constraints we just do the normal check, but now it's
171-
* okay to throw error.
174+
* okay to throw error. In the HOT-update case, we must use the live
175+
* HOT child's TID here, else check_exclusion_constraint will think
176+
* the child is a conflict.
172177
*/
173178
check_exclusion_constraint(trigdata->tg_relation, indexRel, indexInfo,
174-
&(new_row->t_self), values, isnull,
179+
&tmptid, values, isnull,
175180
estate, false, false);
176181
}
177182

‎src/test/regress/input/constraints.source

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -434,6 +434,7 @@ DROP TABLE circles;
434434

435435
CREATE TABLE deferred_excl (
436436
f1 int,
437+
f2 int,
437438
CONSTRAINT deferred_excl_con EXCLUDE (f1 WITH =) INITIALLY DEFERRED
438439
);
439440

@@ -448,6 +449,15 @@ INSERT INTO deferred_excl VALUES(3);
448449
INSERT INTO deferred_excl VALUES(3); -- no fail here
449450
COMMIT; -- should fail here
450451

452+
-- bug #13148: deferred constraint versus HOT update
453+
BEGIN;
454+
INSERT INTO deferred_excl VALUES(2, 1); -- no fail here
455+
DELETE FROM deferred_excl WHERE f1 = 2 AND f2 IS NULL; -- remove old row
456+
UPDATE deferred_excl SET f2 = 2 WHERE f1 = 2;
457+
COMMIT; -- should not fail
458+
459+
SELECT * FROM deferred_excl;
460+
451461
ALTER TABLE deferred_excl DROP CONSTRAINT deferred_excl_con;
452462

453463
-- This should fail, but worth testing because of HOT updates

‎src/test/regress/output/constraints.source

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -604,6 +604,7 @@ DROP TABLE circles;
604604
-- Check deferred exclusion constraint
605605
CREATE TABLE deferred_excl (
606606
f1 int,
607+
f2 int,
607608
CONSTRAINT deferred_excl_con EXCLUDE (f1 WITH =) INITIALLY DEFERRED
608609
);
609610
NOTICE: CREATE TABLE / EXCLUDE will create implicit index "deferred_excl_con" for table "deferred_excl"
@@ -623,6 +624,19 @@ INSERT INTO deferred_excl VALUES(3); -- no fail here
623624
COMMIT; -- should fail here
624625
ERROR: conflicting key value violates exclusion constraint "deferred_excl_con"
625626
DETAIL: Key (f1)=(3) conflicts with existing key (f1)=(3).
627+
-- bug #13148: deferred constraint versus HOT update
628+
BEGIN;
629+
INSERT INTO deferred_excl VALUES(2, 1); -- no fail here
630+
DELETE FROM deferred_excl WHERE f1 = 2 AND f2 IS NULL; -- remove old row
631+
UPDATE deferred_excl SET f2 = 2 WHERE f1 = 2;
632+
COMMIT; -- should not fail
633+
SELECT * FROM deferred_excl;
634+
f1 | f2
635+
----+----
636+
1 |
637+
2 | 2
638+
(2 rows)
639+
626640
ALTER TABLE deferred_excl DROP CONSTRAINT deferred_excl_con;
627641
-- This should fail, but worth testing because of HOT updates
628642
UPDATE deferred_excl SET f1 = 3;

0 commit comments

Comments
 (0)
0