Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rBgB5-00BtoM-4x for pgsql-admin@arkaria.postgresql.org; Fri, 08 Dec 2023 19:06:23 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rBgB3-00AvXT-Rr for pgsql-admin@arkaria.postgresql.org; Fri, 08 Dec 2023 19:06:21 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rBgB3-00AvXH-Fo for pgsql-admin@lists.postgresql.org; Fri, 08 Dec 2023 19:06:21 +0000 Received: from cloud.gatewaynet.com ([185.90.37.94]) by magus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rBgB0-00AroD-Cb for pgsql-admin@lists.postgresql.org; Fri, 08 Dec 2023 19:06:20 +0000 Content-Type: multipart/alternative; boundary="------------DpD4RvYXQe2Uiv46JYQZsUXq" Message-ID: Date: Fri, 8 Dec 2023 21:06:14 +0200 MIME-Version: 1.0 Subject: Re: application_name backend (not) reporting back to the client : pgbouncer, PgSQL 16.1, pgbouncer 1.21.0 To: Tom Lane Cc: pgsql-admin@lists.postgresql.org, nc@gatewaynet.com, Achilleas Mantzios References: <1436714.1702046865@sss.pgh.pa.us> <06bf30c8-371e-5575-e1cc-a6dc1140b8af@cloud.gatewaynet.com> <1454680.1702054572@sss.pgh.pa.us> Content-Language: en-US From: Achilleas Mantzios In-Reply-To: <1454680.1702054572@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------DpD4RvYXQe2Uiv46JYQZsUXq Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit Στις 8/12/23 18:56, ο/η Tom Lane έγραψε: > Achilleas Mantzios - cloud 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 --------------DpD4RvYXQe2Uiv46JYQZsUXq Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit
Στις 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
--------------DpD4RvYXQe2Uiv46JYQZsUXq--