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