Skip to content

Backfill vs_code_settings uuid with default value

What does this MR do and why?

Backfill the vs_code_settings table's uuid field with default randomly generated UUID.

Why?

We added the uuid field after introducing the vs_code_settings table without a not null constraint. This merge request is the first step in enforcing a not null constraint as described in the NOT NULL constraint guidelines. https://docs.gitlab.com/ee/development/database/not_null_constraints.html

How to set up and validate locally

  1. Create one or more vs_code_settings records without a UUID set.
  2. Run the migration introduced by this MR.
  3. The UUID field should have a value after running this migration.

Migration details

Queries and execution plan

-- Query 1: First query to select rows for migration. 
SELECT
    "vs_code_settings"."id"
FROM
    "vs_code_settings"
WHERE
    "vs_code_settings"."id" BETWEEN 19 AND 21
    AND "vs_code_settings"."uuid" IS NULL
ORDER BY
    "vs_code_settings"."id" ASC
LIMIT 1

-- Query 1: Execution plan
Limit  (cost=0.01..0.02 rows=1 width=8) (actual time=0.042..0.043 rows=0 loops=1)
   Buffers: shared hit=3
   I/O Timings: read=0.000 write=0.000
   ->  Sort  (cost=0.01..0.02 rows=1 width=8) (actual time=0.041..0.041 rows=0 loops=1)
         Sort Key: vs_code_settings.id
         Sort Method: quicksort  Memory: 25kB
         Buffers: shared hit=3
         I/O Timings: read=0.000 write=0.000
         ->  Seq Scan on public.vs_code_settings  (cost=0.00..0.00 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=1)
               Filter: ((vs_code_settings.uuid IS NULL) AND (vs_code_settings.id >= 19) AND (vs_code_settings.id <= 21))
               Rows Removed by Filter: 0
               I/O Timings: read=0.000 write=0.000
-- Query 2: An upsert operation that updates every record with a nil uuid
INSERT INTO "vs_code_settings" ("id", "user_id", "created_at", "updated_at", "setting_type", "content", "uuid", "version")
    VALUES (20, 1, '2023-11-22 11:40:32.627558', '2023-12-12 09:50:56.747793', 'keybindings', '{"mac":"[]"}', '1f385ab6-2ce9-4441-8c73-55da36837e2b', 2)
ON CONFLICT ("id")
    DO UPDATE SET
        "user_id" = excluded."user_id", "created_at" = excluded."created_at", "updated_at" = excluded."updated_at", "setting_type" = excluded."setting_type", "content" = excluded."content", "uuid" = excluded."uuid", "version" = excluded."version"
    RETURNING
        "id"
-- Query 2: Execution plan
ModifyTable on public.vs_code_settings  (cost=0.00..0.01 rows=1 width=116) (actual time=1.808..1.810 rows=1 loops=1)  
   Buffers: shared hit=24 read=3 dirtied=5 written=3  
   I/O Timings: read=1.054 write=0.000  
   ->  Result  (cost=0.00..0.01 rows=1 width=116) (actual time=0.002..0.002 rows=1 loops=1)  
         I/O Timings: read=0.000 write=0.000  

How big is the table on gitlab.com, any duration estimation?

The vs_code_settings table has two rows in gitlabdotcom.

Is this feature also available on self managed?

Yes, this feature is also available on self-managed instances, however, it is not enabled yet.

Will this run EE or on core?

This migration will run on Core. It is for the Web IDE which is available in all product tiers.

MR acceptance checklist

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

Related to #432992 (closed)

Edited by Enrique Alcántara

Merge request reports

Loading