TypeError when flushing the session

[Former user]

When flushing the session, SQLAlchemy tries to find out which attributes have been modified by comparing them to their original value. It seems to rely on the fact that '==' never fails in Python. Unfortunately (1) it sometimes does, as is the case when comparing an "offset-aware" and an "offset-naive" datetime. Unfortunately (2), not all databases support offset-aware datetimes and so it means you can't write portable code between e.g. PostgreSQL and MySQL (MySQL will return you offset-naive datetimes even if you set offset-aware datetimes in the first place).

A nice fix would be to catch TypeErrors raised by '==' and interpret them as meaning the attribute value has changed and has to be updated.

File '/usr/lib/python2.6/site-packages/SQLAlchemy-0.5.4p2-py2.6.egg/sqlalchemy/orm/scoping.py', line 121 in do
  return getattr(self.registry(), name)(*args, **kwargs)
File '/usr/lib/python2.6/site-packages/SQLAlchemy-0.5.4p2-py2.6.egg/sqlalchemy/orm/session.py', line 1354 in flush
  self._flush(objects)
File '/usr/lib/python2.6/site-packages/SQLAlchemy-0.5.4p2-py2.6.egg/sqlalchemy/orm/session.py', line 1432 in _flush
  flush_context.execute()
File '/usr/lib/python2.6/site-packages/SQLAlchemy-0.5.4p2-py2.6.egg/sqlalchemy/orm/unitofwork.py', line 257 in execute
  UOWExecutor().execute(self, tasks)
File '/usr/lib/python2.6/site-packages/SQLAlchemy-0.5.4p2-py2.6.egg/sqlalchemy/orm/unitofwork.py', line 720 in execute
  self.execute_save_steps(trans, task)
File '/usr/lib/python2.6/site-packages/SQLAlchemy-0.5.4p2-py2.6.egg/sqlalchemy/orm/unitofwork.py', line 735 in execute_save_steps
  self.save_objects(trans, task)
File '/usr/lib/python2.6/site-packages/SQLAlchemy-0.5.4p2-py2.6.egg/sqlalchemy/orm/unitofwork.py', line 726 in save_objects
  task.mapper._save_obj(task.polymorphic_tosave_objects, trans)
File '/usr/lib/python2.6/site-packages/SQLAlchemy-0.5.4p2-py2.6.egg/sqlalchemy/orm/mapper.py', line 1340 in _save_obj
  history = attributes.get_state_history(state, prop.key, passive=True)
File '/usr/lib/python2.6/site-packages/SQLAlchemy-0.5.4p2-py2.6.egg/sqlalchemy/orm/attributes.py', line 1299 in get_state_history
  return state.get_history(key, **kwargs)
File '/usr/lib/python2.6/site-packages/SQLAlchemy-0.5.4p2-py2.6.egg/sqlalchemy/orm/state.py', line 88 in get_history
  return self.manager.get_impl(key).get_history(self, self.dict, **kwargs)
File '/usr/lib/python2.6/site-packages/SQLAlchemy-0.5.4p2-py2.6.egg/sqlalchemy/orm/attributes.py', line 437 in get_history
  self, state, dict_.get(self.key, NO_VALUE))
File '/usr/lib/python2.6/site-packages/SQLAlchemy-0.5.4p2-py2.6.egg/sqlalchemy/orm/attributes.py', line 1271 in from_attribute
  attribute.is_equal(current, original) is True):
File '/usr/lib/python2.6/site-packages/SQLAlchemy-0.5.4p2-py2.6.egg/sqlalchemy/types.py', line 54 in compare_values
  return x == y
TypeError: can't compare offset-naive and offset-aware datetimes

[Former user]

Ouch, I forgot to add myself to the cc field. My e-mail is pitrou --at-- free.fr.

[Mike Bayer]

you can write portable code by using only timezone-naive datatypes. If you are using "TIMESTAMP WITH TIMEZONE" in your application with postgres, then yes your app is not portable to a DB that does not offer such a type, no different than if you used PG's INET type.

The failure of the == operator is helpful here to indicate that the application is being asked to do something whose behavior is undefined (i.e., what timezone should be silently applied to the naive timestamp when performing the comparison of a naive/un-naive type ? alternatively how do we justify silently "whacking" the timezone of one of the types?). Since it is very likely that the two datetimes to be compared are identical once the timezones are reconciled.

If you need auto-coercion of types to be timezone aware or naive upon parameter binding or result retrieval, use a TypeDecorator.

[Former user]

I think the original suggestion is still justified.

zzzeek asks "what timezone should be silently applied?" The answer is none. The new value should be simply determined to be different (and hence dirty) and the update should occur.

Claiming the two datetimes are likely to be identical (or more accurately, equivalent) seems to be missing the point to me. They might be describing an equivalent point in real time, but their timezone is different, and that change should be preserved.

The TypeDecorator remains a work-around though.

[Mike Bayer]

Assuming this is the suggestion claimed as "justified":

"A nice fix would be to catch TypeErrors? raised by '==' and interpret them as meaning the attribute value has changed and has to be updated. "

The suggestion is basically that we reverse Python's own decision to determine this condition to be ambiguous and warranting an error - instead asking that we make an assumption, that the objects are simply not equivalent.

I'd point out that a the developer of an application which is actually erroneously mixing offset aware/naive types, and then fails silently in this regard, creating unneeded UPDATE statements as well as events which may have many more side effects (like creating version Ids, version rows, triggers, etc), would be extremely surprised to find that we've vetoed Python's own behavior here - it violates a core tenet of SQLAlchemy which is that we don't redefine or wrap underlying behaviors and conditions where not necessary (like testing for constraint violations in memory instead of just letting the database do so). It leads to confusing and less predictable behavior, more code, and poorer performance.

Since we don't want to reverse Python's decision, I'd suggest bringing it up on the Python bugtracker. If SQLAlchemy should consider offset-aware and offset-naive datetimes to simply be "not equal", why shouldn't Python ?

Please continue any SQLAlchemy-specific discussion of this on the development list at http://groups.google.com/group/sqlalchemy-devel . The ticket should remain closed until a new decision is agreed upon.