| 1 | -- SPDX-License-Identifier: AGPL-3.0-or-later |
| 2 | |
| 3 | -- name: InsertWorkflowJob :one |
| 4 | INSERT INTO workflow_jobs ( |
| 5 | run_id, job_index, job_key, job_name, |
| 6 | runs_on, needs_jobs, if_expr, timeout_minutes, |
| 7 | permissions, job_env |
| 8 | ) VALUES ( |
| 9 | $1, $2, $3, $4, $5, $6, $7, $8, $9, $10 |
| 10 | ) |
| 11 | RETURNING id, run_id, job_index, job_key, job_name, runs_on, |
| 12 | runner_id, needs_jobs, if_expr, timeout_minutes, permissions, |
| 13 | job_env, status, conclusion, cancel_requested, |
| 14 | started_at, completed_at, version, created_at, updated_at; |
| 15 | |
| 16 | -- name: GetWorkflowJobByID :one |
| 17 | SELECT id, run_id, job_index, job_key, job_name, runs_on, |
| 18 | runner_id, needs_jobs, if_expr, timeout_minutes, permissions, |
| 19 | job_env, status, conclusion, cancel_requested, |
| 20 | started_at, completed_at, version, created_at, updated_at |
| 21 | FROM workflow_jobs |
| 22 | WHERE id = $1; |
| 23 | |
| 24 | -- name: UpdateWorkflowJobStatus :one |
| 25 | UPDATE workflow_jobs |
| 26 | SET status = $2, |
| 27 | conclusion = sqlc.narg(conclusion)::check_conclusion, |
| 28 | started_at = sqlc.narg(started_at)::timestamptz, |
| 29 | completed_at = sqlc.narg(completed_at)::timestamptz, |
| 30 | version = version + 1, |
| 31 | updated_at = now() |
| 32 | WHERE id = $1 |
| 33 | RETURNING id, run_id, job_index, job_key, job_name, runs_on, |
| 34 | runner_id, needs_jobs, if_expr, timeout_minutes, permissions, |
| 35 | job_env, status, conclusion, cancel_requested, |
| 36 | started_at, completed_at, version, created_at, updated_at; |
| 37 | |
| 38 | -- name: RequestWorkflowJobCancel :one |
| 39 | UPDATE workflow_jobs |
| 40 | SET cancel_requested = true, |
| 41 | status = CASE |
| 42 | WHEN status = 'queued' THEN 'cancelled'::workflow_job_status |
| 43 | ELSE status |
| 44 | END, |
| 45 | conclusion = CASE |
| 46 | WHEN status = 'queued' THEN 'cancelled'::check_conclusion |
| 47 | ELSE conclusion |
| 48 | END, |
| 49 | started_at = CASE |
| 50 | WHEN status = 'queued' THEN COALESCE(started_at, now()) |
| 51 | ELSE started_at |
| 52 | END, |
| 53 | completed_at = CASE |
| 54 | WHEN status = 'queued' THEN COALESCE(completed_at, now()) |
| 55 | ELSE completed_at |
| 56 | END, |
| 57 | version = version + 1, |
| 58 | updated_at = now() |
| 59 | WHERE id = $1 |
| 60 | AND status IN ('queued', 'running') |
| 61 | AND (status = 'queued' OR cancel_requested = false) |
| 62 | RETURNING id, run_id, job_index, job_key, job_name, runs_on, |
| 63 | runner_id, needs_jobs, if_expr, timeout_minutes, permissions, |
| 64 | job_env, status, conclusion, cancel_requested, |
| 65 | started_at, completed_at, version, created_at, updated_at; |
| 66 | |
| 67 | -- name: RequestWorkflowRunCancel :many |
| 68 | UPDATE workflow_jobs |
| 69 | SET cancel_requested = true, |
| 70 | status = CASE |
| 71 | WHEN status = 'queued' THEN 'cancelled'::workflow_job_status |
| 72 | ELSE status |
| 73 | END, |
| 74 | conclusion = CASE |
| 75 | WHEN status = 'queued' THEN 'cancelled'::check_conclusion |
| 76 | ELSE conclusion |
| 77 | END, |
| 78 | started_at = CASE |
| 79 | WHEN status = 'queued' THEN COALESCE(started_at, now()) |
| 80 | ELSE started_at |
| 81 | END, |
| 82 | completed_at = CASE |
| 83 | WHEN status = 'queued' THEN COALESCE(completed_at, now()) |
| 84 | ELSE completed_at |
| 85 | END, |
| 86 | version = version + 1, |
| 87 | updated_at = now() |
| 88 | WHERE run_id = $1 |
| 89 | AND status IN ('queued', 'running') |
| 90 | AND (status = 'queued' OR cancel_requested = false) |
| 91 | RETURNING id, run_id, job_index, job_key, job_name, runs_on, |
| 92 | runner_id, needs_jobs, if_expr, timeout_minutes, permissions, |
| 93 | job_env, status, conclusion, cancel_requested, |
| 94 | started_at, completed_at, version, created_at, updated_at; |
| 95 | |
| 96 | -- name: CountRunningJobsForRunner :one |
| 97 | SELECT COUNT(*)::integer |
| 98 | FROM workflow_jobs |
| 99 | WHERE runner_id = sqlc.arg(runner_id)::bigint AND status = 'running'; |
| 100 | |
| 101 | -- name: ClaimQueuedWorkflowJob :one |
| 102 | WITH candidate AS ( |
| 103 | SELECT j.id |
| 104 | FROM workflow_jobs j |
| 105 | JOIN workflow_runs r ON r.id = j.run_id |
| 106 | JOIN repos repo ON repo.id = r.repo_id |
| 107 | LEFT JOIN actions_site_policy sp ON sp.id = true |
| 108 | LEFT JOIN actions_org_policies op ON op.org_id = repo.owner_org_id |
| 109 | LEFT JOIN actions_repo_policies rp ON rp.repo_id = r.repo_id |
| 110 | WHERE j.status = 'queued' |
| 111 | AND r.status IN ('queued', 'running') |
| 112 | AND (r.need_approval = false OR r.approved_by_user_id IS NOT NULL) |
| 113 | AND j.cancel_requested = false |
| 114 | AND j.runner_id IS NULL |
| 115 | AND CASE |
| 116 | WHEN COALESCE(sp.actions_enabled, true) = false THEN false |
| 117 | WHEN COALESCE(rp.actions_enabled, 'inherit'::actions_policy_state) = 'enabled' THEN true |
| 118 | WHEN COALESCE(rp.actions_enabled, 'inherit'::actions_policy_state) = 'disabled' THEN false |
| 119 | WHEN COALESCE(op.actions_enabled, 'inherit'::actions_policy_state) = 'enabled' THEN true |
| 120 | WHEN COALESCE(op.actions_enabled, 'inherit'::actions_policy_state) = 'disabled' THEN false |
| 121 | ELSE COALESCE(sp.actions_enabled, true) |
| 122 | END |
| 123 | AND ( |
| 124 | SELECT COUNT(*)::integer |
| 125 | FROM workflow_jobs running_job |
| 126 | JOIN workflow_runs running_run ON running_run.id = running_job.run_id |
| 127 | WHERE running_job.status = 'running' |
| 128 | AND running_run.repo_id = r.repo_id |
| 129 | ) < COALESCE(rp.max_repo_concurrent_jobs, op.max_repo_concurrent_jobs, sp.max_repo_concurrent_jobs, 20) |
| 130 | AND ( |
| 131 | SELECT COUNT(*)::integer |
| 132 | FROM workflow_jobs running_job |
| 133 | JOIN workflow_runs running_run ON running_run.id = running_job.run_id |
| 134 | JOIN repos running_repo ON running_repo.id = running_run.repo_id |
| 135 | WHERE running_job.status = 'running' |
| 136 | AND ( |
| 137 | (repo.owner_user_id IS NOT NULL AND running_repo.owner_user_id = repo.owner_user_id) |
| 138 | OR (repo.owner_org_id IS NOT NULL AND running_repo.owner_org_id = repo.owner_org_id) |
| 139 | ) |
| 140 | ) < COALESCE(rp.max_owner_concurrent_jobs, op.max_owner_concurrent_jobs, sp.max_owner_concurrent_jobs, 100) |
| 141 | AND (j.runs_on = '' OR j.runs_on = ANY(sqlc.arg(labels)::text[])) |
| 142 | AND NOT EXISTS ( |
| 143 | SELECT 1 |
| 144 | FROM workflow_jobs dep |
| 145 | WHERE dep.run_id = j.run_id |
| 146 | AND dep.job_key = ANY(j.needs_jobs) |
| 147 | AND (dep.status <> 'completed' OR dep.conclusion <> 'success') |
| 148 | ) |
| 149 | AND NOT EXISTS ( |
| 150 | SELECT 1 |
| 151 | FROM workflow_runs blocker |
| 152 | WHERE r.concurrency_group <> '' |
| 153 | AND blocker.repo_id = r.repo_id |
| 154 | AND blocker.concurrency_group = r.concurrency_group |
| 155 | AND blocker.id <> r.id |
| 156 | AND blocker.status IN ('queued', 'running') |
| 157 | AND (blocker.created_at, blocker.id) < (r.created_at, r.id) |
| 158 | AND EXISTS ( |
| 159 | SELECT 1 |
| 160 | FROM workflow_jobs blocker_job |
| 161 | WHERE blocker_job.run_id = blocker.id |
| 162 | AND blocker_job.status IN ('queued', 'running') |
| 163 | AND blocker_job.cancel_requested = false |
| 164 | ) |
| 165 | ) |
| 166 | ORDER BY j.created_at ASC, j.id ASC |
| 167 | FOR UPDATE OF j SKIP LOCKED |
| 168 | LIMIT 1 |
| 169 | ), |
| 170 | claimed AS ( |
| 171 | UPDATE workflow_jobs j |
| 172 | SET runner_id = sqlc.arg(runner_id)::bigint, |
| 173 | status = 'running', |
| 174 | started_at = COALESCE(j.started_at, now()), |
| 175 | version = j.version + 1, |
| 176 | updated_at = now() |
| 177 | FROM candidate c |
| 178 | WHERE j.id = c.id |
| 179 | RETURNING j.id, j.run_id, j.job_index, j.job_key, j.job_name, j.runs_on, |
| 180 | j.runner_id, j.needs_jobs, j.if_expr, j.timeout_minutes, |
| 181 | j.permissions, j.job_env, j.status, j.conclusion, |
| 182 | j.cancel_requested, j.started_at, j.completed_at, j.version, |
| 183 | j.created_at, j.updated_at |
| 184 | ) |
| 185 | SELECT c.id, c.run_id, c.job_index, c.job_key, c.job_name, c.runs_on, |
| 186 | c.runner_id, c.needs_jobs, c.if_expr, c.timeout_minutes, |
| 187 | c.permissions, c.job_env, c.status, c.conclusion, |
| 188 | c.cancel_requested, c.started_at, c.completed_at, c.version, |
| 189 | c.created_at, c.updated_at, |
| 190 | r.repo_id, r.run_index, r.workflow_file, r.workflow_name, |
| 191 | r.head_sha, r.head_ref, r.event, r.event_payload, |
| 192 | COALESCE(owner_user.username, owner_org.slug)::text AS repo_owner, |
| 193 | repo.name AS repo_name |
| 194 | FROM claimed c |
| 195 | JOIN workflow_runs r ON r.id = c.run_id |
| 196 | JOIN repos repo ON repo.id = r.repo_id |
| 197 | LEFT JOIN users owner_user ON owner_user.id = repo.owner_user_id |
| 198 | LEFT JOIN orgs owner_org ON owner_org.id = repo.owner_org_id; |
| 199 | |
| 200 | -- name: ListJobsForRun :many |
| 201 | SELECT id, run_id, job_index, job_key, job_name, runs_on, status, |
| 202 | conclusion, cancel_requested, needs_jobs, started_at, completed_at, created_at, updated_at |
| 203 | FROM workflow_jobs |
| 204 | WHERE run_id = $1 |
| 205 | ORDER BY job_index ASC; |
| 206 | |
| 207 | -- name: ListQueuedWorkflowJobRunsOn :many |
| 208 | SELECT |
| 209 | COALESCE(NULLIF(j.runs_on, ''), '(none)')::text AS runs_on, |
| 210 | COUNT(*)::integer AS queued_jobs, |
| 211 | COUNT(DISTINCT wr.id)::integer AS matching_runner_count, |
| 212 | MIN(j.created_at)::timestamptz AS oldest_queued_at |
| 213 | FROM workflow_jobs j |
| 214 | LEFT JOIN workflow_runners wr |
| 215 | ON (j.runs_on = '' OR j.runs_on = ANY(wr.labels)) |
| 216 | AND wr.status IN ('idle', 'busy') |
| 217 | AND wr.draining_at IS NULL |
| 218 | AND wr.revoked_at IS NULL |
| 219 | WHERE j.status = 'queued' |
| 220 | AND j.cancel_requested = false |
| 221 | AND j.runner_id IS NULL |
| 222 | GROUP BY COALESCE(NULLIF(j.runs_on, ''), '(none)') |
| 223 | ORDER BY queued_jobs DESC, runs_on ASC; |
| 224 |