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

Commitdd75518

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 parentedfef09 commitdd75518

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
@@ -406,6 +406,7 @@ DROP TABLE circles;
406406

407407
CREATE TABLE deferred_excl (
408408
f1 int,
409+
f2 int,
409410
CONSTRAINT deferred_excl_con EXCLUDE (f1 WITH =) INITIALLY DEFERRED
410411
);
411412

@@ -420,6 +421,15 @@ INSERT INTO deferred_excl VALUES(3);
420421
INSERT INTO deferred_excl VALUES(3); -- no fail here
421422
COMMIT; -- should fail here
422423

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

425435
-- 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
@@ -549,6 +549,7 @@ DROP TABLE circles;
549549
-- Check deferred exclusion constraint
550550
CREATE TABLE deferred_excl (
551551
f1 int,
552+
f2 int,
552553
CONSTRAINT deferred_excl_con EXCLUDE (f1 WITH =) INITIALLY DEFERRED
553554
);
554555
NOTICE: CREATE TABLE / EXCLUDE will create implicit index "deferred_excl_con" for table "deferred_excl"
@@ -568,6 +569,19 @@ INSERT INTO deferred_excl VALUES(3); -- no fail here
568569
COMMIT; -- should fail here
569570
ERROR: conflicting key value violates exclusion constraint "deferred_excl_con"
570571
DETAIL: Key (f1)=(3) conflicts with existing key (f1)=(3).
572+
-- bug #13148: deferred constraint versus HOT update
573+
BEGIN;
574+
INSERT INTO deferred_excl VALUES(2, 1); -- no fail here
575+
DELETE FROM deferred_excl WHERE f1 = 2 AND f2 IS NULL; -- remove old row
576+
UPDATE deferred_excl SET f2 = 2 WHERE f1 = 2;
577+
COMMIT; -- should not fail
578+
SELECT * FROM deferred_excl;
579+
f1 | f2
580+
----+----
581+
1 |
582+
2 | 2
583+
(2 rows)
584+
571585
ALTER TABLE deferred_excl DROP CONSTRAINT deferred_excl_con;
572586
-- This should fail, but worth testing because of HOT updates
573587
UPDATE deferred_excl SET f1 = 3;

0 commit comments

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