Since pgbouncer is a connection pooler, it will reuse one actual connection to the database server for potentially many client connections. It has to make sure that whatever session state the first client created is reset when the next client gets the connection. Otherwise the first client might do something like SET statement_timeout = '5min'
, and that would apply to all subsequent clients who happen to be assigned that connection. To avoid that, pgbouncer issues the "server reset query" before handing out the server connection to a new client. As the documentation says
A good choice for Postgres 8.2 and below is:
server_reset_query = RESET ALL; SET SESSION AUTHORIZATION DEFAULT;
for 8.3 and above its enough to do:
server_reset_query = DISCARD ALL;
The main reason this setting exists is that the DISCARD ALL
statement, which was invented for this very purpose, was not available before PostgreSQL 8.3.
The setting applies independently of the pool mode. Note that the client could issue a SET
statement or something like that in any pool mode.
The documentation also says
When transaction pooling is used, the server_reset_query
should be empty, as clients should not use any session features.
I think this is a faith-based approach. If you think your code is clean and you need the extra performance, go for it. I've been bitten by this, though. For example, if you use check_postgres to monitor your pgbouncer connections, it will issue a SET statement_timeout
like I showed above and mess up your connections.
Maybe it would be easiest to run a psql in the background, with it set to execute stdin, and connect its stdin to a named pipe. Then you can continually push data into that pipe, and finally push "end; \quit". Something like:
#!/bin/sh
psql_pipe=/tmp/psql$$
mkfifo -m 600 $psql_pipe
psql < $psql_pipe &
exec 3>$psql_pipe
psql_pid=$!
echo "> Started psql (pid=$psql_pid) reading from $psql_pipe"
trap '
kill $psql_pid
rm -f $psql_pipe
' EXIT
echo "begin;" >&3
echo "select now();" >&3
sleep 2
echo "select now();" >&3
sleep 2
echo "end; \quit" >&3
wait $psql_pid
Note that you can't simply do echo "sql" >$psql_pipe
since the EOF would be transmitted to psql, which would then exit early-- the shell script has to keep its fd open.
Best Answer
I think that in this two lines "server" means: connection to PostgreSQL managed by pgbouncer. "Server connection can be reused" means that established connection to postgreSQL is not closed but kept open and will be used by another client. In this context I understand that "server is released back" to the pool means the same thing: the connection to PostgreSQL is kept open and will be used by another client.
This is just the way connexion pooling works.