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

The following bug has been logged on the website:

Bug reference: 19645
Logged by: 放空
Email address: jj-zhang25(at)mails(dot)tsinghua(dot)edu(dot)cn
PostgreSQL version: 18.6
Operating system: MacOS
Description:

Summary
=======

PostgreSQL refuses to build an index whose operator class cannot honour a
nondeterministic collation:

ERROR: nondeterministic collations are not supported for operator class
"text_pattern_ops"

The check is applied to unique indexes, to plain indexes, and to exclusion
constraints. It is not applied to partition keys. A partitioned table may
therefore be declared with

PARTITION BY RANGE (c text_pattern_ops) -- or (c COLLATE "C")

over a column whose own collation is nondeterministic. Row routing then uses
the
partition key's ordering while query predicates use the column's equality,
and
partition pruning discards partitions that contain matching rows.

The result is wrong answers from SELECT, and rows missed by UPDATE and
DELETE,
under the default plan with no special settings.

Reproduction
============

CREATE COLLATION ci (provider=icu, locale='und-u-ks-level2',
deterministic=false);

CREATE TABLE pp(id int, c text COLLATE ci)
PARTITION BY RANGE (c text_pattern_ops);
CREATE TABLE pp1 PARTITION OF pp FOR VALUES FROM (MINVALUE) TO ('a');
CREATE TABLE pp2 PARTITION OF pp FOR VALUES FROM ('a') TO (MAXVALUE);
INSERT INTO pp VALUES (1,'B'),(2,'b'),(3,'Z'),(4,'z');

Routing follows text_pattern_ops (C ordering), so the uppercase values land
in
the first partition:

SELECT 'pp1' AS part, string_agg(id||':'||c, ', ' ORDER BY id) FROM pp1
UNION ALL SELECT 'pp2', string_agg(id||':'||c, ', ' ORDER BY id) FROM pp2;
part | string_agg
------+------------
pp1 | 1:B, 3:Z
pp2 | 2:b, 4:z

Equality on the column uses the column's collation, under which case does
not
distinguish values:

SELECT id, c, (c='b') AS eq_b, (c='z') AS eq_z FROM pp ORDER BY id;
id | c | eq_b | eq_z
----+---+------+------
1 | B | t | f
2 | b | t | f
3 | Z | f | t
4 | z | f | t

The correct answer for c='b' is therefore {1,2}. Pruning returns only {2}:

SELECT string_agg(id::text,',' ORDER BY id) FROM pp WHERE c='b';
2

SET enable_partition_pruning=off;
SELECT string_agg(id::text,',' ORDER BY id) FROM pp WHERE c='b';
1,2

EXPLAIN (COSTS OFF) SELECT id FROM pp WHERE c='b';
Seq Scan on pp2 pp
Filter: (c = 'b'::text)

Partition pp1 is pruned away although it holds a row satisfying the
predicate.
The same happens for c='z' ({3,4} correct, {4} returned).

DML is affected identically:

UPDATE pp SET id=id+100 WHERE c='b';
UPDATE 1 -- only id=2 is updated; id=1 is not

DELETE FROM pp WHERE c='b';
DELETE 1
SELECT string_agg(id::text,',' ORDER BY id) FROM pp;
1,3,4 -- id=1 survives a delete whose predicate
matched it

Where the check is applied, and where it is not
===============================================

Identical column definition (text COLLATE ci) in every case:

CREATE UNIQUE INDEX m1u ON m1 (c text_pattern_ops);
ERROR: nondeterministic collations are not supported for operator class
"text_pattern_ops"

CREATE INDEX m2i ON m2 (c text_pattern_ops);
ERROR: nondeterministic collations are not supported for operator class
"text_pattern_ops"

CREATE TABLE m3(id int, c text COLLATE ci,
EXCLUDE (c text_pattern_ops WITH =));
ERROR: nondeterministic collations are not supported for operator class
"text_pattern_ops"

CREATE TABLE m4(id int, c text COLLATE ci)
PARTITION BY RANGE (c text_pattern_ops); -- accepted

CREATE TABLE m5(id int, c text COLLATE ci)
PARTITION BY LIST (c COLLATE "C"); -- accepted

The first three show the check exists and that the necessary information is
available at that point. The last two are the gap.

Why this is specific to nondeterministic collations
===================================================

With a deterministic collation, a partition key that uses a different
opclass or
collation is harmless for correctness: equality is byte equality regardless,
so
every row equal to the probe value routes to the same partition and pruning
stays
sound. Only ordering, and therefore which partitions can be pruned, depends
on
the choice.

Control, same structure with a deterministic column collation:

CREATE TABLE d1(id int, c text COLLATE "en_US.UTF-8")
PARTITION BY RANGE (c text_pattern_ops);
... INSERT (1,'B'),(2,'b'),(3,'Z');

SELECT string_agg(id::text,',' ORDER BY id) FROM d1 WHERE c='b';
2
SET enable_partition_pruning=off; -- same query
2 -- agrees

With a nondeterministic collation the column's collation defines equality
itself,
so the partition key's ordering no longer partitions the equality classes:
two
values the column considers equal can be routed to different partitions.
Pruning,
which reasons in the partition key's ordering, then excludes partitions
holding
matching rows.

Suggested fix
=============

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.

Notes
=====

pg_dump reproduces the same declaration, so a dump and restore recreates the
same
state rather than failing. The issue is the wrong query results, not an
unrestorable backup.

ALTER OPERATOR FAMILY was also tested and is correctly protected:

ALTER OPERATOR FAMILY text_pattern_ops USING btree
DROP OPERATOR 3 (text,text);
ERROR: cannot drop operator 3 (text, text) of operator family
text_pattern_ops for access method btree: =(text,text) because it
is
required by the database system

A related but separate gap, where CREATE UNIQUE INDEX with an explicit
COLLATE
clause is likewise accepted over a nondeterministically collated column, is
sent
in a separate message. That one concerns constraint semantics rather than
query
results, and the fix would go in a different place, so I have not combined
them.

Documentation
=============

Section 23.2 (Collation Support) mentions that B-tree deduplication is
unavailable with nondeterministic collations and that some pattern matching
operations are not possible. It does not describe the operator class
restriction
that the error message above states, nor its absence for partition keys. If
the
current behaviour is intended rather than an oversight, the documentation
gap
seems worth closing regardless.

Prior discussion
================

I searched the mailing list archives, the TODO list and the FAQ and found no
prior report of this. There is a thread from December 2023 titled "Check
collation when creating partitioned index" that I was not able to retrieve
in
full; if the present report overlaps that work, I would be glad to be
pointed at
it.