Hi all,
I'm using sqlalchemy 0.9.7 (yes, I know we need to upgrade).
I've recently started to employ psycopg2's
psycopg2.extensions.set_wait_callback function to facilitate terminating
ongoing queries in response to activity on other sockets (such as an
HTTP client disconnect or shutdown message). I essentially have this
working, but there is a wrinkle.
Specifically, when it is time to abort the ongoing query, I arrange for
the wait_callback to raise an exception. psycopg2 dutifully closes the
dbapi connection when an exception occurs, which is what I want it to
do. The problem is, sqlalchemy still thinks the connection is open at
the Connection and ConnectionFairy layers and sometimes runs into a
variety of "connection closed" errors when sqlalchemy tries to do stuff
with the dbapi connection later because it thinks it is still open.
What I want to do is call Connection.invalidate() from inside the
wait_callback, in order to invalidate the Connection, ConnectionRecord,
and ConnectionFairy. This is difficult because the only parameter to
the wait_callback function is the DBAPI connection, not the associated
high level connection object(s). I have no obvious way of getting from
the DBAPI connection to the SA Connection object(s).
I've had a few ideas on how to solve this, one of which seems to work in
my limited testing, but the documentation gives me second thoughts. I
wonder if there is a better way.
Approach 1
The first thing I tried (which did not work at all) was to use:
event.listens_for(SomeEngine, 'handle_error')
in order that I could set the is_disconnect field on the
exception_context object, thereby notifying sqlalchemy of the
disconnect. Turns out that sqlalchemy doesn't wrap calls to fetchmany
such that exceptions from fetchmany get sent to handle_error, so I
abandoned this approach. I wonder if this is indicative of a more
general issue where network errors raised by fetchmany aren't noticed?
Approach 2
The next thing I tried was using:
@event.listens_for(SomeEngine, 'engine_connect')
def receive_engine_connect(conn, branch):
raw = conn.connection.connection
if not hasattr(raw, 'sa_conns'):
raw.sa_conns = weakref.WeakSet()
raw.sa_conns.add(conn)
And then when I know the dbapi connection is about to be closed for
sure, I do something like this (but more carefully):
for x in getattr(dbapi_conn, 'sa_conns', ()):
x.invalidate()
This seemed to work in practice, but this sentence from the
documentation and the body of Connection._revalidate_connection() make
me think existing Connection objects can get reassociated with different
dbapi connections without my knowledge:
"But note there can in fact be multiple PoolEvents.checkout() events
within the lifespan of a single Connection object, if that Connection is
invalidated and re-established."
Approach 3
This is similar to approach 2, except that I use the "checkout" event
instead. This allows me to invoke ConnectionFairy.invalidate() or
ConnectionRecord.invalidate(), but that seems to not be as good as
invoking Connection.invalidate(), because the Connection still doesn't
realize it is closed.
Is there something better I can do to get a mapping from DBAPI
connection to SA Connection? I am inclined to do linear search over all
Connection objects for ones using the dying dbapi connection. I assume
the engine or pool must have a collection of all Connections, and there
aren't ever more than a few in my application.
- Dave
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
