Alex Pooley requested to merge 333576-operator-does-not-exist-integer-bigint-in-app-models-namespace-traversal_hierarchy-rb into master Aug 01, 2021

What does this MR do?

Currently the namespaces.id column is an integer type. Some self managed installations are running with namespaces.id as a bigint. Those installations with a bigint column type are failing due to type incompatability between the namespaces.id column and namespaces.traversal_ids. This MR solves the problem by casting id and traversal_ids to bigint and bigint[] respectively to handle either case where namespaces.id is an integer as expect, or a bigint.

Note that there is a plan move namespaces.id and namespaces.traversal_ids to bigint one day.

All profiling was performed after a reset command in postgres.ai.

Query Changes

`#sync_traversal_ids!`

Namespace::TraversalHierarchy.new(Group.find(9970)).sync_traversal_ids!

New - With bigint cast

UPDATE 
  namespaces 
SET 
  traversal_ids = cte.traversal_ids 
FROM 
  (
    WITH RECURSIVE cte(id, traversal_ids, cycle) AS (
      VALUES 
        (
          9970::bigint, ARRAY[9970]::bigint[], 
          false
        ) 
      UNION ALL 
      SELECT 
        n.id, 
        cte.traversal_ids || n.id::bigint, 
        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
  ) as cte 
WHERE 
  namespaces.id = cte.id 
  AND namespaces.traversal_ids::bigint[] <> cte.traversal_ids

Time: 38.718 ms  
  - planning: 3.222 ms  
  - execution: 35.496 ms  
    - I/O read: 30.824 ms  
    - I/O write: 0.000 ms  
  
Shared buffers:  
  - hits: 1452 (~11.30 MiB) from the buffer pool  
  - reads: 798 (~6.20 MiB) from the OS file cache, including disk I/O  
  - dirtied: 3 (~24.00 KiB)  
  - writes: 0

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/5637/commands/19067

Old - without cast

UPDATE 
  namespaces 
SET 
  traversal_ids = cte.traversal_ids 
FROM 
  (
    WITH RECURSIVE cte(id, traversal_ids, cycle) AS (
      VALUES 
        (
          9970, ARRAY[9970], 
          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
  ) as cte 
WHERE 
  namespaces.id = cte.id 
  AND namespaces.traversal_ids <> cte.traversal_ids

Time: 37.819 ms  
  - planning: 2.925 ms  
  - execution: 34.894 ms  
    - I/O read: 30.390 ms  
    - I/O write: 0.000 ms  
  
Shared buffers:  
  - hits: 1448 (~11.30 MiB) from the buffer pool  
  - reads: 798 (~6.20 MiB) from the OS file cache, including disk I/O  
  - dirtied: 3 (~24.00 KiB)  
  - writes: 0

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/5637/commands/19070

`#incorrect_traversal_ids`

Namespace::TraversalHierarchy.new(Group.find(9970)).incorrect_traversal_ids

New - With bigint cast

SELECT 
  "namespaces".* 
FROM 
  "namespaces" 
  INNER JOIN (
    WITH RECURSIVE cte(id, traversal_ids, cycle) AS (
      VALUES 
        (
          9970::bigint, ARRAY[9970]::bigint[], 
          false
        ) 
      UNION ALL 
      SELECT 
        n.id, 
        cte.traversal_ids || n.id::bigint, 
        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
  ) as cte ON namespaces.id = cte.id 
WHERE 
  (
    namespaces.traversal_ids::bigint[] <> cte.traversal_ids
  )

Time: 41.784 ms  
  - planning: 3.563 ms  
  - execution: 38.221 ms  
    - I/O read: 32.805 ms  
    - I/O write: 0.000 ms  
  
Shared buffers:  
  - hits: 1452 (~11.30 MiB) from the buffer pool  
  - reads: 798 (~6.20 MiB) from the OS file cache, including disk I/O  
  - dirtied: 3 (~24.00 KiB)  
  - writes: 0

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/5637/commands/19073

Old - without cast

SELECT 
  "namespaces".* 
FROM 
  "namespaces" 
  INNER JOIN (
    WITH RECURSIVE cte(id, traversal_ids, cycle) AS (
      VALUES 
        (
          9970, ARRAY[9970], 
          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
  ) as cte ON namespaces.id = cte.id 
WHERE 
  (
    namespaces.traversal_ids <> cte.traversal_ids
  )

Time: 37.996 ms  
  - planning: 3.091 ms  
  - execution: 34.905 ms  
    - I/O read: 30.787 ms  
    - I/O write: 0.000 ms  
  
Shared buffers:  
  - hits: 1448 (~11.30 MiB) from the buffer pool  
  - reads: 798 (~6.20 MiB) from the OS file cache, including disk I/O  
  - dirtied: 3 (~24.00 KiB)  
  - writes: 0

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/5637/commands/19078

How to setup and validate locally (strongly suggested)

How to reproduce the error.

gdk psql
ALTER TABLE namespaces ALTER COLUMN id TYPE bigint;
rspec spec/models/namespace/traversal_hierarchy_spec.rb

Does this MR meet the acceptance criteria?

Conformity

I have included changelog trailers, or none are needed. (Does this MR need a changelog?)
I have added/updated documentation, or it's not needed. (Is documentation required?)
I have properly separated EE content from FOSS, or this MR is FOSS only. (Where should EE code go?)
I have added information for database reviewers in the MR description, or it's not needed. (Does this MR have database related changes?)
I have self-reviewed this MR per code review guidelines.
This MR does not harm performance, or I have asked a reviewer to help assess the performance impact. (Merge request performance guidelines)
I have followed the style guides.
This change is backwards compatible across updates, or this does not apply.

Availability and Testing

I have added/updated tests following the Testing Guide, or it's not needed. (Consider all test levels. See the Test Planning Process.)
I have tested this MR in all supported browsers, or it's not needed.
I have informed the Infrastructure department of a default or new setting change per definition of done, or it's not needed.

Related to #333576 (closed)

Edited Aug 02, 2021 by Alex Pooley

Resolve "operator does not exist: integer[] || bigint in app/models/namespace/traversal_hierarchy.rb"