A PostgreSQL queue claim should select eligible jobs, lock those rows, and record their owner inside one transaction. Start the handler only after that transaction commits. When reviewing agent-written worker code, require overlapping-consumer tests and a recovery rule for committed claims whose worker disappears.

FOR UPDATE SKIP LOCKED helps consumers avoid rows another transaction has locked. Its protection ends with the transaction; the committed state and ownership checks must govern the later handler. PostgreSQL’s SELECT reference explicitly supports this use for queue-like tables while warning that skipped rows give an inconsistent view of the data.

Last reviewed: November 18, 2026 (UTC). References checked against the current PostgreSQL 18 documentation.

Trace one job from selection to a committed owner

Use this review when a coding agent edits polling SQL, ORM locking options, transaction wrappers, worker completion, or recovery. Follow the emitted SQL through the actual driver and connection pool. A method named claim_next_job establishes no database guarantee by itself.

Write down five decisions beside the patch: which jobs are eligible, how many a worker may claim, when ownership becomes durable, how completion proves ownership, and who may recover abandoned work. Include the deployed PostgreSQL major version, isolation level, runtime role, and relevant triggers.

A vulnerable implementation can select a ready row in one autocommit statement and update it later. Two workers can select the same identifier before either records ownership. Even a locking SELECT loses its row lock when its transaction ends. The Explicit Locking reference documents row-lock release at transaction end, including rollback.

Keep selection and the state transition on one connection in the same transaction. After commit, an eligible-state predicate must exclude the claimed row from subsequent polls. Keep the handler’s network calls and long computation outside that short claim transaction so they do not prolong its locks.

Compare the patch with a bounded claim statement

This disposable fixture separates jobs ready to run from running and completed jobs. It deliberately leaves payload handling out so the ownership transition is easy to inspect.

CREATE TABLE review_job (
    id bigint PRIMARY KEY,
    state text NOT NULL DEFAULT 'ready'
        CHECK (state IN ('ready', 'running', 'done')),
    available_at timestamptz NOT NULL,
    claim_no bigint NOT NULL DEFAULT 0 CHECK (claim_no >= 0),
    claimed_by text
);

INSERT INTO review_job (id, available_at) VALUES
    (101, CURRENT_TIMESTAMP - INTERVAL '1 minute'),
    (102, CURRENT_TIMESTAMP - INTERVAL '1 minute'),
    (103, CURRENT_TIMESTAMP + INTERVAL '1 day');

For a manual two-session exercise, prepare the following statement separately in each connection. In application code, bind the worker identifier and batch size through the driver’s parameter API. Validate a nonempty worker identifier and a positive batch size within the application’s configured maximum before executing it.

PREPARE claim_jobs (text, integer) AS
WITH picked AS (
    SELECT j.id
    FROM review_job AS j
    WHERE j.state = 'ready'
      AND j.available_at <= CURRENT_TIMESTAMP
    ORDER BY j.available_at, j.id
    LIMIT $2
    FOR UPDATE OF j SKIP LOCKED
)
UPDATE review_job AS j
SET state = 'running',
    claimed_by = $1,
    claim_no = j.claim_no + 1
FROM picked
WHERE j.id = picked.id
  AND j.state = 'ready'
RETURNING j.id, j.claim_no, j.claimed_by;

The lock belongs inside picked. SELECT documents that an outer locking clause does not automatically apply to referenced WITH queries. An agent moving the clause during a refactor must preserve that boundary.

The primary key makes each selected identifier match one target row. PostgreSQL’s UPDATE reference warns that an UPDATE ... FROM join should produce at most one source row per target; otherwise the source row used is unpredictable. Review extra joins added for priorities, tenants, or labels against that requirement.

RETURNING yields the rows actually updated and their new ownership values. Retain those values provisionally, commit, and only then hand the jobs to the handler. Returning a row does not establish that the surrounding transaction has committed. If a trigger suppresses updates, inspect its impact on returned rows too.

The ordering selects among available candidates with a unique tie breaker. It cannot promise strict first-in-first-out processing: a locked earlier job can be skipped while a later job runs, and handlers can finish in a different order. Avoid OFFSET pagination for claims; SELECT documents that rows stepped over by OFFSET can also be locked.

If the patch changes the queue schema or eligibility index, use the site’s database migration review workflow . Verify the actual query plan and polling latency on representative queue sizes before accepting an index recommendation.

Force two consumers to overlap

Use a disposable database matching the deployment’s major version and explicit Read Committed transactions. Apply the fixture once through the setup identity. Then use two independent connections with the runtime role’s required SELECT and UPDATE privileges and any applicable row policies. Prepare claim_jobs in both connections. Keep administrative fixture creation outside the runtime role.

Run this schedule while both sessions remain open:

StageSession ASession BExpected observation
Claim firstBEGIN; EXECUTE claim_jobs('worker-a', 1);Wait for A’s result.A provisionally returns job 101, claim 1.
OverlapLeave the transaction open.BEGIN; EXECUTE claim_jobs('worker-b', 1);B returns job 102, claim 1, while A still holds its claim open.
Resolve BRemain open.COMMIT;Job 102 has committed running ownership.
Release AROLLBACK;Remain connected.Job 101’s provisional claim is undone.
Claim againRemain connected.BEGIN; EXECUTE claim_jobs('worker-b', 1); COMMIT;B can now claim job 101 at claim 1.

Do not run A’s handler after the rollback. The returned claim was provisional and no longer grants permission to process the job.

Rebuild the fixture and repeat with A committing instead. B’s next poll must exclude A’s running job; job 103 must stay ready because it is not yet eligible. With both due jobs running, a further claim returns zero rows.

Give each test a bounded deadline. B must complete its overlapping claim before A resolves, which demonstrates skipping under contention. Preserve transaction boundaries and returned identifiers in the evidence. A serial run where A commits before B polls cannot establish this behavior.

The fixture has no custom triggers or competing maintenance commands. Repeat the application’s actual query path with its triggers, role, pool, and isolation configuration before approving the revision. These SQL examples and expected outcomes are a proposed acceptance procedure; no database execution is claimed here.

Keep completion tied to the current claim

After the claim commits, the row lock is gone and state = 'running' keeps ordinary consumers from selecting the job. A worker that exits at this point leaves durable running work behind. The sample statement contains no automatic lease or recovery mechanism.

Require a separate policy that decides when a claim is abandoned and atomically changes the row back to ready. In this fixture, recovery preserves claim_no and clears claimed_by. The next successful claim increments the number. Restrict this transition to the authorized recovery path; an ordinary poll must never reset running jobs.

The fixture uses deliberate recovery authorization from the test harness. A production lease needs additional expiry and renewal state, checked atomically by its recovery transition. An earlier observation of a stale heartbeat is insufficient if a concurrent renewal can make that observation obsolete.

After that policy authorizes recovery, bind the observed job ID, claim number, and worker ID to this statement in a short transaction:

UPDATE review_job
SET state = 'ready', claimed_by = NULL
WHERE id = $1
  AND state = 'running'
  AND claim_no = $2
  AND claimed_by = $3
RETURNING id;

Commit a successful recovery before allowing a new claimant to proceed. Zero returned rows means the observed claim no longer matches; re-read its state rather than removing the guards. Also race recovery against completion in the acceptance test. Whichever transition commits first must prevent the other transition from changing that same claim afterward.

The old worker may resume after recovery. Make completion conditional on the identity returned by its claim. This parameterized statement uses job ID, claim number, and worker ID:

UPDATE review_job
SET state = 'done'
WHERE id = $1
  AND state = 'running'
  AND claim_no = $2
  AND claimed_by = $3
RETURNING id;

One returned row means that completion changed the current claim’s state, subject to commit. Zero rows means the worker must stop treating itself as the current owner. Never follow that result with an unconditional update by job ID.

Test the stale-worker case explicitly: commit A’s claim, recover the row, let B claim it with a larger claim_no, then submit A’s completion. Assert zero returned rows and unchanged B ownership. Finally, B’s own completion should return one row and commit successfully.

This guard protects the database transition it checks. If a handler sends an email or invokes another service, that destination needs its own duplicate-effect policy. A conditional completion cannot undo an external action already taken by an old worker. Record that limit whenever recovery permits a second processing attempt.

If an agent adds an upsert to persist results, review its identity and update conditions separately with the site’s PostgreSQL upsert guide .

Classify empty polls, waits, and failed commits

An empty poll means this statement claimed no eligible, immediately lockable rows. Other consumers may hold eligible jobs, jobs may be scheduled for later, or all work may already be running. Use a bounded polling policy and observe ready-job age alongside claim throughput. An empty result alone does not prove that the queue is drained.

SKIP LOCKED applies to row locks. SELECT explicitly says the required table-level lock is still acquired normally; the UPDATE also needs its ordinary table lock. Therefore a schema change or another conflicting table lock can still stall this statement. Keep database timeout handling and transaction cleanup in the worker’s error path. Do not label every slow poll a failure of row skipping.

Explicit Locking documents deadlocks, consistent lock ordering as a defense, and aborting one participant to resolve a deadlock. Review other rows and tables touched by triggers or bookkeeping in the claim transaction. After a known transaction abort, roll back and use the application’s bounded whole-transaction retry policy. Permission or schema errors require repair rather than repeated claims under different credentials.

Treat a lost connection around commit as a separate uncertainty. Preserve the attempted job and ownership identifiers; establish the durable outcome through a trusted recovery path before processing or resetting them. If automatic reconciliation is required, the patch needs a durable claim-attempt identity and a defined protocol beyond this minimal fixture.

Attach evidence for the exact worker revision

The review packet should identify the candidate revision, generated SQL, schema, database major version, runtime role, isolation level, and transaction wrapper. Include the overlapping claim schedule, A’s commit and rollback variants, the future-job case, zero-row completion, and recovery followed by stale completion.

For each case, retain returned job and claim identifiers, committed state, transaction outcome, and whether the handler started. Omit payloads and connection credentials. Block approval if the code starts work before commit, loses the lock boundary, treats an empty poll as definitive drainage, or allows an old claimant to complete newer work.

Choose one changed worker and run those cases through its real connection path. Approve the revision when selection, durable ownership, completion, and recovery agree with the written contract and the evidence shows the consumers actually overlapped.

Sources checked

Three primary PostgreSQL references were retrieved successfully on November 18, 2026 UTC:

  • SELECT : The Locking Clause and LIMIT sections support row skipping, the queue use case, WITH-query lock placement, table-lock limits, ordering, and OFFSET behavior.
  • UPDATE : Description, Outputs, Notes, and Examples support updated-row results, privileges, join cardinality, zero-row success, and a locking CTE used to bound updates.
  • Explicit Locking : Table-Level Locks, Row-Level Locks, and Deadlocks support transaction lifetime, conflicting lock behavior, and deadlock handling.

These maintained references identified PostgreSQL 18 as current when checked. They support an evergreen code-review task; use the matching major-version references for your deployment. The fixture, ownership-number convention, recovery requirements, and acceptance matrix are engineering recommendations derived from the documented SQL behavior.