BUG #19487: Error while executing SQL query involving XML parsing

ср, 20 мая 2026 г. в 04:30, PG Bug reporting form <noreply@postgresql.org>:

The following bug has been logged on the website:

Bug reference: 19487
Logged by: Ilya Portnov
Email address: i.portnov@compassplus.com
PostgreSQL version: 18.3
Operating system: Ubuntu 24.04 LTS
Description:

Hello.

While testing our software product for compatibility with PostgreSQL 18, we
found a problem which appears when executing particular query. Initial
query
we
were executing was quite complex and involved our custom SQL functions; we
tried to simplify it as far as we could, but it's still not very simple.

Environment which we used to reproduce this:

OS: Ubuntu 24.04 LTS
Architecture: x86_64
PostgreSQL version() output:
PostgreSQL 18.3 (Ubuntu 18.3-1.pgdg22.04+1) on x86_64-pc-linux-gnu,
compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04.3) 11.4.0, 64-bit

We also tried on several different Linux distributions and PostgreSQL
builds, the problem still reproduces.

So, steps to reproduce are:

1. Create function:

create or replace function xml_to_text(pXml xml) returns text
as $$
select
case when pXml is document
then (xpath('/*/text()', pXml))[1] ::text
else pXml::text
end;
$$ language sql immutable;

2. Execute query:

select xml_to_text( (xpath('ns:rq/@Day', case
when tbl.strcolumn != '' then XMLParse(document tbl.strcolumn)
when tbl.clobcolumn != '' then XMLParse(DOCUMENT
replace(replace(replace(replace(replace(tbl.clobcolumn, '&#x07;',
'?'),'&#x10;', '?'),'&#x13;', '?'),'&#x1C;', '?'), '&#x1D;', '?'))
else XMLParse(DOCUMENT '<rq></rq>')
end,
array[array['ns', 'http://example.com/schema.xsd&#39;]]
))[1] )
from
(
(select '<ns:rq xmlns:ns="http://example.com/schema.xsd&quot;
Day="2019-12-16T00:00:00.000"/>' as strcolumn, null as clobcolumn)
union all
(select '<ns:rq xmlns:ns="http://example.com/schema.xsd&quot;
Day="2019-12-16T00:00:00.000"></ns:rq>' as strcolumn, null as clobcolumn)
) tbl;

Expected result, which is what we get on PostgreSQL 17.9:

xml_to_text |
-----------------------+
2019-12-16T00:00:00.000|
2019-12-16T00:00:00.000|

Actual result on PostgreSQL 18.3:

SQL Error [2200M]: ERROR: could not parse XML document
Detail: line 1: Start tag expected, '<' not found
2019-12-16T00:00:00.000
^
Where: SQL function "xpath" statement 1
SQL function "xml_to_text" statement 1

It appears that all parts of problematic query are important for this
problem:
if I try to simplify it, for example if I try to remove one of five nested
replace() calls, the problem goes away.

This looks like a problem which was introduced somewhere between PostgreSQL
17 and 18. Any help in fixing it will be appreciated.

Best regards,
Ilya V. Portnov.

Hi, Ilya!

Thanks for the report.
I'm sorry for the long wait with the answer.

# Guilty commit

0dca5d68d7b — Change SQL-language functions to use the plan cache.

- parent `e9e7b66044c`: query OK
- `0dca5d68d7b`: same ERROR as on master

Custom plans substitute Const arguments into the SQL function body.
`eval_const_expressions` then simplifies CASE WHEN results even when the
WHEN condition is a non-Const XmlExpr (`IS DOCUMENT`). Immutable
`xpath()` on the Const non-document content is executed at plan time and
throws.

# Proposal Fix

Fold constant `XmlExpr` in `eval_const_expressions` (same path as
`MinMaxExpr` / `ArrayExpr`), so `IS DOCUMENT` becomes a boolean Const and
CASE drops the unused arm before simplifying `xpath()`.

--
Regards,
Rachitskiy Andrey

Andrey Rachitskiy <pl0h0yp1@gmail.com> writes:

# Proposal Fix
Fold constant `XmlExpr` in `eval_const_expressions` (same path as
`MinMaxExpr` / `ArrayExpr`), so `IS DOCUMENT` becomes a boolean Const and
CASE drops the unused arm before simplifying `xpath()`.

I don't object to simplifying XmlExpr where possible, but this patch
appears to believe that every variant of XmlExpr is immutable.
This is demonstrably false, eg

regression=# set timezone = 'America/New_York';
SET
regression=# SELECT xmlforest('2026-08-28 12:00 UTC'::timestamptz AS foo);
xmlforest
--------------------------------------
<foo>2026-08-28T08:00:00-04:00</foo>
(1 row)

regression=# set timezone = 'America/Chicago';
SET
regression=# SELECT xmlforest('2026-08-28 12:00 UTC'::timestamptz AS foo);
xmlforest
--------------------------------------
<foo>2026-08-28T07:00:00-05:00</foo>
(1 row)

So you need to take a closer look at which variants can be promised to
be immutable.

regards, tom lane

сб, 29 авг. 2026 г. в 00:49, Tom Lane <tgl@sss.pgh.pa.us>:

Andrey Rachitskiy <pl0h0yp1@gmail.com> writes:

# Proposal Fix
Fold constant `XmlExpr` in `eval_const_expressions` (same path as
`MinMaxExpr` / `ArrayExpr`), so `IS DOCUMENT` becomes a boolean Const and
CASE drops the unused arm before simplifying `xpath()`.

I don't object to simplifying XmlExpr where possible, but this patch
appears to believe that every variant of XmlExpr is immutable.
This is demonstrably false, eg

regression=# set timezone = 'America/New_York';
SET
regression=# SELECT xmlforest('2026-08-28 12:00 UTC'::timestamptz AS foo);
xmlforest
--------------------------------------
<foo>2026-08-28T08:00:00-04:00</foo>
(1 row)

regression=# set timezone = 'America/Chicago';
SET
regression=# SELECT xmlforest('2026-08-28 12:00 UTC'::timestamptz AS foo);
xmlforest
--------------------------------------
<foo>2026-08-28T07:00:00-05:00</foo>
(1 row)

So you need to take a closer look at which variants can be promised to
be immutable.

Dear Tom,

Thanks for the review. I'll study it in more detail and come back with
corrections.

--
Regards,
Rachitskiy Andrey

сб, 29 авг. 2026 г. в 00:55, Andrey Rachitskiy <pl0h0yp1@gmail.com>:

сб, 29 авг. 2026 г. в 00:49, Tom Lane <tgl@sss.pgh.pa.us>:

Andrey Rachitskiy <pl0h0yp1@gmail.com> writes:

# Proposal Fix
Fold constant `XmlExpr` in `eval_const_expressions` (same path as
`MinMaxExpr` / `ArrayExpr`), so `IS DOCUMENT` becomes a boolean Const

and

CASE drops the unused arm before simplifying `xpath()`.

I don't object to simplifying XmlExpr where possible, but this patch
appears to believe that every variant of XmlExpr is immutable.
This is demonstrably false, eg

regression=# set timezone = 'America/New_York';
SET
regression=# SELECT xmlforest('2026-08-28 12:00 UTC'::timestamptz AS foo);
xmlforest
--------------------------------------
<foo>2026-08-28T08:00:00-04:00</foo>
(1 row)

regression=# set timezone = 'America/Chicago';
SET
regression=# SELECT xmlforest('2026-08-28 12:00 UTC'::timestamptz AS foo);
xmlforest
--------------------------------------
<foo>2026-08-28T07:00:00-05:00</foo>
(1 row)

So you need to take a closer look at which variants can be promised to
be immutable.

Dear Tom, have a nice weekend!

In v2, I tried to take all the points into account and expanded the tests.

--
Regards,
Rachitskiy Andrey

Andrey Rachitskiy <pl0h0yp1@gmail.com> writes:

In v2, I tried to take all the points into account and expanded the tests.

Hmm, I think it might be better to drop
map_sql_value_to_xml_is_immutable and just treat XMLELEMENT and
XMLFOREST as non-immutable. Trying to be smarter adds quite a bit
of complexity to this patch as well as long-term maintenance risk
(ie, needing to keep map_sql_value_to_xml_is_immutable in step with
the actual behavior of map_sql_value_to_xml; if we mess that up, the
mistake will be very non-obvious, and these test cases are unlikely
to catch it). For my money it's not worth it.

regards, tom lane

вс, 30 авг. 2026 г. в 02:44, Tom Lane <tgl@sss.pgh.pa.us>:

Andrey Rachitskiy <pl0h0yp1@gmail.com> writes:

In v2, I tried to take all the points into account and expanded the

tests.

Hmm, I think it might be better to drop
map_sql_value_to_xml_is_immutable and just treat XMLELEMENT and
XMLFOREST as non-immutable. Trying to be smarter adds quite a bit
of complexity to this patch as well as long-term maintenance risk
(ie, needing to keep map_sql_value_to_xml_is_immutable in step with
the actual behavior of map_sql_value_to_xml; if we mess that up, the
mistake will be very non-obvious, and these test cases are unlikely
to catch it). For my money it's not worth it.

Dear Tom,

I think you're right. Fixed it in v3.

--
Regards,
Rachitskiy Andrey