Re: jsonb_set() strictness considered harmful to data

Hello, On Sat, Oct 19, 2019 at 12:52 AM Pavel Stehule wrote: > > > > so 19. 10. 2019 v 7:41 odesílatel David G. Johnston > napsal: >> >> On Friday, October 18, 2019, Pavel Stehule wrote: >> >>> >>> Probably there will be some applications that needs NULL result in >>> situations when value wa

Re: jsonb_set() strictness considered harmful to data

so 19. 10. 2019 v 7:41 odesílatel David G. Johnston < david.g.johns...@gmail.com> napsal: > On Friday, October 18, 2019, Pavel Stehule > wrote: > > >> Probably there will be some applications that needs NULL result in >> situations when value was not changed or when input value has not expected >

Re: jsonb_set() strictness considered harmful to data

On Friday, October 18, 2019, Pavel Stehule wrote: > Probably there will be some applications that needs NULL result in > situations when value was not changed or when input value has not expected > format. Design using in Postgres allows later customization - you can > implement with COALESCE ve

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 gi

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 Da

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 o

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: ## Ariadne

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 did

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 hav

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 > lon

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 Conil

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'); Typi

Re: jsonb_set() strictness considered harmful to data

## 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 long when you get used to the NULL propagation, but that's still what SQL does - check "+"

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 nicel

Re: jsonb_set() strictness considered harmful to data

Hello, On Fri, Oct 18, 2019 at 4: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

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 bein

Replication of Replication

I have 3 servers running CentOS+PG 11 (postgresql11-server-11.5-1PGDG.rhel7.x86_64): - s1: main db + publication - s2: subscription of the main db all works fine until here... Now, I tried to config this: - s2: publication of the same db that was replicated from s1 - s3: subscription of the db fr

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 the

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. > > Nam

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 documents[1

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 acce

Re: Execute a function through fdw

Le ven. 18 oct. 2019 à 17:53, Patrick FICHE 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 access to my view only af

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 exp

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 cha

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 d

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 va

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; pgsql-gene...@postgresql.

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 e

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: Pat

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 th

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 .. You

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 p

Re: stable for each row before insert trigger

On Fri, Oct 18, 2019 at 11:18 AM Олег Самойлов wrote: > > STABLE indicates (It is inappropriate for AFTER triggers that wish to > > query rows modified by the current command.) > > So, STABLE is inappropriate for such trigger, but is appropriate for BEFORE > trigger? > Well, a before trigg

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 > schema

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 rmt_schema.Get_Tables

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),

Exactly, in our environment, we have a full backup of directory PGDATA and every 15 minutes we do archival backup where there are archival file. So we can restore in a specific time. Before the archival backup we forced switch wal file to generate archival file so we don't lost any transaction.

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 VO

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