The first thing I did after I started having problems was remove the trigger (the above was without the trigger in place).
That being said, it appears that I am having trouble with cx_Oracle and not SQLAlchemy. Using cx_Oracle directly, I can select a row by id, getting the timestamp. Then I try to select the row again, this time also using the timestamp I just retrieved, and get nothing. Thank you for your help. On Thursday, July 19, 2012 11:41:50 PM UTC-4, Michael Bayer wrote: > > you'd have to check cx_oracle's behavior here in conjunction with your > trigger (I'm assuming you're using a trigger here based on your previous > message). SQLAlchemy, as you can see below, runs the UPDATE statement, > adding the current known version to the WHERE criterion. It then checks > the matched row count, which is on the DBAPI as cursor.rowcount, that the > row actually matched, indicating that the row intended to be UPDATED was > located and in fact had the correct version. If your trigger is > interfering with cx_oracle's ability to return the correct rowcount, then > you'd get this issue. So you'd need to distill your test case into a > cx_oracle script that emits the intended UPDATE statement, including the > version criterion, and then confirm that cursor.rowcount is in fact > returning the correct number. The trigger you're doing might be getting in > the way. > > If you aren't using a trigger at all, and this is just default "version" > behavior, that should be working as we do have tests here which we run > against cx_oracle with success. > > > On Jul 19, 2012, at 11:01 PM, Tim wrote: > > I can not get versioning to work in Oracle (it does work for me in sqlite > and Postgresql just changing the connect string). > > I am using timestamp with time zones for the version_id_col. Can anyone > verify that this does work. > > SQLAlchemy==0.7.8 > cx-Oracle==5.1.2 > > Python 2.6.6 (r266:84292, Dec 27 2010, 00:02:40) > [GCC 4.4.5] on linux2 > > >>> import ver.models as m; import transaction > >>> s = m.DBSession() > >>> i = s.query(m.MyModel).first() > 2012-07-19 22:32:54,807 INFO [sqlalchemy.engine.base.Engine][MainThread] > SELECT USER FROM DUAL > 2012-07-19 22:32:54,807 INFO [sqlalchemy.engine.base.Engine][MainThread] > {} > 2012-07-19 22:32:54,818 INFO [sqlalchemy.engine.base.Engine][MainThread] > BEGIN (implicit) > 2012-07-19 22:32:54,819 INFO [sqlalchemy.engine.base.Engine][MainThread] > SELECT models_id, models_name, models_value, models_ins_upd_timestamp > FROM (SELECT models.id AS models_id, models.name AS models_name, > models.value AS models_value, models.ins_upd_timestamp AS > models_ins_upd_timestamp > FROM models) > WHERE ROWNUM <= :ROWNUM_1 > 2012-07-19 22:32:54,819 INFO [sqlalchemy.engine.base.Engine][MainThread] > {'ROWNUM_1': 1} > >>> i.value += 1 > >>> transaction.commit() > 2012-07-19 22:33:18,656 INFO [sqlalchemy.engine.base.Engine][MainThread] > UPDATE models SET value=:value, ins_upd_timestamp=:ins_upd_timestamp WHERE > models.id = :models_id AND models.ins_upd_timestamp = > :models_ins_upd_timestamp > 2012-07-19 22:33:18,656 INFO [sqlalchemy.engine.base.Engine][MainThread] > {'ins_upd_timestamp': datetime.datetime(2012, 7, 19, 22, 33, 18, 655856), > 'models_ins_upd_timestamp': datetime.datetime(2012, 7, 19, 22, 31, 46, > 814740), 'value': 2, 'models_id': 1} > 2012-07-19 22:33:18,659 INFO [sqlalchemy.engine.base.Engine][MainThread] > ROLLBACK > Traceback (most recent call last): > File "<console>", line 1, in <module> > File > "/home/timw/.virtualenvs/sat/lib/python2.6/site-packages/transaction-1.3.0-py2.6.egg/transaction/_manager.py", > > line 107, in commit > return self.get().commit() > File > "/home/timw/.virtualenvs/sat/lib/python2.6/site-packages/transaction-1.3.0-py2.6.egg/transaction/_transaction.py", > > line 354, in commit > reraise(t, v, tb) > File > "/home/timw/.virtualenvs/sat/lib/python2.6/site-packages/transaction-1.3.0-py2.6.egg/transaction/_transaction.py", > > line 345, in commit > self._commitResources() > File > "/home/timw/.virtualenvs/sat/lib/python2.6/site-packages/transaction-1.3.0-py2.6.egg/transaction/_transaction.py", > > line 493, in _commitResources > reraise(t, v, tb) > File > "/home/timw/.virtualenvs/sat/lib/python2.6/site-packages/transaction-1.3.0-py2.6.egg/transaction/_transaction.py", > > line 465, in _commitResources > rm.tpc_begin(self) > File > "/home/timw/.virtualenvs/sat/lib/python2.6/site-packages/zope.sqlalchemy-0.7.1-py2.6.egg/zope/sqlalchemy/datamanager.py", > > line 86, in tpc_begin > self.session.flush() > File > "/home/timw/.virtualenvs/sat/lib/python2.6/site-packages/SQLAlchemy-0.7.8-py2.6-linux-i686.egg/sqlalchemy/orm/session.py", > > line 1583, in flush > self._flush(objects) > File > "/home/timw/.virtualenvs/sat/lib/python2.6/site-packages/SQLAlchemy-0.7.8-py2.6-linux-i686.egg/sqlalchemy/orm/session.py", > > line 1654, in _flush > flush_context.execute() > File > "/home/timw/.virtualenvs/sat/lib/python2.6/site-packages/SQLAlchemy-0.7.8-py2.6-linux-i686.egg/sqlalchemy/orm/unitofwork.py", > > line 331, in execute > rec.execute(self) > File > "/home/timw/.virtualenvs/sat/lib/python2.6/site-packages/SQLAlchemy-0.7.8-py2.6-linux-i686.egg/sqlalchemy/orm/unitofwork.py", > > line 475, in execute > uow > File > "/home/timw/.virtualenvs/sat/lib/python2.6/site-packages/SQLAlchemy-0.7.8-py2.6-linux-i686.egg/sqlalchemy/orm/persistence.py", > > line 59, in save_obj > mapper, table, update) > File > "/home/timw/.virtualenvs/sat/lib/python2.6/site-packages/SQLAlchemy-0.7.8-py2.6-linux-i686.egg/sqlalchemy/orm/persistence.py", > > line 504, in _emit_update_statements > (table.description, len(update), rows)) > StaleDataError: UPDATE statement on table 'models' expected to update 1 > row(s); 0 were matched. > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To view this discussion on the web visit > https://groups.google.com/d/msg/sqlalchemy/-/-_uLzovXdkgJ. > To post to this group, send email to sqlalchemy@googlegroups.com. > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > > > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/nBweg95AeCwJ. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.