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

От:
Fujii Masao <masao.fujii@gmail.com>
Дата:
Вложения:
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

От:
Staroverov Ilja <i.staroverov@ftdata.ru>
Дата:
Вложения:
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

От:
Fujii Masao <masao.fujii@gmail.com>
Дата:
Вложения:
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.

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
FAQ