Re: Transaction control overhauling

On Thu, May 12, 2011 at 9:01 AM, Federico Di Gregorio
<federico(dot)digregorio(at)dndg(dot)it> wrote:

>> 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:
>
> I'd make this a keyword argument function with the following signature:
>
> conn.set_transaction(
>        isolation_level=None,
>        autocommit=None,
>        deferrable=None,
>        readonly=None)
>
> to keep everything related to transaction management in a single
> function. I don't like the proposed autocommit() method because it is
> yet another DBAPI extension and must keep that to a minimum.

The idea though was to have set_transaction mapping closely PG's SET
TRANSACTION statement, and autocommit is a different beast. I also
thought autocommit was a pretty standard extension. But actually,
making a survey of other drivers:

- MySQLdb: conn.autocommit()
- cx_Oracle: conn.autocommit (attribute, not function)
- pyodbc: conn.autocommit (attribute)
- sqlite3: conn.isolation_level = None (shared dna with psycopg, eh?
:) however it's an attribute)
- KInterbaseDB: not supported

So, total anarchy here :\. The autocommit attribute would have been my
favourite, but psycopg uses more often methods than read/write
attributes (probably there is none of them) so the autocommit() method
would blend better. But now, thinking about that, there would be no
natural way to read back the value, for which there is no PG parameter
to SHOW... so the attribute solution seems really the best option
(unless making a pair set_autocommit/autocommit... ugh).

To summarize: an autocommit parameter to set_transaction would be ok
enough as it's independent from the other ones. But it has the
shortcoming of giving no way to read the value back. We would have

conn.set_transaction(autocommit=True)

which is not bad. but

conn.autocommit = True

feels better and allows to read the value back. And it's used quite a
lot, more than going serialized I'd say.

> Also, I
> sorted the parameters in set_transaction() from the probably most used
> to least.

I would think that read_only would more used than deferrable, which
looks a pretty specialized level. No problem anyway as I expect all
the parameters after the first to be called as keyword, e.g. people
may want to use:

conn.set_transaction(READ_COMMITTED)
conn.set_transaction(read_only=True)
conn.set_transaction(SERIALIZED, read_only=True, deferrable=False)

and not

conn.set_transaction(SERIALIZED, None, False, True)

>> 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.
>
> Do we need the ability to pass "default"? When the user call
> set_transaction() with missing or None parameters do we want to send
> "default" or stay with the current value? I favor the latter, e.g.,

Yes, me too: None would mean don't change, not reset to default, and
setting the default would require a different symbol, such as
set_transaction(read_only=DEFAULT). We don't strictly need it of
course: people can still query the connection and later reset to the
original value. I agree it's not the most likely use case though, we
may also avoid this feature.

I'll leave you with the thorny autocommit question... :)

Bye!

-- Daniele