Hi all,
I have a situation where we are using transactions and postgres' server side
cursors. The troublesome sequence is something like:
x = s.query(...)
next(x)
if bail_out:
s.commit()
del x
I'm not entirely sure that this simple example can reproduce the problem; the
true code is more complex, but hopefully this helps paint a picture of the
general control flow.
The problem I'm running into has to do with what python does to garbage collect
an ongoing coroutine. The coroutine is ongoing since we have not exhausted x
(assume the query returns many rows). Python causes the coroutine to
internally raise GeneratorExit when it is destroyed, which in this case
triggers an except block that attempts to close the cursor. The coroutine is
instances() from sqlalchemy's loading.py. The del x line causes the
coroutine's refcount to drop to 0, which causes the GeneratorExit to raise and
get caught by:
# from loading.py
def instances(cursor, context):
...
except Exception:
with util.safe_reraise():
cursor.close()
Upon attempting to close the cursor, postgres complains
"psycopg2.ProgrammingError: named cursor isn't valid anymore" because it was
closed when the enclosing transaction closed.
We consider it a good thing that the cursor closes when the transaction closes,
but we find the exception raised when doing GC undesirable.
I'm wondering if there is any way to make cursors more cognizant of the
transactions that they live in, so that they might realize they are already
closed when their surrounding transaction has closed?
In the meantime, we are working around this problem by checking whether the
session in the query object inside instances() is already dead:
# from loading.py
def instances(cursor, context):
...
except Exception:
with util.safe_reraise():
if query.session.is_active: # <--- New line
cursor.close()
The main shortcoming of this solution is that it only helps this particular
cursor.
While we could try to do things like move the "del x" line up before the
commit(), this isn't a real fix since python doesn't promise to GC things
promptly. Explicitly closing the cursor would be another option, but it's not
readily available to the sqlalchemy client when using the ORM interface. Even
if it were, I imagine we'd forget to do it from time to time.
Any thoughts?
________________________________
The information contained in this e-mail and any attachments from GrammaTech,
Inc may contain confidential and/or proprietary information, and is intended
only for the named recipient to whom it was originally addressed. If you are
not the intended recipient, any disclosure, distribution, or copying of this
e-mail or its attachments is strictly prohibited. If you have received this
e-mail in error, please notify the sender immediately by return e-mail and
permanently delete the e-mail and any attachments.
--
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 sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/PH1P110MB128246BB30B8FC8846504AA0BB359%40PH1P110MB1282.NAMP110.PROD.OUTLOOK.COM.
