Force NOW to return new timestamps without committing the transaction

Question

I'm aware that, in PostgreSQL, the return value of NOW() is the transaction begin timestamp. So if you use NOW() multiple times in the same transaction it will always return the same value.

And that's good in my book.

But I have a small issue with unit-tests in a client application with this and would love to be able to tell PostgreSQL to (temporarily) disable this if at all possible.

The reason I don't want to (can't) use another timestamp function like clock_timestamp() is because the function call to NOW() sits inside of a trigger, and in production code I want the "transaction-start" behaviour.

But in my unit-tests I'm patching the API-level "commit" function so I don't accidentally commit real data to the database during testing (don't worry, I don't use the production DB during testing). So during unit-tests the commit never hits the DB, so I don't get new transaction timestamps.

The database uses temporal tables, and new entries are only appended to the history tables if the timestamp changes to ensure we only consolidate one entry in the history table per transaction.

But when testing the temporal-table behaviour this now causes no entry to ever show up in the history tables. The key fragment of the temporal-table trigger is this:

new_validity_period = tstzrange(
    lower(OLD.validity_period),
    NOW(),
    '[)'
);
IF isempty(new_validity_period) THEN
    RAISE DEBUG 'New entry % will not introduce a new history item', OLD;
    RETURN OLD;
END IF;

So when I do an "insert" operation in my unit-test, and the transaction-time is '2020-01-01 01:02:03', then the validity-period for that entry will be [2020-01-01 01:02:02,). If, still in the same unit-test, I delete the entry (and to test whether it appears in the history table), the operation happens in the same TX, and the code above will read like this:

new_validity_perion = tstzrange(
    '2020-01-01 01:02:03', -- the lower-bound of the 'OLD' row
    '2020-01-01 01:02:03', -- the result of 'NOW()'
    '[)'
)
-- resulting in an empty range because the two timestamps are identical
IF isempty(new_validity_periond) THEN  -- <- Resulting to TRUE
    ...
    RETURN OLD;  -- returning here, not continuing to store the history entry
END IF;
-- code below here is skipped

Is there a way to configure PG to always return the wall-time when calling NOW()? The tests are run against a docker-container so I have full control over the server-config. But it would be even better if I could change the behaviour by setting the config via an SQL command, so I could only change the behaviour for the temporal-table tests.

If that is not possible I would need to use a different database-session/transaction setup for those tests. This is also fine, but I was wondering if I could control this PG behaviour.

Appendix: Unit-Test transaction isolation

This is the code I use to isolate the commit calls in user-code:

@fixture
def rb_session():
    """
    Returns a session which will always be rolled back.
    """

    engine = create_engine(Configurations.getenv("IPBASE_DATABASE_DSN"))

    # Get a *specific* connection from the pool
    connection = engine.connect()
    # Explicitly start a new connection on the connection we got
    # This makes the normal "session.begin()" and "session.commit()"
    # calls in SQLAlchemy no-ops as there is already a transaction
    # in progress.
    transaction = connection.begin()
    # Ensure we use the "primed" connection for all our SQLAlchemy
    # session needs in our unit-tests.
    session = Session(bind=connection)
    try:
        yield session
    finally:
        transaction.rollback()
        session.close()
        connection.close()

Answer 1

I think that that is questionable practice. Any way in which your tests differ from the productive environment increases the danger of testing the wrong thing.

Anyway, you could not use now(), but a different function, say mytimestamp(). On your test system, the function is defined as

CREATE FUNCTION mytimestamp() RETURNS timestamp with time zone
   LANGUAGE sql VOLATILE AS
'SELECT clock_timestamp()';

In the production database, you use

CREATE FUNCTION mytimestamp() RETURNS timestamp with time zone
   LANGUAGE sql VOLATILE AS
'SELECT current_timestamp';

Then you get what you want. The only difference is that current_timestamp (or now(), which is the same thing) is STABLE, not VOLATILE, which may make queries behave differently (or not, if the function is inlined). But that's exactly the kind of thing that I warned you about in the beginning.