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

Commit3419d51

Browse files
committed
Fix check_exclusion_or_unique_constraint for UNIQUE NULLS NOT DISTINCT.
Adjusting this function was overlooked in commit94aa7cc. The onlyvisible symptom (so far) is that INSERT ... ON CONFLICT could go intoan endless loop when inserting a null that has a conflict.Richard Guo and Tom Lane, per bug #17558 from Andrew KesperDiscussion:https://postgr.es/m/17558-3f6599ffcf52fd4a@postgresql.org
1 parent4c7b163 commit3419d51

File tree

3 files changed

+18
-10
lines changed

3 files changed

+18
-10
lines changed

‎src/backend/executor/execIndexing.c

Lines changed: 12 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -699,13 +699,19 @@ check_exclusion_or_unique_constraint(Relation heap, Relation index,
699699
}
700700

701701
/*
702-
* If any of the input values are NULL, the constraint check is assumed to
703-
* pass (i.e., we assume the operators are strict).
702+
* If any of the input values are NULL, and the index uses the default
703+
* nulls-are-distinct mode, the constraint check is assumed to pass (i.e.,
704+
* we assume the operators are strict). Otherwise, we interpret the
705+
* constraint as specifying IS NULL for each column whose input value is
706+
* NULL.
704707
*/
705-
for (i=0;i<indnkeyatts;i++)
708+
if (!indexInfo->ii_NullsNotDistinct)
706709
{
707-
if (isnull[i])
708-
return true;
710+
for (i=0;i<indnkeyatts;i++)
711+
{
712+
if (isnull[i])
713+
return true;
714+
}
709715
}
710716

711717
/*
@@ -717,7 +723,7 @@ check_exclusion_or_unique_constraint(Relation heap, Relation index,
717723
for (i=0;i<indnkeyatts;i++)
718724
{
719725
ScanKeyEntryInitialize(&scankeys[i],
720-
0,
726+
isnull[i] ?SK_ISNULL |SK_SEARCHNULL :0,
721727
i+1,
722728
constr_strats[i],
723729
InvalidOid,

‎src/test/regress/expected/constraints.out

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -449,15 +449,16 @@ DROP TABLE UNIQUE_TBL;
449449
CREATE TABLE UNIQUE_TBL (i int UNIQUE NULLS NOT DISTINCT, t text);
450450
INSERT INTO UNIQUE_TBL VALUES (1, 'one');
451451
INSERT INTO UNIQUE_TBL VALUES (2, 'two');
452-
INSERT INTO UNIQUE_TBL VALUES (1, 'three');
452+
INSERT INTO UNIQUE_TBL VALUES (1, 'three'); -- fail
453453
ERROR: duplicate key value violates unique constraint "unique_tbl_i_key"
454454
DETAIL: Key (i)=(1) already exists.
455455
INSERT INTO UNIQUE_TBL VALUES (4, 'four');
456456
INSERT INTO UNIQUE_TBL VALUES (5, 'one');
457457
INSERT INTO UNIQUE_TBL (t) VALUES ('six');
458-
INSERT INTO UNIQUE_TBL (t) VALUES ('seven');
458+
INSERT INTO UNIQUE_TBL (t) VALUES ('seven'); -- fail
459459
ERROR: duplicate key value violates unique constraint "unique_tbl_i_key"
460460
DETAIL: Key (i)=(null) already exists.
461+
INSERT INTO UNIQUE_TBL (t) VALUES ('eight') ON CONFLICT DO NOTHING; -- no-op
461462
SELECT * FROM UNIQUE_TBL;
462463
i | t
463464
---+------

‎src/test/regress/sql/constraints.sql

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -310,11 +310,12 @@ CREATE TABLE UNIQUE_TBL (i int UNIQUE NULLS NOT DISTINCT, t text);
310310

311311
INSERT INTO UNIQUE_TBLVALUES (1,'one');
312312
INSERT INTO UNIQUE_TBLVALUES (2,'two');
313-
INSERT INTO UNIQUE_TBLVALUES (1,'three');
313+
INSERT INTO UNIQUE_TBLVALUES (1,'three');-- fail
314314
INSERT INTO UNIQUE_TBLVALUES (4,'four');
315315
INSERT INTO UNIQUE_TBLVALUES (5,'one');
316316
INSERT INTO UNIQUE_TBL (t)VALUES ('six');
317-
INSERT INTO UNIQUE_TBL (t)VALUES ('seven');
317+
INSERT INTO UNIQUE_TBL (t)VALUES ('seven');-- fail
318+
INSERT INTO UNIQUE_TBL (t)VALUES ('eight')ON CONFLICT DO NOTHING;-- no-op
318319

319320
SELECT*FROM UNIQUE_TBL;
320321

0 commit comments

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