Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rC52G-00Dtpf-Nx for pgsql-admin@arkaria.postgresql.org; Sat, 09 Dec 2023 21:38:57 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rC52D-003VpH-5N for pgsql-admin@arkaria.postgresql.org; Sat, 09 Dec 2023 21:38:53 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rC52C-003Vnz-Bt for pgsql-admin@lists.postgresql.org; Sat, 09 Dec 2023 21:38:52 +0000 Received: from cloud.gatewaynet.com ([185.90.37.94]) by makus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rC524-009cnc-4g for pgsql-admin@lists.postgresql.org; Sat, 09 Dec 2023 21:38:50 +0000 Content-Type: multipart/alternative; boundary="------------h05wJZSG0ERi8CTOfyi0CPaK" Message-ID: Date: Sat, 9 Dec 2023 23:38:39 +0200 MIME-Version: 1.0 Subject: Re: application_name backend (not) reporting back to the client : pgbouncer, PgSQL 16.1, pgbouncer 1.21.0 To: pgsql-admin@lists.postgresql.org References: <1436714.1702046865@sss.pgh.pa.us> <06bf30c8-371e-5575-e1cc-a6dc1140b8af@cloud.gatewaynet.com> <1454680.1702054572@sss.pgh.pa.us> <1552992.1702076062@sss.pgh.pa.us> <0028c29e-26f3-48e7-9201-f94d2579b9ba@cloud.gatewaynet.com> Content-Language: en-US From: Achilleas Mantzios In-Reply-To: <0028c29e-26f3-48e7-9201-f94d2579b9ba@cloud.gatewaynet.com> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------h05wJZSG0ERi8CTOfyi0CPaK Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit Στις 9/12/23 08:52, ο/η Achilleas Mantzios έγραψε: > Στις 9/12/23 00:54, ο/η Tom Lane έγραψε: >> Achilleas Mantzios writes: >>> the trick is to have "application_name=" with no value.  This causes >>> the >>> server to not set application_name initially. >> No, that sets it to an empty string, while application_name='' sets >> it to two single quotes, according to the testing I did.  In either >> case, that value will be reported to the client as the active value >> during connection start.  Then the subsequent SET causes a new >> report, but (since v14) only if the value being set is different. > Hi again, I am puzzled with your observations, this is what I get with psql straight against PostgreSQL no pgbouncer involved : command : psql "postgresql://amantzio@localhost:5432/dynacom?application_name=" output of tcpdump -A -i lo0 port 5432 23:29:00.415684 IP localhost.31933 > localhost.postgresql: Flags [S], seq 3719751018, win 65535, options [mss 16344,nop,wscale 6,sackOK,TS val 1343970026 ecr 0], length 0 E..<..@.@...........|..8...j.........0....?........ P.Z..... 23:29:00.415709 IP localhost.postgresql > localhost.31933: Flags [S.], seq 853757380, ack 3719751019, win 65535, options [mss 16344,nop,wscale 6,sackOK,TS val 3656173586 ecr 134397 0026], length 0 E..<..@.@............8|.2.M....k.....0....?........ ....P.Z. 23:29:00.415715 IP localhost.31933 > localhost.postgresql: Flags [.], ack 1, win 1277, options [nop,nop,TS val 1343970026 ecr 3656173586], length 0 E..4..@.@...........|..8...k2.M......(..... P.Z..... 23:29:00.415726 IP localhost.31933 > localhost.postgresql: Flags [P.], seq 1:62, ack 1, win 1277, options [nop,nop,TS val 1343970026 ecr 3656173586], length 61 E..q..@.@...........|..8...k2.M......e..... *P.Z........=....user.amantzio.database.dynacom.client_encoding.UTF8.. * 23:29:00.422757 IP localhost.postgresql > localhost.31933: Flags [P.], seq 1:25, ack 62, win 1277, options [nop,nop,TS val 3656173593 ecr 1343970026], length 24 E..L..@.@............8|.2.M..........@..... ....P.Z.R....... SCRAM-SHA-256.. 23:29:00.422823 IP localhost.31933 > localhost.postgresql: Flags [P.], seq 62:117, ack 25, win 1277, options [nop,nop,TS val 1343970033 ecr 3656173593], length 55 E..k..@.@...........|..8....2.M......_..... P.Z.....p...6SCRAM-SHA-256.... n,,n=,r=xLI34cA0s3ulrSX41qzrzJNE 23:29:00.422848 IP localhost.postgresql > localhost.31933: Flags [P.], seq 25:118, ack 117, win 1277, options [nop,nop,TS val 3656173593 ecr 1343970033], length 93 E.....@.@............8|.2.M................ ....P.Z.R...\....r=xLI34cA0s3ulrSX41qzrzJNEw0094sbRThtReDwT6vPnaWkp,s=XM8Nr2tZlFupSjKagSRfoA==,i=4096 23:29:00.426522 IP localhost.31933 > localhost.postgresql: Flags [P.], seq 117:226, ack 118, win 1277, options [nop,nop,TS val 1343970037 ecr 3656173593], length 109 E.....@.@...........|..8....2.N:........... P.Z.....p...lc=biws,r=xLI34cA0s3ulrSX41qzrzJNEw0094sbRThtReDwT6vPnaWkp,p=kw91XX5R0KaMJdhbflhGnqMrcMOLUcXMgQahtbRcvjo= 23:29:00.427387 IP localhost.postgresql > localhost.31933: Flags [P.], seq 118:628, ack 226, win 1277, options [nop,nop,TS val 3656173598 ecr 1343970037], length 510 E..2..@.@............8|.2.N:...L.....'..... ....P.Z.R...6....v=+WtTPDH2DwVEhjUsUdUeuj74aFk7DyEVIU93ZlBV+pM=R........S....in_hot_standby.off.S....integer_datetimes.on.S....TimeZone.Europe/Athens.S....IntervalStyle.postgres.S. .. search_path."$user", public.S....is_superuser.on.S....application_name..S...&default_transaction_read_only.off.S....scram_iterations.4096.S....DateStyle.ISO, MDY.S...#standard_c onforming_strings.on.S...#session_authorization.amantzio.S....client_encoding.UTF8.S....server_version.16.1.S....server_encoding.SQL_ASCII.K.......7b9..Z....I 23:29:00.467550 IP localhost.31933 > localhost.postgresql: Flags [.], ack 628, win 1277, options [nop,nop,TS val 1343970078 ecr 3656173598], length 0 E..4..@.@...........|..8...L2.P8.....(..... P.[..... the client does not send the application_name whereas with : psql "postgresql://amantzio@localhost:5432/dynacom?application_name=''" output of tcpdump -A -i lo0 port 5432 23:31:45.583442 IP localhost.13693 > localhost.postgresql: Flags [S], seq 2640334946, win 65535, options [mss 16344,nop,wscale 6,sackOK,TS val 300427204 ecr 0], length 0 E..<..@.@...........5}.8.`Pb.........0....?........ ..'..... 23:31:45.583468 IP localhost.postgresql > localhost.13693: Flags [S.], seq 3340481072, ack 2640334947, win 65535, options [mss 16344,nop,wscale 6,sackOK,TS val 2261783934 ecr 30042 7204], length 0 E..<..@.@............85}...0.`Pc.....0....?........ ...~..'. 23:31:45.583475 IP localhost.13693 > localhost.postgresql: Flags [.], ack 1, win 1277, options [nop,nop,TS val 300427204 ecr 2261783934], length 0 E..4..@.@...........5}.8.`Pc...1.....(..... ..'....~ 23:31:45.583486 IP localhost.13693 > localhost.postgresql: Flags [P.], seq 1:82, ack 1, win 1277, options [nop,nop,TS val 300427204 ecr 2261783934], length 81 E.....@.@...........5}.8.`Pc...1.....y..... *..'....~...Q....user.amantzio.database.dynacom.application_name.''.client_encoding.UTF8.. * 23:31:45.590522 IP localhost.postgresql > localhost.13693: Flags [P.], seq 1:25, ack 82, win 1277, options [nop,nop,TS val 2261783941 ecr 300427204], length 24 E..L..@.@............85}...1.`P......@..... ......'.R....... SCRAM-SHA-256.. 23:31:45.590584 IP localhost.13693 > localhost.postgresql: Flags [P.], seq 82:137, ack 25, win 1277, options [nop,nop,TS val 300427211 ecr 2261783941], length 55 E..k..@.@...........5}.8.`P....I....._..... ..'.....p...6SCRAM-SHA-256.... n,,n=,r=LAq5jZmt0f+ctvHXopjbPPjG 23:31:45.590609 IP localhost.postgresql > localhost.13693: Flags [P.], seq 25:118, ack 137, win 1277, options [nop,nop,TS val 2261783941 ecr 300427211], length 93 E.....@.@............85}...I.`P............ ......'.R...\....r=LAq5jZmt0f+ctvHXopjbPPjGjTRnO+1qZXj04XCbh+soRT0h,s=XM8Nr2tZlFupSjKagSRfoA==,i=4096 23:31:45.594310 IP localhost.13693 > localhost.postgresql: Flags [P.], seq 137:246, ack 118, win 1277, options [nop,nop,TS val 300427215 ecr 2261783941], length 109 E.....@.@...........5}.8.`P................ ..'.....p...lc=biws,r=LAq5jZmt0f+ctvHXopjbPPjGjTRnO+1qZXj04XCbh+soRT0h,p=a9lhA0XYHDiwFwOVAUzm0eznroj0R1WD9Fd+DQXfuns= 23:31:45.595130 IP localhost.postgresql > localhost.13693: Flags [P.], seq 118:630, ack 246, win 1277, options [nop,nop,TS val 2261783946 ecr 300427215], length 512 E..4..@.@............85}.....`QX.....)..... ......'.R...6....v=WezHjvAUp5Rq8fBZFVGmgaxewdiIGClYMtVVUTyc5lQ=R........S....in_hot_standby.off.S....integer_datetimes.on.S....TimeZone.Europe/Athens.S....IntervalStyle.postgres.S. .. search_path."$user", public.S....is_superuser.on.S....application_name.''.S...&default_transaction_read_only.off.S....scram_iterations.4096.S....DateStyle.ISO, MDY.S...#standard _conforming_strings.on.S...#session_authorization.amantzio.S....client_encoding.UTF8.S....server_version.16.1.S....server_encoding.SQL_ASCII.K.......a.).HZ....I 23:31:45.637878 IP localhost.13693 > localhost.postgresql: Flags [.], ack 630, win 1277, options [nop,nop,TS val 300427259 ecr 2261783946], length 0 E..4..@.@...........5}.8.`QX.........(..... ..'..... In this case I see that the client sends the application name. Please tell me what may I missing here. It does not solve or even alleviate the severity of the problem, but I think this behavior is part of the picture. thank you. >>             regards, tom lane > -- Achilleas Mantzios IT DEV - HEAD IT DEPT Dynacom Tankers Mgmt --------------h05wJZSG0ERi8CTOfyi0CPaK Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit
Στις 9/12/23 08:52, ο/η Achilleas Mantzios έγραψε:
Στις 9/12/23 00:54, ο/η Tom Lane έγραψε:
Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com> writes:
the trick is to have "application_name=" with no value.  This causes the
server to not set application_name initially.
No, that sets it to an empty string, while application_name='' sets
it to two single quotes, according to the testing I did.  In either
case, that value will be reported to the client as the active value
during connection start.  Then the subsequent SET causes a new
report, but (since v14) only if the value being set is different.

Hi again, I am puzzled with your observations, this is what I get with psql straight against PostgreSQL no pgbouncer involved :

command :

psql "postgresql://amantzio@localhost:5432/dynacom?application_name="

output of

tcpdump -A -i lo0 port 5432

23:29:00.415684 IP localhost.31933 > localhost.postgresql: Flags [S], seq 3719751018, win 65535, options [mss 16344,nop,wscale 6,sackOK,TS val 1343970026 ecr 0], length 0
E..<..@.@...........|..8...j.........0....?........
P.Z.....
23:29:00.415709 IP localhost.postgresql > localhost.31933: Flags [S.], seq 853757380, ack 3719751019, win 65535, options [mss 16344,nop,wscale 6,sackOK,TS val 3656173586 ecr 134397
0026], length 0
E..<..@.@............8|.2.M....k.....0....?........
....P.Z.
23:29:00.415715 IP localhost.31933 > localhost.postgresql: Flags [.], ack 1, win 1277, options [nop,nop,TS val 1343970026 ecr 3656173586], length 0
E..4..@.@...........|..8...k2.M......(.....
P.Z.....
23:29:00.415726 IP localhost.31933 > localhost.postgresql: Flags [P.], seq 1:62, ack 1, win 1277, options [nop,nop,TS val 1343970026 ecr 3656173586], length 61
E..q..@.@...........|..8...k2.M......e.....
P.Z........=....user.amantzio.database.dynacom.client_encoding.UTF8..
23:29:00.422757 IP localhost.postgresql > localhost.31933: Flags [P.], seq 1:25, ack 62, win 1277, options [nop,nop,TS val 3656173593 ecr 1343970026], length 24
E..L..@.@............8|.2.M..........@.....
....P.Z.R.......
SCRAM-SHA-256..
23:29:00.422823 IP localhost.31933 > localhost.postgresql: Flags [P.], seq 62:117, ack 25, win 1277, options [nop,nop,TS val 1343970033 ecr 3656173593], length 55
E..k..@.@...........|..8....2.M......_.....
P.Z.....p...6SCRAM-SHA-256.... n,,n=,r=xLI34cA0s3ulrSX41qzrzJNE
23:29:00.422848 IP localhost.postgresql > localhost.31933: Flags [P.], seq 25:118, ack 117, win 1277, options [nop,nop,TS val 3656173593 ecr 1343970033], length 93
E.....@.@............8|.2.M................
....P.Z.R...\....r=xLI34cA0s3ulrSX41qzrzJNEw0094sbRThtReDwT6vPnaWkp,s=XM8Nr2tZlFupSjKagSRfoA==,i=4096
23:29:00.426522 IP localhost.31933 > localhost.postgresql: Flags [P.], seq 117:226, ack 118, win 1277, options [nop,nop,TS val 1343970037 ecr 3656173593], length 109
E.....@.@...........|..8....2.N:...........
P.Z.....p...lc=biws,r=xLI34cA0s3ulrSX41qzrzJNEw0094sbRThtReDwT6vPnaWkp,p=kw91XX5R0KaMJdhbflhGnqMrcMOLUcXMgQahtbRcvjo=
23:29:00.427387 IP localhost.postgresql > localhost.31933: Flags [P.], seq 118:628, ack 226, win 1277, options [nop,nop,TS val 3656173598 ecr 1343970037], length 510
E..2..@.@............8|.2.N:...L.....'.....
....P.Z.R...6....v=+WtTPDH2DwVEhjUsUdUeuj74aFk7DyEVIU93ZlBV+pM=R........S....in_hot_standby.off.S....integer_datetimes.on.S....TimeZone.Europe/Athens.S....IntervalStyle.postgres.S.
.. search_path."$user", public.S....is_superuser.on.S....application_name..S...&default_transaction_read_only.off.S....scram_iterations.4096.S....DateStyle.ISO, MDY.S...#standard_c
onforming_strings.on.S...#session_authorization.amantzio.S....client_encoding.UTF8.S....server_version.16.1.S....server_encoding.SQL_ASCII.K.......7b9..Z....I
23:29:00.467550 IP localhost.31933 > localhost.postgresql: Flags [.], ack 628, win 1277, options [nop,nop,TS val 1343970078 ecr 3656173598], length 0
E..4..@.@...........|..8...L2.P8.....(.....
P.[.....

the client does not send the application_name

whereas with :

psql "postgresql://amantzio@localhost:5432/dynacom?application_name=''"

output of

tcpdump -A -i lo0 port 5432

23:31:45.583442 IP localhost.13693 > localhost.postgresql: Flags [S], seq 2640334946, win 65535, options [mss 16344,nop,wscale 6,sackOK,TS val 300427204 ecr 0], length 0
E..<..@.@...........5}.8.`Pb.........0....?........
..'.....
23:31:45.583468 IP localhost.postgresql > localhost.13693: Flags [S.], seq 3340481072, ack 2640334947, win 65535, options [mss 16344,nop,wscale 6,sackOK,TS val 2261783934 ecr 30042
7204], length 0
E..<..@.@............85}...0.`Pc.....0....?........
...~..'.
23:31:45.583475 IP localhost.13693 > localhost.postgresql: Flags [.], ack 1, win 1277, options [nop,nop,TS val 300427204 ecr 2261783934], length 0
E..4..@.@...........5}.8.`Pc...1.....(.....
..'....~
23:31:45.583486 IP localhost.13693 > localhost.postgresql: Flags [P.], seq 1:82, ack 1, win 1277, options [nop,nop,TS val 300427204 ecr 2261783934], length 81
E.....@.@...........5}.8.`Pc...1.....y.....
..'....~...Q....user.amantzio.database.dynacom.application_name.''.client_encoding.UTF8..
23:31:45.590522 IP localhost.postgresql > localhost.13693: Flags [P.], seq 1:25, ack 82, win 1277, options [nop,nop,TS val 2261783941 ecr 300427204], length 24
E..L..@.@............85}...1.`P......@.....
......'.R.......
SCRAM-SHA-256..
23:31:45.590584 IP localhost.13693 > localhost.postgresql: Flags [P.], seq 82:137, ack 25, win 1277, options [nop,nop,TS val 300427211 ecr 2261783941], length 55
E..k..@.@...........5}.8.`P....I....._.....
..'.....p...6SCRAM-SHA-256.... n,,n=,r=LAq5jZmt0f+ctvHXopjbPPjG
23:31:45.590609 IP localhost.postgresql > localhost.13693: Flags [P.], seq 25:118, ack 137, win 1277, options [nop,nop,TS val 2261783941 ecr 300427211], length 93
E.....@.@............85}...I.`P............
......'.R...\....r=LAq5jZmt0f+ctvHXopjbPPjGjTRnO+1qZXj04XCbh+soRT0h,s=XM8Nr2tZlFupSjKagSRfoA==,i=4096
23:31:45.594310 IP localhost.13693 > localhost.postgresql: Flags [P.], seq 137:246, ack 118, win 1277, options [nop,nop,TS val 300427215 ecr 2261783941], length 109
E.....@.@...........5}.8.`P................
..'.....p...lc=biws,r=LAq5jZmt0f+ctvHXopjbPPjGjTRnO+1qZXj04XCbh+soRT0h,p=a9lhA0XYHDiwFwOVAUzm0eznroj0R1WD9Fd+DQXfuns=
23:31:45.595130 IP localhost.postgresql > localhost.13693: Flags [P.], seq 118:630, ack 246, win 1277, options [nop,nop,TS val 2261783946 ecr 300427215], length 512
E..4..@.@............85}.....`QX.....).....
......'.R...6....v=WezHjvAUp5Rq8fBZFVGmgaxewdiIGClYMtVVUTyc5lQ=R........S....in_hot_standby.off.S....integer_datetimes.on.S....TimeZone.Europe/Athens.S....IntervalStyle.postgres.S.
.. search_path."$user", public.S....is_superuser.on.S....application_name.''.S...&default_transaction_read_only.off.S....scram_iterations.4096.S....DateStyle.ISO, MDY.S...#standard
_conforming_strings.on.S...#session_authorization.amantzio.S....client_encoding.UTF8.S....server_version.16.1.S....server_encoding.SQL_ASCII.K.......a.).HZ....I
23:31:45.637878 IP localhost.13693 > localhost.postgresql: Flags [.], ack 630, win 1277, options [nop,nop,TS val 300427259 ecr 2261783946], length 0
E..4..@.@...........5}.8.`QX.........(.....
..'.....

In this case I see that the client sends the application name. Please tell me what may I missing here.

It does not solve or even alleviate the severity of the problem, but I think this behavior is part of the picture.

thank you.


            regards, tom lane 

-- 
Achilleas Mantzios
 IT DEV - HEAD
 IT DEPT
 Dynacom Tankers Mgmt
--------------h05wJZSG0ERi8CTOfyi0CPaK--