Re: [psycopg] Changing set_session implementation - Mailing list psycopg

On Wed, Aug 02, 2017 at 03:10:15PM +0100, Daniele Varrazzo wrote:

> If you want to be absolutely sure about what the adapter does I
> suggest you to enable statements log on the server and check what
> statements are produced by psycopg. If you find any behaviour
> inconsistent with what documented please let us know.

One thing I noticed is that it seems pycopg2 does not send a
"set default_transaction_read_only to off" when

- a database had been created and ALTERed to permanently
  be "default_transaction_read_only to ON"

- a transaction had been opened on a connection with

        BEGIN ISOLATION LEVEL SERIALIZABLE READ WRITE

  in order to run various comments (such as setting
  default client timezone)

- that transaction had been committed

- the connection is switched to autocommit=true
    (for running DROP DATABASE et al)

        2017-08-02 19:18:13 GMT LOG:  00000: Verbindung empfangen: Host=[local]
        2017-08-02 19:18:13 GMT ORT:  BackendInitialize, postmaster.c:4135
        2017-08-02 19:18:13 GMT LOG:  00000: Verbindung autorisiert: Benutzer=postgres Datenbank=gnumed_v20
        2017-08-02 19:18:13 GMT ORT:  PerformAuthentication, postinit.c:272
        2017-08-02 19:18:13 GMT LOG:  00000: statement: BEGIN ISOLATION LEVEL SERIALIZABLE READ WRITE
        2017-08-02 19:18:13 GMT LOCATION:  exec_simple_query, postgres.c:935
        2017-08-02 19:18:13 GMT LOG:  00000: statement: set timezone to 'Europe/Madrid'
        2017-08-02 19:18:13 GMT LOCATION:  exec_simple_query, postgres.c:935
        2017-08-02 19:18:13 GMT LOG:  00000: statement: COMMIT
        2017-08-02 19:18:13 GMT LOCATION:  exec_simple_query, postgres.c:935
        2017-08-02 19:18:13 GMT LOG:  00000: statement: BEGIN ISOLATION LEVEL SERIALIZABLE READ WRITE
        2017-08-02 19:18:13 GMT LOCATION:  exec_simple_query, postgres.c:935
        2017-08-02 19:18:13 GMT LOG:  00000: statement: set lc_messages to 'C'
        2017-08-02 19:18:13 GMT LOCATION:  exec_simple_query, postgres.c:935
        2017-08-02 19:18:13 GMT LOG:  00000: statement: select md5(gm.concat_table_structure(20::integer)) as md5
        2017-08-02 19:18:13 GMT LOCATION:  exec_simple_query, postgres.c:935
        2017-08-02 19:18:14 GMT LOG:  00000: duration: 1156.407 ms
        2017-08-02 19:18:14 GMT LOCATION:  exec_simple_query, postgres.c:1164
        2017-08-02 19:18:14 GMT LOG:  00000: statement: SELECT datname FROM pg_database WHERE datname='gnumed_v21'
        2017-08-02 19:18:14 GMT LOCATION:  exec_simple_query, postgres.c:935
        2017-08-02 19:18:14 GMT LOG:  00000: statement: COMMIT
        2017-08-02 19:18:14 GMT LOCATION:  exec_simple_query, postgres.c:935
        2017-08-02 19:18:14 GMT LOG:  00000: statement: DROP DATABASE "gnumed_v21"
        2017-08-02 19:18:14 GMT LOCATION:  exec_simple_query, postgres.c:935
        2017-08-02 19:18:14 GMT ERROR:  25006: cannot execute DROP DATABASE in a read-only transaction
        2017-08-02 19:18:14 GMT LOCATION:  PreventCommandIfReadOnly, utility.c:236
        2017-08-02 19:18:14 GMT STATEMENT:  DROP DATABASE "gnumed_v21"
        2017-08-02 19:18:14 GMT LOG:  00000: statement: SET default_transaction_isolation TO DEFAULT
        2017-08-02 19:18:14 GMT LOCATION:  exec_simple_query, postgres.c:935
        2017-08-02 19:18:14 GMT LOG:  00000: statement: SET default_transaction_read_only TO DEFAULT
        2017-08-02 19:18:14 GMT LOCATION:  exec_simple_query, postgres.c:935
        2017-08-02 19:18:14 GMT LOG:  00000: disconnection: session time: 0:00:01.183 user=postgres database=gnumed_v20
host=[local]
        2017-08-02 19:18:14 GMT LOCATION:  log_disconnections, postgres.c:4501

The only other indirect proof I have for this is: if I insert
a manual "set default_transaction_read_only to off" right
before the "drop database ..." (but after having been
switched to autocommit) it works as expected.

If the connection has NOT been used for any other
transactions (that is, if it is switched to autocommit=true
and then to readonly=off) it also works as expected -
psycopg2 sends a "set default_transaction_read_only to off".

Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346