From: Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: application_name backend (not) reporting back to the client : pgbouncer, PgSQL 16.1, pgbouncer 1.21.0
Date: Sat, 9 Dec 2023 23:38:39 +0200
Message-ID: <addc9291-d3a3-4545-9805-8d1f00978614@cloud.gatewaynet.com> (raw)
In-Reply-To: <0028c29e-26f3-48e7-9201-f94d2579b9ba@cloud.gatewaynet.com>
References: <e33e0231-77d7-4dd7-4b7c-97af1d0e091b@cloud.gatewaynet.com>
<1436714.1702046865@sss.pgh.pa.us>
<06bf30c8-371e-5575-e1cc-a6dc1140b8af@cloud.gatewaynet.com>
<1454680.1702054572@sss.pgh.pa.us>
<ff2ee637-f3b6-424d-8ebf-d7cb089487de@cloud.gatewaynet.com>
<1552992.1702076062@sss.pgh.pa.us>
<0028c29e-26f3-48e7-9201-f94d2579b9ba@cloud.gatewaynet.com>
Στις 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: a.mantzios@cloud.gatewaynet.com, pgsql-admin@lists.postgresql.org
Subject: Re: application_name backend (not) reporting back to the client : pgbouncer, PgSQL 16.1, pgbouncer 1.21.0
In-Reply-To: <addc9291-d3a3-4545-9805-8d1f00978614@cloud.gatewaynet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox