Στις 9/12/23 00:54, ο/η Tom Lane έγραψε:
Achilleas Mantzios <a.mantzios@xxxxxxxxxxxxxxxxxxxx> writes:
the trick is to have "application_name=" with no value. This causes theNo, that sets it to an empty string, while application_name='' sets
server to not set application_name initially.
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
