How soon is now in PostgreSQL? - Event-Driven.io
How soon is now? In PostgreSQL, it’s not always as soon as you’d think. I learned that the hard way recently, so you don’t have to.
It took me hours and wasn’t easy to reproduce, even though the fix is one line. I found it in a Cybertec post, as I quite often do when I’m staring at something odd in PostgreSQL. I’m supposed to know my way around the database, but I missed it, which is another reason I want to write this down.
I was working on distributed locking in Emmett. When you scale a service horizontally, you can easily end up with two instances of the same message processor running at once. That’s bad. Both instances would pull the same events, both would write to the same projection storage, and we’d get duplicated side effects, overwritten state and broken checkpoints. So we need to guarantee that exactly one instance of each processor is active at any time. Emmett does that using two things working hand in glove: PostgreSQL advisory locks and a row in the emt_processors table. The row keeps the durable side of ownership: which instance currently holds the processor (processor_instance_id), when it last checked in (last_updated), and what state it’s in (status). I described the full design in Rebuilding Event-Driven Read Models in a safe and resilient way, so I won’t bore you with the whole picture here.
For this story, the part that matters is what happens when an instance crashes. The crashed processor’s connection is gone, so its advisory lock has already been released. A new instance can grab the advisory lock without resistance. But the row in emt_processors still says status = 'running' and still points to the previous owner, because the crash didn’t give anyone a chance to clean it up.
From the outside, we can’t tell whether the previous owner has crashed or is just between heartbeats. So we wait. If the row’s last_updated is older than a configurable timeout, the new instance is allowed to claim ownership anyway. Anyone quiet for that long is treated as gone. To make this graceful, the lock acquisition runs inside a retry policy. A fresh instance starting just after a crash doesn’t fail straight away; it retries until the timeout window expires.
The bug
The takeover decision lives in the upsert against emt_processors. In the real function, that upsert sits inside a Common Table Expression (CTE) alongside a pg_try_advisory_xact_lock call.
For the record: the snippets below skip that wrapping (and trim a couple of unused parameters) to keep the focus on the upsert, where the bug lives. The full version is in the source.
CREATE OR REPLACE FUNCTION emt_try_acquire_processor_lock(<br>p_processor_id TEXT,<br>p_processor_instance_id TEXT,<br>p_lock_timeout_seconds INT<br>RETURNS BOOLEAN<br>LANGUAGE plpgsql<br>AS $$<br>BEGIN<br>INSERT INTO emt_processors (processor_id, processor_instance_id, status, last_updated)<br>VALUES (p_processor_id, p_processor_instance_id, 'running', now())<br>ON CONFLICT (processor_id) DO UPDATE<br>SET processor_instance_id = p_processor_instance_id,<br>status = 'running',<br>last_updated = now()<br>WHERE<br>-- same instance reconnecting<br>emt_processors.processor_instance_id = p_processor_instance_id<br>-- previous owner stopped cleanly<br>OR emt_processors.status = 'stopped'<br>-- previous owner timed out<br>OR emt_processors.last_updated<br>now() - (p_lock_timeout_seconds || ' seconds')::interval;<br>RETURN FOUND;<br>END;<br>$$;
The last branch is the takeover. It reads naturally: if the previous owner hasn’t checked in for longer than the timeout, the new instance can replace them. All tests were green. Stop me if you think you’ve heard this one before. The problem surfaced through user feedback (thanks, Martin!), and it took me a long time to reproduce; none of the existing tests covered the scenario that triggered it. Once I had a new end-to-end test that pinpointed the symptom, the rest was the usual, long, boring, debugging loop.
To see why, open psql and run this:
BEGIN;<br>SELECT now() AS tx_now, clock_timestamp() AS wall_clock;<br>SELECT pg_sleep(2);<br>SELECT now() AS tx_now, clock_timestamp() AS wall_clock;<br>COMMIT;
You’ll get something like:
tx_now | wall_clock<br>-------------------------------+-------------------------------<br>2026-05-25 10:00:00.123456+00 | 2026-05-25 10:00:00.124012+00
tx_now | wall_clock<br>-------------------------------+-------------------------------<br>2026-05-25 10:00:00.123456+00 | 2026-05-25 10:00:02.131845+00
The first column is the same in both rows. The second one is two seconds apart. As it turns out, now() is a synonym for transaction_timestamp(): it returns the time the transaction began, and keeps returning that value for every statement inside the same transaction. A light that never goes out, in other words. clock_timestamp() reads the wall clock each time it’s called, so it advances as time does. Cybertec wrote a good walkthrough of the whole family of timestamp functions if you want the full picture.
What difference does it make? For a column...