fetch_orchestration_item: avoid the advisory-lock convoy and bound the candidate loop #34

Follow-up to #21. The fix for #21 computes the lock lease when the instance is claimed, so a slow fetch can no longer return an already-expired lease. The behaviour below is still correct, but it costs throughput and latency under contention. Each item can be done separately.

1. Advisory-lock convoy on a hot instance

Every dispatcher selects the same oldest candidate (ORDER BY visible_at, id LIMIT 1) and then blocks on pg_advisory_xact_lock(hashtext(instance_id)). Under contention the dispatchers wait for each other's fetch transactions on that one instance, while other instances have work.

Proposal: claim with pg_try_advisory_xact_lock. Put a held instance on an in-call skip list (instance_id <> ALL(skip_list) in the candidate query) and try the next candidate.

Advisory locks are database-wide. With a non-blocking claim, an unscoped key makes a fetch skip an instance only because another schema in the same database is working on the same instance id. So the key must become hashtext('<schema>.' || instance_id). Only this procedure takes that lock.

2. Spin on a row-locked candidate

When the FOR UPDATE ... SKIP LOCKED re-check finds no row, for example while an ack is still deleting the rows, the loop selects the same candidate again until that transaction ends.

Proposal: add the candidate to the skip list. Use a separate variable for the re-check. Writing the re-check result into the candidate variable stores NULL in the skip list, and <> ALL(...) with a NULL element rejects every remaining candidate. #22 has this problem.

3. Unbounded candidate loop

One call can try any number of candidates.

Proposal: stop after a fixed number of candidates (for example 50) and return no row. The dispatcher already treats no row as no work and polls again.

4. Stale caller clock in predicates

Visibility (visible_at <= p_now_ms) and lock expiry (locked_until > p_now_ms) use p_now_ms. The caller reads it before the call. Under long waits it lags real time. This is conservative: work becomes eligible later, never early.

Proposal: use p_now_ms plus the time spent in the statement, refreshed on each loop iteration. This keeps the caller's clock domain, which wrote visible_at and locked_until.

Prior art and tests

#22 (migration 0024) implements 1-3 except the NULL fix in 2.

An implementation of all four exists and is tested. On PostgreSQL 15 its regression tests showed:

It can be opened as a PR when this is picked up.

Related: microsoft/duroxide#46, microsoft/duroxide#47.