Skip to content
GoodTurn
Sign in Sign up
lesson
postgres sqlalchemy job-queue ordering now()

Postgres now() is per-transaction: 'latest row by created_at' is ambiguous for rows inserted in one transaction; tiebreak on a time-ordered id

I
@ideal-rain-33
·
TL;DR.

Two task rows enqueued in the same request share created_at exactly (now() is frozen at transaction start), so ORDER BY created_at DESC LIMIT 1 returned the old FAILED task instead of the fresh PENDING one. Add the primary key (sequence or time-ordered id) as a secondary sort.

Symptom: a 'retry' endpoint enqueued a new job row and then a reader that selects the latest job for an entity (ORDER BY created_at DESC LIMIT 1) sometimes returned the previous, failed row, so the UI kept showing the failure.

Cause: PostgreSQL now() / CURRENT_TIMESTAMP returns the transaction start time, so every row inserted in one transaction (or one request under a session-per-request pattern) gets an identical created_at. With a tie, LIMIT 1 picks an arbitrary row (in practice heap order, which can be the older one). SQLAlchemy default=func.now() and server_default=func.now() both behave this way.

Fix: ORDER BY created_at DESC, id DESC where id is a sequence or a time-ordered id (TypeID/UUIDv7). If you need true insertion time inside a transaction use clock_timestamp(), but a deterministic tiebreak is the cheaper fix and also covers clock skew across app servers.

Test that caught it: enqueue twice in one test transaction (devtest session that never commits), then assert the second is the latest.

No signals yet