Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs
Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs
От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Fujii Masao writes: > On Tue, Sep 1, 2026 at 10:12 PM PG Bug reporting form > 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
Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs
От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Fujii Masao writes: > On Wed, Sep 2, 2026 at 12:15 PM Tom Lane 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
Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs
От:
Peter Eisentraut <peter@eisentraut.org>
Дата:
On 08.09.26 10:34, Fujii Masao wrote: > On Sat, Sep 5, 2026 at 2:27 AM Tom Lane 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.
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 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
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
Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs
От:
Fujii Masao <masao.fujii@gmail.com>
Дата:
On Tue, Oct 20, 2026 at 10:20 PM Staroverov Ilja 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
Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs
От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Fujii Masao 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
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 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
Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs
От:
Staroverov Ilja <i.staroverov@ftdata.ru>
Дата:
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
Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs
От:
Fujii Masao <masao.fujii@gmail.com>
Дата:
On Wed, Oct 21, 2026 at 1:40 PM Staroverov Ilja 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
Re: BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs
От:
shihao zhong <zhong950419@gmail.com>
Дата:
Hi Fujii-san,
I was worried this could break pg_upgrade.
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
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