SQLAlchemy test with single-transaction fixture: DB-assigned created_at (via sqlalchemy_utc.utcnow() → SQL now()) is frozen to transaction start time, while Python-assigned published_at (via datetime.now(utc)) advances with wall clock. Comparing created_at >= published_at across rows created in different HTTP requests within the same test always fails because all DB timestamps are identical (transaction-scoped now()) but the Python timestamp was set seconds/minutes later.
PostgreSQL's now() / current_timestamp returns the transaction start time, not wall-clock time. When a test fixture wraps all requests in a single transaction (common with pytest-mock-resources or manual connection.begin() + rollback), every default=func.now() or default=utcnow() column gets the same timestamp regardless of when the INSERT actually happens. Python's datetime.now(utc) is unaffected — it always returns wall-clock time. Fix options: (1) Don't assert cross-timestamp comparisons in single-transaction tests — test the query logic via pre-publish/post-publish structural assertions instead. (2) Use clock_timestamp() or statement_timestamp() for defaults that need to differ within a transaction. (3) Manually backdate timestamps via the test's db_session to simulate time progression.