From: | Andrew Dunstan <andrew(dot)dunstan(at)2ndquadrant(dot)com> |
---|---|
To: | Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com> |
Cc: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>, Floris Van Nee <florisvannee(at)optiver(dot)com>, Ariadne Conill <ariadne(at)dereferenced(dot)org>, Mark Felder <feld(at)freebsd(dot)org>, "pgsql-generallists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | Re: jsonb_set() strictness considered harmful to data |
Date: | 2019-10-28 13:52:11 |
Message-ID: | 375873e2-c957-3a8d-64f9-26c43c2b16e7@2ndQuadrant.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
On 10/21/19 9:28 AM, Andrew Dunstan wrote:
> On 10/21/19 2:07 AM, Tomas Vondra wrote:
>> On Sun, Oct 20, 2019 at 06:51:05PM -0400, Andrew Dunstan wrote:
>>>> I think the general premise of this thread is that the application
>>>> developer does not realize that may be necessary, because it's a bit
>>>> surprising behavior, particularly when having more experience with
>>>> other
>>>> databases that behave differently. It's also pretty easy to not notice
>>>> this issue for a long time, resulting in significant data loss.
>>>>
>>>> Let's say you're used to the MSSQL or MySQL behavior, you migrate your
>>>> application to PostgreSQL or whatever - how do you find out about this
>>>> behavior? Users are likely to visit
>>>>
>>>> https://www.postgresql.org/docs/12/functions-json.html
>>>>
>>>> but that says nothing about how jsonb_set works with NULL values :-(
>>>
>>>
>>> We should certainly fix that. I accept some responsibility for the
>>> omission.
>>>
>> +1
>>
>>
>
> So let's add something to the JSON funcs page like this:
>
>
> Note: All the above functions except for json_build_object,
> json_build_array, json_to_recordset, json_populate_record, and
> json_populate_recordset and their jsonb equivalents are strict
> functions. That is, if any argument is NULL the function result will be
> NULL and the function won't even be called. Particular care should
> therefore be taken to avoid passing NULL arguments to those functions
> unless a NULL result is expected. This is particularly true of the
> jsonb_set and jsonb_insert functions.
>
>
>
> (We do have a heck of a lot of Note: sections on that page)
>
>
For release 13+, I have given some more thought to what should be done.
I think the bar for altering the behaviour of a function should be
rather higher than we have in the present case, and the longer the
function has been sanctioned by time the higher the bar should be.
However, I think there is a case to be made for providing a non-strict
jsonb_set type function. To advance th4e discussion, attached is a POC
patch that does that. This can also be done as an extension, meaning
that users of back branches could deploy it immediately. I've tested
this against release 12, but I think it could go probably all the way
back to 9.5. The new function is named jsonb_ set_lax, but I'm open to
bikeshedding.
cheers
andrew
--
Andrew Dunstan https://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Attachment | Content-Type | Size |
---|---|---|
jsonb_set_lax-1.patch | text/x-patch | 7.1 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Basques, Bob (CI-StPaul) | 2019-10-28 14:27:19 | Re: SQL pretty pritner? |
Previous Message | Thomas Boussekey | 2019-10-28 13:46:38 | Re: SQL pretty pritner? |
From | Date | Subject | |
---|---|---|---|
Next Message | Thunder | 2019-10-28 13:54:51 | Re:Re:Re: [BUG] standby node can not provide service even it replays all log files |
Previous Message | Konstantin Knizhnik | 2019-10-28 13:48:43 | Re: [Proposal] Global temporary tables |