1use std::time::Duration;
55use async_trait::async_trait;
56use chrono::{DateTime, Utc};
57use sqlx::{PgPool, postgres::PgPoolOptions, Row};
58use uuid::Uuid;
59
60use asyncq::{
61 backend::Backend,
62 error::{Error, Result},
63 job::{JobId, JobRecord, QueueStats},
64};
65
66const MIGRATION_SQL: &str = include_str!("../migrations/0001_create_asyncq_jobs.sql");
67
68#[derive(Clone)]
74pub struct PostgresBackend {
75 pool: PgPool,
76}
77
78impl PostgresBackend {
79 pub async fn new(database_url: &str) -> Result<Self> {
81 let pool = PgPoolOptions::new()
82 .max_connections(20)
83 .connect(database_url)
84 .await
85 .map_err(|e| Error::Backend(e.to_string()))?;
86 Ok(Self { pool })
87 }
88
89 pub fn from_pool(pool: PgPool) -> Self {
91 Self { pool }
92 }
93
94 pub fn pool_ref(&self) -> &PgPool {
96 &self.pool
97 }
98
99 pub async fn migrate(&self) -> Result<()> {
101 sqlx::raw_sql(MIGRATION_SQL)
102 .execute(&self.pool)
103 .await
104 .map_err(|e| Error::Backend(format!("migration failed: {e}")))?;
105 Ok(())
106 }
107
108 pub async fn enqueue_in_tx(
113 &self,
114 kind: &str,
115 queue: &str,
116 payload: Vec<u8>,
117 max_attempts: u32,
118 scheduled_at: DateTime<Utc>,
119 tx: &mut sqlx::Transaction<'_, sqlx::Postgres>,
120 ) -> Result<JobId> {
121 let id = Uuid::new_v4();
122 sqlx::query(
123 r#"INSERT INTO asyncq_jobs
124 (id, kind, queue, payload, max_attempts, scheduled_at)
125 VALUES ($1, $2, $3, $4, $5, $6)"#,
126 )
127 .bind(id)
128 .bind(kind)
129 .bind(queue)
130 .bind(&payload)
131 .bind(max_attempts as i32)
132 .bind(scheduled_at)
133 .execute(&mut **tx)
134 .await
135 .map_err(|e| Error::Backend(e.to_string()))?;
136 Ok(id)
137 }
138}
139
140#[async_trait]
141impl Backend for PostgresBackend {
142 async fn enqueue(&self, record: JobRecord) -> Result<JobId> {
143 let id = record.id;
144 sqlx::query(
145 r#"INSERT INTO asyncq_jobs
146 (id, kind, queue, payload, attempt, max_attempts, scheduled_at, created_at)
147 VALUES ($1, $2, $3, $4, $5, $6, $7, $8)"#,
148 )
149 .bind(record.id)
150 .bind(&record.kind)
151 .bind(&record.queue)
152 .bind(&record.payload)
153 .bind(record.attempt as i32)
154 .bind(record.max_attempts as i32)
155 .bind(record.scheduled_at)
156 .bind(record.created_at)
157 .execute(&self.pool)
158 .await
159 .map_err(|e| Error::Backend(e.to_string()))?;
160 Ok(id)
161 }
162
163 async fn claim(&self, queues: &[&str], timeout: Duration) -> Result<Option<JobRecord>> {
164 let deadline = tokio::time::Instant::now() + timeout;
165 let sleep_ms = 50u64;
166
167 loop {
168 let row = sqlx::query(
170 r#"UPDATE asyncq_jobs
171 SET status = 'running',
172 attempt = attempt + 1,
173 heartbeat_at = NOW()
174 WHERE id = (
175 SELECT id FROM asyncq_jobs
176 WHERE queue = ANY($1)
177 AND status = 'pending'
178 AND scheduled_at <= NOW()
179 ORDER BY scheduled_at ASC
180 LIMIT 1
181 FOR UPDATE SKIP LOCKED
182 )
183 RETURNING id, kind, queue, payload, attempt, max_attempts,
184 scheduled_at, created_at, last_error"#,
185 )
186 .bind(queues)
187 .fetch_optional(&self.pool)
188 .await
189 .map_err(|e| Error::Backend(e.to_string()))?;
190
191 if let Some(row) = row {
192 return Ok(Some(row_to_record(row)?));
193 }
194
195 if timeout.is_zero() || tokio::time::Instant::now() >= deadline {
196 return Ok(None);
197 }
198 tokio::time::sleep(Duration::from_millis(sleep_ms)).await;
199 }
200 }
201
202 async fn ack(&self, id: JobId) -> Result<()> {
203 sqlx::query("UPDATE asyncq_jobs SET status = 'completed' WHERE id = $1")
204 .bind(id)
205 .execute(&self.pool)
206 .await
207 .map_err(|e| Error::Backend(e.to_string()))?;
208 Ok(())
209 }
210
211 async fn nack(&self, id: JobId, error: &str, retry_at: Option<DateTime<Utc>>) -> Result<()> {
212 match retry_at {
213 Some(at) => {
214 sqlx::query(
215 r#"UPDATE asyncq_jobs
216 SET status = 'pending', scheduled_at = $2, last_error = $3,
217 heartbeat_at = NULL
218 WHERE id = $1"#,
219 )
220 .bind(id)
221 .bind(at)
222 .bind(error)
223 .execute(&self.pool)
224 .await
225 .map_err(|e| Error::Backend(e.to_string()))?;
226 }
227 None => {
228 sqlx::query(
229 r#"UPDATE asyncq_jobs
230 SET status = 'dead', last_error = $2, heartbeat_at = NULL
231 WHERE id = $1"#,
232 )
233 .bind(id)
234 .bind(error)
235 .execute(&self.pool)
236 .await
237 .map_err(|e| Error::Backend(e.to_string()))?;
238 }
239 }
240 Ok(())
241 }
242
243 async fn heartbeat(&self, id: JobId) -> Result<()> {
244 sqlx::query("UPDATE asyncq_jobs SET heartbeat_at = NOW() WHERE id = $1")
245 .bind(id)
246 .execute(&self.pool)
247 .await
248 .map_err(|e| Error::Backend(e.to_string()))?;
249 Ok(())
250 }
251
252 async fn reap_stuck(&self, older_than: Duration) -> Result<u64> {
253 let cutoff = Utc::now()
254 - chrono::Duration::from_std(older_than).unwrap_or(chrono::Duration::seconds(120));
255 let result = sqlx::query(
256 r#"UPDATE asyncq_jobs
257 SET status = 'pending', heartbeat_at = NULL
258 WHERE status = 'running' AND heartbeat_at < $1"#,
259 )
260 .bind(cutoff)
261 .execute(&self.pool)
262 .await
263 .map_err(|e| Error::Backend(e.to_string()))?;
264 Ok(result.rows_affected())
265 }
266
267 async fn stats(&self, queue: &str) -> Result<QueueStats> {
268 let row = sqlx::query(
269 r#"SELECT
270 COUNT(*) FILTER (WHERE status = 'pending') AS pending,
271 COUNT(*) FILTER (WHERE status = 'running') AS running,
272 COUNT(*) FILTER (WHERE status = 'completed') AS completed,
273 COUNT(*) FILTER (WHERE status = 'failed') AS failed,
274 COUNT(*) FILTER (WHERE status = 'dead') AS dead
275 FROM asyncq_jobs WHERE queue = $1"#,
276 )
277 .bind(queue)
278 .fetch_one(&self.pool)
279 .await
280 .map_err(|e| Error::Backend(e.to_string()))?;
281
282 Ok(QueueStats {
283 queue: queue.to_owned(),
284 pending: row.get::<i64, _>("pending") as u64,
285 running: row.get::<i64, _>("running") as u64,
286 completed: row.get::<i64, _>("completed") as u64,
287 failed: row.get::<i64, _>("failed") as u64,
288 dead: row.get::<i64, _>("dead") as u64,
289 })
290 }
291
292 async fn dead_jobs(&self, queue: &str, limit: i64, offset: i64) -> Result<Vec<JobRecord>> {
293 let rows = sqlx::query(
294 r#"SELECT id, kind, queue, payload, attempt, max_attempts,
295 scheduled_at, created_at, last_error
296 FROM asyncq_jobs
297 WHERE queue = $1 AND status = 'dead'
298 ORDER BY created_at DESC
299 LIMIT $2 OFFSET $3"#,
300 )
301 .bind(queue)
302 .bind(limit)
303 .bind(offset)
304 .fetch_all(&self.pool)
305 .await
306 .map_err(|e| Error::Backend(e.to_string()))?;
307
308 rows.into_iter().map(row_to_record).collect()
309 }
310
311 async fn retry_dead(&self, id: JobId) -> Result<()> {
312 let result = sqlx::query(
313 r#"UPDATE asyncq_jobs
314 SET status = 'pending', scheduled_at = NOW(),
315 last_error = NULL, heartbeat_at = NULL
316 WHERE id = $1 AND status = 'dead'"#,
317 )
318 .bind(id)
319 .execute(&self.pool)
320 .await
321 .map_err(|e| Error::Backend(e.to_string()))?;
322
323 if result.rows_affected() == 0 {
324 return Err(Error::NotFound(id.to_string()));
325 }
326 Ok(())
327 }
328
329 async fn retry_all_dead(&self, queue: &str) -> Result<u64> {
330 let result = sqlx::query(
331 r#"UPDATE asyncq_jobs
332 SET status = 'pending', scheduled_at = NOW(),
333 last_error = NULL, heartbeat_at = NULL
334 WHERE queue = $1 AND status = 'dead'"#,
335 )
336 .bind(queue)
337 .execute(&self.pool)
338 .await
339 .map_err(|e| Error::Backend(e.to_string()))?;
340 Ok(result.rows_affected())
341 }
342}
343
344fn row_to_record(row: sqlx::postgres::PgRow) -> Result<JobRecord> {
347 Ok(JobRecord {
348 id: row.get("id"),
349 kind: row.get("kind"),
350 queue: row.get("queue"),
351 payload: row.get("payload"),
352 attempt: row.get::<i32, _>("attempt") as u32,
353 max_attempts: row.get::<i32, _>("max_attempts") as u32,
354 scheduled_at: row.get("scheduled_at"),
355 created_at: row.get("created_at"),
356 last_error: row.get("last_error"),
357 })
358}