On Sat, Oct 19, 2019 at 11:26:50AM -0400, Andrew Dunstan wrote:
...
The hyperbole here is misplaced. There is a difference between a bug and
a POLA violation. This might be the latter, but it isn't the former. So
please tone it down a bit. It's not the function that's unsafe, but the
ill-informed use of it.
We invented jsonb_set() (credit to Dmitry Dolgov). And we've had it
since 9.5. That's five releases ago. So it's a bit late to be coming to
us telling us it's not safe (according to your preconceptions of what it
should be doing).
We could change it prospectively (i.e. from release 13 on) if we choose.
But absent an actual bug (i.e. acting contrary to documented behaviour)
we do not normally backpatch such changes, especially when there is a
simple workaround for the perceived problem. And it's that policy that
is in large measure responsible for Postgres' deserved reputation for
stability.
Yeah.
Incidentally, why is your function written in plpgsql? Wouldn't a simple
SQL wrapper be better?
create or replace function safe_jsonb_set
(target jsonb, path text[], new_value jsonb, create_missing
boolean default true)
returns jsonb as
$func$
select case when new_value is null then target else
jsonb_set(target, path, new_value, create_missing) end
$func$ language sql;
And if we were to change it I'm not at all sure that we should do it the
way that's suggested here, which strikes me as no more intuitive than
the current behaviour. Rather I think we should possibly fill in a json
null in the indicated place.
Not sure, but that seems rather confusing to me, because it's mixing SQL
NULL and JSON null, i.e. it's not clear to me why
jsonb_set(..., "...", NULL)
should do the same thing as
jsonb_set(..., "...", 'null':jsonb)
I'm not entirely surprised it's what MySQL does ;-) but I'd say treating
it as a deletion of the key (just like MSSQL) is somewhat more sensible.
But I admit it's quite subjective.
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
