All we need is an easy explanation of the problem, so here it is.
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()
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
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
now(), which is the same thing) is
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.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂