# Three Pitfalls in Scheduled PostgreSQL Jobs

> Three pitfalls in scheduled PostgreSQL SQL: session time zone shifts the window, a CTE DELETE is invisible to its INSERT, and a lone advisory lock lets runs overlap.

Source: https://sheng.page/en/posts/postgres-scheduled-sql-pitfalls/ · Published: 2026-10-01 · Author: Sheng

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.

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

```sql
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.

```sql
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;
```

```text
           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:

```sql
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:

```sql
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:

```text
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 is explicit about this. Data-modifying statements in `WITH` run concurrently with the main query against the same snapshot, so none of them can see what the others did to the tables. The row that `del` removes is still there as far as the `INSERT`'s uniqueness check is concerned. The statement only succeeds 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:

```sql
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:

```sql
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:

```text
 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 moving the lock attempt into each statement's own condition, so a statement that fails to get the lock does nothing:

```sql
BEGIN;

WITH scope AS (
  SELECT timestamptz '2026-09-01 10:00+00' AS ws
  WHERE pg_try_advisory_xact_lock(42)
)
DELETE FROM rollup_hourly r
USING scope
WHERE r.bucket = scope.ws;

WITH scope AS (
  SELECT timestamptz '2026-09-01 10:00+00' AS ws
  WHERE pg_try_advisory_xact_lock(42)
)
INSERT INTO rollup_hourly
SELECT scope.ws, count(*), sum(amount)
FROM bets, scope
WHERE created_at >= scope.ws
  AND created_at <  scope.ws + interval '1 hour'
GROUP BY scope.ws;

COMMIT;
```

With the other session still holding the lock, the result is `DELETE 0` and `INSERT 0 0`, and no data is touched. Asking for the lock twice is fine, because re-acquiring an advisory lock you already hold in the same transaction returns `true`. Both statements get the same answer, so they run together or skip together, and the old totals are never deleted without the new ones going in.

The downside is that a skipped run raises no error. To find out whether a run actually did anything, you have to check the affected row counts 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:

```sql
CREATE TABLE p (id int, d date) PARTITION BY RANGE (d) WITH (fillfactor = 90);
```

```text
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 shares a snapshot with the main query, so an `INSERT` in the same statement cannot see the deletion. 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. Put it in each statement's `WHERE`. Re-acquiring it in the same transaction returns `true`, so the statements stay consistent.
- A run skipped by a try-lock raises no error, so check row counts 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

- [Postgres AT TIME ZONE 'UTC' does NOT do what you think it does](https://bookofrevenue.com/blog/6ab81e9a97a13f0001f7e4e1/postgres-at-time-zone-u-does-not-do-what-you-think-it-does): a short post on the same trap as section 1, where `AT TIME ZONE 'UTC'` turns a `timestamptz` into a `timestamp`.
- PostgreSQL docs, [Time Zone Conversion](https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT) and [date_trunc](https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC): what `AT TIME ZONE` returns for each input type, and the `time_zone` argument to `date_trunc`.
- PostgreSQL docs, [Data-Modifying Statements in WITH](https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-MODIFYING): the passage on sub-statements sharing one snapshot and not seeing each other's changes.
- PostgreSQL docs, [Advisory Lock Functions](https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADVISORY-LOCKS): how `pg_try_advisory_xact_lock` differs from the blocking version.
- PostgreSQL docs, [CREATE TABLE: Storage Parameters](https://www.postgresql.org/docs/current/sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS): storage parameters are not supported on partitioned tables, only on leaf partitions.

> Ideas and technical judgement by Sheng; drafted with Claude · examples re-run on PostgreSQL 16.13.