Re: jsonb_set() strictness considered harmful to data

Hi > What I am talking about is that jsonb_set(..., ..., NULL) returns SQL NULL. > > 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

Re: jsonb_set() strictness considered harmful to data

Hello, On Fri, Oct 18, 2019 at 7:04 PM Adrian Klaver wrote: > > On 10/18/19 4:31 PM, Ariadne Conill wrote: > > Hello, > > > > On Fri, Oct 18, 2019 at 6:01 PM Adrian Klaver > > wrote: > >> > >> On 10/18/19 3:11 PM, Ariadne Conill wrote: > >>> Hello, > >>> > >>> On Fri, Oct 18, 2019 at 5:01 PM

Re: jsonb_set() strictness considered harmful to data

Hello, On Fri, Oct 18, 2019 at 6:52 PM Stephen Frost wrote: > > Greetings, > > * Ariadne Conill (aria...@dereferenced.org) wrote: > > On Fri, Oct 18, 2019 at 6:01 PM Adrian Klaver > > wrote: > > > https://www.postgresql.org/docs/11/functions-json.html > > > " The field/element/path extraction

Re: jsonb_set() strictness considered harmful to data

On 10/18/19 4:31 PM, Ariadne Conill wrote: Hello, On Fri, Oct 18, 2019 at 6:01 PM Adrian Klaver wrote: On 10/18/19 3:11 PM, Ariadne Conill wrote: Hello, On Fri, Oct 18, 2019 at 5:01 PM David G. Johnston wrote: On Fri, Oct 18, 2019 at 2:50 PM Christoph Moench-Tegeder wrote: ##

Re: jsonb_set() strictness considered harmful to data

Greetings, * Ariadne Conill (aria...@dereferenced.org) wrote: > On Fri, Oct 18, 2019 at 5:57 PM Christoph Moench-Tegeder > wrote: > > ## Ariadne Conill (aria...@dereferenced.org): > > > Why don't we fix the database engine to not eat data when the > > > jsonb_set() operation fails? > > > > It

Re: jsonb_set() strictness considered harmful to data

Greetings, * Ariadne Conill (aria...@dereferenced.org) wrote: > On Fri, Oct 18, 2019 at 6:01 PM Adrian Klaver > wrote: > > https://www.postgresql.org/docs/11/functions-json.html > > " The field/element/path extraction operators return NULL, rather than > > failing, if the JSON input does not

Re: jsonb_set() strictness considered harmful to data

Hello, On Fri, Oct 18, 2019 at 5:57 PM Christoph Moench-Tegeder wrote: > > ## Ariadne Conill (aria...@dereferenced.org): > > > Why don't we fix the database engine to not eat data when the > > jsonb_set() operation fails? > > It didn't fail, it worked like SQL (you've been doing SQL for too >

Re: jsonb_set() strictness considered harmful to data

Hello, On Fri, Oct 18, 2019 at 6:01 PM Adrian Klaver wrote: > > On 10/18/19 3:11 PM, Ariadne Conill wrote: > > Hello, > > > > On Fri, Oct 18, 2019 at 5:01 PM David G. Johnston > > wrote: > >> > >> On Fri, Oct 18, 2019 at 2:50 PM Christoph Moench-Tegeder > >> wrote: > >>> > >>> ## Ariadne

Re: jsonb_set() strictness considered harmful to data

On 10/18/19 3:11 PM, Ariadne Conill wrote: Hello, On Fri, Oct 18, 2019 at 5:01 PM David G. Johnston wrote: On Fri, Oct 18, 2019 at 2:50 PM Christoph Moench-Tegeder wrote: ## Ariadne Conill (aria...@dereferenced.org): update users set info=jsonb_set(info, '{bar}', info->'foo');

Re: jsonb_set() strictness considered harmful to data

Hello, On Fri, Oct 18, 2019 at 5:01 PM David G. Johnston wrote: > > On Fri, Oct 18, 2019 at 2:50 PM Christoph Moench-Tegeder > wrote: >> >> ## Ariadne Conill (aria...@dereferenced.org): >> >> >update users set info=jsonb_set(info, '{bar}', info->'foo'); >> > >> > Typically, this works

Re: jsonb_set() strictness considered harmful to data

On Fri, Oct 18, 2019 at 2:50 PM Christoph Moench-Tegeder wrote: > ## Ariadne Conill (aria...@dereferenced.org): > > >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

Re: jsonb_set() strictness considered harmful to data

## Ariadne Conill (aria...@dereferenced.org): >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

Re: jsonb_set() strictness considered harmful to data

On Fri, Oct 18, 2019, at 12:37, Ariadne Conill wrote: > Hello, > > I am one of the primary maintainers of Pleroma, a federated social > networking application written in Elixir, which uses PostgreSQL in > ways that may be considered outside the typical usage scenarios for > PostgreSQL. > >

jsonb_set() strictness considered harmful to data

Hello, I am one of the primary maintainers of Pleroma, a federated social networking application written in Elixir, which uses PostgreSQL in ways that may be considered outside the typical usage scenarios for PostgreSQL. Namely, we leverage JSONB heavily as a backing store for JSON-LD

RE: Execute a function through fdw

Le ven. 18 oct. 2019 à 17:53, Patrick FICHE mailto:patrick.fi...@aqsacom.com>> a écrit : Hi, I got one more issue after I created my view. I created it on my Server 1 but I am unable to view it on the Server 2. I can see all tables through fdw after IMPORT FOREIGN SCHEMA. I was able to get

RE: Execute a function through fdw

Hi, I got one more issue after I created my view. I created it on my Server 1 but I am unable to view it on the Server 2. I can see all tables through fdw after IMPORT FOREIGN SCHEMA. I was able to get access to my view only after recreating the SERVER / USER MAPPING on Server 2. Is it the

Re: DBD::Pg exorts char columns with trailing blanks

On 10/18/19 9:19 AM, Adrian Klaver wrote: On 10/18/19 8:15 AM, Rob Sargent wrote: On 10/18/19 8:51 AM, Adrian Klaver wrote: On 10/18/19 7:42 AM, Matthias Apitz wrote: El día viernes, octubre 18, 2019 a las 03:01:58p. m. +0200, Tom Lane escribió: Matthias Apitz writes: When we export

Re: DBD::Pg exorts char columns with trailing blanks

On 10/18/19 8:15 AM, Rob Sargent wrote: On 10/18/19 8:51 AM, Adrian Klaver wrote: On 10/18/19 7:42 AM, Matthias Apitz wrote: El día viernes, octubre 18, 2019 a las 03:01:58p. m. +0200, Tom Lane escribió: Matthias Apitz writes: When we export char columns with our Perl tools, they come out

Re: DBD::Pg exorts char columns with trailing blanks

On 10/18/19 8:51 AM, Adrian Klaver wrote: On 10/18/19 7:42 AM, Matthias Apitz wrote: El día viernes, octubre 18, 2019 a las 03:01:58p. m. +0200, Tom Lane escribió: Matthias Apitz writes: When we export char columns with our Perl tools, they come out  with trailing blanks (in Sybase they

Re: DBD::Pg exorts char columns with trailing blanks

On 10/18/19 7:42 AM, Matthias Apitz wrote: El día viernes, octubre 18, 2019 a las 03:01:58p. m. +0200, Tom Lane escribió: Matthias Apitz writes: When we export char columns with our Perl tools, they come out with trailing blanks (in Sybase they don't). Can this be suppressed? Switch to

Re: DBD::Pg exorts char columns with trailing blanks

El día viernes, octubre 18, 2019 a las 03:01:58p. m. +0200, Tom Lane escribió: > Matthias Apitz writes: > > When we export char columns with our Perl tools, they come out with > > trailing blanks (in Sybase they don't). Can this be suppressed? > > Switch to varchar, perhaps? > >

Re: connection timeout with psycopg2

On 10/16/19 2:29 AM, Vicente Juan Tomas Monserrat wrote: Hi there, I have been testing out the following architecture for PostgreSQL HA. |+-+ +-+ VIP ++ | +-+ | | | +--v---+ +--v---+ | pgBouncer | | pgBouncer | | + | | + | | keepalived | | keepalived |

RE: CVE-2018-1058

Thanks a lot. It worked! I will have to upgrade the 9.6.5 later. It will me take me more time. Thank you again. -Mensaje original- De: Adrian Klaver [mailto:adrian.kla...@aklaver.com] Enviado el: jueves, 17 de octubre de 2019 10:23 Para: Lizeth Solis Aramayo;

Re: DBD::Pg exorts char columns with trailing blanks

Matthias Apitz writes: > When we export char columns with our Perl tools, they come out with trailing > blanks (in Sybase they don't). Can this be suppressed? Switch to varchar, perhaps? regards, tom lane

Visibility Map Issues

Hello all, We have a data warehouse (postgres 11.5, on centos 7) that contains many instances of the following structure: - table_a - table_b - a view that selects everything from either table_a or table_b All external queries select from the view, so we can rebuild the table that isn't

RE: Execute a function through fdw

Thanks a lot for your answer. Using a view is really a good solution for my case. As I already use fdw for some other cases, I prefer not to mix with dblink. Regards, Patrick Fiche -Original Message- From: Tom Lane Sent: Friday, October 18, 2019 1:55 PM To: Guillaume Lelarge Cc:

DBD::Pg exorts char columns with trailing blanks

Hello, When we export char columns with our Perl tools, they come out with trailing blanks (in Sybase they don't). Can this be suppressed? Thanks matthias -- Sent using Dekko from my Ubuntu device

Re: Postgres Point in time Recovery (PITR),

Hi Daulat, PITR entirely depends on what type of backups you choose. Sometimes, to reduce the amount of downtime involved while restoring and recovering a backup, you may also use a additional delayed standby. You could use the PG built-in feature to delay the replication and fast-forward it to

Re: Execute a function through fdw

Guillaume Lelarge writes: > Le ven. 18 oct. 2019 à 11:51, Patrick FICHE a > écrit : >> Is it possible to execute a function located on a server accessed through >> Postgres fdw. > It's probably easier to create a view on the remote server, and access it > as a foreign table on the local server.

Re: Create role like role

On 10/18/19 5:08 AM, Sonam Sharma wrote: I have created one role reader and granted usage on schema and select all tables role. I have created one more user and have Grant reader to sonam. But still user Sonam is not able to read the tables.. Anything I am missing please let me know ..

Re: Postgres Point in time Recovery (PITR),

On 10/18/19 11:29 AM, Luca Ferrari wrote: On Fri, Oct 18, 2019 at 10:30 AM Andreas Joseph Krogh wrote: We use barman (https://www.pgbarman.org/) for continuous streaming backup and I had to restore from it once, and it went like this: Just for the records, here's an example of restore with

Re: Execute a function through fdw

Le ven. 18 oct. 2019 à 11:51, Patrick FICHE a écrit : > Hello, > > > > Is it possible to execute a function located on a server accessed through > Postgres fdw. > > This function returns a TABLE structure. > > > > I have mapped rmt_schema and there is a function called Get_Tables in this >

Create role like role

I have created one role reader and granted usage on schema and select all tables role. I have created one more user and have Grant reader to sonam. But still user Sonam is not able to read the tables.. Anything I am missing please let me know ..

Execute a function through fdw

Hello, Is it possible to execute a function located on a server accessed through Postgres fdw. This function returns a TABLE structure. I have mapped rmt_schema and there is a function called Get_Tables in this schema. I would like to execute something like : SELECT * FROM

Re: Postgres Point in time Recovery (PITR),

On Fri, Oct 18, 2019 at 10:30 AM Andreas Joseph Krogh wrote: > We use barman (https://www.pgbarman.org/) for continuous streaming backup and > I had to restore from it once, and it went like this: Just for the records, here's an example of restore with pgbackrest: % sudo -u postgres pgbackrest

Re: stable for each row before insert trigger

Luca, I also read this section before ask the question. > 18 окт. 2019 г., в 10:15, Tom Lane написал(а): > > =?utf-8?B?0J7Qu9C10LMg0KHQsNC80L7QudC70L7Qsg==?= writes: >> According to documentation the words "VOLATILE, STABLE, IMMUTABLE" is >> somehow useful with trigger functions, for instance

Re: Sv: Conflict between autovacuum and backup restoration

El 17/10/19 a las 16:12, Andreas Joseph Krogh escribió: But I don't understand why I'm getting those messages about autovacuum blocking db restore process. I guess that after one table is created with COPY sentence, as many rows have been inserted, autoanalyze process runs

Re: Postgres Point in time Recovery (PITR),

On 18 October 2019 07:59:21 CEST, Daulat Ram wrote: >Hello All, >Can you please share some ideas and scenarios how we can do the PITR in >case of disaster. > > >Thanks, Consider Barman. -- 2ndQuadrant - The PostgreSQL Support Company

Sv: Postgres Point in time Recovery (PITR),

På fredag 18. oktober 2019 kl. 07:59:21, skrev Daulat Ram < daulat@exponential.com >: Hello All, Can you please share some ideas and scenarios how we can do the PITR in case of disaster. We use barman (https://www.pgbarman.org/

Re: Postgres Point in time Recovery (PITR),

On Fri, Oct 18, 2019 at 7:59 AM Daulat Ram wrote: > Can you please share some ideas and scenarios how we can do the PITR in case > of disaster. In order to be able to do PITR you need: - a base backup of your database - WALs from the backup going on See

Re: stable for each row before insert trigger

On Fri, Oct 18, 2019 at 9:16 AM Tom Lane wrote: > > =?utf-8?B?0J7Qu9C10LMg0KHQsNC80L7QudC70L7Qsg==?= writes: > > According to documentation the words "VOLATILE, STABLE, IMMUTABLE" is > > somehow useful with trigger functions, for instance mentioned that the > > AFTER INSERT trigger should be

Re: stable for each row before insert trigger

=?utf-8?B?0J7Qu9C10LMg0KHQsNC80L7QudC70L7Qsg==?= writes: > According to documentation the words "VOLATILE, STABLE, IMMUTABLE" is somehow > useful with trigger functions, for instance mentioned that the AFTER INSERT > trigger should be VOLATILE. The question is how this words affect a for each

Re: A little confusion about JSON Path

Laurenz Albe schrieb am 17.10.2019 um 13:25: >> I don't understand why the following two JSON Path expressions aren't doing >> the same thing in Postgres 12: >> >> jsonb_path_exists(data, '$.k1.list.type() ? (@ == "array")'), -- returns >> true as expected >> jsonb_path_exists(data, '$.k1.list

Re: stable for each row before insert trigger

Eh, stupid answer. Of cause, I read the documentation. But what about you? Do you know what is a trigger function? (for each row before insert) A trigger function don't have parameters, instead it get special variables. OLD and NEW for instance or TG_ARGV[]. If trigger function depends only on