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

On 12/8/23 16:47, Tom Lane wrote:
> Achilleas Mantzios - cloud<a(dot)mantzios(at)cloud(dot)gatewaynet(dot)com> writes:
>> 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 .
> Surely this is a pgbouncer issue that you need to discuss with the
> pgbouncer authors. PG does not keep multiple values of application_name
> per session, so you must not be connecting to the session you think
> you are.

Hello Tom,

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. So I am
asking if there has been any change in the backend with regards to
application_name, GUC_REPORT params and the like. Unfortunately I have
no pgsql 12, 13, 14, or 15 handy in order to test with them as well.

The test script that demonstrates the issue is :

prob.sh

#!/usr/local/bin/bash
if [ $# -ne 2 ]
then
 echo "Usage: `basename $0` pipename application_name"
 exit 1
fi
PIPENAME=$1
APPLICATION_NAME=$2
mkfifo -m "a=rw" /tmp/$PIPENAME
sleep 10000 > /tmp/$PIPENAME &
#psql "postgresql://amantzio(at)localhost:6432/dynacom?application_name=''"
-f /tmp/$PIPENAME &
psql "postgresql://amantzio(at)localhost:6432/dynacom?application_name=" -f
/tmp/$PIPENAME &
# application_name on the URL must be set as application_name= (empty),
setting to e.g. ='' does defeats teh problem
echo "SET application_name='$APPLICATION_NAME';"  > /tmp/$PIPENAME
sleep 10
echo "\q"  > /tmp/$PIPENAME
rm /tmp/$PIPENAME

We open psql on the pgbouncer and give show sockets , followed by \watch 1

Then on one terminal we give :

./prob.sh myfifo "TEST"

and immediately after, on a second terminal :

./prob.sh myfifo2 "TEST"

We will see that the second client has empty application_name in show
sockets.

*if* however, one changes the connection in the script to read like :

psql "postgresql://amantzio(at)localhost:6432/dynacom?application_name=''"
-f /tmp/$PIPENAME &

i.e. give an initial non empty application_name (which will be reported
back to the client btw), then the subsequent : SET
application_name='$APPLICATION_NAME';

will change the backend's application_name (from '' to $APPLICATION_NAME
) and always trigger a reporting back to the client, thus solving the
problem. From this point on, the client as long as it does not issue a
new SET application_name=, will always carry this correct
application_name inside the client's state (pgbouncer).

I hope I was clearer this time.

Thank you!

>
> regards, tom lane