Horton

Thread

Commits

  1. Simplify creation of built-in functions with default arguments.

  2. Fix the volatility setting of json{b}_strip_nulls

  1. BUG #19409: Function jsonb_strip_nulls() changed from immutable to stable.

    The Post Office <noreply@postgresql.org> — 2026-02-13T19:51:03Z

    The following bug has been logged on the website:

Bug reference:      19409
Logged by:          Lucio Chiessi
Email address:      lucio.chiessi@trustly.com
PostgreSQL version: 18.1
Operating system:   MacOS 15.7.3
Description:        

Hello.  We are using, in PostgreSQL 16, the function
jsonb_strip_nulls(jsonb) in a calculated table column, as shown below:

attributes_hash text generated always as
(md5(jsonb_strip_nulls(attributes)::text)) stored,

But using PostgreSQL 18 we having this error:
[ERROR] SQL State  : 42P17
[ERROR] Error Code : 0
[ERROR] Message    : ERROR: generation expression is not immutable

But this fails in PostgreSQL 18, because the jsonb_strip_nulls ( target
jsonb [,strip_in_arrays boolean ] ) function changed from immutable to
stable.
In PostgreSQL.  I used the query below in both versions, with this change:

select proname,provolatile,pronargs,proparallel,proargnames
from pg_proc
where proname in ('jsonb_strip_nulls') and prokind = 'f';

In PostgreSQL 16.6, we have the value of 'provolatile' column as 'i', and in
PostgreSQL 18.1 as 's'.
I can't find any information about this change in the Release Notes v18.

Can we consider this a bug, or is this a normal behavior introduced in v18?
Thanks a lot.

  2. Re: BUG #19409: Function jsonb_strip_nulls() changed from immutable to stable.

    Tom Lane <tgl@sss.pgh.pa.us> — 2026-02-13T20:18:19Z

    PG Bug reporting form <noreply@postgresql.org> writes:
> ... this fails in PostgreSQL 18, because the jsonb_strip_nulls ( target
> jsonb [,strip_in_arrays boolean ] ) function changed from immutable to
> stable.

A bit of git excavation shows that this changed here:

Author: Andrew Dunstan <andrew@dunslane.net>
Branch: master Release: REL_18_BR [4603903d2] 2025-03-05 10:04:02 -0500

    Allow json{b}_strip_nulls to remove null array elements
    
    An additional paramater ("strip_in_arrays") is added to these functions.
    It defaults to false. If true, then null array elements are removed as
    well as null valued object fields. JSON that just consists of a single
    null is not affected.
    
    Author: Florents Tselai <florents.tselai@gmail.com>
    
    Discussion: https://postgr.es/m/4BCECCD5-4F40-4313-9E98-9E16BEB0B01D@gmail.com

It looks like a thinko to me, because surely the strip_in_arrays
parameter did not make the function more mutable than before.
Nor did a quick search find any discussion of the point in the
thread.

			regards, tom lane

  3. Re: BUG #19409: Function jsonb_strip_nulls() changed from immutable to stable.

    Florents Tselai <florents.tselai@gmail.com> — 2026-02-13T21:35:04Z

    On Fri, Feb 13, 2026 at 10:18 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> PG Bug reporting form <noreply@postgresql.org> writes:
> > ... this fails in PostgreSQL 18, because the jsonb_strip_nulls ( target
> > jsonb [,strip_in_arrays boolean ] ) function changed from immutable to
> > stable.
>
> A bit of git excavation shows that this changed here:
>
> Author: Andrew Dunstan <andrew@dunslane.net>
> Branch: master Release: REL_18_BR [4603903d2] 2025-03-05 10:04:02 -0500
>
>     Allow json{b}_strip_nulls to remove null array elements
>
>     An additional paramater ("strip_in_arrays") is added to these
> functions.
>     It defaults to false. If true, then null array elements are removed as
>     well as null valued object fields. JSON that just consists of a single
>     null is not affected.
>
>     Author: Florents Tselai <florents.tselai@gmail.com>
>
>     Discussion:
> https://postgr.es/m/4BCECCD5-4F40-4313-9E98-9E16BEB0B01D@gmail.com
>
> It looks like a thinko to me, because surely the strip_in_arrays
> parameter did not make the function more mutable than before.
>

The perils of going from pg_proc.dat to manual defs in system_functions.sql

  4. Re: BUG #19409: Function jsonb_strip_nulls() changed from immutable to stable.

    Andrew Dunstan <andrew@dunslane.net> — 2026-02-16T16:18:28Z

    On 2026-02-13 Fr 3:18 PM, Tom Lane wrote:
> PG Bug reporting form<noreply@postgresql.org> writes:
>> ... this fails in PostgreSQL 18, because the jsonb_strip_nulls ( target
>> jsonb [,strip_in_arrays boolean ] ) function changed from immutable to
>> stable.
> A bit of git excavation shows that this changed here:
>
> Author: Andrew Dunstan<andrew@dunslane.net>
> Branch: master Release: REL_18_BR [4603903d2] 2025-03-05 10:04:02 -0500
>
>      Allow json{b}_strip_nulls to remove null array elements
>      
>      An additional paramater ("strip_in_arrays") is added to these functions.
>      It defaults to false. If true, then null array elements are removed as
>      well as null valued object fields. JSON that just consists of a single
>      null is not affected.
>      
>      Author: Florents Tselai<florents.tselai@gmail.com>
>      
>      Discussion:https://postgr.es/m/4BCECCD5-4F40-4313-9E98-9E16BEB0B01D@gmail.com
>
> It looks like a thinko to me, because surely the strip_in_arrays
> parameter did not make the function more mutable than before.
> Nor did a quick search find any discussion of the point in the
> thread.
>
> 			


Yeah. <paperbag>

In penance for this I have worked up a mechanism to generate 
default-setting statements from pg_proc.dat that I will post separately 
about in -hackers. In the meantime, I guess we should fix it in release 
18 and master, with a release note that people might need to do a manual 
update if affected, along the lines of

    update pg_proc set provolatile = 'i' where oid in (3261,3262);


cheers


andrew

--
Andrew Dunstan
EDB:https://www.enterprisedb.com

  5. Re: BUG #19409: Function jsonb_strip_nulls() changed from immutable to stable.

    Tom Lane <tgl@sss.pgh.pa.us> — 2026-02-16T16:48:19Z

    Andrew Dunstan <andrew@dunslane.net> writes:
> In penance for this I have worked up a mechanism to generate 
> default-setting statements from pg_proc.dat that I will post separately 
> about in -hackers. In the meantime, I guess we should fix it in release 
> 18 and master, with a release note that people might need to do a manual 
> update if affected, along the lines of
>     update pg_proc set provolatile = 'i' where oid in (3261,3262);

Yeah, the $64 question is what to do in REL_18_STABLE.  In master
we can commit this with a catversion bump, but we can't change
v18's catversion.  I think I agree that we should just change v18's
system_functions.sql anyway.  That will at least solve it for people
upgrading in future.

			regards, tom lane

  6. Re: BUG #19409: Function jsonb_strip_nulls() changed from immutable to stable.

    Andrew Dunstan <andrew@dunslane.net> — 2026-02-16T20:19:32Z

    On 2026-02-16 Mo 11:48 AM, Tom Lane wrote:
> Andrew Dunstan<andrew@dunslane.net> writes:
>> In penance for this I have worked up a mechanism to generate
>> default-setting statements from pg_proc.dat that I will post separately
>> about in -hackers. In the meantime, I guess we should fix it in release
>> 18 and master, with a release note that people might need to do a manual
>> update if affected, along the lines of
>>      update pg_proc set provolatile = 'i' where oid in (3261,3262);
> Yeah, the $64 question is what to do in REL_18_STABLE.  In master
> we can commit this with a catversion bump, but we can't change
> v18's catversion.  I think I agree that we should just change v18's
> system_functions.sql anyway.  That will at least solve it for people
> upgrading in future.


OK, barring objections I will make that happen in a day or two.


cheers


andrew

--
Andrew Dunstan
EDB:https://www.enterprisedb.com

  7. Re: BUG #19409: Function jsonb_strip_nulls() changed from immutable to stable.

    Andrew Dunstan <andrew@dunslane.net> — 2026-02-17T21:25:19Z

    On 2026-02-16 Mo 3:19 PM, Andrew Dunstan wrote:
>
>
> On 2026-02-16 Mo 11:48 AM, Tom Lane wrote:
>> Andrew Dunstan<andrew@dunslane.net> writes:
>>> In penance for this I have worked up a mechanism to generate
>>> default-setting statements from pg_proc.dat that I will post separately
>>> about in -hackers. In the meantime, I guess we should fix it in release
>>> 18 and master, with a release note that people might need to do a manual
>>> update if affected, along the lines of
>>>      update pg_proc set provolatile = 'i' where oid in (3261,3262);
>> Yeah, the $64 question is what to do in REL_18_STABLE.  In master
>> we can commit this with a catversion bump, but we can't change
>> v18's catversion.  I think I agree that we should just change v18's
>> system_functions.sql anyway.  That will at least solve it for people
>> upgrading in future.
>
>
> OK, barring objections I will make that happen in a day or two.
>
>
>


In view of later discussions, fixing master seems pointless, as that 
code is going away. When it does we should do a catversion bump.


So I'm just planning to patch v18 now.


cheers


andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com

  8. Re: BUG #19409: Function jsonb_strip_nulls() changed from immutable to stable.

    Tom Lane <tgl@sss.pgh.pa.us> — 2026-02-17T21:55:04Z

    Andrew Dunstan <andrew@dunslane.net> writes:
> In view of later discussions, fixing master seems pointless, as that 
> code is going away. When it does we should do a catversion bump.
> So I'm just planning to patch v18 now.

Makes sense.

			regards, tom lane

  9. Re: BUG #19409: Function jsonb_strip_nulls() changed from immutable to stable.

    Andrew Dunstan <andrew@dunslane.net> — 2026-02-18T18:05:33Z

    On 2026-02-17 Tu 4:55 PM, Tom Lane wrote:
> Andrew Dunstan <andrew@dunslane.net> writes:
>> In view of later discussions, fixing master seems pointless, as that
>> code is going away. When it does we should do a catversion bump.
>> So I'm just planning to patch v18 now.
> Makes sense.
>
> 			



Done.


cheers


andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com

  10. Re: BUG #19409: Function jsonb_strip_nulls() changed from immutable to stable.

    Tom Lane <tgl@sss.pgh.pa.us> — 2026-02-18T19:18:40Z

    Andrew Dunstan <andrew@dunslane.net> writes:
> Done.

I pushed the patch for master too, so this is closed now.

			regards, tom lane