Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitb93c8ea

Browse files
committed
Fix incorrect checking of deferred exclusion constraint after a HOT update.
If a row that potentially violates a deferred exclusion constraint isHOT-updated later in the same transaction, the exclusion constraint wouldbe reported as violated when the check finally occurs, even if the row(s)the new row originally conflicted with have since been removed. Thishappened because the wrong TID was passed to check_exclusion_constraint(),causing the live HOT-updated row to be seen as a conflicting row ratherthan recognized as the row-under-test.Per bug #13148 from Evan Martin. It's been broken since exclusionconstraints were invented, so back-patch to all supported branches.
1 parentc981e59 commitb93c8ea

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
@@ -88,9 +88,10 @@ unique_key_recheck(PG_FUNCTION_ARGS)
8888
* because this trigger gets queued only in response to index insertions;
8989
* which means it does not get queued for HOT updates. The row we are
9090
* called for might now be dead, but have a live HOT child, in which case
91-
* we still need to make the check. Therefore we have to use
92-
* heap_hot_search, not just HeapTupleSatisfiesVisibility as is done in
93-
* the comparable test in RI_FKey_check.
91+
* we still need to make the check --- effectively, we're applying the
92+
* check against the live child row, although we can use the values from
93+
* this row since by definition all columns of interest to us are the
94+
* same.
9495
*
9596
* This might look like just an optimization, because the index AM will
9697
* make this identical test before throwing an error. But it's actually
@@ -158,7 +159,9 @@ unique_key_recheck(PG_FUNCTION_ARGS)
158159
{
159160
/*
160161
* Note: this is not a real insert; it is a check that the index entry
161-
* that has already been inserted is unique.
162+
* that has already been inserted is unique. Passing t_self is
163+
* correct even if t_self is now dead, because that is the TID the
164+
* index will know about.
162165
*/
163166
index_insert(indexRel,values,isnull,&(new_row->t_self),
164167
trigdata->tg_relation,UNIQUE_CHECK_EXISTING);
@@ -167,10 +170,12 @@ unique_key_recheck(PG_FUNCTION_ARGS)
167170
{
168171
/*
169172
* For exclusion constraints we just do the normal check, but now it's
170-
* okay to throw error.
173+
* okay to throw error. In the HOT-update case, we must use the live
174+
* HOT child's TID here, else check_exclusion_constraint will think
175+
* the child is a conflict.
171176
*/
172177
check_exclusion_constraint(trigdata->tg_relation,indexRel,indexInfo,
173-
&(new_row->t_self),values,isnull,
178+
&tmptid,values,isnull,
174179
estate, false, false);
175180
}
176181

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

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

404404
CREATE TABLE deferred_excl (
405405
f1 int,
406+
f2 int,
406407
CONSTRAINT deferred_excl_con EXCLUDE (f1 WITH =) INITIALLY DEFERRED
407408
);
408409

@@ -417,6 +418,15 @@ INSERT INTO deferred_excl VALUES(3);
417418
INSERT INTO deferred_excl VALUES(3); -- no fail here
418419
COMMIT; -- should fail here
419420

421+
-- bug #13148: deferred constraint versus HOT update
422+
BEGIN;
423+
INSERT INTO deferred_excl VALUES(2, 1); -- no fail here
424+
DELETE FROM deferred_excl WHERE f1 = 2 AND f2 IS NULL; -- remove old row
425+
UPDATE deferred_excl SET f2 = 2 WHERE f1 = 2;
426+
COMMIT; -- should not fail
427+
428+
SELECT * FROM deferred_excl;
429+
420430
ALTER TABLE deferred_excl DROP CONSTRAINT deferred_excl_con;
421431

422432
-- 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
@@ -547,6 +547,7 @@ DROP TABLE circles;
547547
-- Check deferred exclusion constraint
548548
CREATE TABLE deferred_excl (
549549
f1 int,
550+
f2 int,
550551
CONSTRAINT deferred_excl_con EXCLUDE (f1 WITH =) INITIALLY DEFERRED
551552
);
552553
NOTICE: CREATE TABLE / EXCLUDE will create implicit index "deferred_excl_con" for table "deferred_excl"
@@ -566,6 +567,19 @@ INSERT INTO deferred_excl VALUES(3); -- no fail here
566567
COMMIT; -- should fail here
567568
ERROR: conflicting key value violates exclusion constraint "deferred_excl_con"
568569
DETAIL: Key (f1)=(3) conflicts with existing key (f1)=(3).
570+
-- bug #13148: deferred constraint versus HOT update
571+
BEGIN;
572+
INSERT INTO deferred_excl VALUES(2, 1); -- no fail here
573+
DELETE FROM deferred_excl WHERE f1 = 2 AND f2 IS NULL; -- remove old row
574+
UPDATE deferred_excl SET f2 = 2 WHERE f1 = 2;
575+
COMMIT; -- should not fail
576+
SELECT * FROM deferred_excl;
577+
f1 | f2
578+
----+----
579+
1 |
580+
2 | 2
581+
(2 rows)
582+
569583
ALTER TABLE deferred_excl DROP CONSTRAINT deferred_excl_con;
570584
-- This should fail, but worth testing because of HOT updates
571585
UPDATE deferred_excl SET f1 = 3;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp