| From: | Peter Eisentraut <peter(at)eisentraut(dot)org> |
|---|---|
| To: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
| Subject: | should check collations when creating partitioned index |
| Date: | 2023-11-13 09:24:03 |
| Message-ID: | 3327cb54-f7f1-413b-8fdb-7a9dceebb938@eisentraut.org |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
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.
| Attachment | Content-Type | Size |
|---|---|---|
| 0001-Check-collation-when-creating-partitioned-index.patch | text/plain | 1.3 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Drouvot, Bertrand | 2023-11-13 09:53:17 | Re: Synchronizing slots from primary to standby |
| Previous Message | yuansong | 2023-11-13 09:13:20 | Re:Re: How to solve the problem of one backend process crashing and causing other processes to restart? |