pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs
15+ messages / 6 participants
[nested] [flat]
Commits from this thread:
2026-10-20 e75fcc9867 Mark GUC-dependent type output functions as STABLE
* BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs
@ 2026-08-29 05:38 PG Bug reporting form <noreply@postgresql.org>
2026-09-02 03:08 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Fujii Masao <masao.fujii@gmail.com>
0 siblings, 1 reply; 15+ messages in thread
From: PG Bug reporting form @ 2026-08-29 05:38 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: 19644
Logged by: 放空
Email address: jj-zhang25@mails.tsinghua.edu.cn
PostgreSQL version: 18.6
Operating system: MacOS
Description:
Summary
=======
Three output functions are marked IMMUTABLE in pg_proc although their
results
depend on a GUC:
byteaout(bytea) depends on bytea_output
float8out(double precision) depends on extra_float_digits
float4out(real) depends on extra_float_digits
Because they are marked IMMUTABLE, casts of these types to text are accepted
in
expression indexes, in generated columns, in unique indexes, in CHECK
constraints, and in partition keys. Changing the corresponding GUC -- a
plain
session-level SET, with no DDL -- then leaves those materialised values
inconsistent with the current setting.
The comparable output functions whose results also depend on a GUC are
marked
STABLE and are correctly rejected at DDL time, which is what makes these
three
stand out:
SELECT p.oid::regprocedure AS function, p.provolatile::text
FROM pg_proc p
WHERE p.proname IN ('byteaout','float8out','float4out','date_out',
'interval_out','cash_out','numeric_out','timestamptz_out')
ORDER BY p.provolatile::text DESC, 1;
function | provolatile
-------------------------------------------+-------------
cash_out(money) | s
date_out(date) | s
timestamptz_out(timestamp with time zone) | s
interval_out(interval) | s
byteaout(bytea) | i
float4out(real) | i
float8out(double precision) | i
numeric_out(numeric) | i
(numeric_out is correctly IMMUTABLE; its output does not depend on any GUC.)
I am reporting the three together because they appear to share one fix --
the
volatility marking -- and because the consequences below are reached through
whichever of the three is used. Five distinct consequences follow, in
increasing
order of severity.
1. Expression index returns wrong results
=========================================
SET bytea_output='hex';
CREATE TABLE b(id int primary key, v bytea);
CREATE INDEX ix ON b ((v::text));
INSERT INTO b(id,v) VALUES (1,'\x41ff'),(2,'\x42fe');
SET bytea_output='escape'; -- session setting, no DDL
SELECT id FROM b WHERE v::text = 'A\377';
id
----
(0 rows)
The default plan uses the index:
EXPLAIN (COSTS OFF) SELECT id FROM b WHERE v::text = 'A\377';
Bitmap Heap Scan on b
Recheck Cond: ((v)::text = 'A\377'::text)
-> Bitmap Index Scan on ix
Index Cond: ((v)::text = 'A\377'::text)
Forcing a sequential scan gives the correct answer:
SET enable_indexscan=off; SET enable_bitmapscan=off;
SELECT id FROM b WHERE v::text = 'A\377';
id
----
1
(1 row)
The row does match the predicate:
SELECT v::text AS actual, v::text = 'A\377' AS eq,
md5(v::text) AS md5_actual, md5('A\377') AS md5_literal
FROM b WHERE id=1;
actual | eq | md5_actual | md5_literal
--------+----+----------------------------------+----------------------------------
A\377 | t | 6b279243ff65734b53ffb78c7a20ce5d |
6b279243ff65734b53ffb78c7a20ce5d
REINDEX restores agreement, confirming the index content is the stale part.
VACUUM FULL does not.
2. Generated column contradicts its own definition
==================================================
SET bytea_output='hex';
CREATE TABLE g2(id int primary key, v bytea,
gv text GENERATED ALWAYS AS (v::text) STORED);
INSERT INTO g2(id,v) VALUES (1,'\x41ff');
SET bytea_output='escape';
INSERT INTO g2(id,v) VALUES (2,'\x41ff');
SELECT id, v, gv, v::text FROM g2 ORDER BY id;
id | v | gv | text
----+-------+--------+-------
1 | A\377 | \x41ff | A\377
2 | A\377 | A\377 | A\377
Both rows hold the identical bytea value, yet their generated columns
differ,
and row 1's stored value differs from what its own definition yields in any
current session.
3. Unique index admits two rows holding the same value
======================================================
bytea:
SET bytea_output='hex';
CREATE TABLE u(id int primary key, v bytea);
CREATE UNIQUE INDEX ux ON u ((v::text));
INSERT INTO u VALUES (1,'\x41ff');
SET bytea_output='escape';
INSERT INTO u VALUES (2,'\x41ff'); -- accepted
SELECT id, v, length(v) FROM u ORDER BY id;
id | v | length
----+-------+--------
1 | A\377 | 2
2 | A\377 | 2
SELECT v, count(*) FROM u GROUP BY v;
v | count
-------+-------
A\377 | 2
float8, where the two rows demonstrably hold the same double precision value
rather than merely similar-looking ones:
SET extra_float_digits=0;
CREATE TABLE fu(id int primary key, v float8);
CREATE UNIQUE INDEX fux ON fu ((v::text));
INSERT INTO fu VALUES (1, pi());
SET extra_float_digits=3;
INSERT INTO fu VALUES (2, pi()); -- accepted
SELECT id, v, (v = pi()) AS is_pi FROM fu ORDER BY id;
id | v | is_pi
----+-------------------+-------
1 | 3.141592653589793 | t
2 | 3.141592653589793 | t
SELECT count(*) FROM fu WHERE v = pi();
2
The index still enforces the constraint against new rows, so it is
simultaneously enforcing and violated.
4. CHECK constraint violated by stored data
===========================================
SET bytea_output='hex';
CREATE TABLE h1(id int primary key, v bytea,
CONSTRAINT ckb CHECK (v::text <> '\x41ff'));
INSERT INTO h1 VALUES (1,'\x42fe');
SET bytea_output='escape';
INSERT INTO h1 VALUES (2,'\x41ff'); -- accepted: v::text is 'A\377'
here
SET bytea_output='hex';
SELECT id, v::text, (v::text <> '\x41ff') AS check_holds FROM h1 ORDER BY
id;
id | text | check_holds
----+--------+-------------
1 | \x42fe | t
2 | \x41ff | f
INSERT INTO h1 VALUES (3,'\x41ff');
ERROR: new row for relation "h1" violates check constraint "ckb"
DETAIL: Failing row contains (3, \x41ff).
The table cannot be restored from its own dump:
$ pg_dump -t h1 ... | psql -d target
ERROR: new row for relation "h1" violates check constraint "ckb"
DETAIL: Failing row contains (2, \x41ff).
target=# SELECT count(*) FROM h1;
0
5. Partition constraint violated by stored data
===============================================
CREATE TABLE ip(id int, v float8) PARTITION BY LIST ((v::text));
SET extra_float_digits=0;
CREATE TABLE ipart PARTITION OF ip FOR VALUES IN ('3.14159265358979');
INSERT INTO ip VALUES (1, pi()); -- routed into ipart
SET extra_float_digits=3;
SELECT id, v::text AS key_now,
(v::text = '3.14159265358979') AS constraint_holds FROM ipart;
id | key_now | constraint_holds
----+-------------------+------------------
1 | 3.141592653589793 | f
The row sits in a partition whose constraint it no longer satisfies. It is
invisible to predicate queries on the parent while still counted by an
unqualified scan:
SELECT count(*) FROM ip; -- 1
SELECT count(*) FROM ip WHERE v::text = '3.141592653589793'; -- 0
Inserting the identical value now fails outright:
INSERT INTO ip VALUES (2, pi());
ERROR: no partition of relation "ip" found for row
DETAIL: Partition key of the failing row contains ((v::text)) =
(3.141592653589793).
The same value is therefore insertable or not depending only on a session
GUC,
and the table cannot be restored from its own dump:
$ pg_dump -t ip -t ipart ... | psql -d target
ERROR: new row for relation "ipart" violates partition constraint
DETAIL: Failing row contains (1, 3.141592653589793).
target=# SELECT count(*) FROM ip;
0
Also affected: hash and BRIN indexes
====================================
The staleness is not specific to btree:
SET bytea_output='hex';
CREATE TABLE k1(id int primary key, v bytea);
CREATE INDEX kx ON k1 USING hash ((v::text)); -- and again USING brin
INSERT INTO k1 VALUES (1,'\x41ff'),(2,'\x42fe');
SET bytea_output='escape';
SET enable_seqscan=off;
SELECT count(*) FROM k1 WHERE v::text='A\377'; -- 0
SET enable_indexscan=off; SET enable_bitmapscan=off;
SELECT count(*) FROM k1 WHERE v::text='A\377'; -- 1
Note on pg_dump
===============
pg_dump does not emit a SET for bytea_output or extra_float_digits, so a
restore
uses the client default. The value under which the original index entries or
generated column values were produced is not recorded anywhere, which is why
the
restores above fail rather than reproducing the source state.
What PostgreSQL gets right
==========================
Equivalent probes against genuinely non-immutable operations are correctly
refused, which is why this looks like a marking oversight rather than a
design
decision:
ALTER TABLE t ADD COLUMN d date GENERATED ALWAYS AS (CAST(ts AS date))
STORED;
ERROR: generation expression is not immutable
CREATE INDEX ON t (CAST(ts AS date));
ERROR: functions in index expression must be marked IMMUTABLE
-- to_char with locale-dependent patterns, cash_out, interval_out:
likewise refused
Changes that genuinely alter equality or ordering are also handled
correctly,
including re-validating constraints and rebuilding dependent indexes when a
column's collation changes to a nondeterministic one.
Suggested fix
=============
Mark byteaout, float8out and float4out (and the corresponding casts to text)
STABLE rather than IMMUTABLE, matching date_out, interval_out and cash_out.
Every consequence above then becomes a DDL-time error, as it already is for
the
other GUC-dependent output functions.
I recognise this would reject expressions that some existing schemas may
use, so
a narrower alternative would be to make the text output of these types
independent of the GUC in index and generated-column contexts. That leaves
the
volatility markings inaccurate for other callers, so the first option seems
preferable, but the compatibility trade-off is a judgement for the project.
Prior discussion
================
I searched the mailing list archives, the TODO list and the FAQ and found no
prior report of this. If it overlaps something I missed, I would be glad to
be
pointed at it.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs
2026-08-29 05:38 BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs PG Bug reporting form <noreply@postgresql.org>
@ 2026-09-02 03:08 ` Fujii Masao <masao.fujii@gmail.com>
2026-09-02 03:15 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 15+ messages in thread
From: Fujii Masao @ 2026-09-02 03:08 UTC (permalink / raw)
To: jj-zhang25@mails.tsinghua.edu.cn; pgsql-bugs@lists.postgresql.org
On Tue, Sep 1, 2026 at 10:12 PM PG Bug reporting form
<noreply@postgresql.org> wrote:
> Suggested fix
> =============
>
> Mark byteaout, float8out and float4out (and the corresponding casts to text)
> STABLE rather than IMMUTABLE, matching date_out, interval_out and cash_out.
+1 to making such changes on master, even though they would cause
several compatibility issues for existing users...
OTOH, for the stable branches, such catalog changes would not be
acceptable. So perhaps the practical option might be just document
the issue and how to handle it during version upgrade in the docs and
release notes, for example.
> Prior discussion
> ================
>
> I searched the mailing list archives, the TODO list and the FAQ and found no
> prior report of this. If it overlaps something I missed, I would be glad to
> be
> pointed at it.
The same issue seems to have been reported previously at [1].
Regards,
[1] https://www.postgresql.org/message-id/17398-8fb0c02a60e2e565%40postgresql.org
--
Fujii Masao
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs
2026-08-29 05:38 BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs PG Bug reporting form <noreply@postgresql.org>
2026-09-02 03:08 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Fujii Masao <masao.fujii@gmail.com>
@ 2026-09-02 03:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-09-04 15:54 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Fujii Masao <masao.fujii@gmail.com>
0 siblings, 1 reply; 15+ messages in thread
From: Tom Lane @ 2026-09-02 03:15 UTC (permalink / raw)
To: Fujii Masao <masao.fujii@gmail.com>; +Cc: jj-zhang25@mails.tsinghua.edu.cn; pgsql-bugs@lists.postgresql.org
Fujii Masao <masao.fujii@gmail.com> writes:
> On Tue, Sep 1, 2026 at 10:12 PM PG Bug reporting form
> <noreply@postgresql.org> wrote:
>> Mark byteaout, float8out and float4out (and the corresponding casts to text)
>> STABLE rather than IMMUTABLE, matching date_out, interval_out and cash_out.
> +1 to making such changes on master, even though they would cause
> several compatibility issues for existing users...
I agree with changing this on master, but I see no reason to do
anything whatever in the back branches. There are few situations
where the mutability marking of a datatype output function matters,
and even if it does, you don't see an actual problem unless you
change one of these GUCs mid-query. If it were a big deal,
we would have noticed this ages ago.
regards, tom lane
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs
2026-08-29 05:38 BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs PG Bug reporting form <noreply@postgresql.org>
2026-09-02 03:08 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Fujii Masao <masao.fujii@gmail.com>
2026-09-02 03:15 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Tom Lane <tgl@sss.pgh.pa.us>
@ 2026-09-04 15:54 ` Fujii Masao <masao.fujii@gmail.com>
2026-09-04 17:27 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 15+ messages in thread
From: Fujii Masao @ 2026-09-04 15:54 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: jj-zhang25@mails.tsinghua.edu.cn; pgsql-bugs@lists.postgresql.org
On Wed, Sep 2, 2026 at 12:15 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Fujii Masao <masao.fujii@gmail.com> writes:
> > On Tue, Sep 1, 2026 at 10:12 PM PG Bug reporting form
> > <noreply@postgresql.org> wrote:
> >> Mark byteaout, float8out and float4out (and the corresponding casts to text)
> >> STABLE rather than IMMUTABLE, matching date_out, interval_out and cash_out.
>
> > +1 to making such changes on master, even though they would cause
> > several compatibility issues for existing users...
>
> I agree with changing this on master, but I see no reason to do
> anything whatever in the back branches.
+1. BTW, what about v19? Since it hasn't been released yet, it's OK to
make this change there, or it's too late even for v19?
On further review, point_out, ISTM that lseg_out, path_out, box_out, poly_out,
circle_out, and line_out are marked IMMUTABLE but depend on extra_float_digits.
Similarly, pg_mcv_list_out is marked IMMUTABLE but depends on bytea_output.
Perhaps we should fix these at the same time...
Regards,
--
Fujii Masao
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs
2026-08-29 05:38 BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs PG Bug reporting form <noreply@postgresql.org>
2026-09-02 03:08 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Fujii Masao <masao.fujii@gmail.com>
2026-09-02 03:15 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Tom Lane <tgl@sss.pgh.pa.us>
2026-09-04 15:54 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Fujii Masao <masao.fujii@gmail.com>
@ 2026-09-04 17:27 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-09-08 08:34 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Fujii Masao <masao.fujii@gmail.com>
0 siblings, 1 reply; 15+ messages in thread
From: Tom Lane @ 2026-09-04 17:27 UTC (permalink / raw)
To: Fujii Masao <masao.fujii@gmail.com>; +Cc: jj-zhang25@mails.tsinghua.edu.cn; pgsql-bugs@lists.postgresql.org
Fujii Masao <masao.fujii@gmail.com> writes:
> On Wed, Sep 2, 2026 at 12:15 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I agree with changing this on master, but I see no reason to do
>> anything whatever in the back branches.
> +1. BTW, what about v19? Since it hasn't been released yet, it's OK to
> make this change there, or it's too late even for v19?
It's been wrong for so long that I'm not that rushed to fix it in 19.
We might find that there are consequences to be dealt with, so better
to let the fix settle in master.
> On further review, point_out, ISTM that lseg_out, path_out, box_out, poly_out,
> circle_out, and line_out are marked IMMUTABLE but depend on extra_float_digits.
> Similarly, pg_mcv_list_out is marked IMMUTABLE but depends on bytea_output.
> Perhaps we should fix these at the same time...
Agreed.
regards, tom lane
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs
2026-08-29 05:38 BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs PG Bug reporting form <noreply@postgresql.org>
2026-09-02 03:08 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Fujii Masao <masao.fujii@gmail.com>
2026-09-02 03:15 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Tom Lane <tgl@sss.pgh.pa.us>
2026-09-04 15:54 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Fujii Masao <masao.fujii@gmail.com>
2026-09-04 17:27 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Tom Lane <tgl@sss.pgh.pa.us>
@ 2026-09-08 08:34 ` Fujii Masao <masao.fujii@gmail.com>
2026-10-19 12:45 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Peter Eisentraut <peter@eisentraut.org>
0 siblings, 1 reply; 15+ messages in thread
From: Fujii Masao @ 2026-09-08 08:34 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: jj-zhang25@mails.tsinghua.edu.cn; pgsql-bugs@lists.postgresql.org
On Sat, Sep 5, 2026 at 2:27 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> It's been wrong for so long that I'm not that rushed to fix it in 19.
> We might find that there are consequences to be dealt with, so better
> to let the fix settle in master.
Sounds reasonable.
> > On further review, point_out, ISTM that lseg_out, path_out, box_out, poly_out,
> > circle_out, and line_out are marked IMMUTABLE but depend on extra_float_digits.
> > Similarly, pg_mcv_list_out is marked IMMUTABLE but depends on bytea_output.
> > Perhaps we should fix these at the same time...
>
> Agreed.
Attached patch marks those GUC-dependent type output functions as STABLE.
In addition to the functions I mentioned upthread, I found that
brin_minmax_multi_summary_out calls the summarized type's output
function, so its result can depend on the GUCs. The patch marks that
function STABLE as well.
Regards,
--
Fujii Masao
Attachments:
v1-0001-Mark-GUC-dependent-type-output-functions-as-STABL.patch (application/octet-stream, 6.7K) download
From 2bed6878bbc0a7b0e2a9dda0cdd1b542342cc0e1 Mon Sep 17 00:00:00 2001
From: Fujii Masao <fujii@postgresql.org>
Date: Tue, 8 Sep 2026 14:47:00 +0900
Subject: [PATCH v1] Mark GUC-dependent type output functions as STABLE
byteaout and pg_mcv_list_out depend on bytea_output GUC, while
float4out, float8out, and the geometric type output functions depend
on extra_float_digits GUC. The BRIN minmax-multi summary output
function also calls the summarized type's output function, so its result
can depend on the type's output settings.
Mark these functions STABLE instead of IMMUTABLE to reflect their
dependence on session settings. This prevents them from being used in
expressions that require immutability, such as expression indexes,
generated columns, and partition keys.
---
src/include/catalog/pg_proc.dat | 46 ++++++++++++++++-----------------
1 file changed, 23 insertions(+), 23 deletions(-)
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 960763ee50b..7f54f92b553 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -49,8 +49,8 @@
proname => 'byteain', prorettype => 'bytea', proargtypes => 'cstring',
prosrc => 'byteain' },
{ oid => '31', descr => 'I/O',
- proname => 'byteaout', prorettype => 'cstring', proargtypes => 'bytea',
- prosrc => 'byteaout' },
+ proname => 'byteaout', provolatile => 's', prorettype => 'cstring',
+ proargtypes => 'bytea', prosrc => 'byteaout' },
{ oid => '1245', descr => 'I/O',
proname => 'charin', prorettype => 'char', proargtypes => 'cstring',
prosrc => 'charin' },
@@ -349,26 +349,26 @@
proname => 'point_in', prorettype => 'point', proargtypes => 'cstring',
prosrc => 'point_in' },
{ oid => '118', descr => 'I/O',
- proname => 'point_out', prorettype => 'cstring', proargtypes => 'point',
- prosrc => 'point_out' },
+ proname => 'point_out', provolatile => 's', prorettype => 'cstring',
+ proargtypes => 'point', prosrc => 'point_out' },
{ oid => '119', descr => 'I/O',
proname => 'lseg_in', prorettype => 'lseg', proargtypes => 'cstring',
prosrc => 'lseg_in' },
{ oid => '120', descr => 'I/O',
- proname => 'lseg_out', prorettype => 'cstring', proargtypes => 'lseg',
- prosrc => 'lseg_out' },
+ proname => 'lseg_out', provolatile => 's', prorettype => 'cstring',
+ proargtypes => 'lseg', prosrc => 'lseg_out' },
{ oid => '121', descr => 'I/O',
proname => 'path_in', prorettype => 'path', proargtypes => 'cstring',
prosrc => 'path_in' },
{ oid => '122', descr => 'I/O',
- proname => 'path_out', prorettype => 'cstring', proargtypes => 'path',
- prosrc => 'path_out' },
+ proname => 'path_out', provolatile => 's', prorettype => 'cstring',
+ proargtypes => 'path', prosrc => 'path_out' },
{ oid => '123', descr => 'I/O',
proname => 'box_in', prorettype => 'box', proargtypes => 'cstring',
prosrc => 'box_in' },
{ oid => '124', descr => 'I/O',
- proname => 'box_out', prorettype => 'cstring', proargtypes => 'box',
- prosrc => 'box_out' },
+ proname => 'box_out', provolatile => 's', prorettype => 'cstring',
+ proargtypes => 'box', prosrc => 'box_out' },
{ oid => '125',
proname => 'box_overlap', prorettype => 'bool', proargtypes => 'box box',
prosrc => 'box_overlap' },
@@ -587,8 +587,8 @@
proname => 'float4in', prorettype => 'float4', proargtypes => 'cstring',
prosrc => 'float4in' },
{ oid => '201', descr => 'I/O',
- proname => 'float4out', prorettype => 'cstring', proargtypes => 'float4',
- prosrc => 'float4out' },
+ proname => 'float4out', provolatile => 's', prorettype => 'cstring',
+ proargtypes => 'float4', prosrc => 'float4out' },
{ oid => '202',
proname => 'float4mul', prorettype => 'float4',
proargtypes => 'float4 float4', prosrc => 'float4mul' },
@@ -628,8 +628,8 @@
proname => 'float8in', prorettype => 'float8', proargtypes => 'cstring',
prosrc => 'float8in' },
{ oid => '215', descr => 'I/O',
- proname => 'float8out', prorettype => 'cstring', proargtypes => 'float8',
- prosrc => 'float8out' },
+ proname => 'float8out', provolatile => 's', prorettype => 'cstring',
+ proargtypes => 'float8', prosrc => 'float8out' },
{ oid => '216',
proname => 'float8mul', prorettype => 'float8',
proargtypes => 'float8 float8', prosrc => 'float8mul' },
@@ -995,8 +995,8 @@
proname => 'poly_in', prorettype => 'polygon', proargtypes => 'cstring',
prosrc => 'poly_in' },
{ oid => '348', descr => 'I/O',
- proname => 'poly_out', prorettype => 'cstring', proargtypes => 'polygon',
- prosrc => 'poly_out' },
+ proname => 'poly_out', provolatile => 's', prorettype => 'cstring',
+ proargtypes => 'polygon', prosrc => 'poly_out' },
{ oid => '350', descr => 'less-equal-greater',
proname => 'btint2cmp', proleakproof => 't', prorettype => 'int4',
@@ -3215,8 +3215,8 @@
proname => 'circle_in', prorettype => 'circle', proargtypes => 'cstring',
prosrc => 'circle_in' },
{ oid => '1451', descr => 'I/O',
- proname => 'circle_out', prorettype => 'cstring', proargtypes => 'circle',
- prosrc => 'circle_out' },
+ proname => 'circle_out', provolatile => 's', prorettype => 'cstring',
+ proargtypes => 'circle', prosrc => 'circle_out' },
{ oid => '1452',
proname => 'circle_same', prorettype => 'bool',
proargtypes => 'circle circle', prosrc => 'circle_same' },
@@ -3337,8 +3337,8 @@
proname => 'line_in', prorettype => 'line', proargtypes => 'cstring',
prosrc => 'line_in' },
{ oid => '1491', descr => 'I/O',
- proname => 'line_out', prorettype => 'cstring', proargtypes => 'line',
- prosrc => 'line_out' },
+ proname => 'line_out', provolatile => 's', prorettype => 'cstring',
+ proargtypes => 'line', prosrc => 'line_out' },
{ oid => '1492',
proname => 'line_eq', prorettype => 'bool', proargtypes => 'line line',
prosrc => 'line_eq' },
@@ -5534,7 +5534,7 @@
proname => 'pg_mcv_list_in', prorettype => 'pg_mcv_list',
proargtypes => 'cstring', prosrc => 'pg_mcv_list_in' },
{ oid => '5019', descr => 'I/O',
- proname => 'pg_mcv_list_out', prorettype => 'cstring',
+ proname => 'pg_mcv_list_out', provolatile => 's', prorettype => 'cstring',
proargtypes => 'pg_mcv_list', prosrc => 'pg_mcv_list_out' },
{ oid => '5020', descr => 'I/O',
proname => 'pg_mcv_list_recv', provolatile => 's',
@@ -12625,8 +12625,8 @@
prorettype => 'pg_brin_minmax_multi_summary', proargtypes => 'cstring',
prosrc => 'brin_minmax_multi_summary_in' },
{ oid => '4639', descr => 'I/O',
- proname => 'brin_minmax_multi_summary_out', prorettype => 'cstring',
- proargtypes => 'pg_brin_minmax_multi_summary',
+ proname => 'brin_minmax_multi_summary_out', provolatile => 's',
+ prorettype => 'cstring', proargtypes => 'pg_brin_minmax_multi_summary',
prosrc => 'brin_minmax_multi_summary_out' },
{ oid => '4640', descr => 'I/O',
proname => 'brin_minmax_multi_summary_recv', provolatile => 's',
--
2.55.0
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs
2026-08-29 05:38 BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs PG Bug reporting form <noreply@postgresql.org>
2026-09-02 03:08 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Fujii Masao <masao.fujii@gmail.com>
2026-09-02 03:15 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Tom Lane <tgl@sss.pgh.pa.us>
2026-09-04 15:54 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Fujii Masao <masao.fujii@gmail.com>
2026-09-04 17:27 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Tom Lane <tgl@sss.pgh.pa.us>
2026-09-08 08:34 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Fujii Masao <masao.fujii@gmail.com>
@ 2026-10-19 12:45 ` Peter Eisentraut <peter@eisentraut.org>
2026-10-20 06:16 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Fujii Masao <masao.fujii@gmail.com>
0 siblings, 1 reply; 15+ messages in thread
From: Peter Eisentraut @ 2026-10-19 12:45 UTC (permalink / raw)
To: Fujii Masao <masao.fujii@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: jj-zhang25@mails.tsinghua.edu.cn; pgsql-bugs@lists.postgresql.org
On 08.09.26 10:34, Fujii Masao wrote:
> On Sat, Sep 5, 2026 at 2:27 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> It's been wrong for so long that I'm not that rushed to fix it in 19.
>> We might find that there are consequences to be dealt with, so better
>> to let the fix settle in master.
>
> Sounds reasonable.
>
>
>>> On further review, point_out, ISTM that lseg_out, path_out, box_out, poly_out,
>>> circle_out, and line_out are marked IMMUTABLE but depend on extra_float_digits.
>>> Similarly, pg_mcv_list_out is marked IMMUTABLE but depends on bytea_output.
>>> Perhaps we should fix these at the same time...
>>
>> Agreed.
>
> Attached patch marks those GUC-dependent type output functions as STABLE.
>
> In addition to the functions I mentioned upthread, I found that
> brin_minmax_multi_summary_out calls the summarized type's output
> function, so its result can depend on the GUCs. The patch marks that
> function STABLE as well.
cube_out() in contrib/cube/ should also be changed. Other than that,
this set looks correct to me.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs
2026-08-29 05:38 BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs PG Bug reporting form <noreply@postgresql.org>
2026-09-02 03:08 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Fujii Masao <masao.fujii@gmail.com>
2026-09-02 03:15 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Tom Lane <tgl@sss.pgh.pa.us>
2026-09-04 15:54 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Fujii Masao <masao.fujii@gmail.com>
2026-09-04 17:27 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Tom Lane <tgl@sss.pgh.pa.us>
2026-09-08 08:34 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Fujii Masao <masao.fujii@gmail.com>
2026-10-19 12:45 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Peter Eisentraut <peter@eisentraut.org>
@ 2026-10-20 06:16 ` Fujii Masao <masao.fujii@gmail.com>
2026-10-20 13:20 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Staroverov Ilja <i.staroverov@ftdata.ru>
0 siblings, 1 reply; 15+ messages in thread
From: Fujii Masao @ 2026-10-20 06:16 UTC (permalink / raw)
To: Peter Eisentraut <peter@eisentraut.org>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; jj-zhang25@mails.tsinghua.edu.cn; pgsql-bugs@lists.postgresql.org
On Mon, Oct 19, 2026 at 9:45 PM Peter Eisentraut <peter@eisentraut.org> wrote:
> cube_out() in contrib/cube/ should also be changed.
Thanks for the review! The attached patch marks cube_out() as STABLE.
Since this change bumps the cube extension version, I think it's better to
commit it separately from the pg_proc.dat changes that mark other
GUC-dependent type output functions as STABLE. That's why I kept it in
a separate patch.
> Other than that,
> this set looks correct to me.
I've pushed this patch. Thanks!
Regards,
--
Fujii Masao
Attachments:
v1-0001-cube-Mark-cube_out-as-STABLE.patch (application/octet-stream, 2.7K) download
From 7f12823a292de153860f125483836869ed6d778d Mon Sep 17 00:00:00 2001
From: Fujii Masao <fujii@postgresql.org>
Date: Tue, 20 Oct 2026 13:27:46 +0900
Subject: [PATCH v1] cube: Mark cube_out() as STABLE
cube_out() calls float8out_internal(), so its result depends on the
extra_float_digits setting. Mark it STABLE rather than IMMUTABLE to
reject new expression indexes, generated columns, and partition keys
that rely on this conversion being immutable.
Note that existing dependent objects are not revalidated by this
change, and their definitions may need to be revised before restoring
into an installation using cube 1.6.
Bump the cube extension version to 1.6.
Suggested-by: Peter Eisentraut <peter@eisentraut.org>
Author: Fujii Masao <masao.fujii@gmail.com>
Discussion: https://postgr.es/m/61ca987c-8bd1-4380-b81a-72c94c36b41c@eisentraut.org
---
contrib/cube/Makefile | 2 +-
contrib/cube/cube--1.5--1.6.sql | 7 +++++++
contrib/cube/cube.control | 2 +-
contrib/cube/meson.build | 1 +
4 files changed, 10 insertions(+), 2 deletions(-)
create mode 100644 contrib/cube/cube--1.5--1.6.sql
diff --git a/contrib/cube/Makefile b/contrib/cube/Makefile
index dfb0d806e4b..bee4a77340d 100644
--- a/contrib/cube/Makefile
+++ b/contrib/cube/Makefile
@@ -9,7 +9,7 @@ OBJS = \
EXTENSION = cube
DATA = cube--1.2.sql cube--1.2--1.3.sql cube--1.3--1.4.sql cube--1.4--1.5.sql \
- cube--1.1--1.2.sql cube--1.0--1.1.sql
+ cube--1.5--1.6.sql cube--1.1--1.2.sql cube--1.0--1.1.sql
PGFILEDESC = "cube - multidimensional cube data type"
HEADERS = cubedata.h
diff --git a/contrib/cube/cube--1.5--1.6.sql b/contrib/cube/cube--1.5--1.6.sql
new file mode 100644
index 00000000000..99090546b9f
--- /dev/null
+++ b/contrib/cube/cube--1.5--1.6.sql
@@ -0,0 +1,7 @@
+/* contrib/cube/cube--1.5--1.6.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION cube UPDATE TO '1.6'" to load this file. \quit
+
+-- Output depends on extra_float_digits.
+ALTER FUNCTION cube_out(cube) STABLE;
diff --git a/contrib/cube/cube.control b/contrib/cube/cube.control
index 50427ec1170..7797f7e08d4 100644
--- a/contrib/cube/cube.control
+++ b/contrib/cube/cube.control
@@ -1,6 +1,6 @@
# cube extension
comment = 'data type for multidimensional cubes'
-default_version = '1.5'
+default_version = '1.6'
module_pathname = '$libdir/cube'
relocatable = true
trusted = true
diff --git a/contrib/cube/meson.build b/contrib/cube/meson.build
index 6526091c688..d0398f73a8a 100644
--- a/contrib/cube/meson.build
+++ b/contrib/cube/meson.build
@@ -40,6 +40,7 @@ install_data(
'cube--1.2--1.3.sql',
'cube--1.3--1.4.sql',
'cube--1.4--1.5.sql',
+ 'cube--1.5--1.6.sql',
kwargs: contrib_data_args,
)
--
2.56.0
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs
2026-08-29 05:38 BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs PG Bug reporting form <noreply@postgresql.org>
2026-09-02 03:08 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Fujii Masao <masao.fujii@gmail.com>
2026-09-02 03:15 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Tom Lane <tgl@sss.pgh.pa.us>
2026-09-04 15:54 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Fujii Masao <masao.fujii@gmail.com>
2026-09-04 17:27 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Tom Lane <tgl@sss.pgh.pa.us>
2026-09-08 08:34 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Fujii Masao <masao.fujii@gmail.com>
2026-10-19 12:45 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Peter Eisentraut <peter@eisentraut.org>
2026-10-20 06:16 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Fujii Masao <masao.fujii@gmail.com>
@ 2026-10-20 13:20 ` Staroverov Ilja <i.staroverov@ftdata.ru>
2026-10-20 16:05 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Fujii Masao <masao.fujii@gmail.com>
0 siblings, 1 reply; 15+ messages in thread
From: Staroverov Ilja @ 2026-10-20 13:20 UTC (permalink / raw)
To: Fujii Masao <masao.fujii@gmail.com>; Peter Eisentraut <peter@eisentraut.org>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; jj-zhang25@mails.tsinghua.edu.cn <jj-zhang25@mails.tsinghua.edu.cn>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Hi,
hstore has the same problem. hstore(record) calls the output functions
of the record's column types, and populate_record(), which the #=
operator also uses, calls their input functions. But both are marked
IMMUTABLE, unlike the core functions that do the same, such as
row_to_json() and jsonb_populate_record(), which are STABLE. So they
are accepted in expression indexes and generated columns, although
their results depend on TimeZone, DateStyle, extra_float_digits and so
on. On master, as of e75fcc986:
CREATE EXTENSION hstore;
SET TimeZone = 'UTC';
CREATE TABLE ev (ts timestamptz);
CREATE INDEX ON ev ((hstore(ROW(ts)) -> 'f1'));
INSERT INTO ev VALUES ('2026-01-01 00:00+00');
SET TimeZone = 'Europe/Moscow';
SELECT count(*) FROM ev
WHERE hstore(ROW(ts)) -> 'f1' = '2026-01-01 03:00:00+03';
count
-------
0
The plan uses the index; with enable_indexscan and enable_bitmapscan
off, the same query returns 1. Likewise, a stored generated column
defined as (populate_record(NULL::rt, h)).ts, where rt is a composite
type with a timestamptz field, gets different values for the same h,
depending on the TimeZone at insert time.
It also leaves a way around e75fcc986: with x float8, ((x / 3)::text)
is now rejected in an index expression, but (hstore(ROW(x / 3)) -> 'f1')
is still accepted.
Both functions have been IMMUTABLE since they were added in 9.0. The
attached patch marks them STABLE, along the lines of the cube patch
upthread, bumps the hstore version to 1.9, and adds a test that such
indexes are rejected. Like the other changes here, I'd think this is
for master only.
I also checked core and the other contrib modules for IMMUTABLE
functions that call the I/O functions of arbitrary types, and found no
others.
--
Ilya Staroverov
Attachments:
v1-0001-hstore-Mark-hstore-record-and-populate_record-as-.patch (application/octet-stream, 5.0K) download
From dab304c28313d45703c8ffd531a72afd8c749346 Mon Sep 17 00:00:00 2001
From: Ilya Staroverov <i.staroverov@ftdata.ru>
Date: Tue, 20 Oct 2026 15:09:48 +0300
Subject: [PATCH v1] hstore: Mark hstore(record) and populate_record() as
STABLE
hstore(record) calls the output functions of the record's column
types, and populate_record(), which the #= operator also uses, calls
their input functions, so their results can depend on settings such as
TimeZone, DateStyle or extra_float_digits. Mark them STABLE rather
than IMMUTABLE, like the core functions that do the same, such as
row_to_json() and jsonb_populate_record(), to reject new expression
indexes, generated columns, and partition keys that rely on them being
immutable.
Note that existing dependent objects are not revalidated by this
change, and their definitions may need to be revised before restoring
into an installation using hstore 1.9.
Bump the hstore extension version to 1.9.
Author: Ilya Staroverov <i.staroverov@ftdata.ru>
Discussion: https://postgr.es/m/19644-d6db3fb5e5ad92f4@postgresql.org
---
contrib/hstore/Makefile | 1 +
contrib/hstore/expected/hstore.out | 11 +++++++++++
contrib/hstore/hstore--1.8--1.9.sql | 9 +++++++++
contrib/hstore/hstore.control | 2 +-
contrib/hstore/meson.build | 1 +
contrib/hstore/sql/hstore.sql | 6 ++++++
6 files changed, 29 insertions(+), 1 deletion(-)
create mode 100644 contrib/hstore/hstore--1.8--1.9.sql
diff --git a/contrib/hstore/Makefile b/contrib/hstore/Makefile
index 48ee98f0d..b02f3fda6 100644
--- a/contrib/hstore/Makefile
+++ b/contrib/hstore/Makefile
@@ -12,6 +12,7 @@ OBJS = \
EXTENSION = hstore
DATA = hstore--1.4.sql \
+ hstore--1.8--1.9.sql \
hstore--1.7--1.8.sql \
hstore--1.6--1.7.sql \
hstore--1.5--1.6.sql \
diff --git a/contrib/hstore/expected/hstore.out b/contrib/hstore/expected/hstore.out
index 9713372b7..58c280c19 100644
--- a/contrib/hstore/expected/hstore.out
+++ b/contrib/hstore/expected/hstore.out
@@ -1134,6 +1134,17 @@ select v #= h from testhstore1 v, (values (hstore 'a=>123',1),('b=>foo,c=>3.21',
(1,foo,1.2,3,0)
(5 rows)
+-- hstore(record) and populate_record() call the I/O functions of the
+-- column types, whose results can depend on settings, so they are not
+-- immutable
+create index on testhstore0 ((hstore(row(a, d)))); -- error
+ERROR: functions in index expression must be marked IMMUTABLE
+LINE 1: create index on testhstore0 ((hstore(row(a, d))));
+ ^
+create index on testhstore0 (((populate_record(null::testhstore0, hstore('d', b))).d)); -- error
+ERROR: functions in index expression must be marked IMMUTABLE
+LINE 1: create index on testhstore0 (((populate_record(null::testhst...
+ ^
-- keys/values
select akeys('aa=>1 , b=>2, cq=>3'::hstore || 'cq=>l, b=>g, fg=>f');
akeys
diff --git a/contrib/hstore/hstore--1.8--1.9.sql b/contrib/hstore/hstore--1.8--1.9.sql
new file mode 100644
index 000000000..25c00b0d9
--- /dev/null
+++ b/contrib/hstore/hstore--1.8--1.9.sql
@@ -0,0 +1,9 @@
+/* contrib/hstore/hstore--1.8--1.9.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION hstore UPDATE TO '1.9'" to load this file. \quit
+
+-- These call the output or input functions of the record's column types,
+-- whose results can depend on settings such as TimeZone.
+ALTER FUNCTION hstore(record) STABLE;
+ALTER FUNCTION populate_record(anyelement, hstore) STABLE;
diff --git a/contrib/hstore/hstore.control b/contrib/hstore/hstore.control
index 89e3c746c..b73c28aa4 100644
--- a/contrib/hstore/hstore.control
+++ b/contrib/hstore/hstore.control
@@ -1,6 +1,6 @@
# hstore extension
comment = 'data type for storing sets of (key, value) pairs'
-default_version = '1.8'
+default_version = '1.9'
module_pathname = '$libdir/hstore'
relocatable = true
trusted = true
diff --git a/contrib/hstore/meson.build b/contrib/hstore/meson.build
index 6175abc70..62c540e77 100644
--- a/contrib/hstore/meson.build
+++ b/contrib/hstore/meson.build
@@ -35,6 +35,7 @@ install_data(
'hstore--1.5--1.6.sql',
'hstore--1.6--1.7.sql',
'hstore--1.7--1.8.sql',
+ 'hstore--1.8--1.9.sql',
kwargs: contrib_data_args,
)
diff --git a/contrib/hstore/sql/hstore.sql b/contrib/hstore/sql/hstore.sql
index ac929e075..d4fcaec19 100644
--- a/contrib/hstore/sql/hstore.sql
+++ b/contrib/hstore/sql/hstore.sql
@@ -259,6 +259,12 @@ select null::testhstore0 #= hstore '';
select null::testhstore1 #= hstore '';
select v #= h from testhstore1 v, (values (hstore 'a=>123',1),('b=>foo,c=>3.21',2),('a=>null',3),('e=>123',4),('f=>blah',5)) x(h,i) order by i;
+-- hstore(record) and populate_record() call the I/O functions of the
+-- column types, whose results can depend on settings, so they are not
+-- immutable
+create index on testhstore0 ((hstore(row(a, d)))); -- error
+create index on testhstore0 (((populate_record(null::testhstore0, hstore('d', b))).d)); -- error
+
-- keys/values
select akeys('aa=>1 , b=>2, cq=>3'::hstore || 'cq=>l, b=>g, fg=>f');
select akeys('""=>1');
--
2.43.0
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs
2026-08-29 05:38 BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs PG Bug reporting form <noreply@postgresql.org>
2026-09-02 03:08 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Fujii Masao <masao.fujii@gmail.com>
2026-09-02 03:15 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Tom Lane <tgl@sss.pgh.pa.us>
2026-09-04 15:54 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Fujii Masao <masao.fujii@gmail.com>
2026-09-04 17:27 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Tom Lane <tgl@sss.pgh.pa.us>
2026-09-08 08:34 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Fujii Masao <masao.fujii@gmail.com>
2026-10-19 12:45 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Peter Eisentraut <peter@eisentraut.org>
2026-10-20 06:16 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Fujii Masao <masao.fujii@gmail.com>
2026-10-20 13:20 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Staroverov Ilja <i.staroverov@ftdata.ru>
@ 2026-10-20 16:05 ` Fujii Masao <masao.fujii@gmail.com>
2026-10-20 16:18 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 15+ messages in thread
From: Fujii Masao @ 2026-10-20 16:05 UTC (permalink / raw)
To: Staroverov Ilja <i.staroverov@ftdata.ru>; +Cc: Peter Eisentraut <peter@eisentraut.org>; Tom Lane <tgl@sss.pgh.pa.us>; jj-zhang25@mails.tsinghua.edu.cn <jj-zhang25@mails.tsinghua.edu.cn>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
On Tue, Oct 20, 2026 at 10:20 PM Staroverov Ilja <i.staroverov@ftdata.ru> wrote:
> The
> attached patch marks them STABLE, along the lines of the cube patch
> upthread, bumps the hstore version to 1.9, and adds a test that such
> indexes are rejected. Like the other changes here, I'd think this is
> for master only.
Thanks for the patch!
I agree that these hstore functions should be marked STABLE.
The added test checks the existing rejection of non-immutable functions
in index expressions. I don't think it's necessary.
> I also checked core and the other contrib modules for IMMUTABLE
> functions that call the I/O functions of arbitrary types, and found no
> others.
Codex reported two other cases:
- Both xpath() overloads are marked IMMUTABLE, but use float8out() for
numeric results, so their output depends on extra_float_digits.
- quote_ident(text) is marked IMMUTABLE, but calls quote_identifier(),
whose output depends on quote_all_identifiers.
Should these functions also be marked STABLE? Thoughts?
Regards,
--
Fujii Masao
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs
2026-08-29 05:38 BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs PG Bug reporting form <noreply@postgresql.org>
2026-09-02 03:08 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Fujii Masao <masao.fujii@gmail.com>
2026-09-02 03:15 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Tom Lane <tgl@sss.pgh.pa.us>
2026-09-04 15:54 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Fujii Masao <masao.fujii@gmail.com>
2026-09-04 17:27 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Tom Lane <tgl@sss.pgh.pa.us>
2026-09-08 08:34 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Fujii Masao <masao.fujii@gmail.com>
2026-10-19 12:45 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Peter Eisentraut <peter@eisentraut.org>
2026-10-20 06:16 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Fujii Masao <masao.fujii@gmail.com>
2026-10-20 13:20 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Staroverov Ilja <i.staroverov@ftdata.ru>
2026-10-20 16:05 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Fujii Masao <masao.fujii@gmail.com>
@ 2026-10-20 16:18 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-10-21 02:39 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Fujii Masao <masao.fujii@gmail.com>
0 siblings, 1 reply; 15+ messages in thread
From: Tom Lane @ 2026-10-20 16:18 UTC (permalink / raw)
To: Fujii Masao <masao.fujii@gmail.com>; +Cc: Staroverov Ilja <i.staroverov@ftdata.ru>; Peter Eisentraut <peter@eisentraut.org>; jj-zhang25@mails.tsinghua.edu.cn <jj-zhang25@mails.tsinghua.edu.cn>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Fujii Masao <masao.fujii@gmail.com> writes:
> Codex reported two other cases:
> - Both xpath() overloads are marked IMMUTABLE, but use float8out() for
> numeric results, so their output depends on extra_float_digits.
> - quote_ident(text) is marked IMMUTABLE, but calls quote_identifier(),
> whose output depends on quote_all_identifiers.
> Should these functions also be marked STABLE? Thoughts?
I don't mind marking xpath() as STABLE, but I wonder if doing so for
quote_ident() would cause performance issues. But a quick look
through our frontend programs didn't turn up any usages that look
like they'd be candidates for constant-folding, so maybe it's fine.
I agree we can't just leave it as-is, so the alternative would be
to force the SQL function to use one behavior or the other. That
might make people unhappy in a different way.
regards, tom lane
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs
2026-08-29 05:38 BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs PG Bug reporting form <noreply@postgresql.org>
2026-09-02 03:08 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Fujii Masao <masao.fujii@gmail.com>
2026-09-02 03:15 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Tom Lane <tgl@sss.pgh.pa.us>
2026-09-04 15:54 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Fujii Masao <masao.fujii@gmail.com>
2026-09-04 17:27 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Tom Lane <tgl@sss.pgh.pa.us>
2026-09-08 08:34 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Fujii Masao <masao.fujii@gmail.com>
2026-10-19 12:45 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Peter Eisentraut <peter@eisentraut.org>
2026-10-20 06:16 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Fujii Masao <masao.fujii@gmail.com>
2026-10-20 13:20 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Staroverov Ilja <i.staroverov@ftdata.ru>
2026-10-20 16:05 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Fujii Masao <masao.fujii@gmail.com>
2026-10-20 16:18 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Tom Lane <tgl@sss.pgh.pa.us>
@ 2026-10-21 02:39 ` Fujii Masao <masao.fujii@gmail.com>
2026-10-21 04:40 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Staroverov Ilja <i.staroverov@ftdata.ru>
0 siblings, 1 reply; 15+ messages in thread
From: Fujii Masao @ 2026-10-21 02:39 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Staroverov Ilja <i.staroverov@ftdata.ru>; Peter Eisentraut <peter@eisentraut.org>; jj-zhang25@mails.tsinghua.edu.cn <jj-zhang25@mails.tsinghua.edu.cn>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
On Wed, Oct 21, 2026 at 1:18 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I don't mind marking xpath() as STABLE, but I wonder if doing so for
> quote_ident() would cause performance issues. But a quick look
> through our frontend programs didn't turn up any usages that look
> like they'd be candidates for constant-folding, so maybe it's fine.
So, simply marking quote_ident() as STABLE seems better to me.
Attached 0003 patch marks both xpath() overloads and quote_ident()
as STABLE.
I'm also attaching v2 of the cube and hstore patches.
The cube patch is unchanged from v1. The hstore patch removes the
unnecessary test.
Regards,
--
Fujii Masao
Attachments: download all (3 files, .tar.gz)
v2-0003-Mark-xpath-and-quote_ident-as-STABLE.patch (application/octet-stream, 3.9K) download
From 338fd36df48a178fdda18bf8cc83ba84554ea2f8 Mon Sep 17 00:00:00 2001
From: Fujii Masao <fujii@postgresql.org>
Date: Wed, 21 Oct 2026 11:14:03 +0900
Subject: [PATCH v2 3/3] Mark xpath and quote_ident as STABLE
xpath calls float8out to format numeric results, so its output can
depend on extra_float_digits GUC. quote_ident calls quote_identifier,
whose output depends on quote_all_identifiers GUC.
Mark both xpath overloads and quote_ident STABLE instead of IMMUTABLE
to reflect their dependence on session settings. This prevents them
from being used in expressions that require immutability, such as
expression indexes, generated columns, and partition keys.
Do not backpatch, since this changes catalog entries and can break
existing schemas that rely on these functions being immutable.
Author: Fujii Masao <masao.fujii@gmail.com>
Discussion: https://postgr.es/m/871538.1792513085@sss.pgh.pa.us
---
src/backend/catalog/system_functions.sql | 2 +-
src/include/catalog/pg_proc.dat | 10 +++++-----
src/test/regress/expected/xml.out | 2 --
3 files changed, 6 insertions(+), 8 deletions(-)
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index c3c0a6e84ed..de026a1a30c 100644
--- a/src/backend/catalog/system_functions.sql
+++ b/src/backend/catalog/system_functions.sql
@@ -243,7 +243,7 @@ RETURN $2 + $1;
CREATE OR REPLACE FUNCTION xpath(text, xml)
RETURNS xml[]
LANGUAGE sql
- IMMUTABLE PARALLEL SAFE STRICT COST 1
+ STABLE PARALLEL SAFE STRICT COST 1
RETURN xpath($1, $2, '{}'::text[]);
CREATE OR REPLACE FUNCTION xpath_exists(text, xml)
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 1f21a8eb6d4..59ad6da9edd 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -4846,8 +4846,8 @@
proargtypes => 'interval text', prosrc => 'interval_to_char' },
{ oid => '1282', descr => 'quote an identifier for usage in a querystring',
- proname => 'quote_ident', prorettype => 'text', proargtypes => 'text',
- prosrc => 'quote_ident' },
+ proname => 'quote_ident', provolatile => 's', prorettype => 'text',
+ proargtypes => 'text', prosrc => 'quote_ident' },
{ oid => '1283', descr => 'quote a literal for usage in a querystring',
proname => 'quote_literal', prorettype => 'text', proargtypes => 'text',
prosrc => 'quote_literal' },
@@ -9443,10 +9443,10 @@
{ oid => '2931',
descr => 'evaluate XPath expression, with namespaces support',
- proname => 'xpath', prorettype => '_xml', proargtypes => 'text xml _text',
- prosrc => 'xpath' },
+ proname => 'xpath', provolatile => 's', prorettype => '_xml',
+ proargtypes => 'text xml _text', prosrc => 'xpath' },
{ oid => '2932', descr => 'evaluate XPath expression',
- proname => 'xpath', prolang => 'sql', prorettype => '_xml',
+ proname => 'xpath', prolang => 'sql', provolatile => 's', prorettype => '_xml',
proargtypes => 'text xml', prosrc => 'see system_functions.sql' },
{ oid => '2614', descr => 'test XML value against XPath expression',
diff --git a/src/test/regress/expected/xml.out b/src/test/regress/expected/xml.out
index 6998745ee03..fbcc54448be 100644
--- a/src/test/regress/expected/xml.out
+++ b/src/test/regress/expected/xml.out
@@ -907,7 +907,6 @@ SELECT xpath(NULL, NULL) IS NULL FROM xmltest;
SELECT xpath('', '<!-- error -->');
ERROR: empty XPath expression
-CONTEXT: SQL function "xpath" statement 1
SELECT xpath('//text()', '<local:data xmlns:local="http://127.0.0.1"><local:piece id="1">number one</local:piece><local:piece id="2" /></local:data>');
xpath
----------------
@@ -1277,7 +1276,6 @@ ERROR: could not parse XML document
DETAIL: line 1: Namespace prefix nosuchprefix on tag is not defined
<nosuchprefix:tag/>
^
-CONTEXT: SQL function "xpath" statement 1
-- XPath deprecates relative namespaces, but they're not supposed to
-- throw an error, only a warning.
SELECT xpath('/*', '<relativens xmlns=''relative''/>');
--
2.56.0
v2-0001-cube-Mark-cube_out-as-STABLE.patch (application/octet-stream, 2.7K) download
From 6e6ce5134404fd541fc5271f6ed49b06a069e356 Mon Sep 17 00:00:00 2001
From: Fujii Masao <fujii@postgresql.org>
Date: Tue, 20 Oct 2026 13:27:46 +0900
Subject: [PATCH v2 1/3] cube: Mark cube_out() as STABLE
cube_out() calls float8out_internal(), so its result depends on the
extra_float_digits setting. Mark it STABLE rather than IMMUTABLE to
reject new expression indexes, generated columns, and partition keys
that rely on this conversion being immutable.
Note that existing dependent objects are not revalidated by this
change, and their definitions may need to be revised before restoring
into an installation using cube 1.6.
Bump the cube extension version to 1.6.
Suggested-by: Peter Eisentraut <peter@eisentraut.org>
Author: Fujii Masao <masao.fujii@gmail.com>
Discussion: https://postgr.es/m/61ca987c-8bd1-4380-b81a-72c94c36b41c@eisentraut.org
---
contrib/cube/Makefile | 2 +-
contrib/cube/cube--1.5--1.6.sql | 7 +++++++
contrib/cube/cube.control | 2 +-
contrib/cube/meson.build | 1 +
4 files changed, 10 insertions(+), 2 deletions(-)
create mode 100644 contrib/cube/cube--1.5--1.6.sql
diff --git a/contrib/cube/Makefile b/contrib/cube/Makefile
index dfb0d806e4b..bee4a77340d 100644
--- a/contrib/cube/Makefile
+++ b/contrib/cube/Makefile
@@ -9,7 +9,7 @@ OBJS = \
EXTENSION = cube
DATA = cube--1.2.sql cube--1.2--1.3.sql cube--1.3--1.4.sql cube--1.4--1.5.sql \
- cube--1.1--1.2.sql cube--1.0--1.1.sql
+ cube--1.5--1.6.sql cube--1.1--1.2.sql cube--1.0--1.1.sql
PGFILEDESC = "cube - multidimensional cube data type"
HEADERS = cubedata.h
diff --git a/contrib/cube/cube--1.5--1.6.sql b/contrib/cube/cube--1.5--1.6.sql
new file mode 100644
index 00000000000..99090546b9f
--- /dev/null
+++ b/contrib/cube/cube--1.5--1.6.sql
@@ -0,0 +1,7 @@
+/* contrib/cube/cube--1.5--1.6.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION cube UPDATE TO '1.6'" to load this file. \quit
+
+-- Output depends on extra_float_digits.
+ALTER FUNCTION cube_out(cube) STABLE;
diff --git a/contrib/cube/cube.control b/contrib/cube/cube.control
index 50427ec1170..7797f7e08d4 100644
--- a/contrib/cube/cube.control
+++ b/contrib/cube/cube.control
@@ -1,6 +1,6 @@
# cube extension
comment = 'data type for multidimensional cubes'
-default_version = '1.5'
+default_version = '1.6'
module_pathname = '$libdir/cube'
relocatable = true
trusted = true
diff --git a/contrib/cube/meson.build b/contrib/cube/meson.build
index 6526091c688..d0398f73a8a 100644
--- a/contrib/cube/meson.build
+++ b/contrib/cube/meson.build
@@ -40,6 +40,7 @@ install_data(
'cube--1.2--1.3.sql',
'cube--1.3--1.4.sql',
'cube--1.4--1.5.sql',
+ 'cube--1.5--1.6.sql',
kwargs: contrib_data_args,
)
--
2.56.0
v2-0002-hstore-Mark-hstore-record-and-populate_record-as-.patch (application/octet-stream, 3.0K) download
From b3bfb8055eaaa780d138be4d263b702f7e52bdd6 Mon Sep 17 00:00:00 2001
From: Ilya Staroverov <i.staroverov@ftdata.ru>
Date: Tue, 20 Oct 2026 15:09:48 +0300
Subject: [PATCH v2 2/3] hstore: Mark hstore(record) and populate_record() as
STABLE
hstore(record) calls the output functions of the record's column
types, and populate_record(), which the #= operator also uses, calls
their input functions, so their results can depend on settings such as
TimeZone, DateStyle or extra_float_digits. Mark them STABLE rather
than IMMUTABLE, like the core functions that do the same, such as
row_to_json() and jsonb_populate_record(), to reject new expression
indexes, generated columns, and partition keys that rely on them being
immutable.
Note that existing dependent objects are not revalidated by this
change, and their definitions may need to be revised before restoring
into an installation using hstore 1.9.
Bump the hstore extension version to 1.9.
Author: Ilya Staroverov <i.staroverov@ftdata.ru>
Discussion: https://postgr.es/m/19644-d6db3fb5e5ad92f4@postgresql.org
---
contrib/hstore/Makefile | 1 +
contrib/hstore/hstore--1.8--1.9.sql | 9 +++++++++
contrib/hstore/hstore.control | 2 +-
contrib/hstore/meson.build | 1 +
4 files changed, 12 insertions(+), 1 deletion(-)
create mode 100644 contrib/hstore/hstore--1.8--1.9.sql
diff --git a/contrib/hstore/Makefile b/contrib/hstore/Makefile
index 48ee98f0d5c..b02f3fda6a8 100644
--- a/contrib/hstore/Makefile
+++ b/contrib/hstore/Makefile
@@ -12,6 +12,7 @@ OBJS = \
EXTENSION = hstore
DATA = hstore--1.4.sql \
+ hstore--1.8--1.9.sql \
hstore--1.7--1.8.sql \
hstore--1.6--1.7.sql \
hstore--1.5--1.6.sql \
diff --git a/contrib/hstore/hstore--1.8--1.9.sql b/contrib/hstore/hstore--1.8--1.9.sql
new file mode 100644
index 00000000000..25c00b0d935
--- /dev/null
+++ b/contrib/hstore/hstore--1.8--1.9.sql
@@ -0,0 +1,9 @@
+/* contrib/hstore/hstore--1.8--1.9.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION hstore UPDATE TO '1.9'" to load this file. \quit
+
+-- These call the output or input functions of the record's column types,
+-- whose results can depend on settings such as TimeZone.
+ALTER FUNCTION hstore(record) STABLE;
+ALTER FUNCTION populate_record(anyelement, hstore) STABLE;
diff --git a/contrib/hstore/hstore.control b/contrib/hstore/hstore.control
index 89e3c746c46..b73c28aa4d3 100644
--- a/contrib/hstore/hstore.control
+++ b/contrib/hstore/hstore.control
@@ -1,6 +1,6 @@
# hstore extension
comment = 'data type for storing sets of (key, value) pairs'
-default_version = '1.8'
+default_version = '1.9'
module_pathname = '$libdir/hstore'
relocatable = true
trusted = true
diff --git a/contrib/hstore/meson.build b/contrib/hstore/meson.build
index 6175abc708e..62c540e77e1 100644
--- a/contrib/hstore/meson.build
+++ b/contrib/hstore/meson.build
@@ -35,6 +35,7 @@ install_data(
'hstore--1.5--1.6.sql',
'hstore--1.6--1.7.sql',
'hstore--1.7--1.8.sql',
+ 'hstore--1.8--1.9.sql',
kwargs: contrib_data_args,
)
--
2.56.0
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs
2026-08-29 05:38 BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs PG Bug reporting form <noreply@postgresql.org>
2026-09-02 03:08 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Fujii Masao <masao.fujii@gmail.com>
2026-09-02 03:15 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Tom Lane <tgl@sss.pgh.pa.us>
2026-09-04 15:54 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Fujii Masao <masao.fujii@gmail.com>
2026-09-04 17:27 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Tom Lane <tgl@sss.pgh.pa.us>
2026-09-08 08:34 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Fujii Masao <masao.fujii@gmail.com>
2026-10-19 12:45 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Peter Eisentraut <peter@eisentraut.org>
2026-10-20 06:16 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Fujii Masao <masao.fujii@gmail.com>
2026-10-20 13:20 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Staroverov Ilja <i.staroverov@ftdata.ru>
2026-10-20 16:05 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Fujii Masao <masao.fujii@gmail.com>
2026-10-20 16:18 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Tom Lane <tgl@sss.pgh.pa.us>
2026-10-21 02:39 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Fujii Masao <masao.fujii@gmail.com>
@ 2026-10-21 04:40 ` Staroverov Ilja <i.staroverov@ftdata.ru>
2026-10-29 14:42 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Fujii Masao <masao.fujii@gmail.com>
0 siblings, 1 reply; 15+ messages in thread
From: Staroverov Ilja @ 2026-10-21 04:40 UTC (permalink / raw)
To: Fujii Masao <masao.fujii@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Peter Eisentraut <peter@eisentraut.org>; jj-zhang25@mails.tsinghua.edu.cn <jj-zhang25@mails.tsinghua.edu.cn>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Hi,
> Codex reported two other cases:
Thanks for taking the hstore patch, and sorry for the wrong claim: I
missed xpath(), which formats its numeric results through
map_sql_value_to_xml_value().
Looking again, more broadly this time, I found one more in core:
age(timestamptz, timestamptz) is marked IMMUTABLE, but it computes the
difference in the session's time zone, so its result depends on
TimeZone:
SET TimeZone = 'UTC';
SELECT age('2026-03-01 00:00+00'::timestamptz,
'2026-01-31 00:00+00'::timestamptz);
age
-------------
1 mon 1 day
SET TimeZone = 'America/New_York';
-- same query
age
---------
29 days
So a stored generated column defined as age(e, s) with timestamptz
columns gets different values for the same s and e, depending on the
TimeZone at insert time.
However, unlike xpath(), age() is probably used in generated columns
and indexes much more often, so marking it STABLE would break more
existing schemas. I'm not proposing a patch; I just wanted you to know
about it, and leave it to you whether it's worth changing.
--
Ilya Staroverov
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs
2026-08-29 05:38 BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs PG Bug reporting form <noreply@postgresql.org>
2026-09-02 03:08 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Fujii Masao <masao.fujii@gmail.com>
2026-09-02 03:15 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Tom Lane <tgl@sss.pgh.pa.us>
2026-09-04 15:54 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Fujii Masao <masao.fujii@gmail.com>
2026-09-04 17:27 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Tom Lane <tgl@sss.pgh.pa.us>
2026-09-08 08:34 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Fujii Masao <masao.fujii@gmail.com>
2026-10-19 12:45 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Peter Eisentraut <peter@eisentraut.org>
2026-10-20 06:16 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Fujii Masao <masao.fujii@gmail.com>
2026-10-20 13:20 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Staroverov Ilja <i.staroverov@ftdata.ru>
2026-10-20 16:05 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Fujii Masao <masao.fujii@gmail.com>
2026-10-20 16:18 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Tom Lane <tgl@sss.pgh.pa.us>
2026-10-21 02:39 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Fujii Masao <masao.fujii@gmail.com>
2026-10-21 04:40 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Staroverov Ilja <i.staroverov@ftdata.ru>
@ 2026-10-29 14:42 ` Fujii Masao <masao.fujii@gmail.com>
2026-11-08 01:30 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs shihao zhong <zhong950419@gmail.com>
0 siblings, 1 reply; 15+ messages in thread
From: Fujii Masao @ 2026-10-29 14:42 UTC (permalink / raw)
To: Staroverov Ilja <i.staroverov@ftdata.ru>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Peter Eisentraut <peter@eisentraut.org>; jj-zhang25@mails.tsinghua.edu.cn <jj-zhang25@mails.tsinghua.edu.cn>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
On Wed, Oct 21, 2026 at 1:40 PM Staroverov Ilja <i.staroverov@ftdata.ru> wrote:
> age(timestamptz, timestamptz) is marked IMMUTABLE, but it computes the
> difference in the session's time zone, so its result depends on
> TimeZone:
You're right.
Codex also found the following IMMUTABLE functions whose results depend
on TimeZone when interpreting time zone abbreviations such as LMT:
timezone(text, timestamp)
timezone(text, timestamptz)
date_trunc(text, timestamptz, text)
date_add(timestamptz, interval, text)
date_subtract(timestamptz, interval, text)
generate_series(timestamptz, timestamptz, interval, text)
jsonb_path_exists(jsonb, jsonpath, jsonb, boolean)
jsonb_path_match(jsonb, jsonpath, jsonb, boolean)
jsonb_path_query(jsonb, jsonpath, jsonb, boolean)
jsonb_path_query_array(jsonb, jsonpath, jsonb, boolean)
jsonb_path_query_first(jsonb, jsonpath, jsonb, boolean)
jsonb_path_exists_opr(jsonb, jsonpath)
jsonb_path_match_opr(jsonb, jsonpath)
Maybe we need to mark these STABLE as well, but I'm concerned about
the impact of doing so...
Regards,
--
Fujii Masao
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs
2026-08-29 05:38 BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs PG Bug reporting form <noreply@postgresql.org>
2026-09-02 03:08 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Fujii Masao <masao.fujii@gmail.com>
2026-09-02 03:15 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Tom Lane <tgl@sss.pgh.pa.us>
2026-09-04 15:54 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Fujii Masao <masao.fujii@gmail.com>
2026-09-04 17:27 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Tom Lane <tgl@sss.pgh.pa.us>
2026-09-08 08:34 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Fujii Masao <masao.fujii@gmail.com>
2026-10-19 12:45 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Peter Eisentraut <peter@eisentraut.org>
2026-10-20 06:16 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Fujii Masao <masao.fujii@gmail.com>
2026-10-20 13:20 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Staroverov Ilja <i.staroverov@ftdata.ru>
2026-10-20 16:05 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Fujii Masao <masao.fujii@gmail.com>
2026-10-20 16:18 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Tom Lane <tgl@sss.pgh.pa.us>
2026-10-21 02:39 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Fujii Masao <masao.fujii@gmail.com>
2026-10-21 04:40 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Staroverov Ilja <i.staroverov@ftdata.ru>
2026-10-29 14:42 ` Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs Fujii Masao <masao.fujii@gmail.com>
@ 2026-11-08 01:30 ` shihao zhong <zhong950419@gmail.com>
0 siblings, 0 replies; 15+ messages in thread
From: shihao zhong @ 2026-11-08 01:30 UTC (permalink / raw)
To: Fujii Masao <masao.fujii@gmail.com>; +Cc: Staroverov Ilja <i.staroverov@ftdata.ru>; Tom Lane <tgl@sss.pgh.pa.us>; Peter Eisentraut <peter@eisentraut.org>; jj-zhang25@mails.tsinghua.edu.cn <jj-zhang25@mails.tsinghua.edu.cn>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Hi Fujii-san,
I was worried this could break pg_upgrade.
With CREATE INDEX ix ON b ((v::text)) on 18, pg_upgrade --check passes,
but the upgrade to master fails:
ERROR: functions in index expression must be marked IMMUTABLE
I started [1] to track this kind of --check gap.
[1]
https://postgr.es/m/CAGRkXqRzM-68S9OhtLsTscZ_hhpHhy=ArWCdmp5GxRxo+UBmig@mail.gmail.com
Thanks,
Shihao
^ permalink raw reply [nested|flat] 15+ messages in thread
end of thread, other threads:[~2026-11-08 01:30 UTC | newest]
Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-08-29 05:38 BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs PG Bug reporting form <noreply@postgresql.org>
2026-09-02 03:08 ` Fujii Masao <masao.fujii@gmail.com>
2026-09-02 03:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-09-04 15:54 ` Fujii Masao <masao.fujii@gmail.com>
2026-09-04 17:27 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-09-08 08:34 ` Fujii Masao <masao.fujii@gmail.com>
2026-10-19 12:45 ` Peter Eisentraut <peter@eisentraut.org>
2026-10-20 06:16 ` Fujii Masao <masao.fujii@gmail.com>
2026-10-20 13:20 ` Staroverov Ilja <i.staroverov@ftdata.ru>
2026-10-20 16:05 ` Fujii Masao <masao.fujii@gmail.com>
2026-10-20 16:18 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-10-21 02:39 ` Fujii Masao <masao.fujii@gmail.com>
2026-10-21 04:40 ` Staroverov Ilja <i.staroverov@ftdata.ru>
2026-10-29 14:42 ` Fujii Masao <masao.fujii@gmail.com>
2026-11-08 01:30 ` shihao zhong <zhong950419@gmail.com>
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