Feedback-ID: ie0a040ee:Fastmail
Content-Type: multipart/mixed; boundary="------------q9X2D6quAWtSgkjFNAXq3Rpm"
Message-ID: <3327cb54-f7f1-413b-8fdb-7a9dceebb938@eisentraut.org>
Date: Mon, 13 Nov 2023 10:24:03 +0100
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Content-Language: en-US
From: Peter Eisentraut <peter@eisentraut.org>
Subject: should check collations when creating partitioned index
To: pgsql-hackers <pgsql-hackers@postgresql.org>
Archived-At: <https://www.postgresql.org/message-id/3327cb54-f7f1-413b-8fdb-7a9dceebb938%40eisentraut.org>
Precedence: bulk

This is a multi-part message in MIME format.
--------------q9X2D6quAWtSgkjFNAXq3Rpm
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

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.
--------------q9X2D6quAWtSgkjFNAXq3Rpm
Content-Type: text/plain; charset=UTF-8;
 name="0001-Check-collation-when-creating-partitioned-index.patch"
Content-Disposition: attachment;
 filename*0="0001-Check-collation-when-creating-partitioned-index.patch"
Content-Transfer-Encoding: base64

RnJvbSAzYWNkNTlkY2Q3ZGZmY2ZkMjJlNjFjY2UzYWMxYmI0YzY5ODJkMTZkIE1vbiBTZXAg
MTcgMDA6MDA6MDAgMjAwMQpGcm9tOiBQZXRlciBFaXNlbnRyYXV0IDxwZXRlckBlaXNlbnRy
YXV0Lm9yZz4KRGF0ZTogTW9uLCAxMyBOb3YgMjAyMyAxMDoxNToyMyArMDEwMApTdWJqZWN0
OiBbUEFUQ0hdIENoZWNrIGNvbGxhdGlvbiB3aGVuIGNyZWF0aW5nIHBhcnRpdGlvbmVkIGlu
ZGV4CgotLS0KIHNyYy9iYWNrZW5kL2NvbW1hbmRzL2luZGV4Y21kcy5jIHwgMTIgKysrKysr
KysrKystCiAxIGZpbGUgY2hhbmdlZCwgMTEgaW5zZXJ0aW9ucygrKSwgMSBkZWxldGlvbigt
KQoKZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL2NvbW1hbmRzL2luZGV4Y21kcy5jIGIvc3Jj
L2JhY2tlbmQvY29tbWFuZHMvaW5kZXhjbWRzLmMKaW5kZXggYzE2MGQ4YTMwMS4uNzI5ZjIw
MDM5NSAxMDA2NDQKLS0tIGEvc3JjL2JhY2tlbmQvY29tbWFuZHMvaW5kZXhjbWRzLmMKKysr
IGIvc3JjL2JhY2tlbmQvY29tbWFuZHMvaW5kZXhjbWRzLmMKQEAgLTEwMTEsMTAgKzEwMTEs
MjAgQEAgRGVmaW5lSW5kZXgoT2lkIHRhYmxlSWQsCiAJCQl7CiAJCQkJaWYgKGtleS0+cGFy
dGF0dHJzW2ldID09IGluZGV4SW5mby0+aWlfSW5kZXhBdHRyTnVtYmVyc1tqXSkKIAkJCQl7
Ci0JCQkJCS8qIE1hdGNoZWQgdGhlIGNvbHVtbiwgbm93IHdoYXQgYWJvdXQgdGhlIGVxdWFs
aXR5IG9wPyAqLworCQkJCQkvKiBNYXRjaGVkIHRoZSBjb2x1bW4sIG5vdyB3aGF0IGFib3V0
IHRoZSBjb2xsYXRpb24gYW5kIGVxdWFsaXR5IG9wPyAqLwogCQkJCQlPaWQJCQlpZHhfb3Bm
YW1pbHk7CiAJCQkJCU9pZAkJCWlkeF9vcGNpbnR5cGU7CiAKKwkJCQkJaWYgKGtleS0+cGFy
dGNvbGxhdGlvbltpXSAhPSBjb2xsYXRpb25JZHNbal0pCisJCQkJCXsKKwkJCQkJCUZvcm1f
cGdfYXR0cmlidXRlIGF0dCA9IFR1cGxlRGVzY0F0dHIoUmVsYXRpb25HZXREZXNjcihyZWwp
LCBrZXktPnBhcnRhdHRyc1tpXSAtIDEpOworCisJCQkJCQllcmVwb3J0KEVSUk9SLAorCQkJ
CQkJCQllcnJjb2RlKEVSUkNPREVfRkVBVFVSRV9OT1RfU1VQUE9SVEVEKSwKKwkJCQkJCQkJ
ZXJybXNnKCJjb2xsYXRpb24gb2YgY29sdW1uIFwiJXNcIiBkb2VzIG5vdCBtYXRjaCBiZXR3
ZWVuIHBhcnRpdGlvbiBrZXkgYW5kIGluZGV4IGRlZmluaXRpb24iLAorCQkJCQkJCQkJICAg
TmFtZVN0cihhdHQtPmF0dG5hbWUpKSk7CisJCQkJCX0KKwogCQkJCQlpZiAoZ2V0X29wY2xh
c3Nfb3BmYW1pbHlfYW5kX2lucHV0X3R5cGUob3BjbGFzc0lkc1tqXSwKIAkJCQkJCQkJCQkJ
CQkJCSZpZHhfb3BmYW1pbHksCiAJCQkJCQkJCQkJCQkJCQkmaWR4X29wY2ludHlwZSkpCi0t
IAoyLjQyLjEKCg==

--------------q9X2D6quAWtSgkjFNAXq3Rpm--