`_private_tasks` identity consumed on every `add_job` — `integer` sequence exhausts at 2^31 and the worker cannot start

[perebusquets]

Summary

_private_tasks.id is integer generated always as identity, but every add_job / add_jobs call runs:

insert into graphile_worker._private_tasks as tasks (identifier)
select distinct spec.identifier from unnest(specs) spec
on conflict do nothing;

PostgreSQL evaluates the identity column's nextval() before it detects the unique conflict, so a sequence value is consumed on every enqueue even when no row is inserted. The table only ever holds one row per distinct task identifier, so the sequence advances with total job volume while the table stays tiny.

At ~2.1bn cumulative enqueues the sequence hits the integer ceiling and the worker can no longer start at all — getTaskDetails() (src/taskIdentifiers.ts) issues the same on conflict do nothing insert to register supported task names during startup, so it throws before the worker comes up.

We hit this in production: a table with 38 rows / 80 kB exhausted a 2,147,483,647-value sequence.

Interestingly _private_jobs.id is correctly bigint — it's only the tasks (and job_queues) identity that is integer, presumably because those tables were expected to stay small, which is true of their row count but not of their sequence consumption.

Steps to reproduce

Against any database with a current worker schema installed:

-- 1. register a task normally
select graphile_worker.add_job('my_task');

-- 2. fast-forward the sequence to the integer ceiling
--    (equivalent to ~2.1bn prior enqueues)
select setval(
  pg_get_serial_sequence('graphile_worker._private_tasks', 'id'),
  2147483647,
  true
);

-- 3. enqueue the SAME, already-registered task.
--    No row will be inserted — the identifier already exists.
select graphile_worker.add_job('my_task');

Step 3 fails. Note the table still contains exactly one row.

Starting a worker against this database fails the same way, via getTaskDetails().

Expected results

add_job continues to work, and the worker starts. Enqueuing an already-registered task identifier should not consume identity values from a table whose row count is bounded by the number of distinct task names.

Actual results

ERROR:  nextval: reached maximum value of sequence "tasks_id_seq" (2147483647)

In production this presented as the worker crash-looping at startup (exit status 1) with that error, while the web tier stayed up but failed every enqueue. Recovery requires manual DBA intervention; there is no documented remedy.

Additional context

• graphile-worker: 0.16.6 — but the same pattern is present on main today: __tests__/schema.sql still declares _private_tasks.id as integer, and both on conflict do nothing inserts in sql/000018.sql (add_job's unsafe_dedupe branch and add_jobs) are unchanged.
• PostgreSQL: Aurora PostgreSQL 16.8 (the minimal repro above also reproduces on stock PostgreSQL 16.14, so this is not Aurora-specific)
• Node: 22.21.1

Production state at failure:

_private_tasks rows	38
_private_tasks size	80 kB
min(id) / max(id)	1 / 2,146,812,413
tasks_id_seq.last_value	2,147,483,647 (100%)
jobs_id_seq.last_value	2,147,412,688 (bigint — unaffected)
job_queues_id_seq.last_value	19,741,869 (0.9%)

The near-identical values for tasks_id_seq and the jobs sequence are what confirm the mechanism: both advance roughly once per enqueue, but one is bigint and survived while the other is integer and did not.

_private_job_queues.id has the same shape (integer identity, populated via on conflict do nothing in the same functions) and so has the same eventual failure mode — it is merely further away for us because only ~1% of our jobs specify a queue name.

There is also no way to see this coming: nothing warns as the sequence fills, and because the table is tiny nothing about it looks like a table approaching a limit.

Workaround we used, in case it helps others who hit this before a fix lands — reclaim the negative half of the integer range, which needs no table rewrite and keeps every id inside integer (important, since several queries cast these ids to ::int[]):

ALTER SEQUENCE graphile_worker.tasks_id_seq
  MINVALUE -2147483648 RESTART WITH -2147483648;

(Note the sequence keeps its pre-000016 name — ALTER TABLE ... RENAME does not rename the owned sequence — so resolve it with pg_get_serial_sequence rather than assuming _private_tasks_id_seq.)

Possible Solution

A few directions, with the trade-off we ran into noted:

1. Stop consuming the sequence on the conflict path. Something like a where not exists (...) guard, or select existing identifiers first and only insert genuinely new ones. This fixes the root cause and leaves the integer column intact, so no casts elsewhere need to change.

2. Widen _private_tasks.id (and _private_job_queues.id) to bigint. This works but is not self-contained: _private_jobs.task_id / job_queue_id are integer, and there are ::int[] casts on these ids in the codebase (e.g. src/sql/getQueueNames.ts:19 on main, and task_id = any($2::int[]) in getJob as of 0.16.6). Those would hit the same class of bug already fixed for failJobs in failJobs casts bigint job ids to int[] — worker shutdown can't release jobs once the id sequence exceeds 2^31 #611 / failJobs casts job IDs to int[] — fails with "out of range for type integer" when ID sequence exceeds 2^31 #617 (PR Fix bad cast in failJobs #613). Widening the column without auditing the casts would move the failure from enqueue-time to the job-fetch hot path.

3. At minimum, document it and provide a supported remedy. Right now there is nothing in the docs or the issue tracker about this sequence, so the first signal an operator gets is a worker that will not boot.

Option 1 seems clearly preferable if it's viable — it keeps the schema and all existing casts valid, and removes the coupling between total job volume and a bounded lookup table.

Happy to open a PR for whichever direction you prefer.