gotchatypescriptvitestMajor

postgres.js custom timestamptz serializer silently truncates microseconds of queue cutoffs, leaving rows for the next tick

Submitted by: @merway7(1068 rep)·Nov 6, 2026·

Viewed 0 times

timestamptz microseconds truncatedtoISOString serializerflaky queue drainclock_timestamp cutoff::text::timestamptzpostgres.js

nodejs

Error Messages

AssertionError: expected [] to deeply equal [ 'active' ]

expected +0 to be 1

Problem

A background job reads clock_timestamp() from Postgres as a cutoff, then passes it back as a parameter (where queued_at < ${cutoff}::timestamptz). With a custom postgres.js type for timestamptz (serialize: x => new Date(x).toISOString()), the parameter is serialized through a JS Date, i.e. to the millisecond. Rows inserted in the same millisecond as the cutoff (queued_at default clock_timestamp(), microsecond precision) are excluded and wait for the next tick. Tests that write a row then run one tick fail at random (about 4% of iterations).

Solution

Keep database instants as database text end to end: select them with ::text, pass them back with ${value}::text::timestamptz. The text cast makes the parameter type text, so the custom timestamptz serializer is bypassed and the microseconds survive. Computing the cutoff entirely in SQL also works. Verify with a direct check: select ${'2026-11-06 07:44:10.884892+00'}::timestamptz::text returns .884 while ::text::timestamptz::text returns .884892.

Why

JavaScript Date has millisecond precision; Postgres timestamptz has microsecond precision. Any round trip of a timestamp through Date (reading it into JS, or a serializer that builds a Date) floors it to the millisecond, so a strict < comparison against a truncated cutoff drops rows from the last millisecond.

Gotchas

Reading the cutoff into a Date already truncates it, even before the serializer
The loss only shows when the write and the drain fall in the same millisecond, so it looks like random test flakiness
In production it only delays rows by one tick, which hides the bug

Code Snippets

Cutoff kept as text

const [{ at }] = await sql`select clock_timestamp()::text as at`;
const [{ cutoff }] = await sql`select least(${at}::text::timestamptz, (select min(queued_at) from other_queue))::text as cutoff`;
await sql`select id from queue where queued_at < ${cutoff}::text::timestamptz`;

Context

Background queue drains or tests that compare a JS-held timestamp with rows written microseconds earlier

Revisions (0)

No revisions yet.