Re: Max connections reached without max connections reached

James Sewell writes: >> I'm also wondering a bit about whether they're being blocked on a lock, >> eg. due to something taking an exclusive lock on pg_authid or pg_database. >> pg_locks might be interesting to check. > postgres=# select * from pg_locks where not granted; The hypothesis I'm think

Re: Max connections reached without max connections reached

> > > > It's used to trigger ETL jobs. There are other bigger consumers of > > connections - the issue isn't the stream of jobs, that works fine under > > normal operation. The issue is that when jobs stay in "startup" and > > don't enter pg_stat_activity the system spirals downwards and no new > >

Re: Max connections reached without max connections reached

On 11/23/21 16:58, James Sewell wrote: > re: EnterpriseDB yes it is - I'm having this same discussion with them > in parallel What version of theirs? PostgreSQL 11.9 (EnterpriseDB Advanced Server 11.9.18) on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat

Re: Max connections reached without max connections reached

> > re: EnterpriseDB yes it is - I'm having this same discussion with them > > in parallel > > What version of theirs? > PostgreSQL 11.9 (EnterpriseDB Advanced Server 11.9.18) on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit > > > > re: rundeck, yes - but th

Re: Max connections reached without max connections reached

On 11/23/21 16:23, James Sewell wrote: The enterprisedb is one of their customized versions? rundeck_cluster below refers to https://digital.ai/technology/rundeck ? re: EnterpriseDB yes it is - I'm having this same discussion with them i

Re: Max connections reached without max connections reached

> The enterprisedb is one of their customized versions? > > rundeck_cluster below refers to https://digital.ai/technology/rundeck? > > re: EnterpriseDB yes it is - I'm having this same discussion with them in parallel re: rundeck, yes - but this is only one of many things connecting. it's not doing

Re: Max connections reached without max connections reached

On 11/23/21 15:53, James Sewell wrote: It's v odd as it happens *sometimes* - having said that it's happening right this moment: [enterprisedb@oprpgs001 edb-as-11]$ ps -ef|grep postgres:  | wc -l 517 [enterprisedb@oprpgs001 ~]$ ps -ef|grep postgres: | grep -i start | wc -l 480 The enterpr

Re: Max connections reached without max connections reached

> > > So I guess the question becomes why are they spending so much time in > the startup state. That should take mere milliseconds, unless the > clients are being slow to handle the authentication exchange? > > I'm also wondering a bit about whether they're being blocked on a lock, > eg. due to s

Re: Max connections reached without max connections reached

> > > Sorry, I could have been clearer - pg_stat_activity is what I'm looking > > at - I'm recording connection info from here every 15 seconds (from a > > superuser account so I don't get locked out). It never peaks above 300 > > (in fact when the incident happens no new connections can come in so

Re: Max connections reached without max connections reached

James Sewell writes: > If I measure from `ps -ef | grep postgres` and look at the connections > then I can see that with the startup connections I am hitting this limit. > So client processes which are listed to the OS as "startup" ARE counted > towards the 597 connections, but are NOT reported i

Re: Max connections reached without max connections reached

On 11/23/21 14:56, James Sewell wrote: Sorry, I could have been clearer - pg_stat_activity is what I'm looking at - I'm recording connection info from here every 15 seconds (from a superuser account so I don't get locked out). It never peaks above 300 (in fact when the incident happens no new

Re: Max connections reached without max connections reached

> > What are you looking at to claim the number of connections is under 600? > Maybe there's some disconnect between what you're measuring and what the > database thinks. > > A different line of thought is that ProcArray slots can be consumed by > things that aren't client connection processes, in

Re: Regex for Word space Word space Word ....

Hi, David J, Any good example of doing test on array? Regards, David On Tuesday, 23 November 2021, David G. Johnston wrote: > On Tue, Nov 23, 2021 at 2:58 AM Shaozhong SHI > wrote: > >> Is there any regex for Word space Word space Word and more? >> >> > What problem are you actually trying to

Re: PQexecParams, placeholders and variable lists of params

On Tue, Nov 23, 2021 at 05:14:44PM +0100, Daniel Frey wrote: > > On 23. Nov 2021, at 16:43, Tom Lane wrote: > > > > PG's array quoting rules are odd enough that I can sympathize with not > > wanting to deal with them. (Although, if you only have to build an > > array and not parse one, taking th

Re: PQexecParams, placeholders and variable lists of params

On 23. Nov 2021, at 16:43, Tom Lane wrote: > PG's array quoting rules are odd enough that I can sympathize with not > wanting to deal with them. (Although, if you only have to build an > array and not parse one, taking the always-quote-even-if-not-necessary > approach makes it easier.) > > I do

Re: PQexecParams, placeholders and variable lists of params

On Tue, Nov 23, 2021 at 10:43:03AM -0500, Tom Lane wrote: > "David G. Johnston" writes: > > On Tue, Nov 23, 2021 at 7:21 AM wrote: > >> Makes sense. Problem is, that, again, the application would be > >> responsible of making sure the individual values don't contain nasty > >> stuff (for example,

Re: PQexecParams, placeholders and variable lists of params

"David G. Johnston" writes: > On Tue, Nov 23, 2021 at 7:21 AM wrote: >> Makes sense. Problem is, that, again, the application would be >> responsible of making sure the individual values don't contain nasty >> stuff (for example, if they are strings) before consolidating them to >> one PostgreSQL

Re: PQexecParams, placeholders and variable lists of params

On Tue, Nov 23, 2021 at 7:21 AM wrote: > Makes sense. Problem is, that, again, the application would be > responsible of making sure the individual values don't contain nasty > stuff (for example, if they are strings) before consolidating them to > one PostgreSQL array literal. > > So long as you

Re: Max connections reached without max connections reached

James Sewell writes: > The system handles a lot of connections - we have a max_connections of 600. > Most are long lived JDBC, but there are a lot of ETL / ad-hoc jobs etc. > Connections normally sit at 300ish, with 70 active at the most. The > machines have 32 CPU cores . PgBouncer is sadly not

Re: Regex for Word space Word space Word ....

On Tue, Nov 23, 2021 at 2:58 AM Shaozhong SHI wrote: > Is there any regex for Word space Word space Word and more? > > What problem are you actually trying to solve? You may find it easier to simply split your string on space and then do tests on elements of the resultant array. David J.

Re: PQexecParams, placeholders and variable lists of params

On Tue, Nov 23, 2021 at 06:39:27PM +0500, Дмитрий Иванов wrote: > Hi > A function cannot have an undefined signature, but can accept an array of > arguments: I see. So you propose passing an array as a single param to PQexecParams, in PostgreSQL's syntax for arrays, e.g.. "{42, 45, 50}". Makes se

Re: Regex for Word space Word space Word ....

On Tue, Nov 23, 2021 at 09:58:00AM +, Shaozhong SHI wrote: > Is there any regex for Word space Word space Word and more? It isn't very clear what you want to achieve. From the other mails in this thread I understand that your words start with an uppercase char and continue with lowercase chars

Re: PQexecParams, placeholders and variable lists of params

Hi A function cannot have an undefined signature, but can accept an array of arguments: CREATE OR REPLACE FUNCTION bpd.object_del_by_id_array( object_array bigint[]) RETURNS SETOF bpd.errarg_action LANGUAGE 'plpgsql' COST 100 VOLATILE SECURITY DEFINER PARALLEL SAFE ROWS 1000

Re: SELECT fails to present result rows depending on the columns to show

El día martes, noviembre 23, 2021 a las 10:09:36 +0100, Thomas Kellerer escribió: > > Broken index could. Then this anomaly shoud have gone after reindex table. > > Ilya refers to the problems decribed here: > > https://wiki.postgresql.org/wiki/Locale_data_changes > > Thanks for the pointer

Re: Regex for Word space Word space Word ....

På tirsdag 23. november 2021 kl. 12:25:29, skrev Shaozhong SHI < shishaozh...@gmail.com >: It only matches First Street from 'My First Street'. I was trying to make it to match words starting capital letter only. You'll want to include unicode-characters, which

Re: Regex for Word space Word space Word ....

> > > I was trying to make it to match words starting capital letter only. > Does this work? https://regex101.com/r/nf4HCN/1 > > Regards, > David > > On Tue, 23 Nov 2021 at 10:59, chlor wrote: > >> On Tue, Nov 23, 2021 at 11:51 AM Shaozhong SHI >> wrote: >> >>> I tried nested regex '[[A-Z][a

PQexecParams, placeholders and variable lists of params

Hi, PQexecParams expects a query string with "$1", "$2"... placeholders, which refer to as many params in the param list. This keeps SQL injection at bay. Is there a way to express "variable length" lists? IOW, if I want to do a query like "SELECT * FROM customers WHERE id IN ($1, $2) AND name

Re: Regex for Word space Word space Word ....

It only matches First Street from 'My First Street'. I was trying to make it to match words starting capital letter only. Regards, David On Tue, 23 Nov 2021 at 10:59, chlor wrote: > On Tue, Nov 23, 2021 at 11:51 AM Shaozhong SHI > wrote: > >> I tried nested regex '[[A-Z][a-z] ]+[[A-Z][a-z]]'

Re: Regex for Word space Word space Word ....

On Tue, Nov 23, 2021 at 11:51 AM Shaozhong SHI wrote: > I tried nested regex '[[A-Z][a-z] ]+[[A-Z][a-z]]' but it did not work. > [A-Z][a-z]+ +[A-Z][a-z]+ will match 'Hello World', but not 'Hello world'. Is that what you want? Try this instead [A-Za-z]+ +[A-Za-z]+ And try also this editor t

Re: Regex for Word space Word space Word ....

I tried nested regex '[[A-Z][a-z] ]+[[A-Z][a-z]]' but it did not work. Regards, David On Tue, 23 Nov 2021 at 09:58, Shaozhong SHI wrote: > Is there any regex for Word space Word space Word and more? > > Regards, > > David >

Regex for Word space Word space Word ....

Is there any regex for Word space Word space Word and more? Regards, David

Re: SELECT fails to present result rows depending on the columns to show

Ilya Anfimov schrieb am 23.11.2021 um 09:31: >> but: >> >> sisis=# select * from titel_worte where desk = '2' and feldnr = 257; >> desknr | feldnr | desk | deskorg | gesanz | aufanz | katkey1 | katkey2 >> ++--+-+++-+- >> (0 row) >> >> sis

Re: SELECT fails to present result rows depending on the columns to show

On Tue, Nov 23, 2021 at 09:15:06AM +0100, Matthias Apitz wrote: > > Hello, > > We encounter the following problem in a 13.1 server on Linux: > > sisis=# select desk, feldnr from titel_worte where desk = '2' and feldnr = > 257; > desk | feldnr > --+ > 2|257 > (1 row) > >

SELECT fails to present result rows depending on the columns to show

Hello, We encounter the following problem in a 13.1 server on Linux: sisis=# select desk, feldnr from titel_worte where desk = '2' and feldnr = 257; desk | feldnr --+ 2|257 (1 row) but: sisis=# select * from titel_worte where desk = '2' and feldnr = 257; desknr | feldn