Files
mtusi2026lab6/db/queries/outbox.sql

51 lines
2.0 KiB
SQL

-- name: CreateInitialOutboxEvent :exec
INSERT INTO outbox (job_id, payload, available_at, dedup_key)
SELECT id, attachment_job_payload(id, attachment_id, pipeline_version, 1, created_at)
|| jsonb_strip_nulls(jsonb_build_object(
'traceparent', NULLIF(sqlc.arg(traceparent)::text, ''),
'tracestate', NULLIF(sqlc.arg(tracestate)::text, '')
)),
next_attempt_at, 'initial/' || id
FROM attachment_jobs WHERE id = sqlc.arg(job_id)::uuid;
-- name: EnqueueExpiredLeases :many
WITH expired AS (
SELECT j.*, 'recovery/' || j.id || '/' || j.lease_generation AS recovery_key
FROM attachment_jobs AS j
JOIN attachments AS a ON a.id = j.attachment_id AND a.pipeline_version = j.pipeline_version
WHERE a.status = 'processing' AND j.lease_until <= clock_timestamp()
AND NOT EXISTS (
SELECT 1 FROM outbox AS o WHERE o.dedup_key = 'recovery/' || j.id || '/' || j.lease_generation
)
ORDER BY j.lease_until, j.id
LIMIT sqlc.arg(batch_size)
FOR UPDATE OF j SKIP LOCKED
)
INSERT INTO outbox (job_id, payload, dedup_key)
SELECT e.id, attachment_job_payload(e.id, e.attachment_id, e.pipeline_version,
LEAST(e.attempts_started + 1, e.max_attempts), clock_timestamp())
|| COALESCE((
SELECT jsonb_strip_nulls(jsonb_build_object(
'traceparent', o.payload->>'traceparent', 'tracestate', o.payload->>'tracestate'
))
FROM outbox AS o WHERE o.job_id = e.id
ORDER BY o.created_at DESC, o.id DESC LIMIT 1
), '{}'::jsonb),
e.recovery_key
FROM expired AS e
ON CONFLICT (dedup_key) DO NOTHING
RETURNING *;
-- name: NextPendingOutboxEvent :one
SELECT id, job_id, payload, available_at, sent_at, created_at, dedup_key
FROM outbox
WHERE sent_at IS NULL AND available_at <= statement_timestamp()
ORDER BY available_at, id
LIMIT 1
FOR UPDATE SKIP LOCKED;
-- name: MarkOutboxEventSent :execrows
UPDATE outbox SET sent_at = clock_timestamp()
WHERE id = sqlc.arg(id) AND sent_at IS NULL;