BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs

Started by PG Bug reporting form3 months ago15 messagesbugs
Jump to latest
#1PG Bug reporting form
noreply@postgresql.org

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.

#2Fujii Masao
masao.fujii@gmail.com
In reply to: PG Bug reporting form (#1)
Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs

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]/messages/by-id/17398-8fb0c02a60e2e565@postgresql.org.

Regards,

[1]: /messages/by-id/17398-8fb0c02a60e2e565@postgresql.org

--
Fujii Masao

#3Tom Lane
tgl@sss.pgh.pa.us
In reply to: Fujii Masao (#2)
Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs

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

#4Fujii Masao
masao.fujii@gmail.com
In reply to: Tom Lane (#3)
Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs

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

#5Tom Lane
tgl@sss.pgh.pa.us
In reply to: Fujii Masao (#4)
Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs

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

#6Fujii Masao
masao.fujii@gmail.com
In reply to: Tom Lane (#5)
Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs

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:

t253631_6
v1-0001-Mark-GUC-dependent-type-output-functions-as-STABL.patchapplication/octet-stream; name=v1-0001-Mark-GUC-dependent-type-output-functions-as-STABL.patchDownload+23-24
#7Peter Eisentraut
peter@eisentraut.org
In reply to: Fujii Masao (#6)
Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs

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.

#8Fujii Masao
masao.fujii@gmail.com
In reply to: Peter Eisentraut (#7)
Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs

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:

t253631_8
v1-0001-cube-Mark-cube_out-as-STABLE.patchapplication/octet-stream; name=v1-0001-cube-Mark-cube_out-as-STABLE.patchDownload+10-3
#9Staroverov Ilja
i.staroverov@ftdata.ru
In reply to: Fujii Masao (#8)
Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs

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:

t253631_9
v1-0001-hstore-Mark-hstore-record-and-populate_record-as-.patchapplication/octet-stream; name=v1-0001-hstore-Mark-hstore-record-and-populate_record-as-.patchDownload+29-2
#10Fujii Masao
masao.fujii@gmail.com
In reply to: Staroverov Ilja (#9)
Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs

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

#11Tom Lane
tgl@sss.pgh.pa.us
In reply to: Fujii Masao (#10)
Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs

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

#12Fujii Masao
masao.fujii@gmail.com
In reply to: Tom Lane (#11)
Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs

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:

t253631_12
v2-0003-Mark-xpath-and-quote_ident-as-STABLE.patchapplication/octet-stream; name=v2-0003-Mark-xpath-and-quote_ident-as-STABLE.patchDownload+6-9
v2-0001-cube-Mark-cube_out-as-STABLE.patchapplication/octet-stream; name=v2-0001-cube-Mark-cube_out-as-STABLE.patchDownload+10-3
v2-0002-hstore-Mark-hstore-record-and-populate_record-as-.patchapplication/octet-stream; name=v2-0002-hstore-Mark-hstore-record-and-populate_record-as-.patchDownload+12-2
#13Staroverov Ilja
i.staroverov@ftdata.ru
In reply to: Fujii Masao (#12)
Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs

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

#14Fujii Masao
masao.fujii@gmail.com
In reply to: Staroverov Ilja (#13)
Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs

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

#15shihao zhong
zhong950419@gmail.com
In reply to: Fujii Masao (#14)
Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs

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]/messages/by-id/CAGRkXqRzM-68S9OhtLsTscZ_hhpHhy=ArWCdmp5GxRxo+UBmig@mail.gmail.com to track this kind of --check gap.

[1]: /messages/by-id/CAGRkXqRzM-68S9OhtLsTscZ_hhpHhy=ArWCdmp5GxRxo+UBmig@mail.gmail.com
/messages/by-id/CAGRkXqRzM-68S9OhtLsTscZ_hhpHhy=ArWCdmp5GxRxo+UBmig@mail.gmail.com

Thanks,
Shihao