agora inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedBUG #19645: Partition key opclass bypasses nondeterministic collation check, wrong results
2+ messages / 2 participants
[nested] [flat]
* BUG #19645: Partition key opclass bypasses nondeterministic collation check, wrong results
@ 2026-08-29 05:39 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 2+ messages in thread
From: PG Bug reporting form @ 2026-08-29 05:39 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: jj-zhang25@mails.tsinghua.edu.cn
The following bug has been logged on the website:
Bug reference: 19645
Logged by: 放空
Email address: jj-zhang25@mails.tsinghua.edu.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.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BUG #19645: Partition key opclass bypasses nondeterministic collation check, wrong results
@ 2026-09-02 06:28 Zexin Li <lizi.openmind@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 0 replies; 2+ messages in thread
From: Zexin Li @ 2026-09-02 06:28 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: jj-zhang25@mails.tsinghua.edu.cn
On Sat, Aug 29, 2026 at 2:39 PM PG Bug reporting form
<noreply@postgresql.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
Attachments:
[application/x-patch] 0001-Check-partition-key-opclass-against-nondeterministic.patch (9.4K, ../../CAAP6ZkR6fRtDqW=c=+bPEHPzQOtFioXjqgGtT6r6Ycc7WKs1zQ@mail.gmail.com/2-0001-Check-partition-key-opclass-against-nondeterministic.patch)
download | inline diff:
From dfcf263959f39c7076dc9e99944749b8b3330539 Mon Sep 17 00:00:00 2001
From: Zexin Li <lizi.openmind@gmail.com>
Date: Wed, 2 Sep 2026 02:39:29 +0000
Subject: [PATCH] Check partition key opclass against nondeterministic
collations
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Commit 2810396312 made index creation refuse text_pattern_ops (and its
varchar and bpchar siblings) in combination with a nondeterministic
collation, because those opclasses compare bytewise while their
equality operator follows the collation. The same check was never
applied to partition keys, so a partitioned table could be declared
with a pattern opclass over a nondeterministically collated column.
Tuple routing and partition pruning then both use the opclass's
bytewise comparison, while the query's equality operator uses the
column's collation, so pruning can discard partitions that contain
matching rows. SELECT, UPDATE and DELETE return wrong results unless
partition pruning is disabled.
Move the existing check from index_create() into a new function
CheckOpclassCollation() and call it from ComputePartitionAttrs() as
well. As a result, a partitioned table whose partition key combines
such an opclass with a nondeterministic collation can no longer be
created. Such declarations already produced wrong answers, so nothing
that worked correctly is lost.
Bug: #19645
Reported-by: 放空 <jj-zhang25@mails.tsinghua.edu.cn>
Discussion: https://postgr.es/m/19645-60a963f2e91478e0@postgresql.org
Backpatch-through: 14
---
src/backend/catalog/index.c | 92 ++++++++++---------
src/backend/commands/tablecmds.c | 6 ++
src/include/catalog/index.h | 2 +
.../regress/expected/collate.icu.utf8.out | 5 +
src/test/regress/sql/collate.icu.utf8.sql | 5 +
5 files changed, 69 insertions(+), 41 deletions(-)
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index ec21b83b..8f8ab52a 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -676,6 +676,54 @@ UpdateIndexRelation(Oid indexoid,
}
+/*
+ * CheckOpclassCollation
+ *
+ * Verify that the given operator class can be used with the given collation,
+ * throwing an error if not. This is applied to index columns and to
+ * partition key columns.
+ *
+ * Btree text_pattern_ops uses texteq as the equality operator, which is
+ * fine as long as the collation is deterministic; texteq then reduces to
+ * bitwise equality and so it is semantically compatible with the other
+ * operators and functions in that opclass. But with a nondeterministic
+ * collation, texteq could yield results that are incompatible with the
+ * actual behavior of the index or partition key (which is determined by
+ * the opclass's comparison function). We prevent such problems by refusing
+ * that opclass in combination with a nondeterministic collation.
+ *
+ * The same applies to varchar_pattern_ops and bpchar_pattern_ops. If we
+ * find more cases, we might decide to create a real mechanism for marking
+ * opclasses as incompatible with nondeterminism; but for now, this small
+ * hack suffices.
+ *
+ * Another solution is to use a special operator, not texteq, as the
+ * equality opclass member; but that is undesirable because it would
+ * prevent index usage in many queries that work fine today.
+ */
+void
+CheckOpclassCollation(Oid opclass, Oid collation)
+{
+ if (!OidIsValid(collation))
+ return;
+
+ if ((opclass == TEXT_BTREE_PATTERN_OPS_OID ||
+ opclass == VARCHAR_BTREE_PATTERN_OPS_OID ||
+ opclass == BPCHAR_BTREE_PATTERN_OPS_OID) &&
+ !get_collation_isdeterministic(collation))
+ {
+ HeapTuple classtup;
+
+ classtup = SearchSysCache1(CLAOID, ObjectIdGetDatum(opclass));
+ if (!HeapTupleIsValid(classtup))
+ elog(ERROR, "cache lookup failed for operator class %u", opclass);
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("nondeterministic collations are not supported for operator class \"%s\"",
+ NameStr(((Form_pg_opclass) GETSTRUCT(classtup))->opcname))));
+ }
+}
+
/*
* index_create
*
@@ -811,49 +859,11 @@ index_create(Relation heapRelation,
errmsg("user-defined indexes on system catalog tables are not supported")));
/*
- * Btree text_pattern_ops uses texteq as the equality operator, which is
- * fine as long as the collation is deterministic; texteq then reduces to
- * bitwise equality and so it is semantically compatible with the other
- * operators and functions in that opclass. But with a nondeterministic
- * collation, texteq could yield results that are incompatible with the
- * actual behavior of the index (which is determined by the opclass's
- * comparison function). We prevent such problems by refusing creation of
- * an index with that opclass and a nondeterministic collation.
- *
- * The same applies to varchar_pattern_ops and bpchar_pattern_ops. If we
- * find more cases, we might decide to create a real mechanism for marking
- * opclasses as incompatible with nondeterminism; but for now, this small
- * hack suffices.
- *
- * Another solution is to use a special operator, not texteq, as the
- * equality opclass member; but that is undesirable because it would
- * prevent index usage in many queries that work fine today.
+ * Check that each operator class can be used with its collation; see
+ * CheckOpclassCollation for the rationale.
*/
for (i = 0; i < indexInfo->ii_NumIndexKeyAttrs; i++)
- {
- Oid collation = collationIds[i];
- Oid opclass = opclassIds[i];
-
- if (collation)
- {
- if ((opclass == TEXT_BTREE_PATTERN_OPS_OID ||
- opclass == VARCHAR_BTREE_PATTERN_OPS_OID ||
- opclass == BPCHAR_BTREE_PATTERN_OPS_OID) &&
- !get_collation_isdeterministic(collation))
- {
- HeapTuple classtup;
-
- classtup = SearchSysCache1(CLAOID, ObjectIdGetDatum(opclass));
- if (!HeapTupleIsValid(classtup))
- elog(ERROR, "cache lookup failed for operator class %u", opclass);
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("nondeterministic collations are not supported for operator class \"%s\"",
- NameStr(((Form_pg_opclass) GETSTRUCT(classtup))->opcname))));
- ReleaseSysCache(classtup);
- }
- }
- }
+ CheckOpclassCollation(opclassIds[i], collationIds[i]);
/*
* Concurrent index build on a system catalog is unsafe because we tend to
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index fd144d78..ad4733a0 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -20772,6 +20772,12 @@ ComputePartitionAttrs(ParseState *pstate, Relation rel, List *partParams, AttrNu
am_oid == HASH_AM_OID ? "hash" : "btree",
am_oid);
+ /*
+ * Check that the operator class can be used with the collation, in
+ * the same way as index_create does for index columns.
+ */
+ CheckOpclassCollation(partopclass[attn], partcollation[attn]);
+
attn++;
}
}
diff --git a/src/include/catalog/index.h b/src/include/catalog/index.h
index b952ad07..fc00407b 100644
--- a/src/include/catalog/index.h
+++ b/src/include/catalog/index.h
@@ -64,6 +64,8 @@ extern void index_check_primary_key(Relation heapRel,
bool is_alter_table,
const IndexStmt *stmt);
+extern void CheckOpclassCollation(Oid opclass, Oid collation);
+
#define INDEX_CREATE_IS_PRIMARY (1 << 0)
#define INDEX_CREATE_ADD_CONSTRAINT (1 << 1)
#define INDEX_CREATE_SKIP_BUILD (1 << 2)
diff --git a/src/test/regress/expected/collate.icu.utf8.out b/src/test/regress/expected/collate.icu.utf8.out
index fcfcc658..15f58be7 100644
--- a/src/test/regress/expected/collate.icu.utf8.out
+++ b/src/test/regress/expected/collate.icu.utf8.out
@@ -3211,6 +3211,11 @@ SELECT (SELECT count(*) FROM test23a_0) <> (SELECT count(*) FROM test23a_1);
t
(1 row)
+-- operator classes that cannot be used with a nondeterministic collation
+-- are rejected for partition keys, as they are for indexes
+CREATE TABLE test24 (a int, b text COLLATE case_insensitive) PARTITION BY RANGE (b text_pattern_ops); -- error
+ERROR: nondeterministic collations are not supported for operator class "text_pattern_ops"
+CREATE TABLE test24 (a int, b text COLLATE case_insensitive) PARTITION BY RANGE (b COLLATE "C" text_pattern_ops); -- ok
CREATE TABLE test30 (a int, b char(3) COLLATE case_insensitive) PARTITION BY LIST (b);
CREATE TABLE test30_1 PARTITION OF test30 FOR VALUES IN ('abc');
INSERT INTO test30 VALUES (1, 'abc');
diff --git a/src/test/regress/sql/collate.icu.utf8.sql b/src/test/regress/sql/collate.icu.utf8.sql
index ce4e2bb3..2a9ed589 100644
--- a/src/test/regress/sql/collate.icu.utf8.sql
+++ b/src/test/regress/sql/collate.icu.utf8.sql
@@ -1180,6 +1180,11 @@ INSERT INTO test23a VALUES (2, ARRAY['DEF']);
-- they end up in the same partition (but it's platform-dependent which one)
SELECT (SELECT count(*) FROM test23a_0) <> (SELECT count(*) FROM test23a_1);
+-- operator classes that cannot be used with a nondeterministic collation
+-- are rejected for partition keys, as they are for indexes
+CREATE TABLE test24 (a int, b text COLLATE case_insensitive) PARTITION BY RANGE (b text_pattern_ops); -- error
+CREATE TABLE test24 (a int, b text COLLATE case_insensitive) PARTITION BY RANGE (b COLLATE "C" text_pattern_ops); -- ok
+
CREATE TABLE test30 (a int, b char(3) COLLATE case_insensitive) PARTITION BY LIST (b);
CREATE TABLE test30_1 PARTITION OF test30 FOR VALUES IN ('abc');
INSERT INTO test30 VALUES (1, 'abc');
--
2.34.1
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2026-09-02 06:28 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-08-29 05:39 BUG #19645: Partition key opclass bypasses nondeterministic collation check, wrong results PG Bug reporting form <noreply@postgresql.org>
2026-09-02 06:28 ` Zexin Li <lizi.openmind@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox