From: Tom Lane <tgl@sss.pgh.pa.us>
To: Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>
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
In-reply-to: <ff2ee637-f3b6-424d-8ebf-d7cb089487de@cloud.gatewaynet.com>
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>
 <ff2ee637-f3b6-424d-8ebf-d7cb089487de@cloud.gatewaynet.com>
Comments: In-reply-to Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>
	message dated "Fri, 08 Dec 2023 21:06:14 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <1552991.1702076062.1@sss.pgh.pa.us>
Content-Transfer-Encoding: 8bit
Date: Fri, 08 Dec 2023 17:54:22 -0500
Message-ID: <1552992.1702076062@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1552992.1702076062%40sss.pgh.pa.us>
Precedence: bulk

Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com> writes:
> the trick is to have "application_name=" with no value.  This causes the 
> server to not set application_name initially.

No, that sets it to an empty string, while application_name='' sets
it to two single quotes, according to the testing I did.  In either
case, that value will be reported to the client as the active value
during connection start.  Then the subsequent SET causes a new
report, but (since v14) only if the value being set is different.

> 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 ?

pgbouncer would have to regurgitate the latest ParameterStatus
messages to the new client (during its faked-up initial connection
handshake) to ensure everything's synchronized correctly.
If for some reason it's not doing that, that would be a pretty
serious bug if you ask me, since clients might be relying on
hearing the truth about settings like client_encoding.  Since
the whole point of pgbouncer is that the backend doesn't know
a new client has been slotted in, it's not clear to me how or
why the backend should solve this.

			regards, tom lane