On 09/20/2016 09:13 AM, Chris Wilson wrote:
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.
hi Chris -
thanks for the clear test case, this is very helpful.
So you're doing the xmin thing which was a newer use case I got the
library to support once someone asked that it be possible. As you
noticed, there are no values to actually UPDATE. If we hit an attribute
on the base table as well as the child table, then we see two UPDATEs
like we expect:
UPDATE animal SET species=%(species)s WHERE animal.id = %(animal_id)s
AND animal.xmin = %(animal_xmin)s RETURNING animal.xmin
2016-09-20 09:28:06,700 INFO sqlalchemy.engine.base.Engine
{'animal_xmin': '547653', 'animal_id': 1, 'species': 'asdf'}
2016-09-20 09:28:06,701 INFO sqlalchemy.engine.base.Engine UPDATE dog
SET toy=%(toy)s WHERE dog.id_animal = %(dog_id_animal)s
2016-09-20 09:28:06,701 INFO sqlalchemy.engine.base.Engine {'toy':
'Bone', 'dog_id_animal': 1}
the problem is in the absence of any columns on the base table, we still
need to bump "xmin" but there's no values to put into the UPDATE, except
the set of a column to itself which is of course something the ORM never
does normally.
The only thing I could find on google about this, which refers to
setting a value to itself at
http://dba.stackexchange.com/questions/40815/is-there-better-way-to-touch-a-row-than-performing-a-dummy-update,
only concludes that it's better to use a distinct version counter, which
is how I'd do it also. I believe the XMIN thing here someone wanted so
that a particular schema need not be changed.
To add a dummy "id=id", one way is to just intercept UPDATE statements
that have no SET clause and to just add this in (using the
before_execute() event). To do this at the ORM is presenting more of a
challenge, mostly because this column is a primary key column which
doesn't support updating against a SQL expression at this level; issue
https://bitbucket.org/zzzeek/sqlalchemy/issues/3801/cant-use-sql-expression-for-pk-col
is added.
We can set a non-PK column to a SQL expression that is the same column
like this:
with session.begin():
dog.name = Animal.name.__clause_element__()
dog.toy = 'Bone'
Bugs here also include that I need to produce a ClauseElement directly
for persistence to pick up on it, I shouldn't need to call upon
__clause_element__() like that; issue
https://bitbucket.org/zzzeek/sqlalchemy/issues/3802/sql-expression-in-orm-update-only-look-for
is added.
That's the most immediate workaround. We can put that into an event:
from sqlalchemy import event
@event.listens_for(Dog, "before_update")
def upd(mapper, connection, obj):
# TODO: check for other columns already changed, check that "name"
# not already modified
obj.name = Animal.name.__clause_element__()
with session.begin():
dog.toy = 'Bone'
Above would be your solution for now (short of "use a normal version
column" which I would recommend :) ). I don't know that I want the ORM
to do this automatically just yet. I would prefer to just add more
docs to the "server versioned column" examples noting that you need to
do something like this for joined inheritance (once the bugs are fixed).
Note the point of setting to a SQL expression is to force the ORM to
actually include the column; if we set it to itself, it sees no net
change and it wouldn't work, unless you used a special value that didn't
naturally compare against itself.
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
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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.
