$ bin/octocatalog-diff --octocatalog-diff-args --no-truncate-details --to staging belvedere
*******************************************
  File[/etc/prometheus-sql-exporter/swh-scheduler.yml] =>
   parameters =>
     content =>
      @@ -2,5 +2,5 @@
         scope: database
         cluster: secondary
      -  database: ^softwareheritage-scheduler$
      +  database: ^(swh|softwareheritage)-scheduler$
         interval: '1h'
         help: "Software Heritage Scheduler task delay spread. Positive delay for tasks whose execution is late"
      @@ -194,2 +194,26 @@
         values:
           - sum
      +
      +- name: swh_scheduler
      +  scope: database
      +  database: ^(softwareheritage|swh)-scheduler$
      +  interval: '15m'
      +  help: "Software Heritage Scheduler Metrics"
      +  query: |
      +    select l.name, l.instance_name, sm.visit_type,
      +          extract(epoch from sm.last_update) as last_update,
      +           sm.origins_known, sm.origins_enabled, sm.origins_never_visited,
      +           sm.origins_with_pending_changes
      +    from scheduler_metrics sm
      +      inner join listers l on sm.lister_id=l.id
      +    order by l.name, l.instance_name
      +  labels:
      +    - name
      +    - instance_name
      +    - visit_type
      +  values:
      +    - last_update
      +    - origins_known
      +    - origins_enabled
      +    - origins_never_visited
      +    - origins_with_pending_changes
*******************************************
*** End octocatalog-diff on belvedere.internal.softwareheritage.org
$ bin/octocatalog-diff --octocatalog-diff-args --no-truncate-details --to staging db1.internal.staging.swh.network
...
*******************************************
+ File[/etc/prometheus-sql-exporter/swh-scheduler.yml] =>
   parameters =>
      "ensure": "present"
      "group": "root"
      "mode": "0644"
      "notify": "Service[prometheus-sql-exporter]"
      "owner": "root"
      "content": >>>
- name: swh_scheduler_delay
  scope: database
  cluster: secondary
  database: ^(swh|softwareheritage)-scheduler$
  interval: '1h'
  help: "Software Heritage Scheduler task delay spread. Positive delay for tasks whose execution is late"
  query: |
    with task_count_by_bucket as (
      -- get the count of tasks by delay bucket. Tasks are grouped by their
      -- characteristics (type, status, policy, priority, current interval),
      -- then by delay buckets that are 1 hour wide between -24 and +24 hours,
      -- and 1 day wide outside of this range.
      -- A positive delay means the task execution is late wrt scheduling.
      select
        "type",
        status,
        "policy",
        priority,
        current_interval,
        (
          -- select the bucket widths
          case when delay between - 24 * 3600 and 24 * 3600 then
            (ceil(delay / 3600)::bigint) * 3600
          else
            (ceil(delay / (24 * 3600))::bigint) * 24 * 3600
          end
        ) as delay_bucket,
        count(*)
      from
        task
        join lateral (
          -- this is where the "positive = late" convention is set
          select
            extract(epoch from (now() - next_run)) as delay
        ) as d on true
        group by
          "type",
          status,
          "policy",
          priority,
          current_interval,
          delay_bucket
        order by
          "type",
          status,
          "policy",
          priority,
          current_interval,
          delay_bucket
    ),
    delay_bounds as (
      -- get the minimum and maximum delay bucket for each task group. This will
      -- let us generate all the buckets, even the empty ones in the next CTE.
      select
        "type",
        status,
        "policy",
        priority,
        current_interval,
        min(delay_bucket) as min,
        max(delay_bucket) as max
      from
        task_count_by_bucket
      group by
        "type",
        status,
        "policy",
        priority,
        current_interval
    ),
    task_buckets as (
      -- Generate all time buckets for all categories.
      select
        "type",
        status,
        "policy",
        priority,
        current_interval,
        delay_bucket
      from
        delay_bounds
        join lateral (
          -- 1 hour buckets
          select
            generate_series(- 23, 23) * 3600 as delay_bucket
          union
          -- 1 day buckets. The "- 1" is used to make sure we generate an empty
          -- bucket as lowest delay bucket, so prometheus quantile calculations
          -- stay accurate
          select
            generate_series(min / (24 * 3600) - 1, max / (24 * 3600)) * 24 * 3600 as delay_bucket
        ) as buckets on true
    ),
    task_count_for_all_buckets as (
      -- This join merges the non-empty buckets (task_count_by_bucket) with
      -- the full list of buckets (task_buckets).
      -- The join clause can't use the "using (x, y, z)" syntax, as it uses
      -- equality and priority and current_interval can be null. This also
      -- forces us to label all the fields in the select. Ugh.
      select
        task_buckets."type",
        task_buckets.status,
        task_buckets."policy",
        task_buckets.priority,
        task_buckets.current_interval,
        task_buckets.delay_bucket,
        coalesce(count, 0) as count -- make sure empty buckets have a 0 count instead of null
      from
        task_buckets
      left join task_count_by_bucket
        on task_count_by_bucket."type" = task_buckets."type"
        and task_count_by_bucket.status = task_buckets.status
        and task_count_by_bucket. "policy" = task_buckets."policy"
        and task_count_by_bucket.priority is not distinct from task_buckets.priority
        and task_count_by_bucket.current_interval is not distinct from task_buckets.current_interval
        and task_count_by_bucket.delay_bucket = task_buckets.delay_bucket
    ),
    cumulative_buckets as (
      -- Prometheus wants cumulative histograms: for each bucket, the value
      -- needs to be the total of all measurements below the given value (this
      -- allows downsampling by just throwing away some buckets). We use the
      -- "sum over partition" window function to compute this.
      -- Prometheus also expects a "+Inf" bucket for the total count. We
      -- generate it with a null le value so we can sort it after the rest of
      -- the buckets.

      -- cumulative data
      select
        "type",
        status,
        "policy",
        priority,
        current_interval,
        delay_bucket as le,
        sum(count) over (
          partition by
            "type",
            status,
            "policy",
            priority,
            current_interval
          order by
            delay_bucket
        )
      from
        task_count_for_all_buckets
      union all
      -- +Inf data
      select
        "type",
        status,
        "policy",
        priority,
        current_interval,
        null as le,
        sum(count)
      from
        task_count_for_all_buckets
      group by
        "type",
        status,
        "policy",
        priority,
        current_interval
      -- sorting of all buckets
      order by
        "type",
        status,
        "policy",
        priority,
        current_interval,
        le asc NULLS last -- make sure +Inf ends up last
    )
    -- The final query, which at this point just has to make sure that all
    -- labels are text (or the SQL exporter croaks)
    select
      -- we retrieve the backend name here as that's what we have e.g. on the celery side
      (select backend_name from task_type where cumulative_buckets."type" = task_type."type") as task,
      status::text as status,
      policy::text as policy,
      coalesce(priority::text, '') as priority,
      coalesce(current_interval::text, '') as current_interval,
      coalesce(le::text, '+Inf') as le,
      sum
    from
      cumulative_buckets
  labels:
    - task
    - status
    - policy
    - priority
    - current_interval
    - le
  values:
    - sum

- name: swh_scheduler
  scope: database
  database: ^(softwareheritage|swh)-scheduler$
  interval: '15m'
  help: "Software Heritage Scheduler Metrics"
  query: |
    select l.name, l.instance_name, sm.visit_type,
          extract(epoch from sm.last_update) as last_update,
           sm.origins_known, sm.origins_enabled, sm.origins_never_visited,
           sm.origins_with_pending_changes
    from scheduler_metrics sm
      inner join listers l on sm.lister_id=l.id
    order by l.name, l.instance_name
  labels:
    - name
    - instance_name
    - visit_type
  values:
    - last_update
    - origins_known
    - origins_enabled
    - origins_never_visited
    - origins_with_pending_changes
<<<
*******************************************
+ File[/etc/prometheus-sql-exporter/swh-storage.yml] =>
   parameters =>
      "ensure": "present"
      "group": "root"
      "mode": "0644"
      "notify": "Service[prometheus-sql-exporter]"
      "owner": "root"
      "content": >>>
- name: swh_archive_object_count
  help: Software Heritage Archive object counters
  scope: database
  cluster: main
  database: softwareheritage
  labels:
    - object_type
  values:
    - value
  query: >-
    select label as object_type, value from swh_stat_counters()
<<<
*******************************************
*** End octocatalog-diff on db1.internal.staging.swh.network