Postgres background jobs: queue worker vs periodic full recompute deadlock and long locks

A queue worker (per-row: rewrite derived key rows, then upsert a status row) and a periodic full recompute (lock status row FOR UPDATE, delete all derived rows, recompute, reinsert) running on the same database deadlock because they take locks in opposite order. The full recompute that deletes all derived rows at the start also holds those row locks plus FK KEY SHARE locks on every parent row for its whole duration, so user deletes/merges of parent rows and every worker pass wait minutes; a parent deleted mid-recompute makes the reinsertion fail with a foreign key violation.

1) Serialize all exam jobs with one transaction-level advisory lock taken WITHOUT waiting: select pg_try_advisory_xact_lock(hashtextextended('app:jobname', 0)); whoever finds it taken returns immediately and retries next tick. 2) Check for work before taking the lock (an empty queue must not take it): when timers of 30 s and 60 s fire in the same tick, an empty worker pass holding the lock for a few ms made the periodic job lose the race every minute. 3) Full recompute: compute values, keys and scores in ON COMMIT DROP temp tables (ANALYZE them), then write only the difference (anti-join delete of vanished rows, anti-join insert of new ones). Lock parents only for rows actually inserted, via a CTE vivantes as (select id from parent where id in (...) for key share) joined in the INSERT: rows deleted concurrently are skipped instead of raising an FK error. Result on 50k rows: 37 s of compute holding no row locks, 0.4 s of writes; concurrent deletes take ~50 ms instead of waiting for the full job. 4) After a failed periodic run, back off (e.g. 1 h) instead of retrying every minute, or it will monopolize the lock.

Lock order inversion between two transactions (key rows then status row vs status row then key rows) produces 40P01; deleting all derived rows first makes every one of them locked until commit, and FK inserts take KEY SHARE on parents, which conflicts with DELETE.