1use chrono::{Duration, Utc};
11use sayiir_core::codec;
12use sayiir_core::snapshot::{ExecutionPosition, WorkflowSnapshot, WorkflowSnapshotState};
13use sayiir_core::task_claim::{AvailableTask, TaskClaim};
14use sayiir_persistence::{BackendError, SnapshotStore, TaskClaimStore};
15use sqlx::Row;
16
17use crate::backend::PostgresBackend;
18use crate::error::PgError;
19
20const ELIGIBILITY_PREDICATE: &str = "NOT EXISTS (
34 SELECT 1 FROM sayiir_workflow_claims c
35 WHERE c.instance_id = s.instance_id
36 AND (c.expires_at IS NULL OR c.expires_at > now())
37 )
38 AND NOT EXISTS (
39 SELECT 1 FROM sayiir_workflow_signals sig
40 WHERE sig.instance_id = s.instance_id
41 )";
42
43impl<C> TaskClaimStore for PostgresBackend<C>
44where
45 C: codec::SnapshotCodec,
46{
47 #[tracing::instrument(
48 name = "db.claim_task",
49 skip(self),
50 fields(db.system = "postgresql"),
51 err(level = tracing::Level::ERROR),
52 )]
53 async fn claim_task(
54 &self,
55 instance_id: &str,
56 task_id: &sayiir_core::TaskId,
57 worker_id: &str,
58 ttl: Option<Duration>,
59 ) -> Result<Option<TaskClaim>, BackendError> {
60 let expires_at = ttl.and_then(|d| Utc::now().checked_add_signed(d));
61
62 let query = "
75 WITH probe AS (
76 SELECT instance_id, current_task_id, status
77 FROM sayiir_workflow_snapshots
78 WHERE instance_id = $1
79 ),
80 lock AS (
81 SELECT pg_try_advisory_xact_lock(hashtextextended($1::text, 0)) AS got
82 ),
83 upsert AS (
84 INSERT INTO sayiir_workflow_claims
85 (instance_id, task_id, worker_id, expires_at)
86 SELECT probe.instance_id, probe.current_task_id, $3, $4
87 FROM probe, lock
88 WHERE lock.got
89 AND probe.status = 'InProgress'
90 AND probe.current_task_id = $2
91 AND NOT EXISTS (
92 SELECT 1 FROM sayiir_workflow_claims c
93 WHERE c.instance_id = probe.instance_id
94 AND (c.expires_at IS NULL OR c.expires_at > now())
95 )
96 -- Re-execution race protection lives in the
97 -- worker''s validate_task_preconditions path, not
98 -- here: gating claim_task on a completed row in
99 -- workflow_tasks would permanently lock out
100 -- recovery when a worker dies BETWEEN
101 -- save_task_result (which writes the workflow_tasks
102 -- row) and save_snapshot (which advances
103 -- current_task_id). After that partial-completion,
104 -- every subsequent claim_task would reject and the
105 -- workflow would stall forever. The sleeping-giants
106 -- storm hits exactly this pattern under load.
107 -- Gate ON CONFLICT DO UPDATE on the existing claim
108 -- being expired. Without this, two transactions that
109 -- both pass the SELECT-side NOT EXISTS check (e.g.
110 -- because the prior claim's row just got deleted by
111 -- release, or because both see the same expired row)
112 -- can both INSERT, and ON CONFLICT silently picks the
113 -- last writer — leaving the earlier worker thinking
114 -- it owns a claim that the row actually attributes
115 -- to a different worker. The WHERE here turns the
116 -- upsert into 'steal expired only'; concurrent
117 -- inserts on a still-live claim now produce zero
118 -- upsert rows for the loser (caller sees Ok(None)
119 -- and retries), instead of misreporting success.
120 ON CONFLICT (instance_id) DO UPDATE
121 SET task_id = EXCLUDED.task_id,
122 worker_id = EXCLUDED.worker_id,
123 claimed_at = now(),
124 expires_at = EXCLUDED.expires_at
125 WHERE sayiir_workflow_claims.expires_at IS NOT NULL
126 AND sayiir_workflow_claims.expires_at <= now()
127 RETURNING
128 FLOOR(EXTRACT(EPOCH FROM claimed_at))::BIGINT AS claimed_epoch,
129 FLOOR(EXTRACT(EPOCH FROM expires_at))::BIGINT AS expires_epoch
130 )
131 SELECT claimed_epoch, expires_epoch, TRUE AS snapshot_exists
132 FROM upsert
133 UNION ALL
134 SELECT
135 NULL::bigint, NULL::bigint,
136 EXISTS (SELECT 1 FROM probe)
137 WHERE NOT EXISTS (SELECT 1 FROM upsert)
138 ";
139 let row = sqlx::query(query)
140 .bind(instance_id)
141 .bind(task_id.as_bytes().as_slice())
142 .bind(worker_id)
143 .bind(expires_at)
144 .fetch_one(&self.pool)
145 .await
146 .map_err(PgError)?;
147
148 if let Some(claimed_epoch) = row.get::<Option<i64>, _>("claimed_epoch") {
149 return Ok(Some(TaskClaim {
150 instance_id: std::sync::Arc::from(instance_id),
151 task_id: *task_id,
152 worker_id: worker_id.to_string(),
153 claimed_at: claimed_epoch.cast_unsigned(),
154 expires_at: row
155 .get::<Option<i64>, _>("expires_epoch")
156 .map(i64::cast_unsigned),
157 }));
158 }
159
160 if !row.get::<bool, _>("snapshot_exists") {
161 return Err(BackendError::NotFound(format!("{instance_id}:{task_id}")));
162 }
163 Ok(None)
164 }
165
166 #[tracing::instrument(
167 name = "db.release_task_claim",
168 skip(self),
169 fields(db.system = "postgresql"),
170 err(level = tracing::Level::ERROR),
171 )]
172 async fn release_task_claim(
173 &self,
174 instance_id: &str,
175 task_id: &sayiir_core::TaskId,
176 worker_id: &str,
177 ) -> Result<(), BackendError> {
178 let row = sqlx::query(
197 "WITH del AS (
198 DELETE FROM sayiir_workflow_claims
199 WHERE instance_id = $1 AND worker_id = $2 AND task_id = $3
200 RETURNING 1
201 ),
202 probe AS (
203 SELECT worker_id, task_id
204 FROM sayiir_workflow_claims
205 WHERE instance_id = $1 AND NOT EXISTS (SELECT 1 FROM del)
206 )
207 SELECT
208 EXISTS (SELECT 1 FROM del) AS done,
209 (SELECT worker_id FROM probe) AS owner,
210 (SELECT task_id FROM probe) AS current_task_id",
211 )
212 .bind(instance_id)
213 .bind(worker_id)
214 .bind(task_id.as_bytes().as_slice())
215 .fetch_one(&self.pool)
216 .await
217 .map_err(PgError)?;
218
219 disambiguate_writeback(&row, instance_id, task_id, worker_id)
220 }
221
222 #[tracing::instrument(
223 name = "db.extend_task_claim",
224 skip(self),
225 fields(db.system = "postgresql"),
226 err(level = tracing::Level::ERROR),
227 )]
228 async fn extend_task_claim(
229 &self,
230 instance_id: &str,
231 task_id: &sayiir_core::TaskId,
232 worker_id: &str,
233 additional_duration: Duration,
234 ) -> Result<(), BackendError> {
235 let row = sqlx::query(
251 "WITH upd AS (
252 UPDATE sayiir_workflow_claims
253 SET expires_at = expires_at + $3
254 WHERE instance_id = $1
255 AND worker_id = $2
256 AND task_id = $4
257 AND expires_at IS NOT NULL
258 RETURNING 1
259 ),
260 probe AS (
261 SELECT worker_id, task_id
262 FROM sayiir_workflow_claims
263 WHERE instance_id = $1 AND NOT EXISTS (SELECT 1 FROM upd)
264 )
265 SELECT
266 EXISTS (SELECT 1 FROM upd) AS done,
267 (SELECT worker_id FROM probe) AS owner,
268 (SELECT task_id FROM probe) AS current_task_id",
269 )
270 .bind(instance_id)
271 .bind(worker_id)
272 .bind(additional_duration)
273 .bind(task_id.as_bytes().as_slice())
274 .fetch_one(&self.pool)
275 .await
276 .map_err(PgError)?;
277
278 disambiguate_writeback(&row, instance_id, task_id, worker_id)
279 }
280
281 #[tracing::instrument(
282 name = "db.find_available_tasks",
283 skip(self),
284 fields(db.system = "postgresql"),
285 err(level = tracing::Level::ERROR),
286 )]
287 #[allow(clippy::cast_precision_loss, clippy::too_many_lines)]
288 async fn find_available_tasks(
289 &self,
290 worker_id: &str,
291 limit: usize,
292 aging_interval: Duration,
293 worker_tags: &[String],
294 ) -> Result<Vec<AvailableTask>, BackendError> {
295 let aging_secs = (aging_interval.num_milliseconds() as f64 / 1000.0).max(1.0);
300 let worker_tags_vec: Vec<&str> = worker_tags.iter().map(String::as_str).collect();
301 let tag_filter = if worker_tags.is_empty() {
302 ""
303 } else {
304 "AND s.task_tags <@ $3"
305 };
306
307 let query = format!(
325 "SELECT r.instance_id, h.data, r.trace_parent
326 FROM (
327 SELECT s.instance_id, s.history_version, s.trace_parent
328 FROM sayiir_workflow_snapshots s
329 WHERE s.status = 'InProgress'
330 AND (
331 s.position_kind = 'AtTask'
332 OR (s.position_kind = 'AtDelay'
333 AND s.delay_wake_at IS NOT NULL
334 AND s.delay_wake_at <= now())
335 )
336 AND {ELIGIBILITY_PREDICATE}
337 {tag_filter}
338 ORDER BY
339 (s.task_priority - EXTRACT(EPOCH FROM (now() - s.updated_at)) / $2) ASC,
340 s.updated_at ASC
341 LIMIT $1
342 FOR UPDATE OF s SKIP LOCKED
343 ) r
344 JOIN sayiir_workflow_snapshot_history h
345 ON h.instance_id = r.instance_id AND h.version = r.history_version"
346 );
347
348 let mut q = sqlx::query(&query)
349 .bind(i64::try_from(limit).unwrap_or(i64::MAX))
350 .bind(aging_secs);
351 if !worker_tags.is_empty() {
352 q = q.bind(&worker_tags_vec);
353 }
354 let rows = q.fetch_all(&self.pool).await.map_err(PgError)?;
355
356 let mut decoded: Vec<WorkflowSnapshot> = Vec::with_capacity(rows.len());
360 for row in &rows {
361 let raw: &[u8] = row.get("data");
362 let mut snapshot = self.decode(raw)?;
363 snapshot.trace_parent = row.get("trace_parent");
364 decoded.push(snapshot);
365 }
366 let instance_id_strs: Vec<&str> = decoded.iter().map(|s| s.instance_id.as_ref()).collect();
367 let mut outputs_by_instance =
368 crate::history::fetch_task_outputs_batched(&self.pool, &instance_id_strs).await?;
369 for snapshot in &mut decoded {
370 if let Some(outputs) = outputs_by_instance.remove(snapshot.instance_id.as_ref()) {
371 snapshot.hydrate_task_outputs(outputs);
372 }
373 }
374
375 let mut available: Vec<(bool, AvailableTask)> = Vec::with_capacity(decoded.len());
376 for mut snapshot in decoded {
377 let task_id_to_dispatch: sayiir_core::TaskId = match &snapshot.state {
382 WorkflowSnapshotState::InProgress {
384 position:
385 ExecutionPosition::AtDelay {
386 wake_at,
387 next_task_id,
388 delay_id,
389 ..
390 },
391 ..
392 } if Utc::now() >= *wake_at => {
393 let next_id_opt = *next_task_id;
394 let delay_id_owned = *delay_id;
395 if let Some(next_id) = next_id_opt {
396 snapshot.update_position(ExecutionPosition::AtTask { task_id: next_id });
397 self.save_snapshot(&mut snapshot).await?;
398 next_id
399 } else {
400 let output = snapshot
402 .get_task_result_bytes(&delay_id_owned)
403 .unwrap_or_default();
404 snapshot.mark_completed(output);
405 self.save_snapshot(&mut snapshot).await?;
406 continue;
407 }
408 }
409 WorkflowSnapshotState::InProgress {
410 position: ExecutionPosition::AtTask { task_id },
411 completed_tasks,
412 ..
413 } => {
414 if completed_tasks.contains_key(task_id) {
415 continue;
416 }
417 let task_id_copy = *task_id;
418 if let Some(rs) = snapshot.task_retries.get(&task_id_copy)
419 && Utc::now() < rs.next_retry_at
420 {
421 continue;
422 }
423 task_id_copy
424 }
425 _ => continue,
426 };
427
428 let bias = snapshot.has_failed_on_worker(&task_id_to_dispatch, worker_id);
429 if let Some(task) = build_available_task(snapshot, task_id_to_dispatch) {
430 available.push((bias, task));
431 }
432
433 if available.len() >= limit {
434 break;
435 }
436 }
437
438 available.sort_by_key(|(bias, _)| *bias);
439 tracing::debug!(count = available.len(), "available tasks found");
440 Ok(available.into_iter().map(|(_, task)| task).collect())
441 }
442
443 async fn wait_for_wakeup(
444 &self,
445 timeout: std::time::Duration,
446 ) -> Result<Option<sayiir_persistence::TaskWakeupHint>, BackendError> {
447 Ok(self.wakeup.wait(timeout).await)
448 }
449
450 #[tracing::instrument(
451 name = "db.find_hinted_task",
452 skip(self, hint),
453 fields(
454 db.system = "postgresql",
455 instance_id = %hint.instance_id,
456 task_id = %sayiir_core::TaskId::from_bytes(hint.task_id),
457 ),
458 err(level = tracing::Level::ERROR),
459 )]
460 async fn find_hinted_task(
461 &self,
462 hint: &sayiir_persistence::TaskWakeupHint,
463 ) -> Result<Option<AvailableTask>, BackendError> {
464 let query = format!(
477 "SELECT
478 s.trace_parent,
479 h.data,
480 (SELECT array_agg(t.task_id ORDER BY t.task_id)
481 FROM sayiir_workflow_tasks t
482 WHERE t.instance_id = s.instance_id
483 AND t.status = 'completed'
484 AND t.output IS NOT NULL) AS task_ids,
485 (SELECT array_agg(t.output ORDER BY t.task_id)
486 FROM sayiir_workflow_tasks t
487 WHERE t.instance_id = s.instance_id
488 AND t.status = 'completed'
489 AND t.output IS NOT NULL) AS task_outputs
490 FROM sayiir_workflow_snapshots s
491 JOIN sayiir_workflow_snapshot_history h
492 ON h.instance_id = s.instance_id AND h.version = s.history_version
493 WHERE s.instance_id = $1
494 AND s.current_task_id = $2
495 AND s.status = 'InProgress'
496 AND {ELIGIBILITY_PREDICATE}"
497 );
498 let row = sqlx::query(&query)
499 .bind(&hint.instance_id)
500 .bind(hint.task_id.as_slice())
501 .fetch_optional(&self.pool)
502 .await
503 .map_err(PgError)?;
504
505 let Some(row) = row else { return Ok(None) };
506
507 let data: Vec<u8> = row.get("data");
508 let mut snapshot = self.decode(&data)?;
509 snapshot.trace_parent = row.get("trace_parent");
510
511 let task_ids: Option<Vec<Vec<u8>>> = row.get("task_ids");
515 let task_outputs: Option<Vec<Vec<u8>>> = row.get("task_outputs");
516 if let (Some(ids), Some(outs)) = (task_ids, task_outputs) {
517 let mut outputs = Vec::with_capacity(ids.len());
519 for (id_bytes, out_bytes) in ids.into_iter().zip(outs.into_iter()) {
520 let tid = sayiir_core::TaskId::from_slice(&id_bytes).map_err(|_| {
521 BackendError::Backend(format!(
522 "sayiir_workflow_tasks.task_id for instance {} is {} bytes (expected 32)",
523 hint.instance_id,
524 id_bytes.len()
525 ))
526 })?;
527 outputs.push((tid, bytes::Bytes::from(out_bytes)));
528 }
529 snapshot.hydrate_task_outputs(outputs);
530 }
531
532 let hint_task_id = sayiir_core::TaskId::from_bytes(hint.task_id);
533 let matches = match &snapshot.state {
534 WorkflowSnapshotState::InProgress {
535 position: ExecutionPosition::AtTask { task_id },
536 completed_tasks,
537 ..
538 } => *task_id == hint_task_id && !completed_tasks.contains_key(task_id),
539 _ => false,
540 };
541 if !matches {
542 return Ok(None);
543 }
544
545 Ok(build_available_task(snapshot, hint_task_id))
546 }
547}
548
549fn disambiguate_writeback(
568 row: &sqlx::postgres::PgRow,
569 instance_id: &str,
570 task_id: &sayiir_core::TaskId,
571 worker_id: &str,
572) -> Result<(), BackendError> {
573 if row.get::<bool, _>("done") {
574 return Ok(());
575 }
576 let owner: Option<String> = row.get("owner");
577 let Some(owner) = owner else {
578 return Err(BackendError::NotFound(format!("{instance_id}:{task_id}")));
579 };
580 if owner != worker_id {
581 return Err(BackendError::Backend(format!(
582 "Claim owned by different worker: {owner}"
583 )));
584 }
585 let current: Option<Vec<u8>> = row.get("current_task_id");
587 match current {
588 Some(bytes) if bytes.as_slice() == task_id.as_bytes().as_slice() => Ok(()),
589 _ => Err(BackendError::NotFound(format!("{instance_id}:{task_id}"))),
590 }
591}
592
593fn build_available_task(
602 snapshot: WorkflowSnapshot,
603 task_id: sayiir_core::TaskId,
604) -> Option<AvailableTask> {
605 let completed_empty = match &snapshot.state {
606 sayiir_core::snapshot::WorkflowSnapshotState::InProgress {
607 completed_tasks, ..
608 } => completed_tasks.is_empty(),
609 _ => return None,
610 };
611 let input = if completed_empty {
612 snapshot.initial_input_bytes()
613 } else {
614 snapshot.get_last_task_output()
615 };
616
617 input.map(|input_bytes| AvailableTask {
618 instance_id: snapshot.instance_id.clone(),
619 task_id,
620 input: input_bytes,
621 workflow_definition_hash: snapshot.definition_hash,
622 trace_parent: snapshot.trace_parent.clone(),
623 snapshot: std::sync::Arc::new(snapshot),
624 })
625}