Oracle, version_id_col, and timestamps with time zones

[Tim]

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.

[Michael Bayer]

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.

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

[Tim]

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.

To unsubscribe from this group, send email to sqlalchemy+unsubscribe@googlegroups.com.

[Michael Bayer]

On Jul 20, 2012, at 11:19 AM, Tim wrote:

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.

ah - you might want to truncate those microseconds off of your timestamp, they're possibly getting in the way.

To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/nBweg95AeCwJ.

To post to this group, send email to sqlal...@googlegroups.com.

To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.

[Tim]

The microseconds are getting in the way and getting rid of them does work. Unfortunately, the trigger does not get rid of them and there are several applications which write to these tables and depend on the microseconds.

It seems to work in cx_Oracle if I call the setinputsizes and use cx_Oracle.TIMESTAMP as the input size. Do you have some suggestions on how to add this to a custom type, or a decorated type?

[Tim]

I used TypeDecorator and get_dbapi_type to return the type object I wanted.

The parameters get the type cx_Oracle.TIMESTAMP for any filtering I do myself, but the ones used in the version feature seem to ignore this and thus still fail to locate the row.

[Michael Bayer]

the sqlalchemy.types.TIMESTAMP type has dbapi.TIMESTAMP established as the DBAPI type object to use.    When the cx_oracle dialect is in use, the cursor.setinputsizes() method is called, passing in a value for every type that has a "dbapi" type defined with it, with the exception of a handful of string types which I've observed cause cx_oracle to make poorer decisions.

So when you use sqlalchemy.types.TIMESTAMP, setinputsizes() should be called for any statement where the type is known.   This should be the case for the version_id criteria used by the ORM.

If you use a TypeDecorator with get_dbapi_type(), that may or may not work in this particular case - it's very possible that your custom TypeDecorator is reduced to its underlying type object before get_dbapi_type() is called.

To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/tiMo6LLnpQEJ.

To post to this group, send email to sqlal...@googlegroups.com.

To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.

[Tim]

I think I've found what is causing my problems. See http://paste.ofcode.org/38cMYRa7u268EsuUnWQXjfg

Also, I want to thank you for you help. It is very much appreciated.

Tim

[Michael Bayer]

wow, nice job.   I have to write a test for that but that's a definite high priority for the next SQLA release:  http://www.sqlalchemy.org/trac/ticket/2539

To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/HJ3XcaBdb9MJ.

To post to this group, send email to sqlal...@googlegroups.com.

To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.