Re: jsonb_set() strictness considered harmful to data

Hello,

On Fri, Oct 18, 2019 at 4:50 PM Christoph Moench-Tegeder
<cmt(at)burggraben(dot)net> wrote:
>
> ## Ariadne Conill (ariadne(at)dereferenced(dot)org):
>
> > update users set info=jsonb_set(info, '{bar}', info->'foo');
> >
> > Typically, this works nicely, except for cases where evaluating
> > info->'foo' results in an SQL null being returned. When that happens,
> > jsonb_set() returns an SQL null, which then results in data loss.[3]
>
> So why don't you use the facilities of SQL to make sure to only
> touch the rows which match the prerequisites?
>
> UPDATE users SET info = jsonb_set(info, '{bar}', info->'foo')
> WHERE info->'foo' IS NOT NULL;

Why don't we fix the database engine to not eat data when the
jsonb_set() operation fails? Telling people to work around design
flaws in the software is what I would expect of MySQL, not a database
known for its data integrity.

Obviously, it is possible to adjust the UPDATE statement to only match
certain pre-conditions, *if you know those pre-conditions may be a
problem*. What happens with us, and with other people who have hit
this bug with jsonb_set() is that they hit issues that were not
previously known about, and that's when jsonb_set() eats your data.

I would also like to point out that the MySQL equivalent, json_set()
when presented with a similar failure simply returns the unmodified
input. It is not unreasonable to do the same in PostgreSQL.
Personally, as a developer, I expect PostgreSQL to be on their game
better than MySQL.

> No special wrappers required.

A special wrapper is needed because jsonb_set() does broken things
when invoked in situations that do not match the preconceptions of
those situations. We will have to ship this wrapper for several years
because of the current behaviour of the jsonb_set() function.

Ariadne