Re: application_name backend (not) reporting back to the client : pgbouncer, PgSQL 16.1, pgbouncer 1.21.0

Στις 9/12/23 08:52, ο/η Achilleas Mantzios έγραψε:
> Στις 9/12/23 00:54, ο/η Tom Lane έγραψε:
>> Achilleas Mantzios <a(dot)mantzios(at)cloud(dot)gatewaynet(dot)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(at)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..<(dot)(dot)(at)(dot)@...........|..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..<(dot)(dot)(at)(dot)@............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(dot)(dot)4(dot)(dot)(at)(dot)@...........|..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(dot)(dot)q(dot)(dot)(at)(dot)@...........|..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(dot)(dot)L(dot)(dot)(at)(dot)@............8|(dot)2(dot)M(dot)(dot)(dot)(dot)(dot)(dot)(dot)(dot)(dot)(dot)(at)(dot)(dot)(dot)(dot)(dot)
....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(dot)(dot)k(dot)(dot)(at)(dot)@...........|..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(dot)(dot)(dot)(dot)(dot)(at)(dot)@............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(dot)(dot)(dot)(dot)(dot)(at)(dot)@...........|..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(dot)(dot)2(dot)(dot)(at)(dot)@............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(dot)(dot)4(dot)(dot)(at)(dot)@...........|..8...L2.P8.....(.....
P.[.....

the client does not send the application_name

whereas with :

psql "postgresql://amantzio(at)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..<(dot)(dot)(at)(dot)@...........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..<(dot)(dot)(at)(dot)@............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(dot)(dot)4(dot)(dot)(at)(dot)@...........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(dot)(dot)(dot)(dot)(dot)(at)(dot)@...........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(dot)(dot)L(dot)(dot)(at)(dot)@(dot)(dot)(dot)(dot)(dot)(dot)(dot)(dot)(dot)(dot)(dot)(dot)85}(dot)(dot)(dot)1(dot)`P(dot)(dot)(dot)(dot)(dot)(dot)(at)(dot)(dot)(dot)(dot)(dot)
......'.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(dot)(dot)k(dot)(dot)(at)(dot)@...........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(dot)(dot)(dot)(dot)(dot)(at)(dot)@............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(dot)(dot)(dot)(dot)(dot)(at)(dot)@...........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(dot)(dot)4(dot)(dot)(at)(dot)@............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(dot)(dot)4(dot)(dot)(at)(dot)@...........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