Mocking now() in PostgreSQL

[AMDmi3]

TL;DR: Use this as a fixture to make PostgreSQL now() return fixed timestamp for your tests:

CREATE SCHEMA mock_now;

CREATE OR REPLACE PROCEDURE mock_now.enable() AS $$
DECLARE
    query TEXT;
BEGIN
    query := 'ALTER DATABASE ' || quote_ident(current_database()) || ' SET search_path TO mock_now,pg_catalog,"$user",public';
    SET search_path TO mock_now,pg_catalog,"$user",public;
    EXECUTE query;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION mock_now.now() RETURNS timestamptz AS $$
BEGIN
    return '2024-01-01 00:00:00+00'::timestamptz;
END;
$$ LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE;

CALL mock_now.enable();

Sharing because for me coming to this was not trivial:

• So I started with CREATE SCHEMA + CREATE FUNCTION mock_now.now() + SET search_path in a fixture
• Tests don't see it, probably because they use different connection that the one which applies fixtures.
• I move SET search_path into test. One line less convenience, but still acceptable.
• This works randomly, turns out that despite the docs say that the test pool only opens one connection, there are multiple connections (and pool.options().get_max_connections() says 5), so SET only affects one of these
• I discover that you can set search_path for the database, but database name is volatile
• I discover that you can query the database name, construct the alter table query and execute it
• Voila.

Notes:

• In theory, this is still not reliable as despite is sets search_path for both the database and session, there may be connections opened even before the fixture is applied, and these may be reused by the test. But it seems to work now, even if applied after migrations and other fixtures.
• pg_catalog in search_path is important, as otherwise it's implicitly prepended to the path, and builtin now() still has higher precedence. Had to learn this the hard way too.