When creating a partitioned index, the partition key must be a subset of
the index's columns. DefineIndex() explains:
* If this table is partitioned and we're creating a unique index,
primary
* key, or exclusion constraint, make sure that the partition key is a
* subset of the index's columns. Otherwise it would be possible to
* violate uniqueness by putting values that ought to be unique in
* different partitions.
But this currently doesn't check that the collations between the
partition key and the index definition match. So you can construct a
unique index that fails to enforce uniqueness.
Here is a non-partitioned case for reference:
create collation case_insensitive (provider=icu,
locale='und-u-ks-level2', deterministic=false);
create table t0 (a int, b text);
create unique index i0 on t0 (b collate case_insensitive);
insert into t0 values (1, 'a'), (2, 'A'); -- violates unique constraint
Here is a partitioned case that doesn't work correctly:
create table t1 (a int, b text) partition by hash (b);
create table t1a partition of t1 for values with (modulus 2, remainder 0);
create table t1b partition of t1 for values with (modulus 2, remainder 1);
create unique index i1 on t1 (b collate case_insensitive);
insert into t1 values (1, 'a'), (2, 'A'); -- this succeeds
The attached patch adds the required collation check. In the example,
it would not allow the index i1 to be created.From 3acd59dcd7dffcfd22e61cce3ac1bb4c6982d16d Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@eisentraut.org>
Date: Mon, 13 Nov 2023 10:15:23 +0100
Subject: [PATCH] Check collation when creating partitioned index
---
src/backend/commands/indexcmds.c | 12 +++++++++++-
1 file changed, 11 insertions(+), 1 deletion(-)
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index c160d8a301..729f200395 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -1011,10 +1011,20 @@ DefineIndex(Oid tableId,
{
if (key->partattrs[i] ==
indexInfo->ii_IndexAttrNumbers[j])
{
- /* Matched the column, now what about
the equality op? */
+ /* Matched the column, now what about
the collation and equality op? */
Oid idx_opfamily;
Oid idx_opcintype;
+ if (key->partcollation[i] !=
collationIds[j])
+ {
+ Form_pg_attribute att =
TupleDescAttr(RelationGetDescr(rel), key->partattrs[i] - 1);
+
+ ereport(ERROR,
+
errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+
errmsg("collation of column \"%s\" does not match between partition key and
index definition",
+
NameStr(att->attname)));
+ }
+
if
(get_opclass_opfamily_and_input_type(opclassIds[j],
&idx_opfamily,
&idx_opcintype))
--
2.42.1
