[Sqlalchemy-tickets] Issue #3667: Support PostgreSQL's "WITH HOLD" cursor option (zzzeek/sqlalchemy)

[Scott M. <iss...@bi...>]

New issue 3667: Support PostgreSQL's "WITH HOLD" cursor option
https://bitbucket.org/zzzeek/sqlalchemy/issues/3667/support-postgresqls-with-hold-cursor

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?