Dear Michael,
I have been trying to add row versioning to our existing multi-table
polymorphism setup and I think I may have found an issue that I hope you
can help with.
The following example appears to generate invalid SQL when it tries to
update the base class table, but there are no columns to fetch; it just
wants to get the current version number:
from sqlalchemy import Column, FetchedValue, ForeignKey, Integer, Text,
create_engine
from sqlalchemy.ext.declarative import declarative_base, declared_attr
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm.attributes import InstrumentedAttribute
Base = declarative_base()
class Animal(Base):
__tablename__ = 'animal'
id = Column(Integer, primary_key=True)
name = Column(Text)
species = Column(Text)
_version = Column("xmin", Integer, server_default=FetchedValue(),
server_onupdate=FetchedValue(), system=True)
# _version = Column("xmin", Integer)
@declared_attr
def __mapper_args__(cls):
if isinstance(cls._version, InstrumentedAttribute):
version_col = cls._version.property.columns[0]
else:
version_col = cls._version
return {
'polymorphic_on': cls.species,
'polymorphic_identity': cls.__name__,
'version_id_col': version_col,
'version_id_generator': False,
}
class Dog(Animal):
__tablename__ = 'dog'
id_animal = Column(Integer, ForeignKey(Animal.id), primary_key=True)
toy = Column(Text)
engine = create_engine('postgresql://user@host/database')
engine.echo = True
Base.metadata.create_all(engine)
DBSession = sessionmaker(bind=engine)
session = DBSession(autocommit=True)
with session.begin():
dog = Dog(name="fido")
session.add(dog)
with session.begin():
dog.toy = 'Bone'
Which raises the following exception:
File sqlalchemy_issue_repro_2.py, line 59, in : dog.toy = 'Bone'
<file:\user!wilsoc!nexus!versioning!sqlalchemy_issue_repro_2.py:59:exception>
File sqlalchemy-1.0.9-py2.7-win-amd64.egg\sqlalchemy\orm\session.py, line
490, in __exit__ : self.rollback()
<file:\R:\sw\external\20160915-0-64\python27\lib\site-packages\sqlalchemy-1.0.9-py2.7-win-amd64.egg\sqlalchemy\orm\session.py:490:exception>
File sqlalchemy-1.0.9-py2.7-win-amd64.egg\sqlalchemy\util\langhelpers.py,
line 60, in __exit__ : compat.reraise(exc_type, exc_value, exc_tb)
<file:\R:\sw\external\20160915-0-64\python27\lib\site-packages\sqlalchemy-1.0.9-py2.7-win-amd64.egg\sqlalchemy\util\langhelpers.py:60:exception>
File sqlalchemy-1.0.9-py2.7-win-amd64.egg\sqlalchemy\orm\session.py, line
487, in __exit__ : self.commit()
<file:\R:\sw\external\20160915-0-64\python27\lib\site-packages\sqlalchemy-1.0.9-py2.7-win-amd64.egg\sqlalchemy\orm\session.py:487:exception>
File sqlalchemy-1.0.9-py2.7-win-amd64.egg\sqlalchemy\orm\session.py, line
392, in commit : self._prepare_impl()
<file:\R:\sw\external\20160915-0-64\python27\lib\site-packages\sqlalchemy-1.0.9-py2.7-win-amd64.egg\sqlalchemy\orm\session.py:392:exception>
File sqlalchemy-1.0.9-py2.7-win-amd64.egg\sqlalchemy\orm\session.py, line
372, in _prepare_impl : self.session.flush()
<file:\R:\sw\external\20160915-0-64\python27\lib\site-packages\sqlalchemy-1.0.9-py2.7-win-amd64.egg\sqlalchemy\orm\session.py:372:exception>
File sqlalchemy-1.0.9-py2.7-win-amd64.egg\sqlalchemy\orm\session.py, line
2015, in flush : self._flush(objects)
<file:\R:\sw\external\20160915-0-64\python27\lib\site-packages\sqlalchemy-1.0.9-py2.7-win-amd64.egg\sqlalchemy\orm\session.py:2015:exception>
File sqlalchemy-1.0.9-py2.7-win-amd64.egg\sqlalchemy\orm\session.py, line
2133, in _flush : transaction.rollback(_capture_exception=True)
<file:\R:\sw\external\20160915-0-64\python27\lib\site-packages\sqlalchemy-1.0.9-py2.7-win-amd64.egg\sqlalchemy\orm\session.py:2133:exception>
File sqlalchemy-1.0.9-py2.7-win-amd64.egg\sqlalchemy\util\langhelpers.py,
line 60, in __exit__ : compat.reraise(exc_type, exc_value, exc_tb)
<file:\R:\sw\external\20160915-0-64\python27\lib\site-packages\sqlalchemy-1.0.9-py2.7-win-amd64.egg\sqlalchemy\util\langhelpers.py:60:exception>
File sqlalchemy-1.0.9-py2.7-win-amd64.egg\sqlalchemy\orm\session.py, line
2097, in _flush : flush_context.execute()
<file:\R:\sw\external\20160915-0-64\python27\lib\site-packages\sqlalchemy-1.0.9-py2.7-win-amd64.egg\sqlalchemy\orm\session.py:2097:exception>
File sqlalchemy-1.0.9-py2.7-win-amd64.egg\sqlalchemy\orm\unitofwork.py,
line 373, in execute : rec.execute(self)
<file:\R:\sw\external\20160915-0-64\python27\lib\site-packages\sqlalchemy-1.0.9-py2.7-win-amd64.egg\sqlalchemy\orm\unitofwork.py:373:exception>
File sqlalchemy-1.0.9-py2.7-win-amd64.egg\sqlalchemy\orm\unitofwork.py,
line 532, in execute : uow
<file:\R:\sw\external\20160915-0-64\python27\lib\site-packages\sqlalchemy-1.0.9-py2.7-win-amd64.egg\sqlalchemy\orm\unitofwork.py:532:exception>
File sqlalchemy-1.0.9-py2.7-win-amd64.egg\sqlalchemy\orm\persistence.py,
line 170, in save_obj : mapper, table, update)
<file:\R:\sw\external\20160915-0-64\python27\lib\site-packages\sqlalchemy-1.0.9-py2.7-win-amd64.egg\sqlalchemy\orm\persistence.py:170:exception>
File sqlalchemy-1.0.9-py2.7-win-amd64.egg\sqlalchemy\orm\persistence.py,
line 672, in _emit_update_statements : execute(statement, params)
<file:\R:\sw\external\20160915-0-64\python27\lib\site-packages\sqlalchemy-1.0.9-py2.7-win-amd64.egg\sqlalchemy\orm\persistence.py:672:exception>
File sqlalchemy-1.0.9-py2.7-win-amd64.egg\sqlalchemy\engine\base.py, line
914, in execute : return meth(self, multiparams, params)
<file:\R:\sw\external\20160915-0-64\python27\lib\site-packages\sqlalchemy-1.0.9-py2.7-win-amd64.egg\sqlalchemy\engine\base.py:914:exception>
File sqlalchemy-1.0.9-py2.7-win-amd64.egg\sqlalchemy\sql\elements.py, line
323, in _execute_on_connection : return
connection._execute_clauseelement(self, multiparams, params)
<file:\R:\sw\external\20160915-0-64\python27\lib\site-packages\sqlalchemy-1.0.9-py2.7-win-amd64.egg\sqlalchemy\sql\elements.py:323:exception>
File sqlalchemy-1.0.9-py2.7-win-amd64.egg\sqlalchemy\engine\base.py, line
1010, in _execute_clauseelement : compiled_sql, distilled_params
<file:\R:\sw\external\20160915-0-64\python27\lib\site-packages\sqlalchemy-1.0.9-py2.7-win-amd64.egg\sqlalchemy\engine\base.py:1010:exception>
File sqlalchemy-1.0.9-py2.7-win-amd64.egg\sqlalchemy\engine\base.py, line
1146, in _execute_context : context)
<file:\R:\sw\external\20160915-0-64\python27\lib\site-packages\sqlalchemy-1.0.9-py2.7-win-amd64.egg\sqlalchemy\engine\base.py:1146:exception>
File sqlalchemy-1.0.9-py2.7-win-amd64.egg\sqlalchemy\engine\base.py, line
1341, in _handle_dbapi_exception : exc_info
<file:\R:\sw\external\20160915-0-64\python27\lib\site-packages\sqlalchemy-1.0.9-py2.7-win-amd64.egg\sqlalchemy\engine\base.py:1341:exception>
File sqlalchemy-1.0.9-py2.7-win-amd64.egg\sqlalchemy\util\compat.py, line
199, in raise_from_cause : reraise(type(exception), exception, tb=exc_tb)
<file:\R:\sw\external\20160915-0-64\python27\lib\site-packages\sqlalchemy-1.0.9-py2.7-win-amd64.egg\sqlalchemy\util\compat.py:199:exception>
File sqlalchemy-1.0.9-py2.7-win-amd64.egg\sqlalchemy\engine\base.py, line
1139, in _execute_context : context)
<file:\R:\sw\external\20160915-0-64\python27\lib\site-packages\sqlalchemy-1.0.9-py2.7-win-amd64.egg\sqlalchemy\engine\base.py:1139:exception>
File sqlalchemy-1.0.9-py2.7-win-amd64.egg\sqlalchemy\engine\default.py,
line 450, in do_execute : cursor.execute(statement, parameters)
<file:\R:\sw\external\20160915-0-64\python27\lib\site-packages\sqlalchemy-1.0.9-py2.7-win-amd64.egg\sqlalchemy\engine\default.py:450:exception>
ProgrammingError: (psycopg2.ProgrammingError) syntax error at or near
"WHERE"
LINE 1: UPDATE animal SET WHERE animal.id = 2 AND animal.xmin = '63...
^
[SQL: 'UPDATE animal SET WHERE animal.id = %(animal_id)s AND animal.xmin =
%(animal_xmin)s RETURNING animal.xmin'] [parameters: {'animal_id': 2,
'animal_xmin': '635104190'}]
I think the problem is in sqlalchemy\orm\persistence.py, in
the _collect_update_commands() function. It tries to generate a dict of
columns that need updating, but none of the columns in the base table have
changed, so the dict is empty. Normally it would skip updating this table
here:
elif not (params or value_params):
continue
But because versioning is enabled, it takes this branch instead:
if update_version_id is not None and \
mapper.version_id_col in mapper._cols_by_table[table]:
and then tries to execute a null UPDATE just in order to get the new
version number, which fails.
It would work if we did a dummy update, for example assigning the PK to
itself, as this would change the version number (xmin) returned by the
server. Keeping the version number on the base table allows it to be shared
by all polymorphic subclasses.
Thanks in advance for your consideration,
Chris Wilson.
--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
