executemany() incorrectly used for updates to a table with row versioning with psycopg2

[Tom Flannaghan]

Hi,

We are currently trying to upgrade to sqlalchemy 1.0.8 and have hit a problem with executemany() and row versioning. We are using postgres and psycopg2 as a backend. We have tables that use server side versioning (as discussed at http://docs.sqlalchemy.org/en/rel_0_9/orm/versioning.html#server-side-version-counters), and when multiple rows are updated, the UPDATE statements are batched together using executemany() in the new version of sqlalchemy. Unfortunately, psycopg2 does not support iterating over the results of an executemany() query (see http://initd.org/psycopg/docs/cursor.html#cursor.executemany) so the new row versions returned by the update statement cannot be read.

Here is an example that illustrates the problem:

from sqlalchemy import Column, FetchedValue, Integer, String

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy.orm import sessionmaker

Base = declarative_base()

class Port(Base):

    ''' Table for storing ports. '''

    __tablename__ = "port"

    

    name = Column(String, primary_key=True)

    port = Column(Integer)

    

    xmin = Column(Integer, server_default=FetchedValue(), server_onupdate=FetchedValue(), system=True)

    __mapper_args__ = {"version_id_col": xmin,

                       "version_id_generator": False}

                        

engine = create_engine(...)

Session = sessionmaker(bind=engine)

session = Session()

session.bind.echo = True

p1 = Port(name='a', port=1)

p2 = Port(name='b', port=2)

session.add(p1)

session.add(p2)

session.flush()

# this update fails

p1.port = 3

p2.port = 4

session.flush()

session.rollback()

The SQL generated for the update looks like this:

2015-10-19 14:46:30,289 INFO sqlalchemy.engine.base.Engine UPDATE comm.port SET port=%(port)s WHERE comm.port.name = %(comm_port_name)s AND comm.port.xmin = %(comm_port_xmin)s RETURNING comm.port.xmin

2015-10-19 14:46:30,289 INFO sqlalchemy.engine.base.Engine ({'comm_port_xmin': '536394944', 'comm_port_name': 'a', 'port': 3}, {'comm_port_xmin': '536394944', 'comm_port_name': 'b', 'port': 4})

This code raises the following exception:

File user!flannt!untitled6.py, line 52, in : session.flush() 

File I:\sw\external\20151014-0\python27\lib\site-packages\sqlalchemy-1.0.8-py2.7-win32.egg\sqlalchemy\orm\session.py, line 2004, in flush : self._flush(objects) 

File I:\sw\external\20151014-0\python27\lib\site-packages\sqlalchemy-1.0.8-py2.7-win32.egg\sqlalchemy\orm\session.py, line 2122, in _flush : transaction.rollback(_capture_exception=True) 

File I:\sw\external\20151014-0\python27\lib\site-packages\sqlalchemy-1.0.8-py2.7-win32.egg\sqlalchemy\util\langhelpers.py, line 60, in __exit__ : compat.reraise(exc_type, exc_value, exc_tb) 

File I:\sw\external\20151014-0\python27\lib\site-packages\sqlalchemy-1.0.8-py2.7-win32.egg\sqlalchemy\orm\session.py, line 2086, in _flush : flush_context.execute() 

File I:\sw\external\20151014-0\python27\lib\site-packages\sqlalchemy-1.0.8-py2.7-win32.egg\sqlalchemy\orm\unitofwork.py, line 373, in execute : rec.execute(self) 

File I:\sw\external\20151014-0\python27\lib\site-packages\sqlalchemy-1.0.8-py2.7-win32.egg\sqlalchemy\orm\unitofwork.py, line 532, in execute : uow 

File I:\sw\external\20151014-0\python27\lib\site-packages\sqlalchemy-1.0.8-py2.7-win32.egg\sqlalchemy\orm\persistence.py, line 170, in save_obj : mapper, table, update) 

File I:\sw\external\20151014-0\python27\lib\site-packages\sqlalchemy-1.0.8-py2.7-win32.egg\sqlalchemy\orm\persistence.py, line 692, in _emit_update_statements : execute(statement, multiparams) 

File I:\sw\external\20151014-0\python27\lib\site-packages\sqlalchemy-1.0.8-py2.7-win32.egg\sqlalchemy\engine\base.py, line 914, in execute : return meth(self, multiparams, params) 

File I:\sw\external\20151014-0\python27\lib\site-packages\sqlalchemy-1.0.8-py2.7-win32.egg\sqlalchemy\sql\elements.py, line 323, in _execute_on_connection : return connection._execute_clauseelement(self, multiparams, params) 

File I:\sw\external\20151014-0\python27\lib\site-packages\sqlalchemy-1.0.8-py2.7-win32.egg\sqlalchemy\engine\base.py, line 1010, in _execute_clauseelement : compiled_sql, distilled_params 

File I:\sw\external\20151014-0\python27\lib\site-packages\sqlalchemy-1.0.8-py2.7-win32.egg\sqlalchemy\engine\base.py, line 1159, in _execute_context : result = context._setup_crud_result_proxy() 

File I:\sw\external\20151014-0\python27\lib\site-packages\sqlalchemy-1.0.8-py2.7-win32.egg\sqlalchemy\engine\default.py, line 832, in _setup_crud_result_proxy : row = result.fetchone() 

File I:\sw\external\20151014-0\python27\lib\site-packages\sqlalchemy-1.0.8-py2.7-win32.egg\sqlalchemy\engine\result.py, line 1026, in fetchone : self.cursor, self.context) 

File I:\sw\external\20151014-0\python27\lib\site-packages\sqlalchemy-1.0.8-py2.7-win32.egg\sqlalchemy\engine\base.py, line 1341, in _handle_dbapi_exception : exc_info 

File I:\sw\external\20151014-0\python27\lib\site-packages\sqlalchemy-1.0.8-py2.7-win32.egg\sqlalchemy\util\compat.py, line 199, in raise_from_cause : reraise(type(exception), exception, tb=exc_tb) 

File I:\sw\external\20151014-0\python27\lib\site-packages\sqlalchemy-1.0.8-py2.7-win32.egg\sqlalchemy\engine\result.py, line 1017, in fetchone : row = self._fetchone_impl() 

File I:\sw\external\20151014-0\python27\lib\site-packages\sqlalchemy-1.0.8-py2.7-win32.egg\sqlalchemy\engine\result.py, line 898, in _fetchone_impl : return self.cursor.fetchone() 

ProgrammingError: (psycopg2.ProgrammingError) no results to fetch

[Steven Winfield]

The problem seems to be that the result set from an executemany() call in psycopg2 is discarded - here is a good stackoverflow article on this subject:

http://stackoverflow.com/questions/21624844/getting-ids-of-multiple-rows-inserted-in-psycopg2

In _emit_update_statements in orm/persistence.py, and here in the v1.0 migration docs, one of the conditions for using executemany is:

"The mapping does not use a version_id_col, or the backend dialect supports a “sane” rowcount for an executemany() operation;"

...but I'm not sure that is enough - psycopg2 does support sane rowcounts, and its dialect's supports_sane_rowcount and supports_sane_multi_rowcount are both rightly True (I work with Tom, and we're using psycopg2 v2.6 by the way - I know those values are predicated on the psycopg2 version).

I'm working on a patch that adds another member to the default dialect called "supports_executemany_results" that defaults to True but is set to False in PGDialect_psycopg2 in dialects/postgres/psycopg2.py. This can then be used in the determination of allow_multirow (line 646 in orm/persistence.py). This will preserve the current behaviour of other dialects, but of course there could be others with the same problem.

Steve

[Mike Bayer]

On 10/19/15 11:44 AM, Steven Winfield wrote:
> The problem seems to be that the result set from an executemany() call
> in psycopg2 is discarded - here is a good stackoverflow article on this
> subject:
> http://stackoverflow.com/questions/21624844/getting-ids-of-multiple-rows-inserted-in-psycopg2
>
> In _emit_update_statements in orm/persistence.py, and here

> <http://docs.sqlalchemy.org/en/rel_1_0/changelog/migration_10.html?highlight=version_id_col#update-statements-are-now-batched-with-executemany-in-a-flush>

> in the v1.0 migration docs, one of the conditions for using executemany is:
> "The mapping does not use a |version_id_col|

> <http://docs.sqlalchemy.org/en/rel_1_0/orm/mapping_api.html#sqlalchemy.orm.mapper.params.version_id_col>,

> or the backend dialect supports a “sane” rowcount for an executemany()
> operation;"
>
> ...but I'm not sure that is enough - psycopg2 does support sane
> rowcounts, and its dialect's supports_sane_rowcount and
> supports_sane_multi_rowcount are both rightly True (I work with Tom, and
> we're using psycopg2 v2.6 by the way - I know those values are
> predicated on the psycopg2 version).
>
> I'm working on a patch that adds another member to the default dialect
> called "supports_executemany_results" that defaults to True

I wouldn't do that, no DBAPI has results for executemany. The flag is
not needed.

This is very likely yet another 1.0 regression caused by
http://docs.sqlalchemy.org/en/rel_1_0/changelog/migration_10.html#update-statements-are-now-batched-with-executemany-in-a-flush
and not checking closely enough for the right conditions. I'll add a
new ticket soon.

but is set
> to False in PGDialect_psycopg2 in dialects/postgres/psycopg2.py. This
> can then be used in the determination of allow_multirow (line 646 in
> orm/persistence.py). This will preserve the current behaviour of other
> dialects, but of course there could be others with the same problem.
>
> Steve
>
> On Monday, October 19, 2015 at 4:22:36 PM UTC+1, Tom Flannaghan wrote:
>
> Hi,
>
> We are currently trying to upgrade to sqlalchemy 1.0.8 and have hit
> a problem with executemany() and row versioning. We are using
> postgres and psycopg2 as a backend. We have tables that use server
> side versioning (as discussed at
> http://docs.sqlalchemy.org/en/rel_0_9/orm/versioning.html#server-side-version-counters

> <http://docs.sqlalchemy.org/en/rel_0_9/orm/versioning.html#server-side-version-counters>),

> and when multiple rows are updated, the UPDATE statements are
> batched together using executemany() in the new version of
> sqlalchemy. Unfortunately, psycopg2 does not support iterating over
> the results of an executemany() query (see
> http://initd.org/psycopg/docs/cursor.html#cursor.executemany

> <http://initd.org/psycopg/docs/cursor.html#cursor.executemany>) so

> <http://comm.port.name> = %(comm_port_name)s AND comm.port.xmin =

> --
> 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+...@googlegroups.com
> <mailto:sqlalchemy+...@googlegroups.com>.
> To post to this group, send email to sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Mike Bayer]

issue
https://bitbucket.org/zzzeek/sqlalchemy/issues/3556/update-executemany-enhancement-breaks
which is now resolved. I will try to release 1.0.9 this week; please
confirm the fix in the latest rel_1_0 branch fixes this issue for you as
well, thanks!

[Tom Flannaghan]

Hi Mike,

Your fix worked - the example I gave and our other tests pass now.

Many thanks,

Tom

You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/_ui4mhIUmwM/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.