Subject: Re: should check collations when creating partitioned index

From: Jeff Davis <[email-masked]>
Date: Fri, 17 Nov 2023 12:02:33 -0800

On Mon, 2023-11-13 at 10:24 +0100, Peter Eisentraut wrote:
> 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.

In the patch, you check for an exact collation match. Considering this
case only depends on equality, I think it would be correct if the
requirement was that (a) both collations are deterministic; or (b) the
collations match exactly.

This is related to the discussion here:

https://postgr.es/m/[email-masked]

Regards,
	Jeff Davis

---

From: Tom Lane <[email-masked]>
Date: Fri, 17 Nov 2023 15:18:57 -0500

> collations match exactly.

You keep harping on this idea that we are only concerned with equality,
but I think you are wrong.  We expect a btree index to provide ordering
not only equality, and this example definitely is a btree index.

Possibly, with a great deal more specificity added to the check, we
could distinguish the cases where ordering can't matter and allow
collation variance then.  I do not see the value of that, especially
not when measured against the risk of introducing subtle bugs.

			regards, tom lane

---

From: Jeff Davis <[email-masked]>
Date: Fri, 17 Nov 2023 13:08:09 -0800

> not when measured against the risk of introducing subtle bugs.

Fair point.

As background, I don't see a complete solution to our collation
problems and on the horizon. You've probably noticed that I'm looking
for various ways to mitigate the problem, and this thread was about
reducing the number of situations in which we rely on collation.

I'll focus on other potential improvements/mitigations and see if I can
make progress somewhere else.

Regards,
	Jeff Davis