BUG #19409: Function jsonb_strip_nulls() changed from immutable to stable.
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.
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: /messages/by-id/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
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 -0500Allow 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:
/messages/by-id/4BCECCD5-4F40-4313-9E98-9E16BEB0B01D@gmail.comIt 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
Attachments:
v1-0001-Restore-json-b-_strip_nulls-immutability.patchapplication/octet-stream; name=v1-0001-Restore-json-b-_strip_nulls-immutability.patchDownload+2-3
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 -0500Allow 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:/messages/by-id/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
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
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
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
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
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