Achilleas Mantzios - cloud <a.mantzios@xxxxxxxxxxxxxxxxxxxx> 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@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@localhost:6432/dynacom?application_name=' '" or even
psql "postgresql://amantzio@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.
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.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
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
