world-postgres: each failed job release costs the queue a Graphile Worker worker for good #4750

Packages: @workflow/world-postgres@5.0.2 (main) and 4.3.x, graphile-worker@0.16.6

What happens

Graphile Worker 0.16.6 retries completeJob and failJob only on 40001, 40P01, 57P03, EHOSTUNREACH and ETIMEDOUT (RETRYABLE_ERROR_CODES in lib.js), up to 100 times. Some other errors can reach a worker while it releases the job it just ran:

• a refused or reset connection;
• a session the server ends (57P01, "Connection terminated unexpectedly");
• pg's connect timeout ("timeout expired", no code).

Any of them ends the worker, and so does a retryable error that outlasts the 100 retries:

[Graphile Worker] Failed to release job '<id>' after success; committing seppuku
[Graphile Worker] Worker exited, but pool is in continuous mode, is active, and is not shutting down... Did something go wrong?

_runTaskList in main.js only removes the worker (// TODO: handle when a worker shuts down (spawn a new one)). The runner's promise does not settle, so nothing above Graphile Worker notices.

Every database failover can do this to each worker that finishes a job during it. A process loses workers one failover at a time. Once it has none, it claims nothing, while its runner, its LISTEN connection and its stale-lock sweep still look healthy. The loss shows only in Graphile Worker's own log lines.

The job whose release failed may also stay locked, unless the release committed before the error. It stays locked until Graphile Worker resets locks older than 4 hours, which it checks every 8–10 minutes.

• getJob also locks the job's named queue, so with enableInvoke that run's executor queue waits as well.
• That is a separate effect. world-postgres: step jobs are permanently retired by lost workers, not by failures #3992 covers a related one: attempts consumed by lost processes.

Reproduction

This uses a real Postgres and a single worker (queueConcurrency: 1). A trigger refuses one job's completion; P0001 is not retried either, just like a dropped connection:

CREATE FUNCTION refuse_release() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  IF OLD.key = 'release-refused' THEN
    RAISE EXCEPTION 'release refused';
  END IF;
  RETURN OLD;
END $$;
CREATE TRIGGER refuse_release BEFORE DELETE ON graphile_worker._private_jobs
  FOR EACH ROW EXECUTE FUNCTION refuse_release();

Queue one message with idempotencyKey: 'release-refused', then any other message. The first is delivered, its release fails, and the worker exits. The second is never claimed.

Proposal

• On a runner's first worker:fatalError, start a new runner in its place.
• Retire the old runner only once the new one is up. A start needs a connection, because Graphile Worker migrates first, and the database is probably still going away. Until then, the old runner's remaining workers keep retrying their fetches.
• Retry a start that fails with capped backoff.
• Let the old runner's in-flight jobs finish. Stopping a runner makes Graphile Worker abort its jobs' signals after 5s, and the queue forwards that signal to the delivery. Aborting healthy deliveries would cost each an attempt and a duplicate execution.
• Don't replace a runner that is stopping, and don't repeat world.start()'s active-run recovery.
• Report each loss to an optional onWorkerLost callback, since Graphile Worker logs it only through its own logger.

Graphile Worker 0.17 adds a poolWorkerPrematureExit hook whose replaceWithNewWorker() replaces just the worker. Moving to 0.17 is a larger change (new migrations, locked_by semantics), so this proposal works on 0.16.6.

For context: on the service where we found this, the workflow database dropped every connection five times in 30 days (maintenance windows and a failover). No release landed in one of those windows yet, but each could have cost a worker. We run a fix there as a dependency patch, which a published package cannot ship. I'll open a PR with the runner replacement.