From: Achilleas Mantzios - cloud <a.mantzios@cloud.gatewaynet.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
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
Date: Fri, 8 Dec 2023 17:25:12 +0200
Message-ID: <06bf30c8-371e-5575-e1cc-a6dc1140b8af@cloud.gatewaynet.com> (raw)
In-Reply-To: <1436714.1702046865@sss.pgh.pa.us>
References: <e33e0231-77d7-4dd7-4b7c-97af1d0e091b@cloud.gatewaynet.com>
<1436714.1702046865@sss.pgh.pa.us>
On 12/8/23 16:47, Tom Lane wrote:
> Achilleas Mantzios - cloud<a.mantzios@cloud.gatewaynet.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@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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: a.mantzios@cloud.gatewaynet.com, tgl@sss.pgh.pa.us, 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: <06bf30c8-371e-5575-e1cc-a6dc1140b8af@cloud.gatewaynet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox