Re: 2.7.2 still problem with readonly/autocommit, was: Changing set_session implementation

On Thu, Aug 10, 2017 at 1:45 PM, Karsten Hilbert
<Karsten(dot)Hilbert(at)gmx(dot)net> wrote:
> While the below tests had been run with 2.7.1 the problem
> persists with 2.7.3.
>
> I am again attaching the script for reproducing the problem.
>
> Can anyone test, reproduce, suggest a fix ?

Hi Karsten

Building psycopg in debug mode gives you all the information to infer
its behaviour. Here are redacted outputs.

You want to set a connection in autocommit and change a a transaction
parameter to a non default. If you change parameter in a non
autocommit database the state will be kept in the python object and
applied at begin time:

>>> conn = psycopg2.connect('')
[26725] psyco_connect: dsn = '', async = 0

>>> cur = conn.cursor()

>>> cur.execute("select 1")
[26725] pq_execute_command_locked: pgconn = 0x902a6f0, query = BEGIN
[26725] pq_execute: executing SYNC query: pgconn = 0x902a6f0
[26725] select 1

>>> conn.rollback()

>>> conn.readonly = False
[26725] conn_set_session: autocommit 0, isolevel 5, readonly 0, deferrable 2

>>> cur.execute("select 1")
[26725] pq_execute_command_locked: pgconn = 0x902a6f0, query = BEGIN READ WRITE
[26725] pq_execute: executing SYNC query: pgconn = 0x902a6f0
[26725] select 1

>>> conn.rollback()

If instead you set the connection in autocommit and try changing the
session state psycopg will change the session state. This is with a
database defaulting to readonly:

>>> conn = psycopg2.connect('')
[26725] psyco_connect: dsn = '', async = 0

>>> conn.autocommit = True
[26725] conn_set_session: autocommit 1, isolevel 5, readonly 2, deferrable 2

>>> conn.readonly = False
[26725] pq_set_guc_locked: setting default_transaction_read_only to off
[26725] pq_execute_command_locked: pgconn = 0x8f84280, query = SET
default_transaction_read_only TO 'off'
[26725] conn_set_session: autocommit 1, isolevel 5, readonly 0, deferrable 2

>>> cur = conn.cursor()
>>> cur.execute("create database k")
[26725] pq_execute: executing SYNC query: pgconn = 0x8f84280
[26725] create database k

>>> cur.statusmessage
'CREATE DATABASE'

As you can see the SET statement is issued *on setting the readonly
property* when the connection is autocommit. What will not happen is
psycopg setting the readonly state when switching autocommit, copying
it from the internal state to the session state. It won't because this
would be run three queries when switching to autocommit=True; implicit
operations in unexpected moments usually spell trouble.

This should be enough to work around your problem: put the connection
in read-write state *after* setting autocommit, or execute "SET
default_transaction_read_only TO 'off'" manually before creating the
database if for some reason in your workflow you cannot do that.

Hope this helps. Feel free to propose a merge request with change in
documentation if you find it would have helped.

-- Daniele