From: | Achilleas Mantzios <a(dot)mantzios(at)cloud(dot)gatewaynet(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-admin(at)lists(dot)postgresql(dot)org, nc(at)gatewaynet(dot)com, Achilleas Mantzios <itdev(at)gatewaynet(dot)com> |
Subject: | Re: application_name backend (not) reporting back to the client : pgbouncer, PgSQL 16.1, pgbouncer 1.21.0 |
Date: | 2023-12-08 19:06:14 |
Message-ID: | ff2ee637-f3b6-424d-8ebf-d7cb089487de@cloud.gatewaynet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
Στις 8/12/23 18:56, ο/η Tom Lane έγραψε:
> Achilleas Mantzios - cloud<a(dot)mantzios(at)cloud(dot)gatewaynet(dot)com> writes:
>> I would very much like this to have been a pgbouncer issue but it is
>> not. I was hoping I expressed the situation clearly, I am sorry if I
>> didn't. This is not about any multiple values per session, I send a
>> simple test below that reproduces the problem very easily (against *any*
>> pgbouncer 1.18+). The behavior of pgsql 16.1 is that it does not report
>> a SET application_name=... back to the client if the new value is the
>> same as the current one. This wasn't the behavior in pgsql 10.
> No, but it's been true since v14 (cf commit 2432b1a04). In any case,
Thanks!! This :
https://github.com/postgres/postgres/commit/2432b1a04087edc2fd9536c7c9aa4ca03fd1b363
looks like exactly the explanation I was looking for!
> the test case you're showing doesn't look like it'd exercise that
> behavior, since the SET is installing a new value.
Our app is java, and our jdbc driver by default reports no
application_name, I dont dare to tell the jdbc version tho, its old :( .
So if the connection string is like in :
psql "postgresql://amantzio(at)localhost:6432/dynacom?application_name="
then no application_name is set on the server with the startup packet
(just like with our jdbc), so if the subsequent SET from the client runs
against a server backend with this application_name already set by a
previous client (pgbouncer in transaction mode), and is found to be
equal, the backend will not report back to the client, as per the commit
2432b1a . *But* if the connection string is like
psql "postgresql://amantzio(at)localhost:6432/dynacom?application_name=' '"
or even
psql "postgresql://amantzio(at)localhost:6432/dynacom?application_name=''"
Then the server will accept this as non null/empty value, will set the
application_name, and a subsequent SET (with a real application name)
will cause the server to report back no matter what.
At least this is my theory.
>
> I did a bit of testing of the behavior of "application_name=" in the
> connection string followed by an explicit SET, and AFAICS we do send
the trick is to have "application_name=" with no value. This causes the
server to not set application_name initially. So that that if the client
is served by a backend (in the pool) which has the value of
application_name equal to the newly set by SET, it will think there is
no change so no need to report back.
> a ParameterStatus report from the SET, with no apparent change in
> behavior from quite far back (I tried 9.5 for comparison). So I
> continue to maintain that this is a pgbouncer problem. Maybe it
> has not been updated for the no-duplicate-reports server behavior?
> Although it's still hard to see why that would matter here.
Hm so, you think pgbouncer in transaction mode, should somehow "hack"
application name in order to enforce a subsequent report from the server
? But how can pgbouncer predict the future ? And AFAIK, pgbouncer does
not parse or reads any statements (well before the prepared statement
feature, I mean)
Please check the scenario
Client C1 connects to S1, sets application_name. BEGINS, COMMITS, the
server gets freed to server the next client.
Client C2 connects to the same S1, sets application_name, and gets no
report back. So it stays with application_name empty. Then this breaks
the application.
How could pgbouncer prevent this from happening ?
>
> regards, tom lane
--
Achilleas Mantzios
IT DEV - HEAD
IT DEPT
Dynacom Tankers Mgmt
From | Date | Subject | |
---|---|---|---|
Next Message | Ron Johnson | 2023-12-08 20:00:30 | Re: Postgres storage migration |
Previous Message | Rajesh Kumar | 2023-12-08 19:05:13 | Re: Postgres storage migration |