Обсуждение: [psycopg] Changing set_session implementation
Hello,
some time ago I've found a problem in production with psycopg's
set_session(): the current implementation changes the state of the
connection using "set default_transaction_read_only" and similar
session parameter. Unfortunately they don't play well with
transaction-level connection pools, so other application may receive
misconfigured connection.
I've changed the set_session/set_isolation_level/autocommit connection
methods and properties to use better the BEGIN parameters in order to
set the session characteristics without executing extra queries (as it
was the case before 2.4 or something). The new implementation doesn't
change session parameters, unless in autocommit (where no BEGIN is
issued, so there is no other way to honour transaction parameters).
So:
conn.set_session(readonly=True)
will result in transactions starting with 'BEGIN READ ONLY'; after
commit the session will keep the server defaults. Conversely:
conn.set_session(readonly=True, autocommit=True)
will behave as before and result in the execution of 'SET
default_transaction_read_only TO on', which will not make you win any
new friend if you use it with pgbouncer...
Other changes connected to this refactoring are:
- conn.set_isolation_level() will throw an exception if executed
during a transaction; previously it would have executed a quiet
ROLLBACK
- conn.isolation_level doesn't read the value from the server anymore
but will return a value stored in the Python connection object by the
user. The default value is the new ISOLATION_LEVEL_DEFAULT, meaning
"begin a transaction, but use whatever isolation level the server is
configured for".
I hope the new behaviour will make the adapter more predictable at no
performance price. A diff of the code and docs is available
https://github.com/psycopg/psycopg2/compare/no-set-default-session and
will be released with psycopg 2.7 (which should be imminent now, but
I'm happy to get feedback about this change).
Cheers,
-- Daniele
Great work! > On Feb 4, 2017, at 08:33, Daniele Varrazzo <daniele.varrazzo@gmail.com> wrote: > - conn.isolation_level doesn't read the value from the server anymore > but will return a value stored in the Python connection object by the > user. The default value is the new ISOLATION_LEVEL_DEFAULT, meaning > "begin a transaction, but use whatever isolation level the server is > configured for". Just to clarify, is this also now the default for starting a new transaction (i.e., it won't send a SET ISOLATION LEVEL acrossby default)? -- -- Christophe Pettus xof@thebuild.com
On Wed, Feb 8, 2017 at 10:47 AM, Christophe Pettus <xof@thebuild.com> wrote: >> On Feb 4, 2017, at 08:33, Daniele Varrazzo <daniele.varrazzo@gmail.com> wrote: >> - conn.isolation_level doesn't read the value from the server anymore >> but will return a value stored in the Python connection object by the >> user. The default value is the new ISOLATION_LEVEL_DEFAULT, meaning >> "begin a transaction, but use whatever isolation level the server is >> configured for". > > Just to clarify, is this also now the default for starting a new transaction (i.e., it won't send a SET ISOLATION LEVELacross by default)? Correct: by default psycopg would just run BEGIN to start a new transaction, leaving the choice of the isolation level to the database config file, the connection setting etc. If an isolation level different from DEFAULT is selected (or other session characteristics, such as READ ONLY) then it will use "BEGIN ISOLATION LEVEL ..." to start further transactions. -- Daniele
> On Feb 8, 2017, at 05:35, Daniele Varrazzo <daniele.varrazzo@gmail.com> wrote: > Correct: by default psycopg would just run BEGIN to start a new > transaction, leaving the choice of the isolation level to the database > config file, the connection setting etc. If an isolation level > different from DEFAULT is selected (or other session characteristics, > such as READ ONLY) then it will use "BEGIN ISOLATION LEVEL ..." to > start further transactions. Exactly the behavior I was hoping for! Anything that reduces the overheat chatter at session establishment is a plus tome. Thanks! -- -- Christophe Pettus xof@thebuild.com
On Sat, Feb 04, 2017 at 04:33:13PM +0000, Daniele Varrazzo wrote:
> I've changed the set_session/set_isolation_level/autocommit connection
> methods and properties to use better the BEGIN parameters
...
> Conversely:
>
> conn.set_session(readonly=True, autocommit=True)
Does this mean that
conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
is now a no-op as observed by the following log snippet or
just diverted to set_session(autocommit=True) ?
code:
_log.debug('conn state now: %s', gmPG2.capture_conn_state(self.conn))
_log.debug('setting isolation level to autocommit - should be redundant ?')
self.conn.set_isolation_level(0)
_log.debug('conn state now: %s', gmPG2.capture_conn_state(self.conn))
log:
2017-07-27 16:03:01 DEBUG gm.bootstrapper MainThread (-1219281664)
(./bootstrap_gm_db_system.py::__create_db()#810): conn state now: <connection object at 0xb64eca04; dsn:
'dbname=gnumed_v21port=5432 user=postgres sslmode=prefer fallback_application_name=GNUmed client_encoding=utf8
application_name=postgres@template.db',closed: 0>
type: <class 'psycopg2.extras.DictConnection'>
identity: 3058616836
backend PID: 2864
protocol version: 3
encoding: UTF8
isolation level (psyco): 3 (ISOLATION_LEVEL_SERIALIZABLE)
readonly: False
autocommit: True
closed: 0
connection status: 1 (STATUS_READY)
transaction status: 0 (TRANSACTION_STATUS_IDLE)
deferrable: None
async: 0
executing async op: False
2017-07-27 16:03:01 DEBUG gm.bootstrapper MainThread (-1219281664)
(./bootstrap_gm_db_system.py::__create_db()#811): setting isolation level to autocommit - should be redundant ?
2017-07-27 16:03:01 DEBUG gm.bootstrapper MainThread (-1219281664)
(./bootstrap_gm_db_system.py::__create_db()#813): conn state now: <connection object at 0xb64eca04; dsn:
'dbname=gnumed_v21port=5432 user=postgres sslmode=prefer fallback_application_name=GNUmed client_encoding=utf8
application_name=postgres@template.db',closed: 0>
type: <class 'psycopg2.extras.DictConnection'>
identity: 3058616836
backend PID: 2864
protocol version: 3
encoding: UTF8
isolation level (psyco): 3 (ISOLATION_LEVEL_SERIALIZABLE)
readonly: False
autocommit: True
closed: 0
connection status: 1 (STATUS_READY)
transaction status: 0 (TRANSACTION_STATUS_IDLE)
deferrable: None
async: 0
executing async op: False
Note that it doesn't actually change the isolation level
(which makes sense because autocommit mode is not an
isolation level). Should conn.isolation_level show
ISOLATION_LEVEL_DEFAULT in this case (meaning "use what the
server/db is configured for") ? It doesn't seem to make a
roundtrip to the server to _get_ the default because the
database in question defaults to READ_COMMITED. The
SERIALIZABLE results from an earlier call to
.set_isolation_mode() in my code.
(Also note that the connection had already been set to
autocommit by "set_session(autocommit=True)" before.)
Thanks for the clarification,
Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
Hopefully I just hit summer vacation -- or else do I need to
provide more information to get an answer on the below ?
Thanks,
Karsten
On Thu, Jul 27, 2017 at 04:17:33PM +0200, Karsten Hilbert wrote:
> On Sat, Feb 04, 2017 at 04:33:13PM +0000, Daniele Varrazzo wrote:
>
> > I've changed the set_session/set_isolation_level/autocommit connection
> > methods and properties to use better the BEGIN parameters
> ...
> > Conversely:
> >
> > conn.set_session(readonly=True, autocommit=True)
>
> Does this mean that
>
> conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
>
> is now a no-op as observed by the following log snippet or
> just diverted to set_session(autocommit=True) ?
>
>
> code:
>
> _log.debug('conn state now: %s', gmPG2.capture_conn_state(self.conn))
> _log.debug('setting isolation level to autocommit - should be redundant ?')
> self.conn.set_isolation_level(0)
> _log.debug('conn state now: %s', gmPG2.capture_conn_state(self.conn))
>
> log:
>
> 2017-07-27 16:03:01 DEBUG gm.bootstrapper MainThread (-1219281664)
(./bootstrap_gm_db_system.py::__create_db()#810): conn state now: <connection object at 0xb64eca04; dsn:
'dbname=gnumed_v21port=5432 user=postgres sslmode=prefer fallback_application_name=GNUmed client_encoding=utf8
application_name=postgres@template.db',closed: 0>
> type: <class 'psycopg2.extras.DictConnection'>
> identity: 3058616836
> backend PID: 2864
> protocol version: 3
> encoding: UTF8
> isolation level (psyco): 3 (ISOLATION_LEVEL_SERIALIZABLE)
> readonly: False
> autocommit: True
> closed: 0
> connection status: 1 (STATUS_READY)
> transaction status: 0 (TRANSACTION_STATUS_IDLE)
> deferrable: None
> async: 0
> executing async op: False
> 2017-07-27 16:03:01 DEBUG gm.bootstrapper MainThread (-1219281664)
(./bootstrap_gm_db_system.py::__create_db()#811): setting isolation level to autocommit - should be redundant ?
> 2017-07-27 16:03:01 DEBUG gm.bootstrapper MainThread (-1219281664)
(./bootstrap_gm_db_system.py::__create_db()#813): conn state now: <connection object at 0xb64eca04; dsn:
'dbname=gnumed_v21port=5432 user=postgres sslmode=prefer fallback_application_name=GNUmed client_encoding=utf8
application_name=postgres@template.db',closed: 0>
> type: <class 'psycopg2.extras.DictConnection'>
> identity: 3058616836
> backend PID: 2864
> protocol version: 3
> encoding: UTF8
> isolation level (psyco): 3 (ISOLATION_LEVEL_SERIALIZABLE)
> readonly: False
> autocommit: True
> closed: 0
> connection status: 1 (STATUS_READY)
> transaction status: 0 (TRANSACTION_STATUS_IDLE)
> deferrable: None
> async: 0
> executing async op: False
>
>
> Note that it doesn't actually change the isolation level
> (which makes sense because autocommit mode is not an
> isolation level). Should conn.isolation_level show
> ISOLATION_LEVEL_DEFAULT in this case (meaning "use what the
> server/db is configured for") ? It doesn't seem to make a
> roundtrip to the server to _get_ the default because the
> database in question defaults to READ_COMMITED. The
> SERIALIZABLE results from an earlier call to
> .set_isolation_mode() in my code.
>
> (Also note that the connection had already been set to
> autocommit by "set_session(autocommit=True)" before.)
>
>
> Thanks for the clarification,
> Karsten
> --
> GPG key ID E4071346 @ eu.pool.sks-keyservers.net
> E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
>
>
> --
> Sent via psycopg mailing list (psycopg@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/psycopg
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
Hi Karsten, sorry for the late reply,
no, `conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)` is not a no-op:
In [1]: import psycopg2
In [2]: psycopg2.__version__
Out[2]: '2.7.3 (dt dec pq3 ext lo64)'
In [3]: cnn = psycopg2.connect('')
In [4]: cnn.autocommit
Out[4]: False
In [5]: cnn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
In [6]: cnn.autocommit
Out[6]: True
but if your connection was already autocommit then sure it will not do anything.
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.
Hope this helps
-- Daniele
On Mon, Jul 31, 2017 at 11:51 AM, Karsten Hilbert
<Karsten.Hilbert@gmx.net> wrote:
> Hopefully I just hit summer vacation -- or else do I need to
> provide more information to get an answer on the below ?
>
> Thanks,
> Karsten
>
> On Thu, Jul 27, 2017 at 04:17:33PM +0200, Karsten Hilbert wrote:
>
>> On Sat, Feb 04, 2017 at 04:33:13PM +0000, Daniele Varrazzo wrote:
>>
>> > I've changed the set_session/set_isolation_level/autocommit connection
>> > methods and properties to use better the BEGIN parameters
>> ...
>> > Conversely:
>> >
>> > conn.set_session(readonly=True, autocommit=True)
>>
>> Does this mean that
>>
>> conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
>>
>> is now a no-op as observed by the following log snippet or
>> just diverted to set_session(autocommit=True) ?
>>
>>
>> code:
>>
>> _log.debug('conn state now: %s', gmPG2.capture_conn_state(self.conn))
>> _log.debug('setting isolation level to autocommit - should be redundant ?')
>> self.conn.set_isolation_level(0)
>> _log.debug('conn state now: %s', gmPG2.capture_conn_state(self.conn))
>>
>> log:
>>
>> 2017-07-27 16:03:01 DEBUG gm.bootstrapper MainThread (-1219281664)
(./bootstrap_gm_db_system.py::__create_db()#810): conn state now: <connection object at 0xb64eca04; dsn:
'dbname=gnumed_v21port=5432 user=postgres sslmode=prefer fallback_application_name=GNUmed client_encoding=utf8
application_name=postgres@template.db',closed: 0>
>> type: <class 'psycopg2.extras.DictConnection'>
>> identity: 3058616836
>> backend PID: 2864
>> protocol version: 3
>> encoding: UTF8
>> isolation level (psyco): 3 (ISOLATION_LEVEL_SERIALIZABLE)
>> readonly: False
>> autocommit: True
>> closed: 0
>> connection status: 1 (STATUS_READY)
>> transaction status: 0 (TRANSACTION_STATUS_IDLE)
>> deferrable: None
>> async: 0
>> executing async op: False
>> 2017-07-27 16:03:01 DEBUG gm.bootstrapper MainThread (-1219281664)
(./bootstrap_gm_db_system.py::__create_db()#811): setting isolation level to autocommit - should be redundant ?
>> 2017-07-27 16:03:01 DEBUG gm.bootstrapper MainThread (-1219281664)
(./bootstrap_gm_db_system.py::__create_db()#813): conn state now: <connection object at 0xb64eca04; dsn:
'dbname=gnumed_v21port=5432 user=postgres sslmode=prefer fallback_application_name=GNUmed client_encoding=utf8
application_name=postgres@template.db',closed: 0>
>> type: <class 'psycopg2.extras.DictConnection'>
>> identity: 3058616836
>> backend PID: 2864
>> protocol version: 3
>> encoding: UTF8
>> isolation level (psyco): 3 (ISOLATION_LEVEL_SERIALIZABLE)
>> readonly: False
>> autocommit: True
>> closed: 0
>> connection status: 1 (STATUS_READY)
>> transaction status: 0 (TRANSACTION_STATUS_IDLE)
>> deferrable: None
>> async: 0
>> executing async op: False
>>
>>
>> Note that it doesn't actually change the isolation level
>> (which makes sense because autocommit mode is not an
>> isolation level). Should conn.isolation_level show
>> ISOLATION_LEVEL_DEFAULT in this case (meaning "use what the
>> server/db is configured for") ? It doesn't seem to make a
>> roundtrip to the server to _get_ the default because the
>> database in question defaults to READ_COMMITED. The
>> SERIALIZABLE results from an earlier call to
>> .set_isolation_mode() in my code.
>>
>> (Also note that the connection had already been set to
>> autocommit by "set_session(autocommit=True)" before.)
>>
>>
>> Thanks for the clarification,
>> Karsten
>> --
>> GPG key ID E4071346 @ eu.pool.sks-keyservers.net
>> E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
>>
>>
>> --
>> Sent via psycopg mailing list (psycopg@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/psycopg
>
> --
> GPG key ID E4071346 @ eu.pool.sks-keyservers.net
> E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
>
>
> --
> Sent via psycopg mailing list (psycopg@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/psycopg
On Wed, Aug 02, 2017 at 03:10:15PM +0100, Daniele Varrazzo wrote:
> Hi Karsten, sorry for the late reply,
No problem, thanks.
> no, `conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)` is not a no-op:
>
> In [1]: import psycopg2
>
> In [2]: psycopg2.__version__
> Out[2]: '2.7.3 (dt dec pq3 ext lo64)'
>
> In [3]: cnn = psycopg2.connect('')
>
> In [4]: cnn.autocommit
> Out[4]: False
>
> In [5]: cnn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
>
> In [6]: cnn.autocommit
> Out[6]: True
I see. I thought as much. Put another way,
cnn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
and
cnn.set_session(autocommit = True)
do the same thing.
Thanks,
Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
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
Regarding the below problem with readwrite autocommit
connections in default-readonly databases I have whittled
down a test case to the following (attached):
db = u'gnumed_v20' # a database configured "alter database %s set default_transaction_read_only to on"
user = 'gm-dbo' # a user with CREATE DATABASE powers
cmd_def_tx_ro = "SELECT upper(source), name, upper(setting) FROM pg_settings WHERE name =
'default_transaction_read_only'"
cmd_create_db = "create database %s_copy template %s" % (db, db)
import sys
import psycopg2
conn = psycopg2.connect(dbname = db, user = user)
print 'conn:', conn
print 'readonly:', conn.readonly
print 'autocommit:', conn.autocommit
conn.autocommit = False
conn.readonly = False # <========== comment out for success
conn.autocommit = True
print 'autocommit now:', conn.autocommit
conn.readonly = False
print 'readonly now:', conn.readonly
curs = conn.cursor()
curs.execute(cmd_def_tx_ro)
print 'should show DEFAULT_TRANSACTION_READ_ONLY set to ON'
print curs.fetchall()
curs.close()
conn.commit()
print 'the following CREATE DATABASE should fail'
curs = conn.cursor()
try:
curs.execute(cmd_create_db)
except psycopg2.InternalError as ex:
print 'SQL failed:'
print ex
print 'shutting down'
curs.close()
conn.rollback()
conn.close()
which shows on the console:
ncq@hermes:~$ python x-test-default_ro.py
conn: <connection object at 0xb674e6bc; dsn: 'dbname=gnumed_v20 user=gm-dbo', closed: 0>
readonly: None
autocommit: False
autocommit now: True
readonly now: False
should show DEFAULT_TRANSACTION_READ_ONLY set to ON
[('DATABASE', 'default_transaction_read_only', 'ON')]
the following CREATE DATABASE should fail
SQL failed:
cannot execute CREATE DATABASE in a read-only transaction
shutting down
The PostgreSQL log shows:
2017-08-03 21:00:19 GMT LOG: 00000: Verbindung empfangen: Host=[local]
2017-08-03 21:00:19 GMT ORT: BackendInitialize, postmaster.c:4135
2017-08-03 21:00:19 GMT LOG: 00000: Verbindung autorisiert: Benutzer=gm-dbo Datenbank=gnumed_v20
2017-08-03 21:00:19 GMT ORT: PerformAuthentication, postinit.c:272
2017-08-03 21:00:19 GMT LOG: 00000: statement: SELECT upper(source), name, upper(setting) FROM pg_settings WHERE
name= 'default_transaction_read_only'
2017-08-03 21:00:19 GMT LOCATION: exec_simple_query, postgres.c:935
2017-08-03 21:00:19 GMT LOG: 00000: statement: create database gnumed_v20_copy template gnumed_v20
2017-08-03 21:00:19 GMT LOCATION: exec_simple_query, postgres.c:935
2017-08-03 21:00:19 GMT ERROR: 25006: cannot execute CREATE DATABASE in a read-only transaction
2017-08-03 21:00:19 GMT LOCATION: PreventCommandIfReadOnly, utility.c:236
2017-08-03 21:00:19 GMT STATEMENT: create database gnumed_v20_copy template gnumed_v20
2017-08-03 21:00:19 GMT LOG: 00000: disconnection: session time: 0:00:00.007 user=gm-dbo database=gnumed_v20
host=[local]
2017-08-03 21:00:19 GMT LOCATION: log_disconnections, postgres.c:4501
Commenting out the marked line makes it work and the log shows:
2017-08-03 21:01:24 GMT LOG: 00000: Verbindung empfangen: Host=[local]
2017-08-03 21:01:24 GMT ORT: BackendInitialize, postmaster.c:4135
2017-08-03 21:01:24 GMT LOG: 00000: Verbindung autorisiert: Benutzer=postgres Datenbank=postgres
2017-08-03 21:01:24 GMT ORT: PerformAuthentication, postinit.c:272
2017-08-03 21:01:25 GMT LOG: 00000: Anweisung: drop database gnumed_v20_copy ;
2017-08-03 21:01:25 GMT ORT: exec_simple_query, postgres.c:935
2017-08-03 21:01:26 GMT LOG: 00000: Verbindungsende: Sitzungszeit: 0:00:02.324 Benutzer=postgres
Datenbank=postgresHost=[local]
2017-08-03 21:01:26 GMT ORT: log_disconnections, postgres.c:4501
2017-08-03 21:01:33 GMT LOG: 00000: Verbindung empfangen: Host=[local]
2017-08-03 21:01:33 GMT ORT: BackendInitialize, postmaster.c:4135
2017-08-03 21:01:33 GMT LOG: 00000: Verbindung autorisiert: Benutzer=gm-dbo Datenbank=gnumed_v20
2017-08-03 21:01:33 GMT ORT: PerformAuthentication, postinit.c:272
2017-08-03 21:01:33 GMT LOG: 00000: statement: SET default_transaction_read_only TO 'off'
2017-08-03 21:01:33 GMT LOCATION: exec_simple_query, postgres.c:935
2017-08-03 21:01:33 GMT LOG: 00000: statement: SELECT upper(source), name, upper(setting) FROM pg_settings WHERE
name= 'default_transaction_read_only'
2017-08-03 21:01:33 GMT LOCATION: exec_simple_query, postgres.c:935
2017-08-03 21:01:33 GMT LOG: 00000: statement: create database gnumed_v20_copy template gnumed_v20
2017-08-03 21:01:33 GMT LOCATION: exec_simple_query, postgres.c:935
2017-08-03 21:01:37 GMT LOG: 00000: duration: 3447.360 ms
2017-08-03 21:01:37 GMT LOCATION: exec_simple_query, postgres.c:1164
2017-08-03 21:01:37 GMT LOG: 00000: disconnection: session time: 0:00:03.454 user=gm-dbo database=gnumed_v20
host=[local]
2017-08-03 21:01:37 GMT LOCATION: log_disconnections, postgres.c:4501
So, under some circumstances psycopg2 does send the
2017-08-03 21:01:33 GMT LOG: 00000: statement: SET default_transaction_read_only TO 'off'
while sometimes it does not, despite both times being in
autocommit=True and readonly=False when the "create database"
is sent.
Of course, the
conn.autocommit = False
conn.readonly = False # <========== comment out for success
conn.autocommit = True
conn.readonly = False
seems redundant but that is the relevant sequence of events
condensed from a larger body of code. Redundant as it may
seem it should still work, no ?
I am not sure I fully grok what is happening...
Thanks for insights,
Karsten
On Wed, Aug 02, 2017 at 09:38:21PM +0200, Karsten Hilbert wrote:
> 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_v20host=[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
>
>
> --
> Sent via psycopg mailing list (psycopg@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/psycopg
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
Вложения
[psycopg] problem with readonly/autocommit, was: Changing set_sessionimplementation
От
Karsten Hilbert
Дата:
Hi,
has anyone been able to reproduce the problem described
below? I'd be glad to provide more information if needed.
Thanks !
Karsten
On Thu, Aug 03, 2017 at 11:08:18PM +0200, Karsten Hilbert wrote:
> Regarding the below problem with readwrite autocommit
> connections in default-readonly databases I have whittled
> down a test case to the following (attached):
>
> db = u'gnumed_v20' # a database configured "alter database %s set default_transaction_read_only to on"
> user = 'gm-dbo' # a user with CREATE DATABASE powers
>
> cmd_def_tx_ro = "SELECT upper(source), name, upper(setting) FROM pg_settings WHERE name =
'default_transaction_read_only'"
> cmd_create_db = "create database %s_copy template %s" % (db, db)
>
> import sys
> import psycopg2
>
> conn = psycopg2.connect(dbname = db, user = user)
> print 'conn:', conn
> print 'readonly:', conn.readonly
> print 'autocommit:', conn.autocommit
> conn.autocommit = False
> conn.readonly = False # <========== comment out for success
> conn.autocommit = True
> print 'autocommit now:', conn.autocommit
> conn.readonly = False
> print 'readonly now:', conn.readonly
> curs = conn.cursor()
> curs.execute(cmd_def_tx_ro)
> print 'should show DEFAULT_TRANSACTION_READ_ONLY set to ON'
> print curs.fetchall()
> curs.close()
> conn.commit()
> print 'the following CREATE DATABASE should fail'
> curs = conn.cursor()
> try:
> curs.execute(cmd_create_db)
> except psycopg2.InternalError as ex:
> print 'SQL failed:'
> print ex
>
> print 'shutting down'
>
> curs.close()
> conn.rollback()
> conn.close()
>
> which shows on the console:
>
> ncq@hermes:~$ python x-test-default_ro.py
> conn: <connection object at 0xb674e6bc; dsn: 'dbname=gnumed_v20 user=gm-dbo', closed: 0>
> readonly: None
> autocommit: False
> autocommit now: True
> readonly now: False
> should show DEFAULT_TRANSACTION_READ_ONLY set to ON
> [('DATABASE', 'default_transaction_read_only', 'ON')]
> the following CREATE DATABASE should fail
> SQL failed:
> cannot execute CREATE DATABASE in a read-only transaction
>
> shutting down
>
> The PostgreSQL log shows:
>
> 2017-08-03 21:00:19 GMT LOG: 00000: Verbindung empfangen: Host=[local]
> 2017-08-03 21:00:19 GMT ORT: BackendInitialize, postmaster.c:4135
> 2017-08-03 21:00:19 GMT LOG: 00000: Verbindung autorisiert: Benutzer=gm-dbo Datenbank=gnumed_v20
> 2017-08-03 21:00:19 GMT ORT: PerformAuthentication, postinit.c:272
> 2017-08-03 21:00:19 GMT LOG: 00000: statement: SELECT upper(source), name, upper(setting) FROM pg_settings WHERE
name= 'default_transaction_read_only'
> 2017-08-03 21:00:19 GMT LOCATION: exec_simple_query, postgres.c:935
> 2017-08-03 21:00:19 GMT LOG: 00000: statement: create database gnumed_v20_copy template gnumed_v20
> 2017-08-03 21:00:19 GMT LOCATION: exec_simple_query, postgres.c:935
> 2017-08-03 21:00:19 GMT ERROR: 25006: cannot execute CREATE DATABASE in a read-only transaction
> 2017-08-03 21:00:19 GMT LOCATION: PreventCommandIfReadOnly, utility.c:236
> 2017-08-03 21:00:19 GMT STATEMENT: create database gnumed_v20_copy template gnumed_v20
> 2017-08-03 21:00:19 GMT LOG: 00000: disconnection: session time: 0:00:00.007 user=gm-dbo database=gnumed_v20
host=[local]
> 2017-08-03 21:00:19 GMT LOCATION: log_disconnections, postgres.c:4501
>
> Commenting out the marked line makes it work and the log shows:
>
> 2017-08-03 21:01:24 GMT LOG: 00000: Verbindung empfangen: Host=[local]
> 2017-08-03 21:01:24 GMT ORT: BackendInitialize, postmaster.c:4135
> 2017-08-03 21:01:24 GMT LOG: 00000: Verbindung autorisiert: Benutzer=postgres Datenbank=postgres
> 2017-08-03 21:01:24 GMT ORT: PerformAuthentication, postinit.c:272
> 2017-08-03 21:01:25 GMT LOG: 00000: Anweisung: drop database gnumed_v20_copy ;
> 2017-08-03 21:01:25 GMT ORT: exec_simple_query, postgres.c:935
> 2017-08-03 21:01:26 GMT LOG: 00000: Verbindungsende: Sitzungszeit: 0:00:02.324 Benutzer=postgres
Datenbank=postgresHost=[local]
> 2017-08-03 21:01:26 GMT ORT: log_disconnections, postgres.c:4501
> 2017-08-03 21:01:33 GMT LOG: 00000: Verbindung empfangen: Host=[local]
> 2017-08-03 21:01:33 GMT ORT: BackendInitialize, postmaster.c:4135
> 2017-08-03 21:01:33 GMT LOG: 00000: Verbindung autorisiert: Benutzer=gm-dbo Datenbank=gnumed_v20
> 2017-08-03 21:01:33 GMT ORT: PerformAuthentication, postinit.c:272
> 2017-08-03 21:01:33 GMT LOG: 00000: statement: SET default_transaction_read_only TO 'off'
> 2017-08-03 21:01:33 GMT LOCATION: exec_simple_query, postgres.c:935
> 2017-08-03 21:01:33 GMT LOG: 00000: statement: SELECT upper(source), name, upper(setting) FROM pg_settings WHERE
name= 'default_transaction_read_only'
> 2017-08-03 21:01:33 GMT LOCATION: exec_simple_query, postgres.c:935
> 2017-08-03 21:01:33 GMT LOG: 00000: statement: create database gnumed_v20_copy template gnumed_v20
> 2017-08-03 21:01:33 GMT LOCATION: exec_simple_query, postgres.c:935
> 2017-08-03 21:01:37 GMT LOG: 00000: duration: 3447.360 ms
> 2017-08-03 21:01:37 GMT LOCATION: exec_simple_query, postgres.c:1164
> 2017-08-03 21:01:37 GMT LOG: 00000: disconnection: session time: 0:00:03.454 user=gm-dbo database=gnumed_v20
host=[local]
> 2017-08-03 21:01:37 GMT LOCATION: log_disconnections, postgres.c:4501
>
> So, under some circumstances psycopg2 does send the
>
> 2017-08-03 21:01:33 GMT LOG: 00000: statement: SET default_transaction_read_only TO 'off'
>
> while sometimes it does not, despite both times being in
> autocommit=True and readonly=False when the "create database"
> is sent.
>
> Of course, the
>
> conn.autocommit = False
> conn.readonly = False # <========== comment out for success
> conn.autocommit = True
> conn.readonly = False
>
> seems redundant but that is the relevant sequence of events
> condensed from a larger body of code. Redundant as it may
> seem it should still work, no ?
>
> I am not sure I fully grok what is happening...
>
> Thanks for insights,
> Karsten
>
>
> On Wed, Aug 02, 2017 at 09:38:21PM +0200, Karsten Hilbert wrote:
>
> > 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_v20host=[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
> >
> >
> > --
> > Sent via psycopg mailing list (psycopg@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/psycopg
>
> --
> GPG key ID E4071346 @ eu.pool.sks-keyservers.net
> E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
>
> db = u'gnumed_v20' # a database configured "alter database %s set default_transaction_read_only to on"
> user = 'gm-dbo' # a user with CREATE DATABASE powers
>
>
>
> cmd_def_tx_ro = "SELECT upper(source), name, upper(setting) FROM pg_settings WHERE name =
'default_transaction_read_only'"
> cmd_create_db = "create database %s_copy template %s" % (db, db)
>
>
> import sys
> import psycopg2
>
>
> conn = psycopg2.connect(dbname = db, user = user)
> print 'conn:', conn
> print 'readonly:', conn.readonly
> print 'autocommit:', conn.autocommit
> conn.autocommit = False
> conn.readonly = False # <============ comment out for success
> conn.autocommit = True
> print 'autocommit now:', conn.autocommit
> conn.readonly = False
> print 'readonly now:', conn.readonly
> curs = conn.cursor()
> curs.execute(cmd_def_tx_ro)
> print 'should show DEFAULT_TRANSACTION_READ_ONLY set to ON'
> print curs.fetchall()
> curs.close()
> conn.commit()
> print 'the following CREATE DATABASE should fail'
> curs = conn.cursor()
> try:
> curs.execute(cmd_create_db)
> except psycopg2.InternalError as ex:
> print 'SQL failed:'
> print ex
>
> print 'shutting down'
>
> curs.close()
> conn.rollback()
> conn.close()
>
> --
> Sent via psycopg mailing list (psycopg@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/psycopg
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
[psycopg] 2.7.2 still problem with readonly/autocommit, was: Changingset_session implementation
От
Karsten Hilbert
Дата:
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 ?
Thanks,
Karsten
On Mon, Aug 07, 2017 at 10:35:26PM +0200, Karsten Hilbert wrote:
> has anyone been able to reproduce the problem described
> below? I'd be glad to provide more information if needed.
>
> Thanks !
>
> Karsten
>
>
> On Thu, Aug 03, 2017 at 11:08:18PM +0200, Karsten Hilbert wrote:
>
> > Regarding the below problem with readwrite autocommit
> > connections in default-readonly databases I have whittled
> > down a test case to the following (attached):
> >
> > db = u'gnumed_v20' # a database configured "alter database %s set default_transaction_read_only to on"
> > user = 'gm-dbo' # a user with CREATE DATABASE powers
> >
> > cmd_def_tx_ro = "SELECT upper(source), name, upper(setting) FROM pg_settings WHERE name =
'default_transaction_read_only'"
> > cmd_create_db = "create database %s_copy template %s" % (db, db)
> >
> > import sys
> > import psycopg2
> >
> > conn = psycopg2.connect(dbname = db, user = user)
> > print 'conn:', conn
> > print 'readonly:', conn.readonly
> > print 'autocommit:', conn.autocommit
> > conn.autocommit = False
> > conn.readonly = False # <========== comment out for success
> > conn.autocommit = True
> > print 'autocommit now:', conn.autocommit
> > conn.readonly = False
> > print 'readonly now:', conn.readonly
> > curs = conn.cursor()
> > curs.execute(cmd_def_tx_ro)
> > print 'should show DEFAULT_TRANSACTION_READ_ONLY set to ON'
> > print curs.fetchall()
> > curs.close()
> > conn.commit()
> > print 'the following CREATE DATABASE should fail'
> > curs = conn.cursor()
> > try:
> > curs.execute(cmd_create_db)
> > except psycopg2.InternalError as ex:
> > print 'SQL failed:'
> > print ex
> >
> > print 'shutting down'
> >
> > curs.close()
> > conn.rollback()
> > conn.close()
> >
> > which shows on the console:
> >
> > ncq@hermes:~$ python x-test-default_ro.py
> > conn: <connection object at 0xb674e6bc; dsn: 'dbname=gnumed_v20 user=gm-dbo', closed: 0>
> > readonly: None
> > autocommit: False
> > autocommit now: True
> > readonly now: False
> > should show DEFAULT_TRANSACTION_READ_ONLY set to ON
> > [('DATABASE', 'default_transaction_read_only', 'ON')]
> > the following CREATE DATABASE should fail
> > SQL failed:
> > cannot execute CREATE DATABASE in a read-only transaction
> >
> > shutting down
> >
> > The PostgreSQL log shows:
> >
> > 2017-08-03 21:00:19 GMT LOG: 00000: Verbindung empfangen: Host=[local]
> > 2017-08-03 21:00:19 GMT ORT: BackendInitialize, postmaster.c:4135
> > 2017-08-03 21:00:19 GMT LOG: 00000: Verbindung autorisiert: Benutzer=gm-dbo Datenbank=gnumed_v20
> > 2017-08-03 21:00:19 GMT ORT: PerformAuthentication, postinit.c:272
> > 2017-08-03 21:00:19 GMT LOG: 00000: statement: SELECT upper(source), name, upper(setting) FROM pg_settings
WHEREname = 'default_transaction_read_only'
> > 2017-08-03 21:00:19 GMT LOCATION: exec_simple_query, postgres.c:935
> > 2017-08-03 21:00:19 GMT LOG: 00000: statement: create database gnumed_v20_copy template gnumed_v20
> > 2017-08-03 21:00:19 GMT LOCATION: exec_simple_query, postgres.c:935
> > 2017-08-03 21:00:19 GMT ERROR: 25006: cannot execute CREATE DATABASE in a read-only transaction
> > 2017-08-03 21:00:19 GMT LOCATION: PreventCommandIfReadOnly, utility.c:236
> > 2017-08-03 21:00:19 GMT STATEMENT: create database gnumed_v20_copy template gnumed_v20
> > 2017-08-03 21:00:19 GMT LOG: 00000: disconnection: session time: 0:00:00.007 user=gm-dbo database=gnumed_v20
host=[local]
> > 2017-08-03 21:00:19 GMT LOCATION: log_disconnections, postgres.c:4501
> >
> > Commenting out the marked line makes it work and the log shows:
> >
> > 2017-08-03 21:01:24 GMT LOG: 00000: Verbindung empfangen: Host=[local]
> > 2017-08-03 21:01:24 GMT ORT: BackendInitialize, postmaster.c:4135
> > 2017-08-03 21:01:24 GMT LOG: 00000: Verbindung autorisiert: Benutzer=postgres Datenbank=postgres
> > 2017-08-03 21:01:24 GMT ORT: PerformAuthentication, postinit.c:272
> > 2017-08-03 21:01:25 GMT LOG: 00000: Anweisung: drop database gnumed_v20_copy ;
> > 2017-08-03 21:01:25 GMT ORT: exec_simple_query, postgres.c:935
> > 2017-08-03 21:01:26 GMT LOG: 00000: Verbindungsende: Sitzungszeit: 0:00:02.324 Benutzer=postgres
Datenbank=postgresHost=[local]
> > 2017-08-03 21:01:26 GMT ORT: log_disconnections, postgres.c:4501
> > 2017-08-03 21:01:33 GMT LOG: 00000: Verbindung empfangen: Host=[local]
> > 2017-08-03 21:01:33 GMT ORT: BackendInitialize, postmaster.c:4135
> > 2017-08-03 21:01:33 GMT LOG: 00000: Verbindung autorisiert: Benutzer=gm-dbo Datenbank=gnumed_v20
> > 2017-08-03 21:01:33 GMT ORT: PerformAuthentication, postinit.c:272
> > 2017-08-03 21:01:33 GMT LOG: 00000: statement: SET default_transaction_read_only TO 'off'
> > 2017-08-03 21:01:33 GMT LOCATION: exec_simple_query, postgres.c:935
> > 2017-08-03 21:01:33 GMT LOG: 00000: statement: SELECT upper(source), name, upper(setting) FROM pg_settings
WHEREname = 'default_transaction_read_only'
> > 2017-08-03 21:01:33 GMT LOCATION: exec_simple_query, postgres.c:935
> > 2017-08-03 21:01:33 GMT LOG: 00000: statement: create database gnumed_v20_copy template gnumed_v20
> > 2017-08-03 21:01:33 GMT LOCATION: exec_simple_query, postgres.c:935
> > 2017-08-03 21:01:37 GMT LOG: 00000: duration: 3447.360 ms
> > 2017-08-03 21:01:37 GMT LOCATION: exec_simple_query, postgres.c:1164
> > 2017-08-03 21:01:37 GMT LOG: 00000: disconnection: session time: 0:00:03.454 user=gm-dbo database=gnumed_v20
host=[local]
> > 2017-08-03 21:01:37 GMT LOCATION: log_disconnections, postgres.c:4501
> >
> > So, under some circumstances psycopg2 does send the
> >
> > 2017-08-03 21:01:33 GMT LOG: 00000: statement: SET default_transaction_read_only TO 'off'
> >
> > while sometimes it does not, despite both times being in
> > autocommit=True and readonly=False when the "create database"
> > is sent.
> >
> > Of course, the
> >
> > conn.autocommit = False
> > conn.readonly = False # <========== comment out for success
> > conn.autocommit = True
> > conn.readonly = False
> >
> > seems redundant but that is the relevant sequence of events
> > condensed from a larger body of code. Redundant as it may
> > seem it should still work, no ?
> >
> > I am not sure I fully grok what is happening...
> >
> > Thanks for insights,
> > Karsten
> >
> >
> > On Wed, Aug 02, 2017 at 09:38:21PM +0200, Karsten Hilbert wrote:
> >
> > > 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_v20host=[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
> > >
> > >
> > > --
> > > Sent via psycopg mailing list (psycopg@postgresql.org)
> > > To make changes to your subscription:
> > > http://www.postgresql.org/mailpref/psycopg
> >
> > --
> > GPG key ID E4071346 @ eu.pool.sks-keyservers.net
> > E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
Вложения
Re: [psycopg] 2.7.2 still problem with readonly/autocommit, was:Changing set_session implementation
От
Daniele Varrazzo
Дата:
On Thu, Aug 10, 2017 at 1:45 PM, Karsten Hilbert
<Karsten.Hilbert@gmx.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
Re: [psycopg] 2.7.2 still problem with readonly/autocommit, was:Changing set_session implementation
От
Karsten Hilbert
Дата:
On Fri, Aug 11, 2017 at 12:22:55AM +0100, Daniele Varrazzo wrote:
> 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.
I agree with this rationale and I understand your
explanation. Thanks for taking the time. What I am saying,
however, is that I think I have found a bug. Notice this
sequence in my test script:
conn.autocommit = False
conn.readonly = False
conn.autocommit = True
conn.readonly = False
After this I would assume the connection to be in readwrite
mode (per your explanation, psycopg2 would (also) emit a "set
default_trans...off" when the last readonly=False is given.
However, it does NOT, as the PostgreSQL log snippet shows.
Here is the console output (I will check whether I can get
access to a debug build):
conn: <connection object at 0xb66a88b4; dsn: 'dbname=gnumed_v20 user=gm-dbo', closed: 0>
readonly: None
autocommit: False
setting autocommit to False
autocommit now: False
setting readonly to False
readonly now: False
setting autocommit to True
autocommit now: True
readonly now: False
querying DEFAULT_TRANSACTION_READ_ONLY state (should show "ON")
[('DATABASE', 'default_transaction_read_only', 'ON')]
the following SQL will fail: create database gnumed_v20_copy template gnumed_v20
(note that the transaction being talked about is implicit to PostgreSQL, due to autocommit mode)
SQL failed:
cannot execute CREATE DATABASE in a read-only transaction
shutting down
If I comment out the *first* call to readonly=False (after
autocommit=False, before autocommit=True) it does work.
It seems as if psycopg2 "remembers" that it has already
called readonly=False.
Here is the script again:
db = u'gnumed_v20' # a database configured "alter database %s set default_transaction_read_only to on"
user = 'gm-dbo' # a user with CREATE DATABASE powers
cmd_def_tx_ro = "SELECT upper(source), name, upper(setting) FROM pg_settings WHERE name =
'default_transaction_read_only'"
cmd_create_db = "create database %s_copy template %s" % (db, db)
import sys
import psycopg2
conn = psycopg2.connect(dbname = db, user = user)
print 'conn:', conn
print 'readonly:', conn.readonly
print 'autocommit:', conn.autocommit
print 'setting autocommit to False'
conn.autocommit = False
print 'autocommit now:', conn.autocommit
print 'setting readonly to False'
conn.readonly = False # comment out for success
print 'readonly now:', conn.readonly
print 'setting autocommit to True'
conn.autocommit = True
print 'autocommit now:', conn.autocommit
conn.readonly = False
print 'readonly now:', conn.readonly
curs = conn.cursor()
curs.execute(cmd_def_tx_ro)
print 'querying DEFAULT_TRANSACTION_READ_ONLY state (should show "ON")'
print curs.fetchall()
curs.close()
conn.commit()
print 'the following SQL will fail:', cmd_create_db
print '(note that the transaction being talked about is implicit to PostgreSQL, due to autocommit mode)'
curs = conn.cursor()
try:
curs.execute(cmd_create_db)
except psycopg2.InternalError as ex:
print 'SQL failed:'
print ex
print 'shutting down'
curs.close()
conn.rollback()
conn.close()
> This should be enough to work around your problem: put the connection
> in read-write state *after* setting autocommit
I do, as you can see from the script above.
> 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.
This is the workaround I am currently using.
I still wonder why the above does not work.
> Hope this helps. Feel free to propose a merge request with change in
> documentation if you find it would have helped.
At this point I am still unsure whether the above is a bug.
The documentation change would amount to something like
once in autocommit mode do not rely on psycopg2 methods
for switching readonly on or off, do not even rely on
what the psycopg2 connection object _tells_ you about the
state it is in
While it seems true at the moment I don't think this goes
along with the overall excellent quality of psycopg2 :-)
May I kindly ask you to run my script against a readonly
database ? The particular dance between autocommit/readonly
on/off is needed to show the problem.
Meanwhile I will try to somehow run a debug build.
Thanks for thinking along,
Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
Re: [psycopg] 2.7.2 still problem with readonly/autocommit, was:Changing set_session implementation
От
Karsten Hilbert
Дата:
> Here is the script again:
Improved and attached for convenience.
Replace database name and user name inside it.
Run it with or without "show_problem" as the first and only
command line argument.
> Meanwhile I will try to somehow run a debug build.
Debian does not offer a debug build as far as I can tell and,
consequently, setting
PSYCOPG_DEBUG=1
does not have any effect.
Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
Вложения
Re: [psycopg] 2.7.2 still problem with readonly/autocommit, was:Changing set_session implementation
От
Daniele Varrazzo
Дата:
On Fri, Aug 11, 2017 at 9:19 AM, Karsten Hilbert <Karsten.Hilbert@gmx.net> wrote: > I agree with this rationale and I understand your > explanation. Thanks for taking the time. What I am saying, > however, is that I think I have found a bug. Notice this > sequence in my test script: > > conn.autocommit = False > conn.readonly = False > conn.autocommit = True > conn.readonly = False > > After this I would assume the connection to be in readwrite > mode (per your explanation, psycopg2 would (also) emit a "set > default_trans...off" when the last readonly=False is given. > > However, it does NOT, as the PostgreSQL log snippet shows. Ok, I see your point, and yes, I think it is a bug. In [9]: cnn.autocommit = False [23759] conn_set_session: autocommit 0, isolevel 5, readonly 2, deferrable 2 In [10]: cnn.readonly = True [23759] conn_set_session: autocommit 0, isolevel 5, readonly 1, deferrable 2 In [11]: cnn.autocommit = True [23759] conn_set_session: autocommit 1, isolevel 5, readonly 1, deferrable 2 In [12]: cnn.readonly = True [23759] conn_set_session: autocommit 1, isolevel 5, readonly 1, deferrable 2 The operation in [12] would be idempotent: the code checks that the new value is the same of the previous one and bails out early. But checking the internal state for idempotence when in autocommit is wrong: the only state that matters is what's in the server session. I'll take a look at the logic of the state switching. -- Daniele
Re: [psycopg] 2.7.2 still problem with readonly/autocommit, was:Changing set_session implementation
От
Karsten Hilbert
Дата:
On Fri, Aug 11, 2017 at 12:34:35PM +0100, Daniele Varrazzo wrote: > > After this I would assume the connection to be in readwrite > > mode (per your explanation, psycopg2 would (also) emit a "set > > default_trans...off" when the last readonly=False is given. > > > > However, it does NOT, as the PostgreSQL log snippet shows. > > Ok, I see your point, and yes, I think it is a bug. Thank you very much and I am sorry for being so stubborn ! :-) > In [9]: cnn.autocommit = False > [23759] conn_set_session: autocommit 0, isolevel 5, readonly 2, deferrable 2 > > In [10]: cnn.readonly = True > [23759] conn_set_session: autocommit 0, isolevel 5, readonly 1, deferrable 2 > > In [11]: cnn.autocommit = True > [23759] conn_set_session: autocommit 1, isolevel 5, readonly 1, deferrable 2 > > In [12]: cnn.readonly = True > [23759] conn_set_session: autocommit 1, isolevel 5, readonly 1, deferrable 2 > > The operation in [12] would be idempotent: the code checks that the > new value is the same of the previous one and bails out early. That was my suspicion. > But > checking the internal state for idempotence when in autocommit is > wrong: the only state that matters is what's in the server session. Aha, I understand ! > I'll take a look at the logic of the state switching. Thanks ! Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
Re: [psycopg] 2.7.2 still problem with readonly/autocommit, was:Changing set_session implementation
От
Karsten Hilbert
Дата:
Hi Danielle, I wonder whether there is any news regarding the bug reported below ? Thanks a lot, Karsten On Fri, Aug 11, 2017 at 03:33:27PM +0200, Karsten Hilbert wrote: > > > After this I would assume the connection to be in readwrite > > > mode (per your explanation, psycopg2 would (also) emit a "set > > > default_trans...off" when the last readonly=False is given. > > > > > > However, it does NOT, as the PostgreSQL log snippet shows. > > > > Ok, I see your point, and yes, I think it is a bug. > > Thank you very much and I am sorry for being so stubborn ! :-) > > > In [9]: cnn.autocommit = False > > [23759] conn_set_session: autocommit 0, isolevel 5, readonly 2, deferrable 2 > > > > In [10]: cnn.readonly = True > > [23759] conn_set_session: autocommit 0, isolevel 5, readonly 1, deferrable 2 > > > > In [11]: cnn.autocommit = True > > [23759] conn_set_session: autocommit 1, isolevel 5, readonly 1, deferrable 2 > > > > In [12]: cnn.readonly = True > > [23759] conn_set_session: autocommit 1, isolevel 5, readonly 1, deferrable 2 > > > > The operation in [12] would be idempotent: the code checks that the > > new value is the same of the previous one and bails out early. > > That was my suspicion. > > > But > > checking the internal state for idempotence when in autocommit is > > wrong: the only state that matters is what's in the server session. > > Aha, I understand ! > > > I'll take a look at the logic of the state switching. > > Thanks ! > > Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via psycopg mailing list (psycopg@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/psycopg
Re: [psycopg] 2.7.2 still problem with readonly/autocommit, was:Changing set_session implementation
От
Daniele Varrazzo
Дата:
On Fri, Sep 29, 2017 at 10:50 PM, Karsten Hilbert <Karsten.Hilbert@gmx.net> wrote: > I wonder whether there is any news regarding the bug reported > below ? There is a bug open, no timescale for it. It will be dealt with when I have time, or earlier if anyone contributes a solution. In honesty it is not on top of my priority list: even considering the time I can devolve to free-software projects (which is only a fraction of my free time), I have more pressing issues in other project (pg_repack screwing up logical decoding...). And I'm sure not in the position to use time paid by my employer to fix this bug. https://github.com/psycopg/psycopg2/issues/580 -- Daniele -- Sent via psycopg mailing list (psycopg@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/psycopg
Re: [psycopg] 2.7.2 still problem with readonly/autocommit, was:Changing set_session implementation
От
Karsten Hilbert
Дата:
On Sun, Oct 01, 2017 at 02:42:33PM +0100, Daniele Varrazzo wrote: >> I wonder whether there is any news regarding the bug reported >> below ? > > There is a bug open, no timescale for it. It will be dealt with when I > have time, or earlier if anyone contributes a solution. In honesty it > is not on top of my priority list: even considering the time I can > devolve to free-software projects (which is only a fraction of my free > time), I have more pressing issues in other project (pg_repack > screwing up logical decoding...). And I'm sure not in the position to > use time paid by my employer to fix this bug. > > https://github.com/psycopg/psycopg2/issues/580 Thank you for the clarification. Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via psycopg mailing list (psycopg@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/psycopg
Re: [psycopg] 2.7.2 still problem with readonly/autocommit, was:Changing set_session implementation
От
Daniele Varrazzo
Дата:
On Fri, Sep 29, 2017 at 10:50 PM, Karsten Hilbert <Karsten.Hilbert@gmx.net> wrote: > Hi Danielle, > > I wonder whether there is any news regarding the bug reported > below ? Hello Karsten, FYI I have fixed the bug you reported, in the maint_2_7 and master branches. Please let me know if you think the adapter now works as expected. I would like to release a bugfix 2.7 version in the next week or two. Best regards, -- Daniele
Re: [psycopg] 2.7.2 still problem with readonly/autocommit, was:Changing set_session implementation
От
Karsten Hilbert
Дата:
On Thu, Jan 18, 2018 at 11:23:57AM +0000, Daniele Varrazzo wrote: > FYI I have fixed the bug you reported, in the maint_2_7 and master > branches. Please let me know if you think the adapter now works as > expected. Many thanks for taking care of that issue. From my reading of the code it should work properly. Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346