Resolve "Traversal IDs column sync" [RUN ALL RSPEC] [RUN AS-IF-FOSS]

Merged Alex Pooley requested to merge 300377-traversal-ids-column-sync into master

What does this MR do?

Ensure the Namespace#traversal_ids column is set with the sequence of namespaces between root_ancestor and self when a group is created or updated.

The Namespace::TraversalHierarchy.sync_traversal_ids! query has changed to include a FOR UPDATE to maintain atomic commits.

View the SQL
UPDATE 
  namespaces 
SET 
  traversal_ids = cte.traversal_ids 
FROM 
  (
    WITH RECURSIVE cte(id, traversal_ids, cycle) AS (
      VALUES 
        (2, ARRAY[2], false) 
      UNION ALL 
      SELECT 
        n.id, 
        cte.traversal_ids || n.id, 
        n.id = ANY(cte.traversal_ids) 
      FROM 
        namespaces n, 
        cte 
      WHERE 
        n.parent_id = cte.id 
        AND NOT cycle
    ) 
    SELECT 
      id, 
      traversal_ids 
    FROM 
      cte FOR 
    UPDATE 
      ) as cte 
WHERE 
  namespaces.id = cte.id 
  AND namespaces.traversal_ids <> cte.traversal_ids

This move is being made as part of a larger effort to improve performance of namespace queries by replacing recursive queries with linear queries.

There is some more general information in the epic at &5296

The work has been created under a new module to satisfy this new rubocop rule !51236 (merged)

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

Security

If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:

  • Label as security and @ mention @gitlab-com/gl-security/appsec
  • The MR includes necessary changes to maintain consistency between UI, API, email, or other methods
  • Security reports checked/validated by a reviewer from the AppSec team

Related to #300377 (closed)

Edited by Thong Kuah