On Tue, Dec 12/24/13, 2013 at 11:52:29AM -0500, Michael Bayer wrote: > > On Dec 24, 2013, at 10:48 AM, Chris Withers <ch...@simplistix.co.uk> wrote: > > > Hi All, > > > > I feel like I've asked this before but apologies, I cannot find the > > previous thread. > > > > So, when using the support for psycopg2's range types I added, I sometimes > > see the following during a flush: > > > > File > > "/Users/chris/buildout-eggs/SQLAlchemy-0.8.2-py2.7-macosx-10.5-x86_64.egg/sqlalchemy/orm/session.py", > > line 1818, in flush > > self._flush(objects) > > File > > "/Users/chris/buildout-eggs/SQLAlchemy-0.8.2-py2.7-macosx-10.5-x86_64.egg/sqlalchemy/orm/session.py", > > line 1936, in _flush > > transaction.rollback(_capture_exception=True) > > File > > "/Users/chris/buildout-eggs/SQLAlchemy-0.8.2-py2.7-macosx-10.5-x86_64.egg/sqlalchemy/util/langhelpers.py", > > line 58, in __exit__ > > compat.reraise(exc_type, exc_value, exc_tb) > > File > > "/Users/chris/buildout-eggs/SQLAlchemy-0.8.2-py2.7-macosx-10.5-x86_64.egg/sqlalchemy/orm/session.py", > > line 1900, in _flush > > flush_context.execute() > > File > > "/Users/chris/buildout-eggs/SQLAlchemy-0.8.2-py2.7-macosx-10.5-x86_64.egg/sqlalchemy/orm/unitofwork.py", > > line 372, in execute > > rec.execute(self) > > File > > "/Users/chris/buildout-eggs/SQLAlchemy-0.8.2-py2.7-macosx-10.5-x86_64.egg/sqlalchemy/orm/unitofwork.py", > > line 525, in execute > > uow > > File > > "/Users/chris/buildout-eggs/SQLAlchemy-0.8.2-py2.7-macosx-10.5-x86_64.egg/sqlalchemy/orm/persistence.py", > > line 45, in save_obj > > uowtransaction) > > File > > "/Users/chris/buildout-eggs/SQLAlchemy-0.8.2-py2.7-macosx-10.5-x86_64.egg/sqlalchemy/orm/persistence.py", > > line 140, in _organize_states_for_save > > states): > > File > > "/Users/chris/buildout-eggs/SQLAlchemy-0.8.2-py2.7-macosx-10.5-x86_64.egg/sqlalchemy/orm/persistence.py", > > line 767, in _connections_for_states > > for state in _sort_states(states): > > File > > "/Users/chris/buildout-eggs/SQLAlchemy-0.8.2-py2.7-macosx-10.5-x86_64.egg/sqlalchemy/orm/persistence.py", > > line 792, in _sort_states > > sorted(persistent, key=lambda q: q.key[1]) > > File > > "/Users/chris/buildout-eggs/psycopg2-2.5.1-py2.7-macosx-10.5-x86_64.egg/psycopg2/_range.py", > > line 138, in __lt__ > > 'Range objects cannot be ordered; please refer to the PostgreSQL' > > > > What's going on here? Is it SQLAlchemy, psycopg2 or my code that's at fault? > > well hard to say “bug” or “feature needed”, SQLAlchemy’s UOW wants to emit > UPDATE statements in primary key order so that the chance of deadlocks > against other transactions is minimized. But it appears you’re using a range > type as a primary key and that psycopg2 is not very happy about SQLAlchemy’s > assumption that primary keys can be sorted. > > Workarounds include not using ranges as primary keys, overriding PG’s range > type with some decorated type that is sortable. Potential SQLAlchemy > feature would be, “don’t sort by primary key” flag? Guess so. > >
IMO psycopg2's implementation should be patched, since they basically just didn't implement ordering. PostgreSQL itself has no problem ordering range types (though the ordering is somewhat arbitrary): http://www.postgresql.org/docs/9.2/static/rangetypes.html#RANGETYPES-GIST -Ryan
signature.asc
Description: Digital signature
