Re: Max connections reached without max connections reached

[Tom Lane]

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 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 relation.
Try "select * from pg_locks where locktype = 'relation' and database = 0".

regards, tom lane

Re: Max connections reached without max connections reached

[James Sewell]

>
>
> > 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
> > connections are allowed.
>
> Is there a correlation to these ETL jobs or the other consumers?
>
> If so what are the consumers trying to do at that time?
>

Not really no - it seems to be pretty randomly timed.

-- 
The contents of this email are confidential and may be subject to legal or 
professional privilege and copyright. No representation is made that this 
email is free of viruses or other defects. If you have received this 
communication in error, you may not copy or distribute any part of it or 
otherwise disclose its contents to anyone. Please advise the sender of your 
incorrect receipt of this correspondence.

Re: Max connections reached without max connections reached

[Adrian Klaver]

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 
4.8.5-36), 64-bit



 > re: rundeck, yes - but this is only one of many things
connecting. it's
 > not doing anything special.

Except sending a stream of connections to the server.

Any idea what they are doing?


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 
connections are allowed.


Is there a correlation to these ETL jobs or the other consumers?

If so what are the consumers trying to do at that time?



James




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Max connections reached without max connections reached

[James Sewell]

> > 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 this is only one of many things connecting. it's
> > not doing anything special.
>
> Except sending a stream of connections to the server.
>
> Any idea what they are doing?
>

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 connections
are allowed.

James

-- 
The contents of this email are confidential and may be subject to legal or 
professional privilege and copyright. No representation is made that this 
email is free of viruses or other defects. If you have received this 
communication in error, you may not copy or distribute any part of it or 
otherwise disclose its contents to anyone. Please advise the sender of your 
incorrect receipt of this correspondence.

Re: Max connections reached without max connections reached

[Adrian Klaver]

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 
in parallel


What version of theirs?

re: rundeck, yes - but this is only one of many things connecting. it's 
not doing anything special.


Except sending a stream of connections to the server.

Any idea what they are doing?

--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Max connections reached without max connections reached

[James Sewell]

> 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 anything special.

-- 
The contents of this email are confidential and may be subject to legal or 
professional privilege and copyright. No representation is made that this 
email is free of viruses or other defects. If you have received this 
communication in error, you may not copy or distribute any part of it or 
otherwise disclose its contents to anyone. Please advise the sender of your 
incorrect receipt of this correspondence.

Re: Max connections reached without max connections reached

[Adrian Klaver]

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 enterprisedb is one of their customized versions?

rundeck_cluster below refers to https://digital.ai/technology/rundeck?



postgres=# select count(*) from pg_stat_activity;
  count
---
    97
(1 row)

postgres=# select * from pg_locks where not granted;
  locktype | database | relation | page | tuple | virtualxid | 
transactionid | classid | objid | objsubid | virtualtransaction | pid | 
mode | granted | fastpath

--+--+--+--+---++---+-+---+--++-+--+-+--
(0 rows)
Logs are showing a stream of:

2021-11-24 10:50:58 AEDT [869]: [1-1] 
user=u_rundeck_rw,db=rundeck_cluster,client=10.154.19.23 
10.154.19.23(57122) (0:53300)FATAL:  remaining connection slots are 
reserved for non-replication superuser connections
2021-11-24 10:50:58 AEDT [870]: [1-1] 
user=u_rundeck_rw,db=rundeck_cluster,client=10.154.19.23 
10.154.19.23(57124) (0:53300)FATAL:  remaining connection slots are 
reserved for non-replication superuser connections
2021-11-24 10:50:58 AEDT [871]: [1-1] 
user=u_rundeck_rw,db=rundeck_cluster,client=10.154.19.23 
10.154.19.23(57126) (0:53300)FATAL:  remaining connection slots are 
reserved for non-replication superuser connections


James





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Max connections reached without max connections reached

[James Sewell]

>
>
> 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 something taking an exclusive lock on pg_authid or pg_database.
> pg_locks might be interesting to check.
>

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

postgres=# select count(*) from pg_stat_activity;
 count
---
   97
(1 row)

postgres=# select * from pg_locks where not granted;
 locktype | database | relation | page | tuple | virtualxid | transactionid
| classid | objid | objsubid | virtualtransaction | pid | mode | granted |
fastpath
--+--+--+--+---++---+-+---+--++-+--+-+--
(0 rows)

Logs are showing a stream of:

2021-11-24 10:50:58 AEDT [869]: [1-1]
user=u_rundeck_rw,db=rundeck_cluster,client=10.154.19.23
10.154.19.23(57122) (0:53300)FATAL:  remaining connection slots are
reserved for non-replication superuser connections
2021-11-24 10:50:58 AEDT [870]: [1-1]
user=u_rundeck_rw,db=rundeck_cluster,client=10.154.19.23
10.154.19.23(57124) (0:53300)FATAL:  remaining connection slots are
reserved for non-replication superuser connections
2021-11-24 10:50:58 AEDT [871]: [1-1]
user=u_rundeck_rw,db=rundeck_cluster,client=10.154.19.23
10.154.19.23(57126) (0:53300)FATAL:  remaining connection slots are
reserved for non-replication superuser connections

James

-- 
The contents of this email are confidential and may be subject to legal or 
professional privilege and copyright. No representation is made that this 
email is free of viruses or other defects. If you have received this 
communication in error, you may not copy or distribute any part of it or 
otherwise disclose its contents to anyone. Please advise the sender of your 
incorrect receipt of this correspondence.

Re: Max connections reached without max connections reached

[James Sewell]

>
> > 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 it
> > falls to around 100) - yet I'm seeing the log lines claim that I'm still
> > hitting 597 (600 - 3 reserved).
>
> What is the query you are using against pg_stat_activity?
>
>
For general counts ad-hoc we just use:

SELECT count(*) FROM pg_stat_activity

The monitoring system runs:

SELECT CASE WHEN usename IS NOT NULL THEN usename ELSE 'SYSTEM' END as
role,
datname AS database,
state,
wait_event,
count(*) AS connection
FROM pg_stat_activity
GROUP BY 1,2,3,4

-- 
The contents of this email are confidential and may be subject to legal or 
professional privilege and copyright. No representation is made that this 
email is free of viruses or other defects. If you have received this 
communication in error, you may not copy or distribute any part of it or 
otherwise disclose its contents to anyone. Please advise the sender of your 
incorrect receipt of this correspondence.

Re: Max connections reached without max connections reached

[Tom Lane]

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 in pg_stat_activity

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 something taking an exclusive lock on pg_authid or pg_database.
pg_locks might be interesting to check.

regards, tom lane

Re: Max connections reached without max connections reached

[Adrian Klaver]

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 connections can come in so it 
falls to around 100) - yet I'm seeing the log lines claim that I'm still 
hitting 597 (600 - 3 reserved).


What is the query you are using against pg_stat_activity?



James



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Max connections reached without max connections reached

[James Sewell]

>
> 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 particular
> (1) parallel-query workers
> (2) autovacuum workers
> Looking into pg_stat_activity when you see this issue might help
> clarify that.
>

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 it falls
to around 100) - yet I'm seeing the log lines claim that I'm still hitting
597 (600 - 3 reserved).

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 in pg_stat_activity

James

-- 
The contents of this email are confidential and may be subject to legal or 
professional privilege and copyright. No representation is made that this 
email is free of viruses or other defects. If you have received this 
communication in error, you may not copy or distribute any part of it or 
otherwise disclose its contents to anyone. Please advise the sender of your 
incorrect receipt of this correspondence.

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

[Shaozhong SHI]

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

[tomas]

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 the always-quote-even-if-not-necessary
> > approach makes it easier.)
> > 
> > I don't see many other alternatives though.  *Somehow* you have to
> > separate one value from the next.  If you don't want to pass 'em as
> > distinct parameters, then you have to obey some kind of composite-value
> > syntax.
> 
> Would it be possible to extend PQexecParams() et.al. like this:
> 
> You currently have paramValues[], paramLengths[], and paramFormats[] (plus 
> other parameters that I'll ignore here).
> 
> The format may currently be 0 or 1 (TEXT or BINARY). What if we allow 2 for 
> ARRAY? The corresponding  length then specifies how many parameters following 
> are part of the array. The value should point to a structure, that contains 
> pointers to the values, lengths, and formats of the elements. This also 
> allows nested arrays.

That sounds attractive; I think for my particular case it'd be
overengineering, though...

> If the client library knows that the server is too old to understand it, it 
> may temporarily assemble a string for those (correctly escaped) values and 
> replace the entries in the original values/lengths/formats arrays temporarily 
> before passing it to the old PQexecParams() implementation.
> 
> If the server is new enough the protocol itself can be extended to send the 
> array more efficiently instead of quoting and copying data around.
> 
> This would also hide the quoting rules for arrays nicely, as it doesn't 
> require additional methods for escaping. (Currently, escaping for arrays is 
> different from other escaping methods, it needs to be done manually and, 
> frankly, it's a PITA).

...but in the general case it sounds useful, yes :)

Cheers
 - t


signature.asc
Description: PGP signature

Re: PQexecParams, placeholders and variable lists of params

[Daniel Frey]

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 don't see many other alternatives though.  *Somehow* you have to
> separate one value from the next.  If you don't want to pass 'em as
> distinct parameters, then you have to obey some kind of composite-value
> syntax.

Would it be possible to extend PQexecParams() et.al. like this:

You currently have paramValues[], paramLengths[], and paramFormats[] (plus 
other parameters that I'll ignore here).

The format may currently be 0 or 1 (TEXT or BINARY). What if we allow 2 for 
ARRAY? The corresponding  length then specifies how many parameters following 
are part of the array. The value should point to a structure, that contains 
pointers to the values, lengths, and formats of the elements. This also allows 
nested arrays.

If the client library knows that the server is too old to understand it, it may 
temporarily assemble a string for those (correctly escaped) values and replace 
the entries in the original values/lengths/formats arrays temporarily before 
passing it to the old PQexecParams() implementation.

If the server is new enough the protocol itself can be extended to send the 
array more efficiently instead of quoting and copying data around.

This would also hide the quoting rules for arrays nicely, as it doesn't require 
additional methods for escaping. (Currently, escaping for arrays is different 
from other escaping methods, it needs to be done manually and, frankly, it's a 
PITA).

I'm sure a lot of users (and authors of client libraries like myself) would 
really appreciate some improvements in handling array values.

Regards, Daniel

Re: PQexecParams, placeholders and variable lists of params

[tomas]

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, if they are strings) before consolidating them to
> >> one PostgreSQL array literal.
> 
> > So long as you actually pass the literal value via a parameter the worst
> > problem you can have is a syntax error in converting the literal into
> > whatever type is being cast to.
> 
> 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 don't see many other alternatives though.  *Somehow* you have to
> separate one value from the next.  If you don't want to pass 'em as
> distinct parameters, then you have to obey some kind of composite-value
> syntax.

Yes, that is my conclusion, too. Tentatively, I'll go with dynamically
building the query string, but with "$n" placeholders -- counting args
as I go, and pass the args to PQexecParams.

This seems to afford injection protection in exchange of minimal fuss.

Thank you all for your input!

Cheers
 - t


signature.asc
Description: PGP signature

Re: PQexecParams, placeholders and variable lists of params

[Tom Lane]

"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 array literal.

> So long as you actually pass the literal value via a parameter the worst
> problem you can have is a syntax error in converting the literal into
> whatever type is being cast to.

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 don't see many other alternatives though.  *Somehow* you have to
separate one value from the next.  If you don't want to pass 'em as
distinct parameters, then you have to obey some kind of composite-value
syntax.

regards, tom lane

Re: PQexecParams, placeholders and variable lists of params

[David G. Johnston]

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 actually pass the literal value via a parameter the worst
problem you can have is a syntax error in converting the literal into
whatever type is being cast to.

I personally tend to just build up a CSV-like string (my data is usually
controlled enough the using the pipe symbol as a separator
alleviates escaping concerns) and using string_to_array($1,'|') to get the
array of values into the query.

David J.

Re: Max connections reached without max connections reached

[Tom Lane]

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 an option hereas we are
> using many long lived connections which make use of prepared statements.

> Sometimes a strange condition occurs. The number of connections is well
> under 600 (and dropping), but new connections are not being allowed into
> the database, I can see this message in the logs:

>(0:53300)FATAL:  remaining connection slots are reserved for
> non-replication superuser connections

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 particular
(1) parallel-query workers
(2) autovacuum workers
Looking into pg_stat_activity when you see this issue might help
clarify that.

regards, tom lane

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

[David G. Johnston]

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

[tomas]

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

I was hoping to get away "on the cheap" on this, letting PostgreSQL take
care of the injection avoidance ;-)

I'm converging in building the query dynamically, but still with
placeholders. I /know/ how many values are coming, and how many
placeholders used so-far in the query, so it'd be fairly easy to just
insert "$m"..."$n" as needed.

Thanks a lot, Dmitri

Cheers
 - t


signature.asc
Description: PGP signature

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

[tomas]

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. Is that right?

You want exactly one space between words, or more than one?

What is this "...and more"? Arbitrary repetitions?

Which kind of regular expressions do you want to use? POSIX?

If all the answers to the above are "yes", you might try something like

  "(?:[[:upper:]][[:lower:]]*[[:space:]]+)*[[:upper:]][[:lower:]]*"

(Caveat: untested). This would match a single word or more than one
word, separated by one or more spaces, where a word starts with one
upper-case character and continues with zero or more lowercases.

HTH
 - t


signature.asc
Description: PGP signature

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
SET search_path=bpd
AS $BODY$
DECLARE
cfg_desc "bpd"."cfg_action"%ROWTYPE;
action_entity RECORD;

action_result RECORD;
result "bpd"."errarg_action"%ROWTYPE;
BEGIN
SELECT * INTO cfg_desc FROM "bpd"."cfg_action" WHERE id = 'delete';

FOR action_entity IN SELECT id, "name" FROM bpd.object WHERE id =
ANY(object_array)
LOOP
action_result = "bpd"."object_del"(action_entity.id);
result."err_id" = action_result.outresult;
result."errdesc" = action_result.outdesc;
result."entity_id" = 20;
result."entity_instance_id" = action_entity.id;
result."entity_instance_name" = action_entity.name;
result."action_id" = cfg_desc."actid";
result."action_desc" = cfg_desc.desc;
RETURN NEXT result;
END LOOP;
END;
$BODY$;
--
Regards, Dmitry!


вт, 23 нояб. 2021 г. в 16:37, :

> 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 like $3;"
>
> is there a way to do that without knowing beforehand how many values go
> into the IN list?
>
> It would be very welcome for you to rub my nose against the place in The
> Fine Manual where I could have found that :-)
>
> Thanks & cheers
>  - tomás
>

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

[Matthias Apitz]

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. What is written there matches with the system
patch by our IT department some days ago to all our SLES 15 servers.

We have to talk and see what we have todo.

matthias



signature.asc
Description: PGP signature

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

[Andreas Joseph Krogh]

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 [A-Z] approach doesn't handle 
well.

How about:

select regexp_matches('Åge is a Man', E'[[:upper:]]\\w+', 'g');






--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 
 

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

[Saurabh Agrawal]

>
>
> 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-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 to learn regex
>> https://regex101.com/
>>
>> ./hans
>>
>>

PQexecParams, placeholders and variable lists of params

[tomas]

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 like $3;"

is there a way to do that without knowing beforehand how many values go
into the IN list?

It would be very welcome for you to rub my nose against the place in The
Fine Manual where I could have found that :-)

Thanks & cheers
 - tomás


signature.asc
Description: PGP signature

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

[Shaozhong SHI]

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]]' 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 to learn regex
> https://regex101.com/
>
> ./hans
>
>

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

[chlor]

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 to learn regex
https://regex101.com/

./hans

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

[Shaozhong SHI]

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

[Shaozhong SHI]

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

[Thomas Kellerer]

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)
>>
>> sisis=# select desk, feldnr, deskorg from titel_worte where desk = '2' and 
>> feldnr = 257;
>>  desk | feldnr | deskorg
>> --++-
>> (0 row)
>>
>> The table was created as:
>>
>> create table titel_worte (
>>   desknr  serial,
>>   feldnr SMALLINT   ,
>>   desk VARCHAR (245)  ,
>>   deskorg VARCHAR (245)  ,
>>   gesanz INTEGER   ,
>>   aufanz INTEGER   ,
>>   katkey1 INTEGER   ,
>>   katkey2 INTEGER
>>  )
>>  ;
>>
>> There are no messages in the serverlog when the SELECT fails to show the
>> row.
>>
>> What could be the reason for this? Thanks
>
>  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

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

[Ilya Anfimov]

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)
> 
> 
> but:
> 
> sisis=# select * from titel_worte where desk = '2' and feldnr = 257;
>  desknr | feldnr | desk | deskorg | gesanz | aufanz | katkey1 | katkey2
> ++--+-+++-+-
> (0 row)
> 
> sisis=# select desk, feldnr, deskorg from titel_worte where desk = '2' and 
> feldnr = 257;
>  desk | feldnr | deskorg
> --++-
> (0 row)
> 
> The table was created as:
> 
> create table titel_worte (
>   desknr  serial,
>   feldnr SMALLINT   ,
>   desk VARCHAR (245)  ,
>   deskorg VARCHAR (245)  ,
>   gesanz INTEGER   ,
>   aufanz INTEGER   ,
>   katkey1 INTEGER   ,
>   katkey2 INTEGER
>  )
>  ;
> 
> There are no messages in the serverlog when the SELECT fails to show the
> row.
> 
> What could be the reason for this? Thanks

 Broken index could. Then this anomaly shoud have gone after reindex table.

> 
>   matthias
> -- 
> Matthias Apitz, ??? g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
> Public GnuPG key: http://www.unixarea.de/key.pub
> 

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

[Matthias Apitz]

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 | feldnr | desk | deskorg | gesanz | aufanz | katkey1 | katkey2
++--+-+++-+-
(0 row)

sisis=# select desk, feldnr, deskorg from titel_worte where desk = '2' and 
feldnr = 257;
 desk | feldnr | deskorg
--++-
(0 row)

The table was created as:

create table titel_worte (
  desknr  serial,
  feldnr SMALLINT   ,
  desk VARCHAR (245)  ,
  deskorg VARCHAR (245)  ,
  gesanz INTEGER   ,
  aufanz INTEGER   ,
  katkey1 INTEGER   ,
  katkey2 INTEGER
 )
 ;

There are no messages in the serverlog when the SELECT fails to show the
row.

What could be the reason for this? Thanks

matthias
-- 
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub