Re: Set readonly transaction per transaction

Alexandor84 <oleksandr(dot)sukhotskyi(at)gmail(dot)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