3

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()
4

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.

7
  • I agree that it is questionable practice. I have poured a lot of thought into it and isolated the "ugly parts" pretty well (only 3 out of ~800 tests). The DB is currently identical to the production system and don't feel comfortable about having 2 differing implementaions of mytimestamp. I would prefer to change the behaviour of NOW in isolation if possible. I have switched to a different test-fixture which uses "true commits" for the tests in question (leaving other tests unmodified) and it works. But making "NOW" behave differently would clean this up a bit.
    – exhuma
    May 19 at 14:42
  • What is the difference? You can hack up PostgreSQL, but in effect that would not be much different. May 19 at 15:11
  • I prefer to keep these kind of obscure hacks in the application code rather than the database. Having a team with mostly developers it makes it easier to debug should anything happen. The team has much more experience in application code than PL/SQL and otehr DB internals. So in the end it's just a decision I took to avoid making team-mates life unnecessarily difficult. If it were the other way around, I'd have put it in the DB.
    – exhuma
    May 19 at 15:21
  • Well, I don't know a different solution, short of modifying PostgreSQL core. May 19 at 16:14
  • 1
    Fair enough. I'll leave this open for a few more days just in case something comes up. If not I'll accept your answer as it is valid.
    – exhuma
    May 19 at 17:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.