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

Commitfbbf680

Browse files
committed
Disallow non-default collation in ADD PRIMARY KEY/UNIQUE USING INDEX.
When creating a uniqueness constraint using a pre-existing index,we have always required that the index have the same properties you'dget if you just let a new index get built. However, when collationswere added, we forgot to add the index's collation to that check.It's hard to trip over this without intentionally trying to break it:you'd have to explicitly specify a different collation in CREATEINDEX, then convert it to a pkey or unique constraint. Still, if youdid that, pg_dump would emit a script that fails to reproduce theindex's collation. The main practical problem is that after apg_upgrade the index would be corrupt, because its actual physicalorder wouldn't match what pg_index says. A more theoretical issue,which is new as of v12, is that if you create the index with anondeterministic collation then it wouldn't be enforcing the normalnotion of uniqueness, causing the constraint to mean somethingdifferent from a normally-created constraint.To fix, just add collation to the conditions checked for indexacceptability in ADD PRIMARY KEY/UNIQUE USING INDEX. We won't tryto clean up after anybody who's already created such a situation;it seems improbable enough to not be worth the effort involved.(If you do get into trouble, a REINDEX should be enough to fix it.)In principle this is a long-standing bug, but I chose not toback-patch --- the odds of causing trouble seem about as greatas the odds of preventing it, and both risks are very low anyway.Per report from Alexey Bashtanov, though this is not his preferredfix.Discussion:https://postgr.es/m/b05ce36a-cefb-ca5e-b386-a400535b1c0b@imap.cc
1 parent7d0bcb0 commitfbbf680

File tree

3 files changed

+26
-5
lines changed

3 files changed

+26
-5
lines changed

‎src/backend/parser/parse_utilcmd.c

Lines changed: 7 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -2147,15 +2147,17 @@ transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt)
21472147
if (i<index_form->indnkeyatts)
21482148
{
21492149
/*
2150-
* Insist on default opclass and sort options. While the
2151-
* index would still work as a constraint with non-default
2152-
* settings, it might not provide exactly the same uniqueness
2153-
* semantics as you'd get from a normally-created constraint;
2154-
* and there's also the dump/reload problem mentioned above.
2150+
* Insist on default opclass, collation, and sort options.
2151+
* While the index would still work as a constraint with
2152+
* non-default settings, it might not provide exactly the same
2153+
* uniqueness semantics as you'd get from a normally-created
2154+
* constraint; and there's also the dump/reload problem
2155+
* mentioned above.
21552156
*/
21562157
defopclass=GetDefaultOpClass(attform->atttypid,
21572158
index_rel->rd_rel->relam);
21582159
if (indclass->values[i]!=defopclass||
2160+
attform->attcollation!=index_rel->rd_indcollation[i]||
21592161
index_rel->rd_indoption[i]!=0)
21602162
ereport(ERROR,
21612163
(errcode(ERRCODE_WRONG_OBJECT_TYPE),

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

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1479,6 +1479,19 @@ primary key, btree, for table "public.cwi_test"
14791479
DROP INDEX cwi_replaced_pkey;-- Should fail; a constraint depends on it
14801480
ERROR: cannot drop index cwi_replaced_pkey because constraint cwi_replaced_pkey on table cwi_test requires it
14811481
HINT: You can drop constraint cwi_replaced_pkey on table cwi_test instead.
1482+
-- Check that non-default index options are rejected
1483+
CREATE UNIQUE INDEX cwi_uniq3_idx ON cwi_test(a desc);
1484+
ALTER TABLE cwi_test ADD UNIQUE USING INDEX cwi_uniq3_idx; -- fail
1485+
ERROR: index "cwi_uniq3_idx" column number 1 does not have default sorting behavior
1486+
LINE 1: ALTER TABLE cwi_test ADD UNIQUE USING INDEX cwi_uniq3_idx;
1487+
^
1488+
DETAIL: Cannot create a primary key or unique constraint using such an index.
1489+
CREATE UNIQUE INDEX cwi_uniq4_idx ON cwi_test(b collate "POSIX");
1490+
ALTER TABLE cwi_test ADD UNIQUE USING INDEX cwi_uniq4_idx; -- fail
1491+
ERROR: index "cwi_uniq4_idx" column number 1 does not have default sorting behavior
1492+
LINE 1: ALTER TABLE cwi_test ADD UNIQUE USING INDEX cwi_uniq4_idx;
1493+
^
1494+
DETAIL: Cannot create a primary key or unique constraint using such an index.
14821495
DROP TABLE cwi_test;
14831496
-- ADD CONSTRAINT USING INDEX is forbidden on partitioned tables
14841497
CREATE TABLE cwi_test(a int) PARTITION BY hash (a);

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

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -538,6 +538,12 @@ ALTER TABLE cwi_test DROP CONSTRAINT cwi_uniq_idx,
538538

539539
DROPINDEX cwi_replaced_pkey;-- Should fail; a constraint depends on it
540540

541+
-- Check that non-default index options are rejected
542+
CREATEUNIQUE INDEXcwi_uniq3_idxON cwi_test(adesc);
543+
ALTERTABLE cwi_test ADD UNIQUE USING INDEX cwi_uniq3_idx;-- fail
544+
CREATEUNIQUE INDEXcwi_uniq4_idxON cwi_test(b collate"POSIX");
545+
ALTERTABLE cwi_test ADD UNIQUE USING INDEX cwi_uniq4_idx;-- fail
546+
541547
DROPTABLE cwi_test;
542548

543549
-- ADD CONSTRAINT USING INDEX is forbidden on partitioned tables

0 commit comments

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