Re: Fixing set-returning functions are not allowed in UPDATE after upgrade to Postgres 12

Hi! Simply replace SET col = unnest(array_value) with SET col = array_value[1] I tried update temprid set ContactFirstName =xpath( '/E-Document/Document/DocumentParties/BuyerParty/ContactData/ContactFirstName/text()',x)[1]::text But got error ERROR: syntax error at or n

Re: Could someone please help us share the procedure to troubleshoot the locks on proc issues.

Thanks Adrian, will share the details. On Fri, Apr 3, 2020 at 4:30 AM Adrian Klaver wrote: > On 4/2/20 12:37 PM, postgann2020 s wrote: > > Hi Team, > > > > Good Evening, > > > > Could someone please help us share the procedure to troubleshoot the > > locks on proc issues. > > > > Environment: >

Re: Backing out of privilege grants rabbit hole

Granted. But we are where we are, so I'm assuming this is going to be hand to hand combat. On Fri, Apr 3, 2020, 12:57 AM raf wrote: > It's probably more sensible to grant permissions to roles that > represent groups, and have roles for individual users that > inherit the permissions of the group

Re: Backing out of privilege grants rabbit hole

It's probably more sensible to grant permissions to roles that represent groups, and have roles for individual users that inherit the permissions of the group roles. Then you don't need to revoke the permissions just because an individiual has left. cheers, raf AC Gomez wrote: > Thanks for the q

Improve COPY performance into table with indexes.

I am using the COPY command to insert 10 million rows from a CSV file into a database table and I am finding the performance is unacceptable. When the COPY command is executed the disk I/O is extremely high which leads to degraded query performance for other queries being executed on the database.

Re: Backing out of privilege grants rabbit hole

Thanks for the quick response. The problem is, in most cases the owner is not the grantee. So if a role, let's say a temp employee, gets grants, then leaves, I can't do a drop owned because that temp never owned those objects, he just was granted access. Is there a "drop granted" kind of thing? On

Re: Backing out of privilege grants rabbit hole

More fully: REASSIGN OWNED BY doomed_role TO successor_role; DROP OWNED BY doomed_role; -- repeat the above commands in each database of the cluster DROP ROLE doomed_role; > On Apr 2, 2020, at 20:37 , Guyren Howe wrote: > > https://www.postgresql.org/docs/12/sql-drop-owned.html >

Re: Backing out of privilege grants rabbit hole

https://www.postgresql.org/docs/12/sql-drop-owned.html > On Apr 2, 2020, at 20:34 , AC Gomez wrote: > > Do I understand correctly that if a role was assigned countless object > privileges and you want to delete that role you have to sift

Backing out of privilege grants rabbit hole

Do I understand correctly that if a role was assigned countless object privileges and you want to delete that role you have to sift through a myriad of privilege grants in what amounts to a time consuming trial and error exercise until you've got them all? Or is there a single command that with ju

Re: Could someone please help us share the procedure to troubleshoot the locks on proc issues.

On 4/2/20 12:37 PM, postgann2020 s wrote: Hi Team, Good Evening, Could someone please help us share the procedure to troubleshoot the locks on proc issues. Environment:  1 pgpool server (Master Pool Node) using Straming replication with load balancing  4 DB nodes (1Master an

Re: Cstore_fdw issue.

On 4/2/20 1:40 PM, Moses Mafusire wrote: Hi, I am new to PostgreSQL, successfully installed PGSql v12.2 on my CentOS 7 machine and I am trying to install cstore_fdw.     1. I have managed to run this command; /sudo yum install protobuf-c-devel/ / / /    2. /Per the instructions I am followin;

Re: Help to find-the-maximum-length-of-field-in-a-particular-column-in-all the tables

Please just choose a single list to email. On Thursday, April 2, 2020, postgann2020 s wrote: > Hi Team, > > Good Evening, > > We are trying to syncing the table field size with other db tables filed > size. > I wouldn’t bother fiddling with arbitrary sizes, just remove,them. > We have multipl

Cstore_fdw issue.

Hi,I am new to PostgreSQL, successfully installed PGSql v12.2 on my CentOS 7 machine and I am trying to install cstore_fdw.     1. I have managed to run this command; sudo yum install protobuf-c-devel     2. Per the instructions I am followin; Once you have protobuf-c installed on

Help to find-the-maximum-length-of-field-in-a-particular-column-in-all the tables

Hi Team, Good Evening, We are trying to syncing the table field size with other db tables filed size. We have multiple tables in multiple schemas in DB. we are tyring to findout maximum size of each column in table for all tables and for all schemas in DB. How to find the maximum length of data

Could someone please help us share the procedure to troubleshoot the locks on proc issues.

Hi Team, Good Evening, Could someone please help us share the procedure to troubleshoot the locks on proc issues. Environment: 1 pgpool server (Master Pool Node) using Straming replication with load balancing 4 DB nodes (1Master and 3 Slaves). Versions: 1. postgres: 9.5.15 2.

RE: Re: too many clients already

Agree. I suspect that this is a mal configured pgpool - the developer thinks that the pool is reusing connections, While it is, in fact, reopening them. -Original Message- From: Tom Lane Sent: Thursday, April 02, 2020 7:40 PM To: Abraham, Danny Cc: pgsql-gene...@postgresql.org Subject

Re: too many clients already

"Abraham, Danny" writes: > Well, I guess the questions is - how do I optimize PG for a stream of very > short life checks... You should be using a connection pooler for a load like that. PG backends are fairly heavyweight things --- you don't want to fire one up for just a single query, at least

RE: Re: too many clients already

Well, I guess the questions is - how do I optimize PG for a stream of very short life checks... See below: 2020-04-02 11:05:37.010 CDTLOG: connection received: host=10.64.72.157 port=45799 2020-04-02 11:05:37.014 CDTLOG: connection received: host=10.64.72.157 port=45814 2020-04-02 11:05:37.01

Re: too many clients already

"Abraham, Danny" writes: > Running on a big and stressed AIX platform and receiving lots of "CDTFATAL: > sorry, too many clients already" > and transient difficulty to log in. > Happens on all PG versions (Tested 9.5,10.4,11.5) > Big installation: max_connections is 1200, shared_buffers is 2GB

RE: Re: too many clients already

va-tlv-ctm-qa22.isr.bmc.com% sql psql: FATAL: sorry, too many clients already va-tlv-ctm-qa22.isr.bmc.com% sql psql (11.5) Type "help" for help. ctrlmdb=> show max_connections; max_connections - 1200 (1 row) ctrlmdb=> show shared_buffers; shared_buffers 2000M

Re: too many clients already

On 4/2/20 8:35 AM, Abraham, Danny wrote: Big installation: max_connections is 1200, shared_buffers is 2GB Have you confirmed that the above is actually in effect by doing?: show max_connections; -Original Message- From: Adrian Klaver Sent: Thursday, April 02, 2020 6:30 PM To: Abra

RE: Re: too many clients already

Big installation: max_connections is 1200, shared_buffers is 2GB -Original Message- From: Adrian Klaver Sent: Thursday, April 02, 2020 6:30 PM To: Abraham, Danny ; pgsql-gene...@postgresql.org Subject: [EXTERNAL] Re: too many clients already On 4/2/20 8:22 AM, Abraham, Danny wrote: > N

Re: too many clients already

On 4/2/20 8:22 AM, Abraham, Danny wrote: No pg-bouncer or connection pooling. ps -elf | grep postgres | grep idle | wc -l ==>61 and BTW: Running, say 500 one command psql in parallel will have the same affect.. Hmm. In psql on the cluster in question what does below return?: show max_co

RE: Re: too many clients already

No pg-bouncer or connection pooling. ps -elf | grep postgres | grep idle | wc -l ==>61 and BTW: Running, say 500 one command psql in parallel will have the same affect.. -Original Message- From: Rob Sargent Sent: Thursday, April 02, 2020 6:10 PM To: Abraham, Danny Cc: pgsql-gene.

Re: too many clients already

On 4/2/20 8:06 AM, Abraham, Danny wrote: Hi, Will appreciate a hint here. Running on a big and stressed AIX platform and receiving lots of "CDTFATAL: sorry, too many clients already" and transient difficulty to log in. Happens on all PG versions (Tested 9.5,10.4,11.5) Big installation: max_

Re: too many clients already

> On Apr 2, 2020, at 9:06 AM, Abraham, Danny wrote: > > Hi, > > Will appreciate a hint here. > > Running on a big and stressed AIX platform and receiving lots of "CDTFATAL: > sorry, too many clients already" > and transient difficulty to log in. > > Happens on all PG versions (Tested 9.5,

too many clients already

Hi, Will appreciate a hint here. Running on a big and stressed AIX platform and receiving lots of "CDTFATAL: sorry, too many clients already" and transient difficulty to log in. Happens on all PG versions (Tested 9.5,10.4,11.5) Big installation: max_connections is 1200, shared_buffers is 2GB

Re: Fixing set-returning functions are not allowed in UPDATE after upgrade to Postgres 12

On Thu, 2020-04-02 at 17:48 +0300, Andrus wrote: > After upgrading to Postgres 12 statement > > update temprid set > ContactFirstName =unnest(xpath( > > '/E-Document/Document/DocumentParties/BuyerParty/ContactData/ContactFirstName/text()',x))::text, > yhik =unnest(xpath( >

Fixing set-returning functions are not allowed in UPDATE after upgrade to Postgres 12

Hi! After upgrading to Postgres 12 statement update temprid set ContactFirstName =unnest(xpath( '/E-Document/Document/DocumentParties/BuyerParty/ContactData/ContactFirstName/text()',x))::text, yhik =unnest(xpath( '/E-Document/Document/DocumentItem/ItemEntry/BaseUnit/text

Re: using a common key value on both sides of a union ?

On Wed, 2020-04-01 at 19:39 -0400, David Gauthier wrote: > psql (9.6.7, server 11.3) on linux > > I want to do something like this > (intentionally bad sql but will illustrate the need) > > select s.name,s.grade from students s where s.class='math' > union > select 'whole class', class_grade fro

Re: postgres: archiver process failed on 000000010000020A00000073

thanks Alvaro, following your instructions I solved the problem. Il 01/04/2020 19:36, Alvaro Herrera ha scritto: > On 2020-Apr-01, Silvio Fabi - NBS srl wrote: > >> WAL Archive process on my DB Postgresql 10 is not working. >> the WAL Archive file 0001020A0073 was accidentally deleted

EINTR while resizing dsm segment.

I provided the subject, and added -hackers. > Hello, > I am running postgres 11.5 and we were having issues with shared segments. > So I increased the max_connection as suggested by you guys and reduced my > work_mem to 600M. > > Right now instead, it is the second time I see this error : > > ER