Fix project approval usage data count (!81823) · Merge requests · GitLab.org / GitLab

What does this MR do and why?

Problem

The following metrics reported the actual value instead of the query when calling it from Gitlab::UsageDataQueries.data:

 :approval_project_rules_with_target_branch=>0,
 :approval_project_rules_with_more_approvers_than_required=>0,
 :approval_project_rules_with_less_approvers_than_required=>0,
 :approval_project_rules_with_exact_required_approvers=>0}

Reason

The count_approval_rules_with_users method uses a count(relation,...) on a query that is using group-by. The structure it returns is something like {"id1" => 123, "id2" => 456} and we the call .size on it afterwards. This is causing the issue to return values instead of queries. To translate it into a query, we'd need something like SELECT COUNT(*) FROM (SELECT COUNT() ...) but we can't express this right now with our count helper method because:

count returns the SQL query (when used with the UsageDataQueries API), which we can't use as input for another count call
count doesn't allow to modify the table it counts on (see proposal for more info)

I've moved the metrics to an instrumentation class and have overriden the to_sql and value methods to output the correct value and query

Note: This MR doesn't change the logic of the query or has an affect of the usage data. It only provides the query now for the usage data query api. Original MR introducing the metrics: !36737 (merged)

Existing Queries

Note: They are the same as we already had for !36737 (merged). Generated via:

 Gitlab::Usage::Metrics::Instrumentations::ApprovalProjectRulesWithUserMetric.new(time_frame: 'none').value

Minimum

 SELECT MIN("approval_project_rules"."id") FROM "approval_project_rules" WHERE "approval_project_rules"."rule_type" = 0

https://console.postgres.ai/shared/3fe4b066-8a53-4fdb-beab-59769f2d53f2

Maximum

SELECT MAX("approval_project_rules"."id") FROM "approval_project_rules" WHERE "approval_project_rules"."rule_type" = 0

https://console.postgres.ai/shared/bfcc173c-e178-4a97-8929-f878aa1b9bbd

Actual Minimum

SELECT MIN("approval_project_rules"."id") FROM "approval_project_rules" INNER JOIN approval_project_rules_users ON approval_project_rules_users.approval_project_rule_id = approval_project_rules.id WHERE "approval_project_rules"."rule_type" = 0

https://console.postgres.ai/shared/601e28c6-aad2-4319-94be-9de67b916cfd

Actual Maximum

SELECT MAX("approval_project_rules"."id") FROM "approval_project_rules" INNER JOIN approval_project_rules_users ON approval_project_rules_users.approval_project_rule_id = approval_project_rules.id WHERE "approval_project_rules"."rule_type" = 0

https://console.postgres.ai/shared/8918301f-8e3a-4310-b77d-bbbaf83e5b69

Query with More approvers

SELECT COUNT("approval_project_rules"."id") AS count_id, "approval_project_rules"."id", "approval_project_rules"."id" AS approval_project_rules_id FROM "approval_project_rules" INNER JOIN approval_project_rules_users ON approval_project_rules_users.approval_project_rule_id = approval_project_rules.id WHERE "approval_project_rules"."rule_type" = 0 AND "approval_project_rules"."id" >= 0 AND "approval_project_rules"."id" < 10000 GROUP BY "approval_project_rules"."id" HAVING (COUNT(approval_project_rules_users) > approvals_required)

https://console.postgres.ai/shared/1b80a99f-1266-4f96-9bbc-53048ab0b114

Query with less approvers

SELECT COUNT("approval_project_rules"."id") AS count_id, "approval_project_rules"."id", "approval_project_rules"."id" AS approval_project_rules_id FROM "approval_project_rules" INNER JOIN approval_project_rules_users ON approval_project_rules_users.approval_project_rule_id = approval_project_rules.id WHERE "approval_project_rules"."rule_type" = 0 AND "approval_project_rules"."id" >= 0 AND "approval_project_rules"."id" < 10000 GROUP BY "approval_project_rules"."id" HAVING (COUNT(approval_project_rules_users) < approvals_required)

https://console.postgres.ai/shared/9d720fed-ed96-4e45-bbaf-19083a67a3f2

Query with equal approvers

SELECT COUNT("approval_project_rules"."id") AS count_id, "approval_project_rules"."id", "approval_project_rules"."id" AS approval_project_rules_id FROM "approval_project_rules" INNER JOIN approval_project_rules_users ON approval_project_rules_users.approval_project_rule_id = approval_project_rules.id WHERE "approval_project_rules"."rule_type" = 0 AND "approval_project_rules"."id" >= 0 AND "approval_project_rules"."id" < 10000 GROUP BY "approval_project_rules"."id" HAVING (COUNT(approval_project_rules_users) = approvals_required)

https://console.postgres.ai/shared/b7bfc8d7-41e6-4177-a9fc-70bb5a5cf784

New Queries

Note: this is only executed on the Datawarehouse

With More approvals required

SELECT
    COUNT(*)
FROM (
    SELECT
        COUNT("approval_project_rules"."id")
    FROM
        "approval_project_rules"
        INNER JOIN approval_project_rules_users ON approval_project_rules_users.approval_project_rule_id = approval_project_rules.id
    WHERE
        "approval_project_rules"."rule_type" = 0
    GROUP BY
        "approval_project_rules"."id"
    HAVING (COUNT(approval_project_rules_users) > approvals_required)) subquery

https://console.postgres.ai/shared/9cef3076-30ae-4349-b991-71ed70942ea0

With less approvals required

SELECT
    COUNT(*)
FROM (
    SELECT
        COUNT("approval_project_rules"."id")
    FROM
        "approval_project_rules"
        INNER JOIN approval_project_rules_users ON approval_project_rules_users.approval_project_rule_id = approval_project_rules.id
    WHERE
        "approval_project_rules"."rule_type" = 0
    GROUP BY
        "approval_project_rules"."id"
    HAVING (COUNT(approval_project_rules_users) < approvals_required)) subquery

https://console.postgres.ai/shared/c05263f3-ae92-40e3-a3b2-2de12103540f

With exact approvals required

SELECT
    COUNT(*)
FROM (
    SELECT
        COUNT("approval_project_rules"."id")
    FROM
        "approval_project_rules"
        INNER JOIN approval_project_rules_users ON approval_project_rules_users.approval_project_rule_id = approval_project_rules.id
    WHERE
        "approval_project_rules"."rule_type" = 0
    GROUP BY
        "approval_project_rules"."id"
    HAVING (COUNT(approval_project_rules_users) = approvals_required)) subquery

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/9075/commands/32019

Screenshots or screen recordings

These are strongly recommended to assist reviewers and reduce the time to merge your change.

How to set up and validate locally

Numbered steps to set up and validate the change are strongly suggested.

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

I have evaluated the MR acceptance checklist for this MR.

Edited Mar 10, 2022 by Nicolas Dular

Fix project approval usage data count