Achilleas Mantzios - cloud <a.mantzios@xxxxxxxxxxxxxxxxxxxx> 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@localhost:6432/dynacom?application_name=''"
-f /tmp/$PIPENAME &
psql
"postgresql://amantzio@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@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
