Re: Rollback on close [Was: Fwd: [DB-SIG] conn.close() idempotence]

On Wed, Oct 19, 2011 at 1:12 PM, Marko Kreen <markokr@gmail.com> wrote:

> First, "in transaction" is not enough, it must check if connections
> is "idle in transaction" and no query has been sent.

Question: is for the middleware different if a connection is idle in
transaction with or without a query has been sent to the database?
However it's easy to check the "idle in transaction" state in the
connection; and actually, because it's us who send the commands, we
could also detect about any sent query.

Your question also makes me think about what should happen if a
close() is issued in a separate thread while a query is running... but
this should be just handled by the serialization code in the
transaction, i.e. the close should wait until the query has finished.

> Secondly, I think there are two types of code to consider:
>
> - Sloppy code - eg read-only web page that does
>
>  db = connect()
>  curs.execute('select ...')
>  curs.execute('select ...')
>  db.close()
>
> - Robust code, where in-transaction-close means
>  problem, and it wants to get rid of connection
>  without touching network.
>
> Although I understand the urge to optimize for first case,
> you take away the possibility of robust code to behave well.

What is an example of situation where a close in transaction without
rollback is a better option than rolling back too?

> So if you really want to restore the rollback-on-close
> behaviour, at least make it configurable.

I'm more for making a decision instead of leaving too many things to
be configured, so if we deem that closing without explicit rollback is
still a better solution I'm fine with leaving it this way and suggest
users to write less sloppy code. I.E. I would *not* like to add an
option such as conn.close(rollback=False).

> OTOH, as the lightweight .close() is only problematic
> with middleware, it seems to hint that this idle-in-tx
> check should be moved into middleware, and psycopg
> should not need to worry about it..

Well, you know the middleware much better than me: I was assuming that
if pgpool discards connection returned idle in transaction to the pool
you have very strong reasons :) I just want to optimize the
communication between the driver and the middleware: what do you think
the "protocol" between psycopg and pgpool should be?

-- Daniele