Re: BUG #19645: Partition key opclass bypasses nondeterministic collation check, wrong results

On Sat, Aug 29, 2026 at 2:39 PM PG Bug reporting form
<noreply(at)postgresql(dot)org> wrote:
> Apply the existing check to partition key expressions: when a partition key
> specifies an operator class or a collation that differs from the column's,
> and either collation is nondeterministic, refuse it with the message already
> used for indexes and exclusion constraints.

I can reproduce this on HEAD and on 16.13. As far as I can tell, the
check that index_create() applies to the pattern_ops opclasses (commit
2810396312) was never added to ComputePartitionAttrs(), so tuple
routing and partition pruning both use the opclass's bytewise
comparison while texteq follows the column's nondeterministic
collation.

I'm not sure the check needs to be as broad as suggested, though. In
my testing, PARTITION BY (c COLLATE "C") over a column with a
nondeterministic collation gives correct results: the qual's collation
no longer matches the partition key's, so PartCollMatchesExprColl()
rejects it and nothing gets pruned. HASH with text_pattern_ops also
seems fine, since that family hashes with the collation-aware
hashtext. So the attached only moves the existing check into a
helper, CheckOpclassCollation(), and calls it from
ComputePartitionAttrs() as well; it checks the same three btree
opclasses as before and nothing else. I may well be missing a case.
A regression test is included; it fails without the fix.

One consequence is that an existing table with such a partition key
would no longer restore from a dump. Those tables were returning
wrong answers anyway.

About the December 2023 thread you mention: as far as I can see it
became commit a11c9c42ea, which compares a unique/PK/exclusion index's
collation against the partition key inside DefineIndex(). It doesn't
look at the partition key's own opclass; on 16.13 the table above is
created without complaint, and a unique index over the key is
accepted as well, since both sides carry the same collation. So I
don't think it reaches this case, but I'd be glad to be corrected.

Regards,
Zexin Li