lock_operation.sql

SELECT
	operation.*
FROM
	operations operation
LEFT JOIN LATERAL (
	SELECT
		_r.state
	FROM
		operations_results _r
	WHERE
		_r.operation_id = operation.id
	ORDER BY
		_r.created_at DESC
	LIMIT
		1
) result ON TRUE
WHERE
	operation.run_after <= now()::timestamptz
	AND operation.name = $1::text
	AND operation.queue = coalesce($2, '')::text
	AND (result IS NULL OR result.state NOT IN ('completed', 'failed'))
ORDER BY
	operation.priority DESC,
	operation.run_after
LIMIT
	1
FOR NO KEY UPDATE OF
	operation
SKIP LOCKED
;