Three Pitfalls in Scheduled PostgreSQL Jobs

SQL that runs on a schedule rarely gets watched. It wakes up once an hour, aggregates a batch of rows and writes the totals somewhere else. Most of the time the numbers look fine, and when something goes wrong there is often no error at all, just figures that drift. During a design review of one system I was adding tests to an hourly rollup job, and three problems came out of it. None of them is hard on its own, but buried in a file of perfectly ordinary-looking SQL they are easy to miss. The examples below use simplified tables and were run on PostgreSQL 16.

Correction (2 November 2026, 18:45 Taipei): section 2 now gives the cause as there being no guaranteed order between the DELETE inside WITH and the INSERT in the main query; the fix in section 3 now takes the lock once and uses that one result to decide whether both the DELETE and the INSERT run. Suggested by Codex (gpt-6-astra), checked by Claude.

Two tables to start with, one for raw bets and one for hourly totals:

CREATE TABLE bets (
  id         bigserial PRIMARY KEY,
  created_at timestamptz NOT NULL,
  amount     numeric NOT NULL
);

CREATE TABLE rollup_hourly (
  bucket timestamptz PRIMARY KEY,
  n      bigint,
  total  numeric
);

INSERT INTO bets (created_at, amount) VALUES ('2026-09-01 10:30:00+00', 100);

1. Comparing timestamp with timestamptz pulls in the session time zone

Each run has to work out which hour it is aggregating, and a common way to do that is to convert the current time to UTC and date_trunc it. The catch is that timestamptz AT TIME ZONE 'UTC' returns a timestamp without time zone. When you compare that value with a timestamptz column, PostgreSQL casts it back using the session’s TimeZone setting, and the window moves with it.

SET TimeZone = 'Asia/Taipei';

WITH w AS (
  SELECT date_trunc('hour', timestamptz '2026-09-01 10:30:00+00' AT TIME ZONE 'UTC') AS ws
)
SELECT pg_typeof(w.ws) AS ws_type,
       (SELECT count(*) FROM bets
         WHERE created_at >= w.ws
           AND created_at <  w.ws + interval '1 hour') AS hits
FROM w;
           ws_type           | hits
-----------------------------+------
 timestamp without time zone |    0

Run the same query after SET TimeZone = 'UTC' and hits is 1. The job meant to cover 10:00 to 11:00 UTC. In a Taipei session it actually compared against 10:00 to 11:00 Taipei time, eight hours earlier, and the bet fell outside the window. Developer laptops and CI usually run in UTC, so the tests pass. The difference only shows up once a connection defaults to Asia/Taipei, or someone reruns the job by hand from psql.

I have spent a fair amount of time telling engineers that the timestamp in their hands may not be the timestamp they think it is, and that UTC conversion often behaves differently from what they expect. On a UTC host you rarely notice. Move to a host in another zone, or follow the data all the way out to the client, and the problems start to pile up. AT TIME ZONE is a good example. The name suggests a conversion to UTC, yet the value it returns has no zone attached, and how that value is read later depends on whatever the current session is set to.

The fix is to keep the window boundaries as timestamptz the whole way through. The quickest version adds a second AT TIME ZONE 'UTC' to convert back:

WHERE created_at >= w.ws AT TIME ZONE 'UTC'
  AND created_at <  (w.ws + interval '1 hour') AT TIME ZONE 'UTC'

Since PostgreSQL 12, date_trunc also accepts a time zone argument. date_trunc('hour', now(), 'UTC') returns a timestamptz directly, so a zone-less value never appears in the middle, and the query is harder to misread.

2. A DELETE inside a CTE is invisible to the INSERT in the same statement

A rollup should be safe to rerun, and the usual approach is to delete the bucket’s old totals and insert fresh ones. Written as a single statement it looks reasonable:

WITH del AS (
  DELETE FROM rollup_hourly WHERE bucket = '2026-09-01 10:00+00'
)
INSERT INTO rollup_hourly
SELECT date_trunc('hour', created_at), count(*), sum(amount)
FROM bets
GROUP BY 1;

If the bucket already holds a row, it fails:

ERROR:  duplicate key value violates unique constraint "rollup_hourly_pkey"
DETAIL:  Key (bucket)=(2026-09-01 10:00:00+00) already exists.

The PostgreSQL documentation says data-modifying statements in WITH run concurrently with the main query, and the order in which their updates actually happen is unpredictable. The main query never uses the output of del, so when the INSERT checks uniqueness, the old row may not have been deleted yet. The statement is only certain to succeed when the bucket starts out empty, which is exactly the case a rerun does not cover.

Splitting it into two statements inside one transaction fixes it:

BEGIN;
DELETE FROM rollup_hourly WHERE bucket = '2026-09-01 10:00+00';
INSERT INTO rollup_hourly
SELECT date_trunc('hour', created_at), count(*), sum(amount)
FROM bets
GROUP BY 1;
COMMIT;

The second statement sees the result of the first, the whole thing is still atomic, and a failure halfway rolls both back. In that system a worker reads db/jobs/*.sql and runs each file inside a single transaction, so the split needed no extra handling.

3. A lone pg_try_advisory_xact_lock does not stop overlapping runs

Scheduled runs can overlap. The previous run may still be going when the next one starts, or two workers wake up at once. The obvious guard is to grab an advisory lock at the top of the file:

BEGIN;
SELECT pg_try_advisory_xact_lock(42) AS got;
DELETE FROM rollup_hourly WHERE bucket = '2026-09-01 10:00+00';
INSERT INTO rollup_hourly
SELECT date_trunc('hour', created_at), count(*), sum(amount)
FROM bets
GROUP BY 1;
COMMIT;

With another session already holding lock 42, running this gives:

 got
-----
 f

DELETE 1
INSERT 0 1
COMMIT

The lock was not acquired and both statements ran anyway. pg_try_advisory_xact_lock simply returns a boolean. A plain SQL file has no if, nothing reads that false, and it ends up as one more row of query output.

What works is taking the lock once and letting that single result decide, so that without the lock neither the DELETE nor the INSERT runs. Plain SQL has no if, but a DO block does:

BEGIN;

DO $$
BEGIN
  IF NOT pg_try_advisory_xact_lock(42) THEN
    RAISE NOTICE 'lock 42 is held, skipping this run';
    RETURN;
  END IF;

  DELETE FROM rollup_hourly WHERE bucket = '2026-09-01 10:00+00';

  INSERT INTO rollup_hourly
  SELECT date_trunc('hour', created_at), count(*), sum(amount)
  FROM bets
  WHERE created_at >= timestamptz '2026-09-01 10:00+00'
    AND created_at <  timestamptz '2026-09-01 10:00+00' + interval '1 hour'
  GROUP BY 1;
END
$$;

COMMIT;

With the other session still holding the lock:

BEGIN
NOTICE:  lock 42 is held, skipping this run
DO
COMMIT

Neither the DELETE nor the INSERT ran, and the old totals are untouched. When the lock is free, both run and the bucket gets the new totals. Because the lock is taken once, the two statements run together or skip together, and the old totals are never deleted without the new ones going in. Taking the lock separately in each statement does not give that guarantee: if the holder commits between the two, the DELETE misses the lock and the INSERT gets it, and with a row already in the bucket the INSERT hits the primary key and the transaction fails.

The downside is that a skipped run raises no error, only a NOTICE. To find out whether a run actually did anything, you have to check the log or record it separately. If you would rather have overlapping runs queue up behind each other, use the blocking pg_advisory_xact_lock instead. In that case a lock on the first line does work, since it waits until it gets the lock.

One more: a partitioned parent takes no storage parameters

This came up in the same review. Setting fillfactor on the parent of a partitioned table is rejected outright:

CREATE TABLE p (id int, d date) PARTITION BY RANGE (d) WITH (fillfactor = 90);
ERROR:  cannot specify storage parameters for a partitioned table
HINT:  Specify storage parameters for its leaf partitions instead.

fillfactor has to be set on each partition, and you need to remember it whenever a new partition is created, or that partition falls back to the default.

Notes

  • The timestamp in your hands may not be the one you think it is, and UTC conversion often behaves differently from what you expect. You rarely see it on a UTC host, and it bites on hosts in other zones or once the data reaches the client.
  • timestamptz AT TIME ZONE 'UTC' returns a timestamp, and comparing it with a timestamptz applies the session time zone. Keep window boundaries as timestamptz throughout, or use the zone-aware date_trunc available since PostgreSQL 12.
  • A DELETE in a WITH clause has no guaranteed order relative to the main query, so an INSERT in the same statement can hit the unique key before the old row is gone. For a rerunnable job, use two statements in one transaction.
  • A standalone pg_try_advisory_xact_lock just returns a boolean, and plain SQL has no control flow. Take the lock once and branch on it in a DO block, because separate attempts in each statement can return different answers.
  • A run skipped by a try-lock raises no error, so watch for the NOTICE or log it. If you want runs to queue, use the blocking pg_advisory_xact_lock.
  • Storage parameters such as fillfactor can only be set on leaf partitions.

Further reading

Ideas and technical judgement by Sheng; drafted with Claude · examples re-run on PostgreSQL 16.13; the corrected DO block re-run on PostgreSQL 16.15.