Description
When WorkflowStageJobs / WorkflowStageJobsByIDMany resolve dependencies and transition a job from pending to available, the scheduled_at column is not updated. It retains its original value from insertion time, which can be hours or months old for long-running workflows.
The UPDATE in both queries only sets state and metadata.workflow_staged_at:
UPDATE river_job
SET
state = jobs_to_make_available.new_state,
metadata = jsonb_set(metadata, '{workflow_staged_at}'::text[], $1::jsonb, true)
FROM jobs_to_make_available
WHERE river_job.id = jobs_to_make_available.idThe jobs_to_make_available CTE already reads scheduled_at to decide the target state (available if scheduled_at <= now() + 5s, otherwise scheduled), so by the time the UPDATE executes, the original scheduled_at value has served its purpose.
Impact
Any monitoring that uses NOW() - scheduled_at on available jobs to measure queue delay will report wildly inflated values for dependency-resolved workflow jobs. For workflows where deps take hours or months to resolve, this produces false alarms on queue health metrics.
Current workaround
We discovered that workflow_staged_at is already stamped in metadata during dep resolution, so we use it as a fallback in our metrics query:
MAX(
CASE
WHEN metadata ? 'workflow_staged_at'
THEN NOW() - (metadata->>'workflow_staged_at')::timestamptz
ELSE NOW() - scheduled_at
END
) as oldest_delayThis works but requires casting a JSONB string to timestamptz in an aggregate query, which is less ergonomic than using the native scheduled_at column directly.
Proposed solutions
Either of these would address the problem:
-
Update scheduled_at = now() in WorkflowStageJobs / WorkflowStageJobsByIDMany when transitioning jobs to available. This makes scheduled_at accurately reflect when the job became eligible for pickup, consistent with how non-workflow jobs behave. For jobs transitioning to scheduled (because their scheduled_at is still in the future), no change is needed — scheduled_at is already correct.
-
Add a first-class available_at column to river_job that records when a job entered the available state, regardless of how it got there (direct insert, scheduled time reached, or workflow dep resolution). This would give monitoring queries a reliable, indexed timestamp without relying on scheduled_at semantics or JSONB metadata. It would also benefit non-workflow use cases like jobs inserted with Pending: true that are later moved to available by application code.
Environment
- River Pro v0.22.0
- PostgreSQL
Description
When
WorkflowStageJobs/WorkflowStageJobsByIDManyresolve dependencies and transition a job frompendingtoavailable, thescheduled_atcolumn is not updated. It retains its original value from insertion time, which can be hours or months old for long-running workflows.The UPDATE in both queries only sets
stateandmetadata.workflow_staged_at:The
jobs_to_make_availableCTE already readsscheduled_atto decide the target state (availableifscheduled_at <= now() + 5s, otherwisescheduled), so by the time the UPDATE executes, the originalscheduled_atvalue has served its purpose.Impact
Any monitoring that uses
NOW() - scheduled_atonavailablejobs to measure queue delay will report wildly inflated values for dependency-resolved workflow jobs. For workflows where deps take hours or months to resolve, this produces false alarms on queue health metrics.Current workaround
We discovered that
workflow_staged_atis already stamped in metadata during dep resolution, so we use it as a fallback in our metrics query:This works but requires casting a JSONB string to
timestamptzin an aggregate query, which is less ergonomic than using the nativescheduled_atcolumn directly.Proposed solutions
Either of these would address the problem:
Update
scheduled_at = now()inWorkflowStageJobs/WorkflowStageJobsByIDManywhen transitioning jobs toavailable. This makesscheduled_ataccurately reflect when the job became eligible for pickup, consistent with how non-workflow jobs behave. For jobs transitioning toscheduled(because theirscheduled_atis still in the future), no change is needed —scheduled_atis already correct.Add a first-class
available_atcolumn toriver_jobthat records when a job entered theavailablestate, regardless of how it got there (direct insert, scheduled time reached, or workflow dep resolution). This would give monitoring queries a reliable, indexed timestamp without relying onscheduled_atsemantics or JSONB metadata. It would also benefit non-workflow use cases like jobs inserted withPending: truethat are later moved toavailableby application code.Environment