Re: pg_dump losing index column collations for unique and primary keys
Tom Lane <[email protected]>
| Newsgroups | gmane.comp.db.postgresql.bugs |
|---|---|
| Message-ID | <[email protected]> |
I wrote: > In short, I'd say the bug here is not pg_dump's fault at all, > but failure to insist on collation match in ADD PRIMARY KEY > USING INDEX. Concretely, I think we should do the attached. I'm not quite sure whether we should back-patch this, though. It's been wrong since we added collations, but the main impact of a back-patch might be to break cases that were working more or less okay for people. A compromise idea is to back-patch only into v12, where the issue became quite a lot more important due to nondeterministic collations. Thoughts? regards, tom lane
disallow-collation-mismatch-in-ADD-USING-INDEX.patch
(text/x-diff, 3.6 KB)
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index ee47547..b761fdf 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -2147,15 +2147,17 @@ transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt)
if (i < index_form->indnkeyatts)
{
/*
- * Insist on default opclass and sort options. While the
- * index would still work as a constraint with non-default
- * settings, it might not provide exactly the same uniqueness
- * semantics as you'd get from a normally-created constraint;
- * and there's also the dump/reload problem mentioned above.
+ * Insist on default opclass, collation, and sort options.
+ * While the index would still work as a constraint with
+ * non-default settings, it might not provide exactly the same
+ * uniqueness semantics as you'd get from a normally-created
+ * constraint; and there's also the dump/reload problem
+ * mentioned above.
*/
defopclass = GetDefaultOpClass(attform->atttypid,
index_rel->rd_rel->relam);
if (indclass->values[i] != defopclass ||
+ attform->attcollation != index_rel->rd_indcollation[i] ||
index_rel->rd_indoption[i] != 0)
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index 1cdb7a9..645ae2c 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -1479,6 +1479,19 @@ primary key, btree, for table "public.cwi_test"
DROP INDEX cwi_replaced_pkey; -- Should fail; a constraint depends on it
ERROR: cannot drop index cwi_replaced_pkey because constraint cwi_replaced_pkey on table cwi_test requires it
HINT: You can drop constraint cwi_replaced_pkey on table cwi_test instead.
+-- Check that non-default index options are rejected
+CREATE UNIQUE INDEX cwi_uniq3_idx ON cwi_test(a desc);
+ALTER TABLE cwi_test ADD UNIQUE USING INDEX cwi_uniq3_idx; -- fail
+ERROR: index "cwi_uniq3_idx" column number 1 does not have default sorting behavior
+LINE 1: ALTER TABLE cwi_test ADD UNIQUE USING INDEX cwi_uniq3_idx;
+ ^
+DETAIL: Cannot create a primary key or unique constraint using such an index.
+CREATE UNIQUE INDEX cwi_uniq4_idx ON cwi_test(b collate "POSIX");
+ALTER TABLE cwi_test ADD UNIQUE USING INDEX cwi_uniq4_idx; -- fail
+ERROR: index "cwi_uniq4_idx" column number 1 does not have default sorting behavior
+LINE 1: ALTER TABLE cwi_test ADD UNIQUE USING INDEX cwi_uniq4_idx;
+ ^
+DETAIL: Cannot create a primary key or unique constraint using such an index.
DROP TABLE cwi_test;
-- ADD CONSTRAINT USING INDEX is forbidden on partitioned tables
CREATE TABLE cwi_test(a int) PARTITION BY hash (a);
diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql
index 7659808..73a55ea 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -538,6 +538,12 @@ ALTER TABLE cwi_test DROP CONSTRAINT cwi_uniq_idx,
DROP INDEX cwi_replaced_pkey; -- Should fail; a constraint depends on it
+-- Check that non-default index options are rejected
+CREATE UNIQUE INDEX cwi_uniq3_idx ON cwi_test(a desc);
+ALTER TABLE cwi_test ADD UNIQUE USING INDEX cwi_uniq3_idx; -- fail
+CREATE UNIQUE INDEX cwi_uniq4_idx ON cwi_test(b collate "POSIX");
+ALTER TABLE cwi_test ADD UNIQUE USING INDEX cwi_uniq4_idx; -- fail
+
DROP TABLE cwi_test;
-- ADD CONSTRAINT USING INDEX is forbidden on partitioned tables