Skip to content

Migrate build_timeout to respect the max

Allison Browne requested to merge 353972-data-migration-fix into master

What does this MR do and why?

Problem

In this commit, we introduced a validation for the build_timeout on project without actually evaluating that existing data conformed to the validation. That causes applications errors when we go to save those records, as described in #353972.

Related to #353972

Solution

Here we set all project build timeout to be 1 second under 1 month (since the validation specifies that the build timeout must be less than a month). This is expected to update 161 rows.

How to set up and validate locally

  1. Use the rails console: 
    rails c
  2. Create some data skipping the validation: 
     Project.update_all(build_timeout: 2.months)
  3. Ensure records are returned when searching for ones that violate the validation: 
    Project.where("build_timeout >= #{1.month.to_i}").count
=> 66
  4. Outside the rails console, migrate the database 
    rake:db:migrate
  5. Back in the rails console, ensure no records are returned: 
    Project.where("build_timeout >= #{1.month.to_i}").count
=> 0

Database

Migration output

▶ rake db:migrate
main: == [advisory_lock_connection] object_id: 231220, pg_backend_pid: 22847
main: == 20231003142534 AddBuildTimeoutIndex: migrating =============================
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.1957s
main: -- index_exists?(:projects, :id, {:where=>"build_timeout >= 2629746", :name=>"index_projects_on_id_where_build_timeout_geq_than_2629746", :algorithm=>:concurrently})
main:    -> 0.0180s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0009s
main: -- add_index(:projects, :id, {:where=>"build_timeout >= 2629746", :name=>"index_projects_on_id_where_build_timeout_geq_than_2629746", :algorithm=>:concurrently})
main:    -> 0.0050s
main: -- execute("RESET statement_timeout")
main:    -> 0.0002s
main: == 20231003142534 AddBuildTimeoutIndex: migrated (0.2380s) ====================

main: == [advisory_lock_connection] object_id: 231220, pg_backend_pid: 22847
ci: == [advisory_lock_connection] object_id: 231460, pg_backend_pid: 22849
ci: == 20231003142534 AddBuildTimeoutIndex: migrating =============================
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0007s
ci: -- index_exists?(:projects, :id, {:where=>"build_timeout >= 2629746", :name=>"index_projects_on_id_where_build_timeout_geq_than_2629746", :algorithm=>:concurrently})
ci:    -> 0.0240s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0002s
ci: -- add_index(:projects, :id, {:where=>"build_timeout >= 2629746", :name=>"index_projects_on_id_where_build_timeout_geq_than_2629746", :algorithm=>:concurrently})
ci:    -> 0.0068s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0005s
ci: == 20231003142534 AddBuildTimeoutIndex: migrated (0.0500s) ====================

ci: == [advisory_lock_connection] object_id: 231460, pg_backend_pid: 22849
main: == [advisory_lock_connection] object_id: 231720, pg_backend_pid: 22852
main: == 20231003142706 LowerProjectBuildTimeoutToRespectMaxValidation: migrating ===
main: == 20231003142706 LowerProjectBuildTimeoutToRespectMaxValidation: migrated (0.0502s)

main: == [advisory_lock_connection] object_id: 231720, pg_backend_pid: 22852
ci: == [advisory_lock_connection] object_id: 231840, pg_backend_pid: 22854
ci: == 20231003142706 LowerProjectBuildTimeoutToRespectMaxValidation: migrating ===
ci: -- The migration is skipped since it modifies the schemas: [:gitlab_main].
ci: -- This database can only apply migrations in one of the following schemas: [:gitlab_ci, :gitlab_internal, :gitlab_shared].
ci: == 20231003142706 LowerProjectBuildTimeoutToRespectMaxValidation: migrated (0.0140s)

ci: == [advisory_lock_connection] object_id: 231840, pg_backend_pid: 22854
main: == [advisory_lock_connection] object_id: 232000, pg_backend_pid: 22857
main: == 20231003145757 RemoveBuildTimeoutIndex: migrating ==========================
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0008s
main: -- indexes(:projects)
main:    -> 0.0347s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0007s
main: -- remove_index(:projects, {:algorithm=>:concurrently, :name=>"index_projects_on_id_where_build_timeout_geq_than_2629746"})
main:    -> 0.0037s
main: -- execute("RESET statement_timeout")
main:    -> 0.0004s
main: == 20231003145757 RemoveBuildTimeoutIndex: migrated (0.0523s) =================

main: == [advisory_lock_connection] object_id: 232000, pg_backend_pid: 22857
ci: == [advisory_lock_connection] object_id: 279120, pg_backend_pid: 22860
ci: == 20231003145757 RemoveBuildTimeoutIndex: migrating ==========================
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0010s
ci: -- indexes(:projects)
ci:    -> 0.0249s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0003s
ci: -- remove_index(:projects, {:algorithm=>:concurrently, :name=>"index_projects_on_id_where_build_timeout_geq_than_2629746"})
ci:    -> 0.0021s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0003s
ci: == 20231003145757 RemoveBuildTimeoutIndex: migrated (0.0494s) =================

ci: == [advisory_lock_connection] object_id: 279120, pg_backend_pid: 22860

Queries in update migration

SELECT "projects"."id" FROM "projects" WHERE (build_timeout >= 2629746) AND "projects"."id" >= 1 ORDER BY "projects"."id" ASC LIMIT 1
UPDATE "projects" SET "build_timeout" = 2629745 WHERE (build_timeout >= 2629746) AND "projects"."id" >= 1 AND "projects"."id" < 14

Update query plan

 ModifyTable on public.projects  (cost=0.14..3.17 rows=0 width=0) (actual time=0.022..0.023 rows=0 loops=1)
   Buffers: shared hit=1
   I/O Timings: read=0.000 write=0.000
   ->  Index Scan using index_projects_on_id_where_build_timeout_geq_than_2629746 on public.projects  (cost=0.14..3.17 rows=1 width=10) (actual time=0.021..0.021 rows=0 loops=1)
         Index Cond: ((projects.id >= 1) AND (projects.id < 14))
         Filter: (projects.build_timeout >= 2629746)
         Rows Removed by Filter: 0
         Buffers: shared hit=1
         I/O Timings: read=0.000 write=0.000

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/22833/commands/73643 https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/22833/commands/73644

Edited by Allison Browne

Merge request reports