Re: Changing set_session implementation

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