Pathogen workflow status page includes non-default branch runs

[joverlee521]

The shown run is a test run in the zika repo that was not run on the main branch.

Looks like we should only be including runs that are from the default branch

status/pathogen-workflows.sql

Lines 122 to 124 in 507429a

	where
	-- XXX FIXME: this correlated subquery is a hack around a steampipe issue… describe why, maybe explore a better work around.
	head_branch = (select default_branch from repository r where r.repository_full_name = workflow.repository_full_name)

[joverlee521]

Huh, the execution plan does not even include the head_branch filter

Execution plan

Aggregate  (cost=20001330465662.70..20001330465662.71 rows=1 width=32)
  CTE repository
    ->  Nested Loop  (cost=0.01..20000002500000.01 rows=100000000 width=64)
          ->  Foreign Scan on net_http_request  (cost=0.00..20000000000000.00 rows=1000000 width=200)
                Filter: (url = 'https://api.github.com/orgs/nextstrain/repos?per_page=100&sort=full_name'::text)
          ->  Function Scan on jsonb_array_elements r  (cost=0.01..1.01 rows=100 width=32)
  CTE workflow_id
    ->  HashSetOp Except  (cost=0.00..3890006.12 rows=40000 width=44)
          ->  Append  (cost=0.00..3885006.12 rows=1000001 width=44)
                ->  Subquery Scan on "*SELECT* 1"  (cost=0.00..3880006.09 rows=1000000 width=44)
                      ->  Nested Loop  (cost=0.00..3870006.09 rows=1000000 width=40)
                            ->  Subquery Scan on "ANY_subquery"  (cost=0.00..3750002.09 rows=200 width=32)
                                  ->  HashSetOp Except  (cost=0.00..3750000.09 rows=200 width=36)
                                        ->  Append  (cost=0.00..3500000.08 rows=100000003 width=36)
                                              ->  Subquery Scan on "*SELECT* 1_1"  (cost=0.00..3000000.00 rows=100000000 width=36)
                                                    ->  CTE Scan on repository  (cost=0.00..2000000.00 rows=100000000 width=32)
                                              ->  Subquery Scan on "*SELECT* 2_1"  (cost=0.00..0.07 rows=3 width=36)
                                                    ->  Values Scan on "*VALUES*"  (cost=0.00..0.04 rows=3 width=32)
                            ->  Foreign Scan on github_workflow  (cost=0.00..600.00 rows=2 width=300)
                                  Filter: ((path ~~ '.github/workflows/%'::text) AND (repository_full_name = "ANY_subquery".repository_full_name))
                ->  Subquery Scan on "*SELECT* 2"  (cost=0.00..0.02 rows=1 width=44)
                      ->  Result  (cost=0.00..0.01 rows=1 width=36)
  CTE workflow
    ->  Nested Loop  (cost=0.00..24006200.00 rows=1000000 width=160)
          ->  CTE Scan on workflow_id  (cost=0.00..800.00 rows=40000 width=40)
          ->  Foreign Scan on github_workflow github_workflow_1  (cost=0.00..600.00 rows=1 width=600)
                Filter: ((workflow_file_content_json @@ '($."jobs".*."uses" starts with "nextstrain/.github/.github/workflows/pathogen-repo-build.yaml@")'::jsonpath) AND (name <> 'CI'::text) AND (workflow_id.id = id) AND (workflow_id.repository_full_name = repository_full_name))
  CTE run
    ->  WindowAgg  (cost=1300062373.23..1300066435.73 rows=125000 width=408)
          ->  Sort  (cost=1300062373.23..1300062685.73 rows=125000 width=384)
                Sort Key: workflow.repository_full_name, workflow.workflow_id, run_1.run_number DESC
                ->  Nested Loop  (cost=0.00..1300030000.00 rows=125000 width=384)
                      ->  CTE Scan on workflow  (cost=0.00..20000.00 rows=1000000 width=160)
                      ->  Foreign Scan on github_actions_repository_workflow_run run_1  (cost=0.00..1300.00 rows=1 width=1300)
                            Filter: ((created_at >= '2024-02-13 21:50:28+00'::timestamp with time zone) AND (workflow.workflow_id = workflow_id) AND (workflow.repository_full_name = repository_full_name) AND (age((CURRENT_DATE)::timestamp with time zone, created_at) <= 'P90D'::interval))
  ->  CTE Scan on run  (cost=0.00..2812.50 rows=41667 width=24)
        Filter: (relative_workflow_run_number <= 30)

[joverlee521]

Hmm, when I try to add a join with the repository CTE, I get an error

ERROR:  rpc error: code = Internal desc = github: rpc error: code = Internal desc = 'List' call for table 'github_actions_repository_workflow_run' is missing 1 required qual: column:'repository_full_name' operator: =

I don't understand what's going on under the hood in steampipe, so I'm just going to work around it with an additional CTE that filters to the default branch.

[tsibley]

Odd! It definitely worked as a filter when I committed it. I'd bet some Steampipe change between versions broke it. It was treading on unstable ground a bit as a hack to work around a joining issue (which I never followed up with and wrote up…). I wonder if the join approach would work now. Hmm.

[tsibley]

Maybe also a reason to pin the Steampipe version.