agora inbox for pgsql-committers@postgresql.orghelp / color / mirror / Atom feed
pgsql: Fall back to SPI for RI checks with mismatched index collations 2+ messages / 1 participants [nested] [flat]
* pgsql: Fall back to SPI for RI checks with mismatched index collations @ 2026-09-11 08:51 Amit Langote <amitlan@postgresql.org> 0 siblings, 0 replies; 2+ messages in thread From: Amit Langote @ 2026-09-11 08:51 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fall back to SPI for RI checks with mismatched index collations The fast path probes using the referenced index's collation. When that collation differs from the referenced column's, it can reject valid references or accept invalid ones. Changing the scan key's collation is not sufficient, since btree navigation must use the ordering under which the index was built. Cache index eligibility lazily after locking the referenced table, reloading the constraint information, and opening the index. Compare index and column collations using the held relation descriptors, and fall back to SPI on a mismatch. Also move the btree eligibility check here, avoiding a lookup of an index that REINDEX CONCURRENTLY could drop before the referenced-table lock is acquired. Cache both acceptance and rejection until the constraint information is reloaded, so subsequent rows need not repeat the comparisons. Check before probing a row, and let the caller use SPI if the index is unsuitable. Add ICU regression coverage for per-row validation, ordinary inserts, reordered index columns, and both directions of collation mismatch. Reported-by: Amit Kapila <amit.kapila16@gmail.com> Discussion: https://postgr.es/m/CAA4eK1Lk1DfgiFPL-HccZm-Nm+exg+FcsODH8KPw6BOBuzfHtA@mail.gmail.com Backpatch-through: 19 Branch ------ REL_19_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/2bc7b71dc168d537c67a307e5ac5d6c764bbc721 Modified Files -------------- src/backend/utils/adt/ri_triggers.c | 93 ++++++++++++++++++++++---- src/test/regress/expected/collate.icu.utf8.out | 36 ++++++++++ src/test/regress/sql/collate.icu.utf8.sql | 34 ++++++++++ src/tools/pgindent/typedefs.list | 1 + 4 files changed, 150 insertions(+), 14 deletions(-) ^ permalink raw reply [nested|flat] 2+ messages in thread
* pgsql: Fall back to SPI for RI checks with mismatched index collations @ 2026-09-11 08:51 Amit Langote <amitlan@postgresql.org> 0 siblings, 0 replies; 2+ messages in thread From: Amit Langote @ 2026-09-11 08:51 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fall back to SPI for RI checks with mismatched index collations The fast path probes using the referenced index's collation. When that collation differs from the referenced column's, it can reject valid references or accept invalid ones. Changing the scan key's collation is not sufficient, since btree navigation must use the ordering under which the index was built. Cache index eligibility lazily after locking the referenced table, reloading the constraint information, and opening the index. Compare index and column collations using the held relation descriptors, and fall back to SPI on a mismatch. Also move the btree eligibility check here, avoiding a lookup of an index that REINDEX CONCURRENTLY could drop before the referenced-table lock is acquired. Cache both acceptance and rejection until the constraint information is reloaded, so subsequent rows need not repeat the comparisons. Check before probing or buffering any row. If batch entry initialization rejects the index, remove that entry while preserving any other pending checks. Add ICU regression coverage for per-row validation, ordinary inserts, reordered index columns, both directions of collation mismatch, and fallback while another foreign key check is pending. Reported-by: Amit Kapila <amit.kapila16@gmail.com> Discussion: https://postgr.es/m/CAA4eK1Lk1DfgiFPL-HccZm-Nm+exg+FcsODH8KPw6BOBuzfHtA@mail.gmail.com Backpatch-through: 19 Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/0b0b413292161cf709b9dbc4d5adee23be53ee34 Modified Files -------------- src/backend/utils/adt/ri_triggers.c | 157 ++++++++++++++++++++----- src/test/regress/expected/collate.icu.utf8.out | 64 ++++++++++ src/test/regress/sql/collate.icu.utf8.sql | 60 ++++++++++ src/tools/pgindent/typedefs.list | 1 + 4 files changed, 254 insertions(+), 28 deletions(-) ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2026-09-11 08:51 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2026-09-11 08:51 pgsql: Fall back to SPI for RI checks with mismatched index collations Amit Langote <amitlan@postgresql.org> 2026-09-11 08:51 pgsql: Fall back to SPI for RI checks with mismatched index collations Amit Langote <amitlan@postgresql.org>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox