Force NOW to return new timestamps without committing the transaction

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', --

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.

StackExchange Database Administrators Q#291855, answer score: 4