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