hi there - this issue does not sound familiar , I guess you are trying to remove the Query iterator before it's exhausted and therefore problems are occurring.
I would need to see a stack trace to understand the issue. Also, you shouldn't use Query for cases like these, use modern 1.4 style statement executions which will return a Result object that is more robustly designed for different row fetching scenarios (though it still uses loading.py internally). On Wed, Feb 16, 2022, at 4:40 PM, David Vitek wrote: > 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 [email protected] > <mailto:sqlalchemy%[email protected]>. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/PH1P110MB128246BB30B8FC8846504AA0BB359%40PH1P110MB1282.NAMP110.PROD.OUTLOOK.COM. > -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/3adb7c7b-074a-4536-b779-771cbaffc6b9%40www.fastmail.com.
