SQLAlchemy and PostgreSQL unexpected timestamp with onupdate=func.now()

Question

In the following code after 5 seconds sleep I expect the second part of date_updated to be changed, but only the millisecond part is changed. If I use database_url = 'sqlite:///:memory:' it works as expected. Why?

class Base(MappedAsDataclass, DeclarativeBase):
    pass


class Test(Base):
    __tablename__ = 'test'

    test_id: Mapped[int] = mapped_column(primary_key=True, init=False)
    name: Mapped[str]
    date_created: Mapped[datetime] = mapped_column(
        TIMESTAMP(timezone=True),
        insert_default=func.now(),
        init=False
    )
    date_updated: Mapped[datetime] = mapped_column(
        TIMESTAMP(timezone=True),
        nullable=True,
        insert_default=None,
        onupdate=func.now(),
        init=False
    )


database_url: URL = URL.create(
    drivername='postgresql+psycopg',
    username='my_username',
    password='my_password',
    host='localhost',
    port=5432,
    database='my_db'
)
engine = create_engine(database_url, echo=True)
Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)

with Session(engine) as session:
    test = Test(name='foo')
    session.add(test)
    session.commit()

    print(test)
    
    time.sleep(5)

    test.name = 'bar'    
    session.commit()

    print(test.date_created.time()) # prints: 08:07:45.413737
    print(test.date_updated.time()) # prints: 08:07:45.426483

Answer 1

PostgreSQL's now() function returns the time of the start of the current transaction. In the code in the question, the second transaction is triggered by the print statement, since a new transaction must be opened to retrieve the values expired by the previous commit. Thus the difference in the observed timestamps is only a few milliseconds.

To obtain the desired behaviour, any of these approaches should work:

• Don't expire on commit (but carefully consider the consequences in production code):

  with orm.Session(engine, expire_on_commit=False) as session:
  

• remove the print() call
• Call session.comit() again after the call to time.sleep() to start a new transaction

SQLite doesn't have a now() function; SQLAlchemy converts the function call to

SELECT CURRENT_TIMESTAMP AS now

SQLite does not seem to restrict CURRENT_TIMESTAMP's value to the start of the current transaction:

sqlite> begin;
sqlite> select current_timestamp as 'now';
2024-05-25 13:21:22
sqlite> select current_timestamp as 'now';
2024-05-25 13:21:24
sqlite> select current_timestamp as 'now';
2024-05-25 13:21:26
sqlite> rollback;