Support PostgreSQL's "WITH HOLD" cursor option

[Scott Milliken]

http://www.postgresql.org/docs/current/static/sql-declare.html

It's currently not possible to specify the cursor option "WITH HOLD". This option is useful when you'd like to commit between reads from a cursor.

In particular, in enables one to do this:

cur = session.query(Foo).yield_per(1)
for foo in cur:
    print(foo)
    # maybe do some writes..
    session.commit()

Currently, this causes an error:

Traceback (most recent call last):
  File "withold_test.py", line 9, in <module>
    for q in c:
  File "SQLAlchemy-1.0.11/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", line 86, in instances
    util.raise_from_cause(err)
  File "SQLAlchemy-1.0.11/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 200, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "SQLAlchemy-1.0.11/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", line 63, in instances
    fetch = cursor.fetchmany(query._yield_per)
  File "SQLAlchemy-1.0.11/lib/python2.7/site-packages/sqlalchemy/engine/result.py", line 997, in fetchmany
    self.cursor, self.context)
  File "SQLAlchemy-1.0.11/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1341, in _handle_dbapi_exception
    exc_info
  File "SQLAlchemy-1.0.11/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 200, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "SQLAlchemy-1.0.11/lib/python2.7/site-packages/sqlalchemy/engine/result.py", line 990, in fetchmany
    l = self.process_rows(self._fetchmany_impl(size))
  File "SQLAlchemy-1.0.11/lib/python2.7/site-packages/sqlalchemy/engine/result.py", line 1149, in _fetchmany_impl
    row = self._fetchone_impl()
  File "SQLAlchemy-1.0.11/lib/python2.7/site-packages/sqlalchemy/engine/result.py", line 1139, in _fetchone_impl
    self.__buffer_rows()
  File "SQLAlchemy-1.0.11/lib/python2.7/site-packages/sqlalchemy/engine/result.py", line 1126, in __buffer_rows
    self.__rowbuffer = collections.deque(self.cursor.fetchmany(size))
sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) named cursor isn't valid anymore

I attached a patch for your review. Example:

cur = session.query(Foo).yield_per(1, with_hold=True)
for foo in cur:
    print(foo)
    # maybe do some writes..
    session.commit()

Happy to submit a pull request if you're prefer.

As an aside, I'd also like to support the "SCROLL" and "NO SCROLL" options, but to be useful we'd need to add APIs to support the direction clause in FETCH and MOVE statements (http://www.postgresql.org/docs/current/static/plpgsql-cursors.html). Do you have an opinion about how you'd like to implement this, or should I just give it a shot?

[Mike Bayer]

please send a PR that supports this option as execution_options(postgresql_with_hold=True), including tests. As far as a bidirectional cursor, if I'm understanding correctly that falls under pep249 "scroll" e.g. https://www.python.org/dev/peps/pep-0249/#scroll, for which I'd argue is better left as a direct cursor recipe as it is extremely special use and we're never going to have ResultProxy handling all that.

[Mike Bayer]

re: the attached patch, Query already supports execution_options() so no need to add a flag to yield_per(). if anything, if "with hold" is of general use without any downside then we'd just have the "serverside" setting of the psycopg2 dialect imply "with hold" someday.

[Scott Milliken]

Ok, it's in pull request #79. Usage:

cur = session.query(Foo).limit(10).execution_options(postgresql_with_hold=True).yield_per(1)
for foo in cur:
    print(foo)
    # maybe do some writes..
    session.commit()

if "with hold" is of general use without any downside

My vote would be to leave it defaulted off, which matches the PostgreSQL behavior. The downside to having it on is that the user may expect that the cursor would see changes made from these writes.

[Scott Milliken]

A downside I discovered, in case it might benefit someone else in the future: PostgreSQL will materialize WITH HOLD cursors on the first commit, executing the entire query and caching it in memory or on disk. This is still useful for saving your python process from materializing all of the results in memory, but not useful for saving PostgreSQL from doing so.

[Mike Bayer]

PR needs tests and at least rudimentary documentation.