Re: jsonb_set() strictness considered harmful to data

2019-10-20 Thread Tomas Vondra
On Sun, Oct 20, 2019 at 06:51:05PM -0400, Andrew Dunstan wrote: On 10/20/19 4:18 PM, Tomas Vondra wrote: On Sun, Oct 20, 2019 at 03:48:05PM -0400, Andrew Dunstan wrote: On 10/20/19 1:14 PM, David G. Johnston wrote: On Sun, Oct 20, 2019 at 5:31 AM Andrew Dunstan mailto:andrew.duns...@2ndquadr

Re: CPU SPIKE

2019-10-20 Thread Tomas Vondra
On Mon, Oct 21, 2019 at 10:45:33AM +0530, Pawan Sharma wrote: Hi All, Having real high CPU issue (95-98%), with SELECT statements and select queries contains multiple AND operator, is it will cause any CPU Spike..??? apps team is using sub-partition, PG11, CPU:24, Mem: 16GB Any query executi

Re: CPU SPIKE

2019-10-20 Thread Pavel Stehule
Hi po 21. 10. 2019 v 7:14 odesílatel Pawan Sharma napsal: > Hi All, > > Having real high CPU issue (95-98%), with SELECT statements and select > queries contains multiple AND operator, is it will cause any CPU Spike..??? > > apps team is using sub-partition, PG11, CPU:24, Mem: 16GB > > ```test=#

CPU SPIKE

2019-10-20 Thread Pawan Sharma
Hi All, Having real high CPU issue (95-98%), with SELECT statements and select queries contains multiple AND operator, is it will cause any CPU Spike..??? apps team is using sub-partition, PG11, CPU:24, Mem: 16GB ```test=# show shared_buffers ; shared_buffers 7680MB (1 row) t

Re: jsonb_set() strictness considered harmful to data

2019-10-20 Thread Abelard Hoffman
> > >> I would argue that only if the target parameter (the actual json value) > is NULL should the result be NULL. The function is documented as returning > the target, with modifications to a small part of its structure as > specified by the other parameters. It is strange for the result to sudde

Re: jsonb_set() strictness considered harmful to data

2019-10-20 Thread rob stone
Hello, On Sun, 2019-10-20 at 18:51 -0400, Andrew Dunstan wrote: > On 10/20/19 4:18 PM, Tomas Vondra wrote: > > > >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 a

Re: jsonb_set() strictness considered harmful to data

2019-10-20 Thread raf
Steven Pousty wrote: > I would think though that raising an exception is better than a > default behavior which deletes data. I can't help but feel the need to make the point that the function is not deleting anything. It is just returning null. The deletion of data is being performed by an updat

Re: jsonb_set() strictness considered harmful to data

2019-10-20 Thread Andrew Dunstan
On 10/20/19 4:18 PM, Tomas Vondra wrote: > On Sun, Oct 20, 2019 at 03:48:05PM -0400, Andrew Dunstan wrote: >> >> On 10/20/19 1:14 PM, David G. Johnston wrote: >>> On Sun, Oct 20, 2019 at 5:31 AM Andrew Dunstan >>> >> > wrote: >>> >>>     And yet another is t

Re: jsonb_set() strictness considered harmful to data

2019-10-20 Thread Paul A Jungwirth
> That said, I think it is reasonable that a PostgreSQL JSON function > behaves in the way that JSON users would expect, so here is my +1 for > interpreting an SQL NULL as a JSON null in the above case Just to chime in as another application developer: the current functionality does seem pretty su

Re: jsonb_set() strictness considered harmful to data

2019-10-20 Thread Laurenz Albe
On Fri, 2019-10-18 at 21:18 -0500, Ariadne Conill wrote: > postgres=# \pset null '(null)' > Null display is "(null)". > postgres=# select jsonb_set('{"a":1,"b":2,"c":3}'::jsonb, '{a}', NULL); > jsonb_set > --- > (null) > (1 row) > > This behaviour is basically giving an application develop

Re: jsonb_set() strictness considered harmful to data

2019-10-20 Thread Steven Pousty
I would think though that raising an exception is better than a default behavior which deletes data. As an app dev I am quite used to all sorts of "APIs" throwing exceptions and have learned to deal with them. This is my way of saying that raising an exception is an improvement over the current si

Re: jsonb_set() strictness considered harmful to data

2019-10-20 Thread Tomas Vondra
On Sun, Oct 20, 2019 at 03:48:05PM -0400, Andrew Dunstan wrote: On 10/20/19 1:14 PM, David G. Johnston wrote: On Sun, Oct 20, 2019 at 5:31 AM Andrew Dunstan mailto:andrew.duns...@2ndquadrant.com>> wrote: And yet another is to raise an exception, which is easy to write but really punts

Re: jsonb_set() strictness considered harmful to data

2019-10-20 Thread Andrew Dunstan
On 10/20/19 1:14 PM, David G. Johnston wrote: > On Sun, Oct 20, 2019 at 5:31 AM Andrew Dunstan > > wrote: > > And yet another is to > raise an exception, which is easy to write but really punts the issue > back to the application programmer who

Re: Too many SET TimeZone and Application_name queries

2019-10-20 Thread Jeff Janes
On Fri, Oct 11, 2019 at 7:49 AM Amarendra Konda wrote: > Hi, > > In our test environment, it was observed that there are too many queries > were getting fired to the database server, > What does "too many" mean here? Is it just more than you like to see in your log file, or is there some object

Re: jsonb_set() strictness considered harmful to data

2019-10-20 Thread David G. Johnston
On Sun, Oct 20, 2019 at 5:31 AM Andrew Dunstan < andrew.duns...@2ndquadrant.com> wrote: > And yet another is to > raise an exception, which is easy to write but really punts the issue > back to the application programmer who will have to decide how to ensure > they never pass in a NULL parameter.

Re: UTC-6 or UTC+6?

2019-10-20 Thread Tom Lane
Luca Ferrari writes: > template1=# select '2019-10-22 16:00:00' at time zone 'Europe/Rome' as my_time > , '2019-10-22 16:00:00' at time zone 'America/Denver' as what_should_be, > '2019-10-22 16:00:00' at time zone 'UTC-6' as utc_minus_6, > '2019-10-22 16:00:00' at time zone 'UTC+6' as utc_plus_6;

Re: jsonb_set() strictness considered harmful to data

2019-10-20 Thread Isaac Morland
On Sun, 20 Oct 2019 at 08:32, Andrew Dunstan wrote: > > Understood. I think the real question here is what it should do instead > when the value is NULL. Your behaviour above is one suggestion, which I > personally find intuitive. Another has been to remove the associated > key. Another is to ret

Re: jsonb_set() strictness considered harmful to data

2019-10-20 Thread Andrew Dunstan
On 10/20/19 4:39 AM, Floris Van Nee wrote: > > FWIW I've been bitten by this 'feature' more than once as well, > accidentally erasing a column. Now I usually write js = jsonb_set(js, > coalesce(new_column, 'null'::jsonb)) to prevent erasing the whole > column, and instead setting the value to a j

UTC-6 or UTC+6?

2019-10-20 Thread Luca Ferrari
I'm a little confused by trying to get the time at a different timezone, formerly UTC-6: template1=# select '2019-10-22 16:00:00' at time zone 'Europe/Rome' as my_time , '2019-10-22 16:00:00' at time zone 'America/Denver' as what_should_be, '2019-10-22 16:00:00' at time zone 'UTC-6' as utc_minus_6

Re: jsonb_set() strictness considered harmful to data

2019-10-20 Thread Steve Atkins
On 19/10/2019 07:52, Ariadne Conill wrote: I would say that any thing like update whatever set column=jsonb_set(column, '{foo}', NULL) should throw an exception. It should do, literally, *anything* else but blank that column. steve=# create table foo (bar jsonb not null); CREATE TABLE ste

jsonb_set() strictness considered harmful to data

2019-10-20 Thread Floris Van Nee
FWIW I've been bitten by this 'feature' more than once as well, accidentally erasing a column. Now I usually write js = jsonb_set(js, coalesce(new_column, 'null'::jsonb)) to prevent erasing the whole column, and instead setting the value to a jsonb null value, but I also found the STRICT behavio