Re: jsonb_set() strictness considered harmful to data
От | Andrew Dunstan |
---|---|
Тема | Re: jsonb_set() strictness considered harmful to data |
Дата | |
Msg-id | 375873e2-c957-3a8d-64f9-26c43c2b16e7@2ndQuadrant.com обсуждение исходный текст |
Ответ на | Re: jsonb_set() strictness considered harmful to data (Andrew Dunstan <andrew.dunstan@2ndquadrant.com>) |
Ответы |
Re: jsonb_set() strictness considered harmful to data
("Mark Felder" <feld@FreeBSD.org>)
Re: jsonb_set() strictness considered harmful to data ("Mark Felder" <feld@FreeBSD.org>) Re: jsonb_set() strictness considered harmful to data (Pavel Stehule <pavel.stehule@gmail.com>) Re: jsonb_set() strictness considered harmful to data (Pavel Stehule <pavel.stehule@gmail.com>) |
Список | pgsql-general |
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
Вложения
В списке pgsql-general по дате отправления: