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 [email protected].
> To unsubscribe from this group, send email to
> [email protected].
> 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 [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
