should check collations when creating partitioned index

Peter Eisentraut <peter@eisentraut.org>

From: Peter Eisentraut <peter@eisentraut.org>
To: pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2023-11-13T09:24:03Z
Lists: pgsql-hackers

Attachments

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.

Commits

  1. Check collation when creating partitioned index