Transaction control overhauling - Mailing list psycopg

Hello,

I think psycopg needs some cleanup in the area of the transactions control.

The main points of the current implementation are:

1. at connection time, psycopg queries default_isolation_level from the server;

2. when starting a transaction, it executes "begin; set isolation
level LEVEL" after what received in 1.

3. the isolation level can be read from conn.isolation_level and
changed using using set_isolation_level(). Supported values are 1
(read committed), 2 (serializable).

4. the set_isolation_level() is also used to put the connection in
"autocommit" mode, passing the value 0.


Shortcomings:

a. In PG 9.1 level "repeatable read" is no more an alias for
serializable, so all the 4 SQL levels should be supported (note that
this wasn't happening before because this area was designed in pre-8.0
era, when levels read uncommitted/repeatable read were not accepted by
SET TRANSACTION)

b. autocommit is not alternative to the isolation level: it is
orthogonal. A connection may be autocommit + serializable. The current
behaviour is implicitly autocommit + GUC default.

c. there is no support to switch a connection read only - feature
sometimes requested. This, again, is orthogonal to the isolation
level, so an implementation such as the one proposed in the ticket #12
(http://psycopg.lighthouseapp.com/projects/62710/tickets/12) doesn't
convince me.

d. PG 9.1 also introduced "set transaction [not] deferrable"
(http://www.postgresql.org/docs/9.1/static/sql-set-transaction.html).

I also feel the combination of points 1 and 2 above somewhat silly: we
query the connection and then explicitly force the level we know to be
default. The only extra thing respect of not doing nothing at all is
that the connection then knows the isolation level.


I think a more sensible behaviour would be:

I. don't issue any query at startup and, by default, don't pass any
isolation level together with BEGIN:

II. add a method conn.set_transaction(isolation_level=None,
read_only=None, deferrable=None) allowing to change one or more of the
transaction settings. Calling the method would terminate the current
transaction and put the new settings in place. Note that there are
several ways for implementing this:

II. 1. store variables in the connection object and pass the relative
SET TRANSACTION at the following BEGIN

II. 2. run a query SET SESSION CHARACTERISTICS AS ... and not store
anything in the connection status

II. 3. run a query to set the GUC instead (SET default_whatever AS
value): very similar to II. 2., but it also allows passing the value
"default", meaning "reset to the configuration value", an option
apparently missing with the SET SESSION CHARACTERISTICS syntax.

III. add an independent method conn.autocommit(value=True). A less
verbose method for a frequently used functionality.

IV. for backwards compatibility, convert isolation_level into a
property, querying the server to know the current status.

V. keep set_isolation_level() for backwards compatibility, converting
it into a wrapper for the other methods.

VI. don't write so many lists in a single email anymore: I'm out of alphabets.


With these changes, I think we should keep complete compatibility with
the past, both as interface and as behaviour, but we end up with less
queries performed and complete support for all the current and
upcoming Postgres features (plus a natural way of extending, adding
new keyword arguments to conn.set_transaction() should the need
arise).

Comments? Shall we go for it?

Cheers,

-- Daniele