Re: jsonb_set() strictness considered harmful to data

On Sat, Oct 19, 2019 at 11:21:26AM -0400, Stephen Frost wrote:
>Greetings,
>
>* Dmitry Dolgov (9erthalion6(at)gmail(dot)com) wrote:
>> If we want to change it, the question is where to stop? Essentially we have:
>>
>> update table set data = some_func(data, some_args_with_null);
>>
>> where some_func happened to be jsonb_set, but could be any strict function.
>
>I don't think it makes any sense to try and extrapolate this out to
>other strict functions. Functions should be strict when it makes sense
>for them to be- in this case, it sounds like it doesn't really make
>sense for jsonb_set to be strict, and that's where we stop it.
>

Yeah. I think the issue here is (partially) that other databases adopted
similar functions after us, but decided to use a different behavior. It
might be more natural for the users, but that does not mean we should
change the other strict functions.

Plus I'm not sure if SQL standard says anything about strict functions
(I found nothing, but I looked only very quickly), but I'm pretty sure
we can't change how basic operators change, and we translate them to
function calls (e.g. 1+2 is int4pl(1,2)).

>> I wonder if in this case it makes sense to think about an alternative? For
>> example, there is generic type subscripting patch, that allows to update a
>> jsonb in the following way:
>>
>> update table set jsonb_data[key] = 'value';
>>
>> It doesn't look like a function, so it's not a big deal if it will handle NULL
>> values differently. And at the same time one can argue, that people, who are
>> not aware about this caveat with jsonb_set and NULL values, will most likely
>> use it due to a bit simpler syntax (more similar to some popular programming
>> languages).
>
>This seems like an entirely independent thing ...
>

Right. Useful, but entirely separate feature.

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services