Create array of data from JSONB in PG 9.5

Hi, I wrote the below SQL which works and give me what I want, but it doesn’t work in 9.5 version. I wrote this query in PG 10. I want to change it so that I can run it on 9.5 as production using 9.5 version. I need help on this. SELECT DISTINCT ARRAY [jsonb_array_elements(data) ->> 'gr

Race condition while creating a new partition

We use this code in order to automatically create new partitions for a partitioned table (Postgres 10.6): begin insert into ; exception when undefined_table then begin -- A concurrent txn has created the new partition exception when others then end; -- Insert data into the ne

naming triggers for execution

On create trigger page of the doc: If multiple triggers of the same kind are defined for the same event, they will be fired in alphabetical order by name. But suppose we have two triggers, one is called for every table for auditing purposes, for example. And other for a specific process of that tab

Re: naming triggers for execution

> Considering that the auditing needs to be the last, how can I be sure it´ll > ran lastly ? > IMHO, auditing should be done in after- triggers, when that the data in the records being inserted, updated or deleted can't be changed but you can still write to the separate auditing tables. -- Mike No

porting horde to Postgresql 12, dropped pg_attrdef

Horde (webmail and more) is not anymore compatible with postgresql 12 after pg_attrdef was dropped. Since I'm a Horde user and I've always liked PostgreSQL I'm trying to update these queries 1) SELECT attr.attname, CASE WHEN split_part(def.adsrc, , 2) ~ '.' THEN substr(spl

Re: Query which shows FK child columns?

Jeff Ross writes: > On 11/14/19 11:49 AM, Ron wrote: >> I have a query which shows the parents and children in FK relations, >> along with the parent column name, but can't seem to find the child >> column names. >> Is there a way to find the child column names without having to dig >> into pg_

Re: naming triggers for execution

> But suppose we have two triggers, one is called for every table for auditing > purposes, for example. And other for a specific process of that table. > Considering that the auditing needs to be the last, how can I be sure it´ll > ran lastly ?     May be I am not getting the full picture.  Why

Re: naming triggers for execution

well, my way of doing auditing is done on replica, so it´s a little different on production server I do on before update IF (tg_op = 'UPDATE') AND (new.* IS DISTINCT FROM old.*) THEN new.userauditing = User_ID new.datetimeauditing = current_timestamp; END IF; RETURN new; Then, on re

Re: PostGreSQL Replication and question on maintenance

On Thu, Nov 14, 2019 at 11:42 PM Pavel Stehule wrote: > these numbers looks crazy high - how much memory has your server - more > than 1TB? > The cluster got 244 GB of RAM and storage capacity it has is 64 TB. > > > pá 15. 11. 2019 v 6:26 odesílatel github kran > napsal: > >> >> Hello postGr

Re: naming triggers for execution

I know it´s possible, but it´s ugly. When you see a trigger called zaudittable is really strange -- Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: porting horde to Postgresql 12, dropped pg_attrdef

Ivan Sergio Borgonovo writes: > Horde (webmail and more) is not anymore compatible with postgresql 12 > after pg_attrdef was dropped. We didn't drop the whole catalog, just the adsrc column, which has been deprecated for ages (because it didn't update to track, eg, column renamings). The correc

Re: naming triggers for execution

On 11/15/19 7:11 AM, PegoraroF10 wrote: well, my way of doing auditing is done on replica, so it´s a little different on production server I do on before update IF (tg_op = 'UPDATE') AND (new.* IS DISTINCT FROM old.*) THEN new.userauditing = User_ID new.datetimeauditing = current_ti

Re: Create array of data from JSONB in PG 9.5

On 11/15/19 2:01 AM, Arup Rakshit wrote: Hi, I wrote the below SQL which works and give me what I want, but it doesn’t work in 9.5 version. I wrote this query in PG 10. I want to change it so that I can run it on 9.5 as production using 9.5 version. I need help on this. SELECT DISTINCT

Re: Create array of data from JSONB in PG 9.5

Arup Rakshit writes: > I wrote the below SQL which works and give me what I want, but it doesn’t > work in 9.5 version. I wrote this query in PG 10. I want to change it so that > I can run it on 9.5 as production using 9.5 version. I need help on this. > SELECT DISTINCT > ARRAY [jsonb_arr

Authentication: MD5 to SCRAM-SHA-256 error

Hello, We're currently using PostgreSQL version 10.5 in a Linux environment. We were wanting to change the password authentication from MD5 to SCRAM-SHA-256. I performed these steps to do so: 1) Modified the postgresql.conf and changed the password_encryption entry from "md5" to "scram-sha-256".

Re: Authentication: MD5 to SCRAM-SHA-256 error

On 11/15/19 8:23 AM, Dave Hughes wrote: Hello, We're currently using PostgreSQL version 10.5 in a Linux environment. We were wanting to change the password authentication from MD5 to SCRAM-SHA-256.  I performed these steps to do so: 1) Modified the postgresql.conf and changed the password_encr

Re: Authentication: MD5 to SCRAM-SHA-256 error

## Dave Hughes (dhughe...@gmail.com): > However when I try to log in now, via command line, I receive the error: > "psql: authentication method 10 not supported". Your client (more precisely: it's libpq) is not ready for SCRAM. I guess you're using an older (<10) client version? Mixed up packages

access to original-statement predicates in an INSTEAD-OF row trigger

I have an INSTEAD-OF row trigger one of whose purposes is to re-direct the original statement (which is operating on a VIEW) to a different real base table. Suppose the original statement is            UPDATE myview VW set VW.counter = 11 where VW.primary_key = and AND VW.counter = 10; an

pgpool High Availability Issue

Hi, I'm working on configuring high availability for pgpool using watchdog. Initially, I tried with two pgpool nodes (along with a pgmaster and pgslave). In this scenario, assuming pgpool node 1 was started first and became the leader. After sometime , the node got disconnected with pgpool node 2

Re: jsonb_set() strictness considered harmful to data

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

Re: here does postgres take its timezone information from?

Palle Girgensohn writes: >> 6 nov. 2019 kl. 03:03 skrev Thomas Munro : >>> *It looks like FreeBSD's port uses the copy of tzdata from the >>> PostgreSQL source tree by default and thus that is what you get if you >>> install PostgreSQL with "pkg". That's not a great default IMHO and >>> should be

Re: access to original-statement predicates in an INSTEAD-OF row trigger

On 11/15/19 10:37 AM, John Lumby wrote: I have an INSTEAD-OF row trigger one of whose purposes is to re-direct the original statement (which is operating on a VIEW) to a different real base table. Suppose the original statement is           UPDATE myview VW set VW.counter = 11 where VW.primary

Re: jsonb_set() strictness considered harmful to data

On 11/15/19 2:14 PM, Pavel Stehule wrote: > Hi > > > > 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

Re: Authentication: MD5 to SCRAM-SHA-256 error

Thanks for replying guys! You both led me down the right path. I didn't realize it, but looks like we had 2 directories where psql was installed. Once I gave the path specifically to PostgreSQL10, it worked like a charm. I'm reaching out to our server admins now to see why there are 2 instances.

Re: here does postgres take its timezone information from?

On Sat, Nov 16, 2019 at 8:38 AM Tom Lane wrote: > Palle Girgensohn writes: > >> 6 nov. 2019 kl. 03:03 skrev Thomas Munro : > >>> *It looks like FreeBSD's port uses the copy of tzdata from the > >>> PostgreSQL source tree by default and thus that is what you get if you > >>> install PostgreSQL wit

Re: jsonb_set() strictness considered harmful to data

pá 15. 11. 2019 v 21:01 odesílatel Andrew Dunstan < andrew.duns...@2ndquadrant.com> napsal: > > On 11/15/19 2:14 PM, Pavel Stehule wrote: > > Hi > > > > > > > > For release 13+, I have given some more thought to what should be > > done. > > I think the bar for altering the behaviour of

Re: access to original-statement predicates in an INSTEAD-OF row trigger

Adrian Klaver wrote : > > On 11/15/19 10:37 AM, John Lumby wrote: > > > Suppose the original statement is > > > UPDATE myview VW set VW.counter = 11 where VW.primary_key = and > > VW.counter = 10; > > > and my trigger constructs this statement > > > UPDATE basetable BT set BT.counte

Re: access to original-statement predicates in an INSTEAD-OF row trigger

On 11/15/19 12:57 PM, John Lumby wrote: Adrian Klaver wrote : On 11/15/19 10:37 AM, John Lumby wrote: Suppose the original statement is UPDATE myview VW set VW.counter = 11 where VW.primary_key = and VW.counter = 10; and my trigger constructs this statement UPDATE basetab

Re: access to original-statement predicates in an INSTEAD-OF row trigger

Adrian Klaver wrote : > > Seems you are looking for Serializable Isolation Level: > True , that would solve the race condition, but it is too drastic. We need to run with Read Committed. I am looking for a solution which does not alter the application or overall behaviour, but just addresses

Re: access to original-statement predicates in an INSTEAD-OF row trigger

On 11/15/19 1:54 PM, John Lumby wrote: Adrian Klaver wrote : Seems you are looking for Serializable Isolation Level: True ,   that would solve the race condition,  but it is too drastic. We need to run with Read Committed. I am looking for a solution which does not alter the application or

Re: here does postgres take its timezone information from?

> 15 nov. 2019 kl. 21:32 skrev Thomas Munro : > > On Sat, Nov 16, 2019 at 8:38 AM Tom Lane > wrote: >> Palle Girgensohn writes: 6 nov. 2019 kl. 03:03 skrev Thomas Munro : > *It looks like FreeBSD's port uses the copy of tzdata from the > PostgreSQL sourc

Re: here does postgres take its timezone information from?

Palle Girgensohn writes: > 15 nov. 2019 kl. 21:32 skrev Thomas Munro : >> Ugh. It doesn't have the old backward compatibility names like >> US/Pacific installed by default, which is a problem if that's what >> initdb picked for your cluster (or you've stored references to any of >> those names in