Re: jsonb_set() strictness considered harmful to data

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