Re: should check collations when creating partitioned index

On 20.11.23 17:25, Tom Lane wrote:
> Peter Eisentraut <peter(at)eisentraut(dot)org> writes:
>> On 14.11.23 17:15, Tom Lane wrote:
>>> I don't love the patch details though. It seems entirely wrong to check
>>> this before we check the opclass match.
>
>> Not sure why? The order doesn't seem to matter?
>
> The case that was bothering me was if we had a non-collated type
> versus a collated type. That would result in throwing an error
> about collation mismatch, when complaining about the opclass seems
> more apropos. However, if we do this:
>
>> I see. That means we shouldn't raise an error on a mismatch but just do
>> if (key->partcollation[i] != collationIds[j])
>> continue;
>
> it might not matter much.

Here is an updated patch that works as indicated above.

The behavior if you try to create an index with mismatching collations
now is that it will skip over the column and complain at the end with
something like

ERROR: 0A000: unique constraint on partitioned table must include all
partitioning columns
DETAIL: UNIQUE constraint on table "t1" lacks column "b" which is part
of the partition key.

which perhaps isn't intuitive, but I think it would be the same if you
somehow tried to build an index with different operator classes than the
partitioning. I think these less-specific error messages are ok in such
edge cases.