From: Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-admin@lists.postgresql.org, nc@gatewaynet.com, Achilleas Mantzios <itdev@gatewaynet.com>
Subject: Re: application_name backend (not) reporting back to the client : pgbouncer, PgSQL 16.1, pgbouncer 1.21.0
Date: Fri, 8 Dec 2023 21:06:14 +0200
Message-ID: <ff2ee637-f3b6-424d-8ebf-d7cb089487de@cloud.gatewaynet.com> (raw)
In-Reply-To: <1454680.1702054572@sss.pgh.pa.us>
References: <e33e0231-77d7-4dd7-4b7c-97af1d0e091b@cloud.gatewaynet.com>
<1436714.1702046865@sss.pgh.pa.us>
<06bf30c8-371e-5575-e1cc-a6dc1140b8af@cloud.gatewaynet.com>
<1454680.1702054572@sss.pgh.pa.us>
Στις 8/12/23 18:56, ο/η Tom Lane έγραψε:
> Achilleas Mantzios - cloud<a.mantzios@cloud.gatewaynet.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@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.
>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: a.mantzios@cloud.gatewaynet.com, tgl@sss.pgh.pa.us, itdev@gatewaynet.com
Subject: Re: application_name backend (not) reporting back to the client : pgbouncer, PgSQL 16.1, pgbouncer 1.21.0
In-Reply-To: <ff2ee637-f3b6-424d-8ebf-d7cb089487de@cloud.gatewaynet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox