Alexandor84 <oleksandr.sukhotskyi@gmail.com> wrote:
> AFAIK PgBouncer in transaction mode is not compatible with any
> SET statements.
It is compatible with any SET statements which don't leave a
persistent state beyond the end of the transaction. That includes
any SET LOCAL as well as a few SET options which only affect the
current transaction, like transaction_isolation,
transaction_read_only, and transaction_deferrable (note that these
three have a default_* setting which persists beyond transaction
boundaries -- those defaults are *not* safe to use).
For example, running through pgbouncer configured for transaction
mode:
test=# show transaction_read_only;
transaction_read_only
-----------------------
off
(1 row)
test=# begin;
BEGIN
test=# set transaction_read_only = on;
SET
test=# show transaction_read_only;
transaction_read_only
-----------------------
on
(1 row)
test=# select count(*) from pg_class;
count
-------
299
(1 row)
test=# commit;
COMMIT
test=# show transaction_read_only;
transaction_read_only
-----------------------
off
(1 row)
There's probably some room for improvements in the pgbouncer
documentation on this point.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company