Re: Transaction control overhauling

I'm not a specialist, but I think that's perfectly valid.

About the II alternatives:

II.1 - Every begin would set transaction again or only the next one? Anyway,
either of them don't look nice.

II.2 - That would be my choice but...

II.3 - I didn't know that the 'default' cant be applied to it, so II.3 is
the easy winner.

Go for it.

2011/5/11 Daniele Varrazzo <daniele(dot)varrazzo(at)gmail(dot)com>

> 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
>
> --
> Sent via psycopg mailing list (psycopg(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/psycopg
>