Race in add_job/add_jobs with keyed jobs can drop scheduling requests (and returns no row under contention)

[leo91000]

Summary

I am the maintainer of graphile_worker_rs, a Rust rewrite of graphile/worker.

A user recently reported a bug, and I think this exposes a race condition in the keyed scheduling path (add_jobs / add_job).

Original report: leo91000/graphile_worker_rs#378

Steps to reproduce

1. Start PostgreSQL and initialize Graphile Worker schema.
2. Run a worker with concurrency 10 and two tasks:

• printer: very short task (e.g. sleeps ~2ms) so keyed jobs frequently become locked/running.
• scheduler: loops many times (e.g. 100), each time calling addJob("printer", { key }, { jobKey: key, jobKeyMode: "preserve_run_at" }) with key chosen from a small keyspace (e.g. 10 keys).

3. Enqueue multiple scheduler jobs concurrently (e.g. 4).
4. Let it run for ~30-60 seconds.

This creates high contention on the same jobKey while some conflicting rows are locked.

Expected results

• addJob(...) should always return a valid job row.
• For replace / preserve_run_at, scheduling should not occasionally return “no row” under contention.

Actual results

Under contention, graphile_worker.add_jobs(...) can return no row for a spec because of:

• ON CONFLICT (key) DO UPDATE ... WHERE jobs.locked_at IS NULL

When that WHERE condition is false, the conflict path does nothing and returns nothing for that spec.
Then add_job(...) (which selects from add_jobs(...) LIMIT 1) can return a null/empty row.

In strict clients this surfaces clearly (example from Rust/sqlx):

• error occurred while decoding column "id": unexpected null; try decoding as an Option

In JS this can manifest as rows[0] missing from add_job(...) result in edge cases.

Additional context

• Reproduced from issue https://github.com/leo91000/graphile_worker_rs/issues/378
• Reproduced against upstream SQL shape in sql/000018.sql:
• add_job delegates to add_jobs (select * into v_job from ...add_jobs(...))
• add_jobs uses ON CONFLICT (key) DO UPDATE ... WHERE jobs.locked_at is null
• Current repo version checked: 0.17.0-rc.0 (from package.json)
• PostgreSQL: reproduced on Docker Postgres (15/16 class behavior)

[benjie]

Interesting catch; how do you propose to fix it?

[leo91000]

I've had success with pg_advisory_xact_lock with key. Seems to work well, however not sure if this is the right solution : https://github.com/leo91000/graphile_worker_rs/blob/main/crates/migrations/src/sql/m000019.rs

[claygeo]

Interesting race condition. I've dealt with similar keyed-job contention in BullMQ with Redis and the pattern is similar even across PostgreSQL and Redis.

The core problem is the classic UPSERT-under-concurrent-lock gap: two transactions both attempt INSERT ... ON CONFLICT DO UPDATE on the same job_key, but the conflicting row is locked by a running worker. The first transaction blocks waiting for the lock, the second transaction sees the locked row and either:

• Times out
• Skips the row entirely (SKIP LOCKED behavior)
• Gets a snapshot where the row doesn't appear (if the running worker deletes it between the SELECT and INSERT)

In PostgreSQL, this is a known issue with ON CONFLICT DO UPDATE under high concurrency. The standard mitigation is advisory locks keyed on the job_key hash:

SELECT pg_advisory_xact_lock(hashtext(job_key));
-- then do the INSERT ... ON CONFLICT

This serializes competing add_job calls for the same key without blocking the worker's lock on the job row itself. The advisory lock scope is the transaction, so it releases automatically on commit/rollback.

The alternative (retry loop with SKIP LOCKED detection) works but adds latency proportional to contention. Advisory locks give constant-time serialization for the scheduling path.

The Rust port user's report is consistent with this: add_job returns no row because the INSERT sees no conflict (row was locked/invisible), and the WHERE clause in the upsert path filters it out.