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

Στις 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