Hello
For the past few days now we were investigating a strange issue that
affected our new test environment : PostgreSQL 16.1 with pgbouncer
1.21.0 . We tested with previous versions of pgbouncer against PgSQL
16.1 and 10.23 and the conclusion was the problem existed only against
pgsql 16 no matter the pgbouncer version. Also the problem existed only
with pool_mode = transaction .
The scenario goes like that :
Client C1 connects and issues :
SET application_name TO 'TEST';
-- some other short SQL
-- sleeps
then immediately after,
Client C2 connects and issues :
SET application_name TO 'TEST';
-- some other short SQL
-- sleeps
By looking at pgbouncer sockets (show sockets) we verified that client
C1 runs against server S1 and gets back the application_name correctly.
However client C2 only gets the correct application name if it is
assigned to a new server. If C2 happens to be assigned to S1, then
although it issues SET application_name TO 'TEST'; and this is executed
by S1 (which has the application_name='TEST' already set), S1 does not
report application_name back to C2, leaving with empty application_name.
If now S1 closes due to server idle timeout, when later C2 wakes app to
do some more work, it will send application_name= (empty) to the new
server reported as "unknown" , breaking our application (which depends
on application_name).
Our suspicion was that pgsql backend does not report the
application_name back to the client if the value sent by the client with
the SET command is the same as the already existing value on the backend.
So, we worked around this by defining an non empty dummy application
name = '' with the startup packet (on the URL), thus making the server
to report back the second correct app name set with the SET command.
So I would like to ask has anything changed in this regard between
PostgreSQL 10.* and 16.* ?
Thank you!