ON CONFLICT DO NOTHING and retried jobs

Put the client's deadline ahead of the external reply. The client stops waiting. The worker doesn't. When that late success shows up, it has to land on the same durable job, even if the retry was already told the outcome is unresolved.

Start from the invariant. Each identifier has one and only one accepted payload identity, and a conflict says nothing about whether the new request matches it or whether the job finished. Read the stored row and compare identities before you pick a response. Under Read Committed, a plain SELECT sees a snapshot taken when that command starts, so be explicit about which later query reads the state you report.

That seems manageable without adding much. Tag the late result with the original job identifier, and let one small status lookup report the job as running, unresolved or completed. Then the retry and the late success end up talking about the same job.

The worst spot is narrower than a timeout. The external service has accepted the action, and the result write never happens. Whoever picks the job up next finds no result and nothing that says whether submitting again is safe.

So keep the accepted payload identity, the execution state, any downstream correlation identifier and the eventual response in the row with the request identifier. The next attempt decides its step from that row alone.

Even then the remote success can land before the write. If the external service can look up an action by correlation identifier, reconcile. If it documents idempotency keys, give it the accepted job's key. If it offers neither, leave the outcome unresolved and settle the recovery policy before anything is resubmitted.

A retry with different input gets a mismatch and leaves the accepted job alone. A matching retry gets the stored result, a running status or that unresolved outcome.

The two statement version is right, and the wait is what makes it work. A inserts the identifier inside an open transaction. B runs INSERT ... ON CONFLICT (request_id) DO NOTHING RETURNING id and waits, because the conflicting row isn't committed yet. A commits, B gets zero rows back, and B's next SELECT under Read Committed sees A's row with its payload identity. If A rolls back instead, B's insert goes through and returns the new id.

So the empty RETURNING is the signal to read. The docs note the DO NOTHING case works this way only under Read Committed, so I wouldn't assume the same steps under Repeatable Read.