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

Commitd959523

Browse files
Disallow NULLS NOT DISTINCT indexes for primary keys
A unique index which is created with non-distinct NULLS cannot beused for backing a primary key constraint. Make sure to disallowsuch table alterations and teach pg_dump to drop the non-distinctNULLS clause on indexes where this has been set.Bug: 17720Reported-by: Reiner Peterke <zedaardv@drizzle.com>Reviewed-by: Peter Eisentraut <peter.eisentraut@enterprisedb.com>Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us>Discussion:https://postgr.es/m/17720-dab8ee0fa85d316d@postgresql.org
1 parent94851e4 commitd959523

File tree

4 files changed

+31
-1
lines changed

4 files changed

+31
-1
lines changed

‎src/backend/catalog/index.c

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -225,6 +225,19 @@ index_check_primary_key(Relation heapRel,
225225
RelationGetRelationName(heapRel))));
226226
}
227227

228+
/*
229+
* Indexes created with NULLS NOT DISTINCT cannot be used for primary key
230+
* constraints. While there is no direct syntax to reach here, it can be
231+
* done by creating a separate index and attaching it via ALTER TABLE ..
232+
* USING INDEX.
233+
*/
234+
if (indexInfo->ii_NullsNotDistinct)
235+
{
236+
ereport(ERROR,
237+
(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
238+
errmsg("primary keys cannot use NULLS NOT DISTINCT indexes")));
239+
}
240+
228241
/*
229242
* Check that all of the attributes in a primary key are marked as not
230243
* null. (We don't really expect to see that; it'd mean the parser messed

‎src/bin/pg_dump/pg_dump.c

Lines changed: 6 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -16431,7 +16431,12 @@ dumpConstraint(Archive *fout, const ConstraintInfo *coninfo)
1643116431
{
1643216432
appendPQExpBufferStr(q,
1643316433
coninfo->contype == 'p' ? "PRIMARY KEY" : "UNIQUE");
16434-
if (indxinfo->indnullsnotdistinct)
16434+
/*
16435+
* PRIMARY KEY constraints should not be using NULLS NOT DISTINCT
16436+
* indexes. Being able to create this was fixed, but we need to
16437+
* make the index distinct in order to be able to restore the dump.
16438+
*/
16439+
if (indxinfo->indnullsnotdistinct && coninfo->contype != 'p')
1643516440
appendPQExpBufferStr(q, " NULLS NOT DISTINCT");
1643616441
appendPQExpBufferStr(q, " (");
1643716442
for (k = 0; k < indxinfo->indnkeyattrs; k++)

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

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1595,6 +1595,12 @@ create unique index on cwi_test (a);
15951595
alter table cwi_test add primary key using index cwi_test_a_idx ;
15961596
ERROR: ALTER TABLE / ADD CONSTRAINT USING INDEX is not supported on partitioned tables
15971597
DROP TABLE cwi_test;
1598+
-- PRIMARY KEY constraint cannot be backed by a NULLS NOT DISTINCT index
1599+
CREATE TABLE cwi_test(a int, b int);
1600+
CREATE UNIQUE INDEX cwi_a_nnd ON cwi_test (a) NULLS NOT DISTINCT;
1601+
ALTER TABLE cwi_test ADD PRIMARY KEY USING INDEX cwi_a_nnd;
1602+
ERROR: primary keys cannot use NULLS NOT DISTINCT indexes
1603+
DROP TABLE cwi_test;
15981604
--
15991605
-- Check handling of indexes on system columns
16001606
--

‎src/test/regress/sql/create_index.sql

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -617,6 +617,12 @@ create unique index on cwi_test (a);
617617
altertable cwi_test addprimary key using index cwi_test_a_idx ;
618618
DROPTABLE cwi_test;
619619

620+
-- PRIMARY KEY constraint cannot be backed by a NULLS NOT DISTINCT index
621+
CREATETABLEcwi_test(aint, bint);
622+
CREATEUNIQUE INDEXcwi_a_nndON cwi_test (a) NULLS NOT DISTINCT;
623+
ALTERTABLE cwi_test ADDPRIMARY KEY USING INDEX cwi_a_nnd;
624+
DROPTABLE cwi_test;
625+
620626
--
621627
-- Check handling of indexes on system columns
622628
--

0 commit comments

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