Re: Max connections reached without max connections reached

On Thu, Nov 25, 2021 at 10:53 AM James Sewell wrote: > > >> > Seems like some of the processes are taking a long time or stuck while >> > reading/writing SLRU pages, and due to that while creating a new >> > connection the backend process is not able to check the transaction >> > status (from

Re: Error message while trying to connect from PGAdmin 4

Hello PostgreSQL 11.11,  PGAdmin 4.27, Windows 10 Pro 20H2 Working fine till yesterday (24.11.2021).   When we tried to connect from PGAdmin, it refused to list server list.  Thought of some corruption happened, we tried to add a server we got the following error message What could be the

Re: Max connections reached without max connections reached

> > Seems like some of the processes are taking a long time or stuck while > > reading/writing SLRU pages, and due to that while creating a new > > connection the backend process is not able to check the transaction > > status (from pg_xlog) of the pg_class tuple and gets stuck/taking a > > long

Re: Inserts and bad performance

On 11/24/21 1:15 PM, Godfrin, Philippe E wrote: [snip] I dropped the unique index , rebuilt the other indexes and no change. IMNSHO, this is the worst possible approach.  Drop everything *except* the unique index, and then (if possible) sort the input file by the unique key.   That'll

Re: Max connections reached without max connections reached

On Thu, Nov 25, 2021 at 9:50 AM Dilip Kumar wrote: > > Does that shed any light? > > Seems like some of the processes are taking a long time or stuck while > reading/writing SLRU pages, and due to that while creating a new > connection the backend process is not able to check the transaction >

Re: Max connections reached without max connections reached

On Thu, Nov 25, 2021 at 9:50 AM Dilip Kumar wrote: > > On Thu, Nov 25, 2021 at 8:58 AM James Sewell > wrote: > >> > >> The hypothesis I'm thinking of is that incoming sessions are being blocked > >> somewhere before they can acquire a ProcArray entry; if so, they'd not > >> show up in either

Re: Max connections reached without max connections reached

On Thu, Nov 25, 2021 at 8:58 AM James Sewell wrote: >> >> The hypothesis I'm thinking of is that incoming sessions are being blocked >> somewhere before they can acquire a ProcArray entry; if so, they'd not >> show up in either pg_stat_activity or pg_locks. What we have to look for >> then is

Re: Max connections reached without max connections reached

> > The hypothesis I'm thinking of is that incoming sessions are being blocked > somewhere before they can acquire a ProcArray entry; if so, they'd not > show up in either pg_stat_activity or pg_locks. What we have to look for > then is evidence of somebody holding a strong lock on a shared

Re: [EXTERNAL] Re: Inserts and bad performance

On Thu, 25 Nov 2021 at 08:59, Godfrin, Philippe E wrote: > Hi Tom. Good point about the index paging out of the buffer. I did that and > no change. I do have the shared buffers at 40GB, so there’s a good bit there, > but I also did all those things on the page you referred, except for using >

Re: [EXTERNAL] Re: Inserts and bad performance

How many partitions? How many rows do they have when performance is slowing considerably? Does this table get many updates or is it insert only? What version of PostgreSQL? Are the inserts randomly distributed among the partitions or targeting one or a few partitions? Are you able to capture an

RE: [EXTERNAL] Re: Inserts and bad performance

The notion of COPY blocks and asynchronously is very interesting From: Gavin Roy Sent: Wednesday, November 24, 2021 1:50 PM To: Godfrin, Philippe E Cc: pgsql-general@lists.postgresql.org Subject: [EXTERNAL] Re: Inserts and bad performance On Wed, Nov 24, 2021 at 2:15 PM Godfrin, Philippe E

RE: [EXTERNAL] Re: Inserts and bad performance

Hi Tom. Good point about the index paging out of the buffer. I did that and no change. I do have the shared buffers at 40GB, so there's a good bit there, but I also did all those things on the page you referred, except for using copy. At this point the data has not been scrubbed, so I'm

Re: Inserts and bad performance

On Wed, Nov 24, 2021 at 2:15 PM Godfrin, Philippe E < philippe.godf...@nov.com> wrote: > Greetings > > I am inserting a large number of rows, 5,10, 15 million. The python code > commits every 5000 inserts. The table has partitioned children. > On the Python client side, if you're using psycopg,

Re: Inserts and bad performance

"Godfrin, Philippe E" writes: > I am inserting a large number of rows, 5,10, 15 million. The python code > commits every 5000 inserts. The table has partitioned children. > At first, when there were a low number of rows inserted, the inserts would > run at a good clip - 30 - 50K inserts per

RE: [EXTERNAL] Re: Inserts and bad performance

My apologies for the dearth of details. No on both the cpu and errors. But I do believe it is IO related. I just can't find it. I thought maybe it was index splitting so I altered the unique index with filterfactor=40 and reindexed. No change. I then dropped the unique index. No change. I

Re: Inserts and bad performance

On Wed, Nov 24, 2021 at 07:15:31PM +, Godfrin, Philippe E wrote: > Greetings > I am inserting a large number of rows, 5,10, 15 million. The python code > commits every 5000 inserts. The table has partitioned children. > > At first, when there were a low number of rows inserted, the inserts

Inserts and bad performance

Greetings I am inserting a large number of rows, 5,10, 15 million. The python code commits every 5000 inserts. The table has partitioned children. At first, when there were a low number of rows inserted, the inserts would run at a good clip - 30 - 50K inserts per second. Now, after inserting oh

Re: RPM package issue

Hi Adrian, thanks for the hint! Ales st 24. 11. 2021 v 18:16 odesílatel Adrian Klaver napsal: > On 11/24/21 08:47, Aleš Zelený wrote: > > Hello all, > > > > I've found on the wiki ( https://wiki.postgresql.org/wiki/Packagers > > ) that the

Re: RPM package issue

On 11/24/21 08:47, Aleš Zelený wrote: Hello all, I've found on the wiki ( https://wiki.postgresql.org/wiki/Packagers ) that the packagers list Is closed, so the first question is how to properly report a package issue? Particularly I've realized,

Re: get last timestamp of table ddl

On Wed, Nov 24, 2021 at 3:30 PM Achilleas Mantzios wrote: > You mean like trying to correlate pg_class.xmin with some timestamp via > track_commit_timestamp or other means? Pretty much yes: since pg_class and pg_attribute comes to my mind. Luca

Re: get last timestamp of table ddl

On Wed, Nov 24, 2021 at 3:09 PM hubert depesz lubaczewski wrote: > You could add event triggers to achieve similar functionality. > https://www.depesz.com/2012/07/29/waiting-for-9-3-event-triggers/ > and > https://www.postgresql.org/docs/current/sql-createeventtrigger.html Thanks, I was ware of

Re: RPM package issue

On 11/24/21 08:47, Aleš Zelený wrote: Hello all, I've found on the wiki ( https://wiki.postgresql.org/wiki/Packagers ) that the packagers list Is closed, so the first question is how to properly report a package issue?

RPM package issue

Hello all, I've found on the wiki ( https://wiki.postgresql.org/wiki/Packagers ) that the packagers list Is closed, so the first question is how to properly report a package issue? Particularly I've realized, that in the repository list /etc/yum.repos.d/pgdg-redhat-all.repo provided by

Re: get last timestamp of table ddl

On 24/11/21 3:53 μ.μ., Luca Ferrari wrote: Hi all, I think I already know the answer, however I came across this table in Oracle that has two columns that triggered my attention: CREATED and LAST_DDL_TIME.

Re: How to set alias data type?

On Wed, Nov 24, 2021 at 01:57:06PM +, Shaozhong SHI wrote: > select 'Total' as Total generate result that set Total as a column name > with unknown type > > When trying to cast > select 'Total' as Total:: text You need to cast value, and not name. select 'Total'::text as Total; Best

Re: get last timestamp of table ddl

On Wed, Nov 24, 2021 at 02:53:24PM +0100, Luca Ferrari wrote: > Hi all, > I think I already know the answer, however I came across this table in > Oracle > > that has two columns that triggered my attention:

How to set alias data type?

select 'Total' as Total generate result that set Total as a column name with unknown type When trying to cast select 'Total' as Total:: text It simply does not work. Regards, David

get last timestamp of table ddl

Hi all, I think I already know the answer, however I came across this table in Oracle that has two columns that triggered my attention: CREATED and LAST_DDL_TIME. Apart from being dates (in the Oracle way), they