Revision control

+ -- Creates a sorted array with the revision_ids.
+ -- The revision_ids represent the set of sha1 used when running these benchmarks.
+ WITH input_ids AS (
+ SELECT ARRAY_AGG(x ORDER BY x) AS sorted_ids
+ FROM UNNEST(%(rev_ids)s::int[]) x
+ ),
+ -- Looks up an existing group_id in revisions_mm_groups whose members exactly match the input array.
+ matching_group AS (
+ SELECT rmg.group_id
+ FROM
+ revisions_mm_groups rmg
+ CROSS JOIN
+ input_ids
+ GROUP BY
+ rmg.group_id, input_ids.sorted_ids
+ HAVING
+ ARRAY_AGG(rmg.submodule_revision_id
+ ORDER BY
+ rmg.submodule_revision_id) = input_ids.sorted_ids
+ LIMIT 1
+ ),
+ -- Inserts a new group_id if no matching group was found.
+ new_group AS (
+ INSERT INTO group_ids
+ SELECT
+ WHERE NOT EXISTS (SELECT 1 FROM matching_group)
+ RETURNING group_id
+ ),
+ -- Inserts each revision_id paired with the new group_id into revisions_mm_groups.
+ -- Only runs if new_group produced a row, i.e. no matching group existed.
+ insert_revisions AS (
+ INSERT INTO revisions_mm_groups (submodule_revision_id, group_id)
+ SELECT UNNEST(%(rev_ids)s::int[]), new_group.group_id
+ FROM new_group
+ WHERE NOT EXISTS (SELECT 1 FROM matching_group)
+ RETURNING group_id
+ )
+ -- Returns the group_id, whether it was found or newly created.
+ SELECT group_id FROM matching_group
+ UNION ALL
+ SELECT group_id FROM insert_revisions;