forked from mtusi2026/mtusi2026lab6
51 lines
2.0 KiB
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;
|