Thread: Set readonly transaction per transaction

Hi All,

I found that JDBC driver set readOnly transaction as: SET SESSION
CHARACTERISTICS AS TRANSACTION READ ONLY

Is there a way to reconfigure JDBC driver to set ReadOnly like:
BEGIN TRANSACTION READ ONLY;
.
.
COMMIT;

Thank you in advance,
Alex



--
View this message in context: http://postgresql.nabble.com/Set-readonly-transaction-per-transaction-tp5829205.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

I use PgBouncer in "transaction" mode and it is not compatible with using
mentioned SET statements.

Alex



--
View this message in context:
http://postgresql.nabble.com/Set-readonly-transaction-per-transaction-tp5829205p5829208.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

No one faced with such problem?



--
View this message in context:
http://postgresql.nabble.com/Set-readonly-transaction-per-transaction-tp5829205p5829583.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

Dave Cramer <pg@fastcrypt.com> wrote:
> On 8 December 2014 at 03:17, Alexandor84 <oleksandr.sukhotskyi@gmail.com> wrote:

>> No one faced with such problem?

> Apparently not. I doubt many people use pgbouncer in transaction
> mode.
>
> Can you explain why you are using transaction mode ?

Hey, now!  I often recommend transaction mode, because I have seen
applications scale to a much higher concurrency that way.  See:

https://wiki.postgresql.org/wiki/Number_Of_Database_Connections

and:

http://stackoverflow.com/questions/10419665/how-does-pgbouncer-help-to-speed-up-django/10420469#10420469

As a suggestion to the original poster, how about executing:

SET read_only = on;

... as the first statement of a new transaction?

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Hi Kevin,

Thank you for the info.
AFAIK PgBouncer in transaction mode is not compatible with any  SET
<https://wiki.postgresql.org/wiki/PgBouncer>   statements.

Alex



--
View this message in context:
http://postgresql.nabble.com/Set-readonly-transaction-per-transaction-tp5829205p5829891.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

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