exception with updates when using onupdate=func.current_timestamp() with Postgres

[Former user]

I'm getting this exception when I update an object that has a column that uses onupdate=func.current_timestamp():

Traceback (most recent call last):
  File "./bug.py", line 40, in ?
    db_session.flush()
  File "build/bdist.darwin-8.7.0-Power_Macintosh/egg/sqlalchemy/orm/session.py", line 234, in flush
  File "build/bdist.darwin-8.7.0-Power_Macintosh/egg/sqlalchemy/orm/unitofwork.py", line 197, in flush
  File "build/bdist.darwin-8.7.0-Power_Macintosh/egg/sqlalchemy/orm/unitofwork.py", line 367, in execute
  File "build/bdist.darwin-8.7.0-Power_Macintosh/egg/sqlalchemy/orm/unitofwork.py", line 635, in execute
  File "build/bdist.darwin-8.7.0-Power_Macintosh/egg/sqlalchemy/orm/unitofwork.py", line 589, in _save_objects
  File "build/bdist.darwin-8.7.0-Power_Macintosh/egg/sqlalchemy/orm/mapper.py", line 818, in save_obj
  File "build/bdist.darwin-8.7.0-Power_Macintosh/egg/sqlalchemy/orm/mapper.py", line 876, in _postfetch
TypeError: can't compare offset-naive and offset-aware datetimes

This occurs after the object is refreshed from the database and then changed. Here's my test case:

#!/usr/local/bin/python

from sqlalchemy import *

engine = create_engine("postgres://user:password@127.0.0.1/test")
#engine = create_engine("sqlite://")                                                                                                                       
metadata = BoundMetaData(engine)

test_table = Table("test", metadata,
                   Column("oid", Integer, primary_key=True),
                   Column("date_record_added", DateTime(), default=func.current_timestamp(), nullable=False),
                   Column("date_last_modified", DateTime(), onupdate=func.current_timestamp()),
                   Column("name", String(20)),
                   )

class Test(object):
    pass

mapper(Test, test_table)

#engine.echo = True                                                                                                                                        
metadata.create_all()

db_session = create_session()

t1 = Test()
t1.name = "one"
db_session.save(t1)
db_session.flush()

t1.name = "two"
db_session.flush()

db_session.refresh(t1)

# The exception is raised with this flush.                                                                                                                
t1.name = "three"
db_session.flush()

db_session.close()

I'm using SQLAlchemy 0.2.7, Python 2.4.3, pyscopg 2.0.4 and Postgres 8.1.3. I also tried it with sqlite but that worked.

[Former user]

This is happening because PostgreSQL interprets the data type "TIMESTAMP" as "TIMESTAMP WITHOUT TIMEZONE" by default now. There's a note in http://www.postgresql.org/docs/8.1/interactive/datatype-datetime.html:

Note:  Prior to PostgreSQL 7.3, writing just timestamp was equivalent to timestamp with time zone. This was changed for SQL compliance.

When I ran the above test case with echo=True, I saw the following SQL going to the database:

CREATE TABLE test (
        oid SERIAL NOT NULL,
        date_record_added TIMESTAMP NOT NULL,
        date_last_modified TIMESTAMP,
        name VARCHAR(20),
        PRIMARY KEY (oid)
)

Then SQLAlchemy sends datetime objects with timezone information to PostgreSQL:

[17:50:57,325](2006-08-23) [engine](engine): INSERT INTO test (oid, date_record_added, date_last_modified, name) VALUES (%(oid)s, %(date_record_added)s, %(date_last_modified)s, %(name)s)
[17:50:57,325](2006-08-23) [engine](engine): {'oid': 1L, 'date_last_modified': None, 'date_record_added': datetime.datetime(2006, 8, 23, 17, 50, 57, 301129, tzinfo=<psycopg2.tz.FixedOffsetTimezone object at 0xb799756c>), 'name': 'one'}

Psycopg's copious debugging output shows it sending the following across the wire to PostgreSQL:

[18629](18629) pq_execute: executing SYNC query:
[18629](18629)     INSERT INTO test (oid, date_record_added, date_last_modified, name) VALUES (1, '2006-08-23T17:50:57.301129+01:00', NULL, 'one')

But PostgreSQL, having dutifully complied with SQL standards by interpreting "TIMESTAMP" as "TIMESTAMP WITHOUT TIMEZONE", throws the timezone information away. Then the db_session.refresh() causes a SELECT to happen:

[17:50:57,371](2006-08-23) [engine](engine): SELECT test.oid AS test_oid, test.date_last_modified AS test_date_last_modified, test.date_record_added AS test_date_record_added, test.name AS test_name
FROM test
WHERE test.oid = %(test_oid)s ORDER BY test.oid
[17:50:57,371](2006-08-23) [engine](engine): {'test_oid': 1L}

Which at the psycopg level looks like this:

[18629](18629) pq_execute: executing SYNC query:
[18629](18629)     SELECT test.oid AS test_oid, test.date_last_modified AS test_date_last_modified, test.date_record_added AS test_date_record_added, test.name AS test_name
FROM test
WHERE test.oid = 1 ORDER BY test.o
...
[18629](18629) typecast_PYDATETIME_cast: s = 2006-08-23 17:50:57.301129

As you can see, the datetime sent back from PostgreSQL has no timezone information attached anymore.

I haven't tracked down precisely where in SQLAlchemy it ends up trying to compare the two datetime objects (one with timezone info attached, one without), because I think this is enough to solve the bug. If {{{lib/sqlalchemy/databases/postgres.py}}} is changed to use "TIMESTAMP WITH TIMEZONE" for Python datetime objects, this bug should go away.

I don't know what that would do to other uses, though; are there any circumstances where timestamps ''without'' timezones are needed?

[Mike Bayer]

after reading the docs for datetime, id say a timestamp without a timezone is actually the norm, since there is no tzinfo class even included with python.

so for this, I added a timestamp=True flag to the DateTime and Time types, so that this indicator can be specified, and importantly can be used in CAST calls which are sort of essential now with postgres. Postgres in turn will add the WITH TIME ZONE or WITHOUT TIME ZONE clause to all time/timestamp types.

So the two ways to work the test program, which I also created into a testcase in testtypes.py, are these:

With time zone:

test_table = Table("test", metadata,
                   Column("id", Integer, primary_key=True),
                   Column("date_record_added", DateTime(timezone=True), default=func.current_timestamp(), nullable=False),
                   Column("date_last_modified", DateTime(timezone=True), onupdate=func.current_timestamp()),
                   Column("name", String(20)),
                   )

without time zone:

test_table = Table("test", metadata,
                   Column("id", Integer, primary_key=True),
                   Column("date_record_added", DateTime(timezone=False), default=cast(func.current_timestamp(), DateTime(timezone=False)), nullable=False),
                   Column("date_last_modified", DateTime(timezone=False), onupdate=cast(func.current_timestamp(), DateTime(timezone=False))),
                   Column("name", String(20)),
                   )

changes are in changeset:1824