Make start_tasks() apply task visibility before handlers run #656

Resolution

Fixed by #667 (078aa1e4) and released in 0.15.1. start_tasks() now forces the visibility update to execute, checks that every claimed message was updated, and rolls back the complete claim on failure. Tests cover effective timeouts, partial and complete update failures, and benign duplicate visibility.

Original problem

Before #667, pgflow.start_tasks() computed effective per-task visibility delays and called pgflow.set_vt_batch() in an unreferenced SELECT CTE. PostgreSQL could skip that CTE, so a claimed task could keep only the shorter visibility set by the initial PGMQ read.

The message could become visible while its step_tasks row remained started. Workers then repeatedly read that message and returned fewer tasks than messages. Future per-step workers must continue treating this healthy duplicate visibility as benign rather than unsupported work.

Previous path

Before #667, pkgs/core/schemas/0120_function_start_tasks.sql contained:

timeouts as (
  select
    task.message_id,
    task.flow_slug,
    coalesce(step.opt_timeout, flow.opt_timeout) + 2 as vt_delay
  from tasks task
  ...
),
set_vt_batch as (
  select pgflow.set_vt_batch(...)
  from timeouts
)
select ...
from tasks ...

The final query does not reference set_vt_batch. This does not guarantee that pgflow.set_vt_batch() runs.

Required behavior

For every task that changes from queued to started:

1. compute the same effective timeout used by stalled recovery;
2. extend that message's visibility before returning the task to a handler;
3. increment the attempt exactly once;
4. return no task whose visibility extension failed;
5. preserve atomic behavior for the claimed batch.

A message that becomes visible while its exact task remains started is a benign duplicate. Reading it must consume no new attempt and must not stop the worker.

Implementation boundary

Make the visibility side effect structurally required. Use PL/pgSQL PERFORM, a referenced CTE whose result the final statement consumes, or another statement form whose execution does not depend on demand for an unreferenced SELECT CTE.

Keep timeout semantics aligned across:

initial PGMQ read visibility
start_tasks() effective visibility extension
#621 stalled-task threshold
requeue_stalled_tasks() visibility reset

Do not fold queue-per-step routing into this fix. #650 will later add canonical queue_name and group visibility updates by queue.

Tests

• A claimed task's PGMQ visibility uses coalesce(step.opt_timeout, flow.opt_timeout) + 2 before start_tasks() returns.
• A shorter step timeout overrides the flow timeout.
• A longer step timeout is not shortened to the flow timeout.
• A null step timeout falls back to the flow timeout.
• A visibility-update failure rolls back the task transition and attempt increment.
• A repeatedly visible message for a started task consumes no additional attempt.
• A mixed batch cannot return a task whose visibility update did not execute.

Relationship to other work

• Parent epic: Epic: staged private per-step queues and queue identity #653.
• requeue_stalled_tasks() uses flow timeout instead of effective step timeout #621 fixed the recovery threshold after a worker stalls and shipped in 0.15.1.
• Skip paths archive sibling messages but never terminalize sibling step_tasks rows, leaving status='started' tasks on completed runs #649 fixed terminal state under skip paths and shipped in 0.15.1.
• Persist physical queue identity for flow tasks #650 must build queue-aware claiming on top of this corrected visibility behavior.
• Add private per-step queues with typed step workers #651 and Add explicit shared queues with multi-flow dispatch #652 must classify visible started messages as benign duplicates rather than unsupported work.

0.15.1 release integration

#666 consolidated this fix with the other task-lifecycle changes in 20260904095427_pgflow_task_lifecycle_hardening.sql. The 0.15.0 upgrade fixture checked both historical data repairs and the final runtime behavior before 0.15.1 shipped.