Hello,
On Fri, Oct 18, 2019 at 5:01 PM David G. Johnston
<david.g.johnston@xxxxxxxxx> wrote:
>
> On Fri, Oct 18, 2019 at 2:50 PM Christoph Moench-Tegeder <cmt@xxxxxxxxxxxxxx> wrote:
>>
>> ## Ariadne Conill (ariadne@xxxxxxxxxxxxxxxx):
>>
>> > 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;
>>
>
> There are many ways to add code to queries to make working with this function safer - though using them presupposes one remembers at the time of writing the query that there is danger and caveats in using this function. I agree that we should have (and now) provided sane defined behavior when one of the inputs to the function is null instead blowing off the issue and defining the function as being strict. Whether that is "ignore and return the original object" or "add the key with a json null scalar value" is debatable but either is considerably more useful than returning SQL NULL.
A great example of how we got burned by this last year: Pleroma
maintains pre-computed counters in JSONB for various types of
activities (posts, followers, followings). Last year, another counter
was added, with a migration. But some people did not run the
migration, because they are users, and that's what users do. This
resulted in Pleroma blanking out the `info` structure for users as
they performed new activities that incremented that counter. At that
time, Pleroma maintained various things like private keys used to sign
things in that JSONB column (we no longer do this because of being
burned by this several times now), which broke federation temporarily
for the affected accounts with other servers for up to a week as those
servers had to learn new public keys for those accounts (since the
original private keys were lost).
I believe that anything that can be catastrophically broken by users
not following upgrade instructions precisely is a serious problem, and
can lead to serious problems. I am sure that this is not the only
project using JSONB which have had users destroy their own data in
such a completely preventable fashion.
Ariadne
