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