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

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 (closed)

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

• Changelog entry
• Documentation (if required)
• Code review guidelines
• Merge request performance guidelines
• Style guides
• Database guides
• Separation of EE specific content

Availability and Testing

• Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process.
• Tested in all supported browsers
• Informed Infrastructure department of a default or new setting change, if applicable per definition of done

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)