From: Manu <manuelreyesbravo@gmail.com>
To: JaeHyuck Sa <jaehyuck.sa.dev@gmail.com>
Cc: Aleksander Alekseev <aleksander@tigerdata.com>
Cc: pgsql-bugs@lists.postgresql.org
Cc: 1217816127@qq.com
Subject: Re: BUG #19602: Vuln46: citext split_part silently returns NULL for a zero field position instead of raising core sp
Date: Fri, 13 Nov 2026 16:54:56 -0300
Message-ID: <179459969625.858285.12508539351287151257@gmail.com> (raw)
In-Reply-To: <CAKs5thwFvKAxjzWUY_ddPZg2tWYYoqtLNg_MPQ+8Adu7oVzV2w@mail.gmail.com>
References: <CAKs5thwFvKAxjzWUY_ddPZg2tWYYoqtLNg_MPQ+8Adu7oVzV2w@mail.gmail.com>
Hi Jaehyuck,
On Fri, Nov 13, 2026 at 3:10 PM Jaehyuck Sa
<jaehyuck.sa.dev@gmail.com> wrote:
> In a session
> that had used the table under 1.8, I ran ALTER EXTENSION citext UPDATE
> and then REINDEX. The index was rebuilt with the 1.8 results, so a
> new session's index scan still missed rows that a seq scan found.
You are right: after a REINDEX in such a session, an index scan from a
new session found 0 of 500 matching rows and a seq scan found 500, and
after a REINDEX from a new session both found 500. A plain SQL function
changed with CREATE OR REPLACE does the same, so it is not specific to
citext. The same happens with a partial index predicate, a partition
key (an older session that had used the table kept routing new rows by
the 1.8 result) and a domain CHECK (an older session accepted a value
1.9 rejects). The attached v4 adds this to the commit message; the code
is unchanged.
Regards,
Manu
Attachments:v4-0001-Make-citext-s-split_part-handle-field-positions-l.patch (text/x-patch, 12.3K) download
From fb3a27f56651e1e5c28da26f3437b99bb808f391 Mon Sep 17 00:00:00 2001
From: Manuel Reyes Bravo <manuelreyesbravo@gmail.com>
Date: Fri, 13 Nov 2026 13:56:22 -0300
Subject: [PATCH v4] Make citext's split_part() handle field positions like the
core one
citext's split_part() splits with a case-insensitive regular expression
and subscripts the resulting array, so it returns NULL for a position of
zero, where the core function raises an error, and for a position past
the last field, where the core function returns an empty string. Since
ec0294fb2c8, the core function also counts negative positions from the
end, while citext's returns NULL for them. With an empty delimiter it
also splits the string into single characters, where the core function
treats the whole string as the only field.
Fix by replacing each case-insensitive occurrence of the delimiter with
the delimiter itself and leaving the splitting to the core function, so
that all the position rules are the core ones. The fields keep their
original case. The new body is a single expression, so the function is
still inlined, as it was in 1.8. This needs a new extension version,
1.9.
Because the final split is exact, the delimiter is now also found where
the regular expression engine fails to match it case-insensitively
against itself, which can happen for the titlecase letters U+01C5,
U+01C8, U+01CB and U+01F2.
After updating to 1.9, results of citext's split_part() stored under
1.8 are not recomputed: indexes using it in an expression or a
predicate need a REINDEX, and rows routed by a partition key, stored
generated columns and materialized views using it need to be rewritten
or refreshed. A session that used such an index, partition key or
domain CHECK constraint before the update may keep the 1.8 body it
inlined until it ends, so the REINDEX and the rewrite of partitioned
tables need a session started after the update. Stored rows that used
a position of zero now raise an error when recomputed, and existing
rows may no longer pass CHECK constraints that use it.
Co-authored-by: Aleksander Alekseev <aleksander@tigerdata.com>
Reported-by: Yuelin Wang <1217816127@qq.com>
Discussion: https://postgr.es/m/19602-5ec4b4e30fa6f5f2@postgresql.org
---
contrib/citext/Makefile | 1 +
contrib/citext/citext--1.8--1.9.sql | 22 +++++++
contrib/citext/citext.control | 2 +-
contrib/citext/expected/citext.out | 85 +++++++++++++++++++++++++
contrib/citext/expected/citext_1.out | 85 +++++++++++++++++++++++++
contrib/citext/expected/citext_utf8.out | 15 +++++
contrib/citext/meson.build | 1 +
contrib/citext/sql/citext.sql | 18 ++++++
contrib/citext/sql/citext_utf8.sql | 6 ++
9 files changed, 234 insertions(+), 1 deletion(-)
create mode 100644 contrib/citext/citext--1.8--1.9.sql
diff --git a/contrib/citext/Makefile b/contrib/citext/Makefileindex fc99060..f4db865 100644--- a/contrib/citext/Makefile+++ b/contrib/citext/Makefile@@ -4,6 +4,7 @@ MODULES = citext
EXTENSION = citext
DATA = citext--1.4.sql \
+ citext--1.8--1.9.sql \
citext--1.7--1.8.sql \
citext--1.6--1.7.sql \
citext--1.5--1.6.sql \
diff --git a/contrib/citext/citext--1.8--1.9.sql b/contrib/citext/citext--1.8--1.9.sqlnew file mode 100644index 0000000..aa02961--- /dev/null+++ b/contrib/citext/citext--1.8--1.9.sql@@ -0,0 +1,22 @@+/* contrib/citext/citext--1.8--1.9.sql */++-- complain if script is sourced in psql, rather than via ALTER EXTENSION+\echo Use "ALTER EXTENSION citext UPDATE TO '1.9'" to load this file. \quit++-- Normalize the case of the delimiter occurrences in the string, and let the+-- core split_part() do the splitting. The fields themselves are not touched,+-- so they keep their original case, while the field positions, the empty+-- delimiter and the zero-position error all behave exactly as in core.+-- The delimiter is escaped twice: once as a regular expression to search for,+-- and once as a replacement string, where a backslash is also special.+CREATE OR REPLACE FUNCTION split_part( citext, citext, int ) RETURNS TEXT+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE+RETURN pg_catalog.split_part(+ pg_catalog.regexp_replace(+ $1::pg_catalog.text,+ pg_catalog.regexp_replace($2::pg_catalog.text,+ '([^a-zA-Z_0-9])', E'\\\\\\1', 'g'),+ pg_catalog.replace($2::pg_catalog.text, E'\\', E'\\\\'),+ 'gi'),+ $2::pg_catalog.text,+ $3);diff --git a/contrib/citext/citext.control b/contrib/citext/citext.controlindex 2b0f3fa..a76f6bb 100644--- a/contrib/citext/citext.control+++ b/contrib/citext/citext.control@@ -1,6 +1,6 @@
# citext extension
comment = 'data type for case-insensitive character strings'
-default_version = '1.8'+default_version = '1.9'
module_pathname = '$libdir/citext'
relocatable = true
trusted = true
diff --git a/contrib/citext/expected/citext.out b/contrib/citext/expected/citext.outindex 8c0bf54..e99c3e1 100644--- a/contrib/citext/expected/citext.out+++ b/contrib/citext/expected/citext.out@@ -2120,6 +2120,91 @@ SELECT split_part('abcTdefTghi', 't'::citext, 2) = 'def' AS t;
t
(1 row)
+-- positions are handled as in the core split_part()+SELECT split_part('abcTdefTghi'::citext, 't'::citext, -1) = 'ghi' AS t;+ t +---+ t+(1 row)++SELECT split_part('abcTdefTghi'::citext, 't'::citext, -3) = 'abc' AS t;+ t +---+ t+(1 row)++SELECT split_part('abcTdefTghi'::citext, 't'::citext, 4) = '' AS t;+ t +---+ t+(1 row)++SELECT split_part('abcTdefTghi'::citext, 't'::citext, -4) = '' AS t;+ t +---+ t+(1 row)++SELECT split_part('abcTdefTghi'::citext, ''::citext, 1) = 'abcTdefTghi' AS t;+ t +---+ t+(1 row)++SELECT split_part('abcTdefTghi'::citext, ''::citext, 2) = '' AS t;+ t +---+ t+(1 row)++-- the delimiter is matched regardless of case, even with characters that are+-- special in a regular expression or in its replacement string+SELECT split_part('a\B\c'::citext, '\'::citext, -1) = 'c' AS t;+ t +---+ t+(1 row)++SELECT split_part('a\1b\1c'::citext, '\1'::citext, 2) = 'b' AS t;+ t +---+ t+(1 row)++SELECT split_part('aB&cb&d'::citext, 'b&'::citext, -2) = 'c' AS t;+ t +---+ t+(1 row)++SELECT split_part('a.*B.*c'::citext, '.*'::citext, -2) = 'B' AS t;+ t +---+ t+(1 row)++-- occurrences of the delimiter do not overlap, and fields keep their case+SELECT split_part('AaA'::citext, 'aa'::citext, -1) = 'A' AS t;+ t +---+ t+(1 row)++SELECT split_part('xaAb'::citext, 'AB'::citext, 1) = 'xa' AS t;+ t +---+ t+(1 row)++SELECT split_part('a,b,c'::citext, ','::citext, -2147483648) = '' AS t;+ t +---+ t+(1 row)++SELECT split_part('abcTdefTghi'::citext, 't'::citext, 0);+ERROR: field position must not be zero+CONTEXT: SQL function "split_part" statement 1
SELECT strpos('high'::citext, 'gh' ) = 3 AS t;
t
---
diff --git a/contrib/citext/expected/citext_1.out b/contrib/citext/expected/citext_1.outindex c5e5f18..66aff57 100644--- a/contrib/citext/expected/citext_1.out+++ b/contrib/citext/expected/citext_1.out@@ -2120,6 +2120,91 @@ SELECT split_part('abcTdefTghi', 't'::citext, 2) = 'def' AS t;
t
(1 row)
+-- positions are handled as in the core split_part()+SELECT split_part('abcTdefTghi'::citext, 't'::citext, -1) = 'ghi' AS t;+ t +---+ t+(1 row)++SELECT split_part('abcTdefTghi'::citext, 't'::citext, -3) = 'abc' AS t;+ t +---+ t+(1 row)++SELECT split_part('abcTdefTghi'::citext, 't'::citext, 4) = '' AS t;+ t +---+ t+(1 row)++SELECT split_part('abcTdefTghi'::citext, 't'::citext, -4) = '' AS t;+ t +---+ t+(1 row)++SELECT split_part('abcTdefTghi'::citext, ''::citext, 1) = 'abcTdefTghi' AS t;+ t +---+ t+(1 row)++SELECT split_part('abcTdefTghi'::citext, ''::citext, 2) = '' AS t;+ t +---+ t+(1 row)++-- the delimiter is matched regardless of case, even with characters that are+-- special in a regular expression or in its replacement string+SELECT split_part('a\B\c'::citext, '\'::citext, -1) = 'c' AS t;+ t +---+ t+(1 row)++SELECT split_part('a\1b\1c'::citext, '\1'::citext, 2) = 'b' AS t;+ t +---+ t+(1 row)++SELECT split_part('aB&cb&d'::citext, 'b&'::citext, -2) = 'c' AS t;+ t +---+ t+(1 row)++SELECT split_part('a.*B.*c'::citext, '.*'::citext, -2) = 'B' AS t;+ t +---+ t+(1 row)++-- occurrences of the delimiter do not overlap, and fields keep their case+SELECT split_part('AaA'::citext, 'aa'::citext, -1) = 'A' AS t;+ t +---+ t+(1 row)++SELECT split_part('xaAb'::citext, 'AB'::citext, 1) = 'xa' AS t;+ t +---+ t+(1 row)++SELECT split_part('a,b,c'::citext, ','::citext, -2147483648) = '' AS t;+ t +---+ t+(1 row)++SELECT split_part('abcTdefTghi'::citext, 't'::citext, 0);+ERROR: field position must not be zero+CONTEXT: SQL function "split_part" statement 1
SELECT strpos('high'::citext, 'gh' ) = 3 AS t;
t
---
diff --git a/contrib/citext/expected/citext_utf8.out b/contrib/citext/expected/citext_utf8.outindex 19538db..df35322 100644--- a/contrib/citext/expected/citext_utf8.out+++ b/contrib/citext/expected/citext_utf8.out@@ -154,3 +154,18 @@ SELECT 'à'::citext ~>=~ 'À'::citext AS t;
t
(1 row)
+-- split_part() positions with a multibyte delimiter, and a titlecase letter+-- (U+01C5) used as the delimiter: the regex engine may not match it+-- case-insensitively against itself, but the split is still found+SELECT split_part('aΔbδc'::citext, 'δ'::citext, -2) = 'b' AS t;+ t +---+ t+(1 row)++SELECT split_part('aDžb'::citext, 'Dž'::citext, 2) = 'b' AS t;+ t +---+ t+(1 row)+diff --git a/contrib/citext/meson.build b/contrib/citext/meson.buildindex 1cc49fc..d8aba86 100644--- a/contrib/citext/meson.build+++ b/contrib/citext/meson.build@@ -27,6 +27,7 @@ install_data(
'citext--1.5--1.6.sql',
'citext--1.6--1.7.sql',
'citext--1.7--1.8.sql',
+ 'citext--1.8--1.9.sql',
kwargs: contrib_data_args,
)
diff --git a/contrib/citext/sql/citext.sql b/contrib/citext/sql/citext.sqlindex aa1cf9a..abc81f6 100644--- a/contrib/citext/sql/citext.sql+++ b/contrib/citext/sql/citext.sql@@ -653,6 +653,24 @@ SELECT split_part('abc~@~def~@~ghi'::citext, '~@~', 2) = 'def' AS t;
SELECT split_part('abcTdefTghi'::citext, 't', 2) = 'def' AS t;
SELECT split_part('abcTdefTghi'::citext, 't'::citext, 2) = 'def' AS t;
SELECT split_part('abcTdefTghi', 't'::citext, 2) = 'def' AS t;
+-- positions are handled as in the core split_part()+SELECT split_part('abcTdefTghi'::citext, 't'::citext, -1) = 'ghi' AS t;+SELECT split_part('abcTdefTghi'::citext, 't'::citext, -3) = 'abc' AS t;+SELECT split_part('abcTdefTghi'::citext, 't'::citext, 4) = '' AS t;+SELECT split_part('abcTdefTghi'::citext, 't'::citext, -4) = '' AS t;+SELECT split_part('abcTdefTghi'::citext, ''::citext, 1) = 'abcTdefTghi' AS t;+SELECT split_part('abcTdefTghi'::citext, ''::citext, 2) = '' AS t;+-- the delimiter is matched regardless of case, even with characters that are+-- special in a regular expression or in its replacement string+SELECT split_part('a\B\c'::citext, '\'::citext, -1) = 'c' AS t;+SELECT split_part('a\1b\1c'::citext, '\1'::citext, 2) = 'b' AS t;+SELECT split_part('aB&cb&d'::citext, 'b&'::citext, -2) = 'c' AS t;+SELECT split_part('a.*B.*c'::citext, '.*'::citext, -2) = 'B' AS t;+-- occurrences of the delimiter do not overlap, and fields keep their case+SELECT split_part('AaA'::citext, 'aa'::citext, -1) = 'A' AS t;+SELECT split_part('xaAb'::citext, 'AB'::citext, 1) = 'xa' AS t;+SELECT split_part('a,b,c'::citext, ','::citext, -2147483648) = '' AS t;+SELECT split_part('abcTdefTghi'::citext, 't'::citext, 0);
SELECT strpos('high'::citext, 'gh' ) = 3 AS t;
SELECT strpos('high', 'gh'::citext) = 3 AS t;
diff --git a/contrib/citext/sql/citext_utf8.sql b/contrib/citext/sql/citext_utf8.sqlindex ba28332..d923d6d 100644--- a/contrib/citext/sql/citext_utf8.sql+++ b/contrib/citext/sql/citext_utf8.sql@@ -59,3 +59,9 @@ SELECT 'à'::citext ~<=~ 'À'::citext AS t;
-- Test ~>~ and ~>=~
SELECT 'à'::citext ~>~ 'À'::citext AS f;
SELECT 'à'::citext ~>=~ 'À'::citext AS t;
++-- split_part() positions with a multibyte delimiter, and a titlecase letter+-- (U+01C5) used as the delimiter: the regex engine may not match it+-- case-insensitively against itself, but the split is still found+SELECT split_part('aΔbδc'::citext, 'δ'::citext, -2) = 'b' AS t;+SELECT split_part('aDžb'::citext, 'Dž'::citext, 2) = 'b' AS t;--
2.56.1
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-bugs@postgresql.org
Cc: manuelreyesbravo@gmail.com, jaehyuck.sa.dev@gmail.com, aleksander@tigerdata.com, 1217816127@qq.com
Subject: Re: BUG #19602: Vuln46: citext split_part silently returns NULL for a zero field position instead of raising core sp
In-Reply-To: <179459969625.858285.12508539351287151257@gmail.com>
References: <CAKs5thwFvKAxjzWUY_ddPZg2tWYYoqtLNg_MPQ+8Adu7oVzV2w@mail.gmail.com> <179459969625.858285.12508539351287151257@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox