Skip to content

Create FK between p_ci_builds_metadata and p_ci_builds

Marius Bobin requested to merge 414396-partitioned-fks-v7 into master

What does this MR do and why?

Create FK between p_ci_builds_metadata and p_ci_builds

It tries to create the FK between the tables duyring the weekend where there is less traffic, so it will have a shot at getting the locks without deadlocks

Screenshots or screen recordings

$ GITLAB_SIMULATE_SAAS=1 pgai use -o ci bin/rails runner "Feature.enable(:p_ci_builds_metadata_foreign_key); Gitlab::Database::CiBuildsPartitioning.new(logger: Gitlab::JsonLogger.new(STDOUT)).execute"
{"severity":"INFO","time":"2023-06-28T12:26:29.441Z","method":"with_lock_retries","class":"Gitlab::Database::CiBuildsPartitioning","message":"Lock timeout is set","current_iteration":1,"lock_timeout_in_ms":10000}
{"severity":"INFO","time":"2023-06-28T12:26:29.837Z","method":"with_lock_retries","class":"Gitlab::Database::CiBuildsPartitioning","message":"Migration finished","current_iteration":1,"lock_timeout_in_ms":10000}
{"severity":"INFO","time":"2023-06-28T12:26:30.190Z","message":"Foreign key successfully created","class":"Gitlab::Database::CiBuildsPartitioning"}

Logs:

  TRANSACTION (0.1ms)  BEGIN /*application:web,db_config_name:main,line:/lib/gitlab/database/schema_cache_with_renamed_table.rb:25:in `columns'*/
  ↳ lib/gitlab/database/schema_cache_with_renamed_table.rb:25:in `columns'
  Feature::FlipperFeature Load (0.5ms)  SELECT "features".* FROM "features" WHERE "features"."key" = 'p_ci_builds_metadata_foreign_key' ORDER BY "features"."id" ASC LIMIT 1 /*application:web,db_config_name:main,line:/lib/gitlab/database.rb:385:in `block in transaction'*/
  ↳ config/initializers/active_record_relation_union_reset.rb:7:in `exec_queries'
  TRANSACTION (0.1ms)  COMMIT /*application:web,db_config_name:main,line:/lib/gitlab/database.rb:418:in `commit'*/
  ↳ lib/gitlab/database.rb:418:in `commit'
  TRANSACTION (0.1ms)  BEGIN /*application:web,db_config_name:main,line:/lib/gitlab/database/schema_cache_with_renamed_table.rb:25:in `columns'*/
  ↳ lib/gitlab/database/schema_cache_with_renamed_table.rb:25:in `columns'
  Feature::FlipperGate Load (0.3ms)  SELECT "feature_gates".* FROM "feature_gates" WHERE "feature_gates"."feature_key" = 'p_ci_builds_metadata_foreign_key' /*application:web,db_config_name:main,line:/lib/gitlab/database.rb:385:in `block in transaction'*/
  ↳ config/initializers/active_record_relation_union_reset.rb:7:in `exec_queries'
  Feature::FlipperGate Destroy (0.2ms)  DELETE FROM "feature_gates" WHERE "feature_gates"."id" = 44 /*application:web,db_config_name:main,line:/lib/gitlab/database.rb:385:in `block in transaction'*/
  ↳ lib/gitlab/database.rb:385:in `block in transaction'
  Feature::FlipperGate Load (0.1ms)  SELECT "feature_gates".* FROM "feature_gates" WHERE "feature_gates"."feature_key" = 'p_ci_builds_metadata_foreign_key' AND "feature_gates"."key" = 'boolean' /*application:web,db_config_name:main,line:/lib/gitlab/database.rb:385:in `block in transaction'*/
  ↳ config/initializers/active_record_relation_union_reset.rb:7:in `exec_queries'
  Feature::FlipperGate Create (0.2ms)  INSERT INTO "feature_gates" ("feature_key", "key", "value", "created_at", "updated_at") VALUES ('p_ci_builds_metadata_foreign_key', 'boolean', 'true', '2023-06-28 12:26:24.577802', '2023-06-28 12:26:24.577802') RETURNING "id" /*application:web,db_config_name:main,line:/lib/gitlab/database.rb:385:in `block in transaction'*/
  ↳ lib/gitlab/database.rb:385:in `block in transaction'
  TRANSACTION (0.1ms)  COMMIT /*application:web,db_config_name:main,line:/lib/gitlab/database.rb:418:in `commit'*/
  ↳ lib/gitlab/database.rb:418:in `commit'
  Feature::FlipperGate Load (0.2ms)  SELECT "feature_gates".* FROM "feature_gates" WHERE "feature_gates"."feature_key" = 'p_ci_builds_metadata_foreign_key' /*application:web,db_config_name:main,line:/ee/lib/ee/feature.rb:37:in `enable'*/
  ↳ config/initializers/active_record_relation_union_reset.rb:7:in `exec_queries'
WARNING: Understand the stability and security risks of enabling in-development features with feature flags.
See https://docs.gitlab.com/ee/administration/feature_flags.html#risks-when-enabling-features-still-in-development for more information.
  Gitlab::Database::PostgresForeignKey Exists? (277.7ms)  SELECT 1 AS one FROM "postgres_foreign_keys" WHERE "postgres_foreign_keys"."constrained_table_name" = 'p_ci_builds_metadata' AND "postgres_foreign_keys"."referenced_table_name" = 'p_ci_builds' AND "postgres_foreign_keys"."name" = 'temp_fk_e20479742e_p' LIMIT 1 /*application:web,db_config_name:ci,line:/lib/gitlab/database/ci_builds_partitioning.rb:66:in `block in foreign_key_exists?'*/
  ↳ lib/gitlab/database/ci_builds_partitioning.rb:66:in `block in foreign_key_exists?'
  Gitlab::Database::PostgresAutovacuumActivity Load (229.6ms)  SELECT "postgres_autovacuum_activity".* FROM "postgres_autovacuum_activity" WHERE "postgres_autovacuum_activity"."wraparound_prevention" = TRUE AND (schema = current_schema()) AND "postgres_autovacuum_activity"."table" IN ('ci_builds', 'ci_builds_metadata') /*application:web,db_config_name:ci,line:/lib/gitlab/database/postgres_autovacuum_activity.rb:16:in `block in for_tables'*/
  ↳ config/initializers/active_record_relation_union_reset.rb:7:in `exec_queries'
  TRANSACTION (173.1ms)  BEGIN /*application:web,db_config_name:ci,line:/lib/gitlab/database/with_lock_retries.rb:172:in `execute'*/
  ↳ lib/gitlab/database/with_lock_retries.rb:172:in `execute'
   (174.0ms)  SET LOCAL lock_timeout TO '10000ms' /*application:web,db_config_name:ci,line:/lib/gitlab/database/with_lock_retries.rb:172:in `execute'*/
  ↳ lib/gitlab/database/with_lock_retries.rb:172:in `execute'
   (391.8ms)  SET LOCAL statement_timeout TO '11s'; LOCK TABLE ci_builds, p_ci_builds, p_ci_builds_metadata IN ACCESS EXCLUSIVE MODE; ALTER TABLE p_ci_builds_metadata ADD CONSTRAINT temp_fk_e20479742e_p FOREIGN KEY (partition_id, build_id) REFERENCES p_ci_builds (partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE; /*application:web,db_config_name:ci,line:/lib/gitlab/database/ci_builds_partitioning.rb:23:in `block (2 levels) in execute'*/
  ↳ lib/gitlab/database/ci_builds_partitioning.rb:23:in `block (2 levels) in execute'
  TRANSACTION (175.2ms)  COMMIT /*application:web,db_config_name:ci,line:/lib/gitlab/database.rb:418:in `commit'*/
  ↳ lib/gitlab/database.rb:418:in `commit'
   (172.3ms)  RESET idle_in_transaction_session_timeout; RESET lock_timeout /*application:web,db_config_name:ci,line:/lib/gitlab/database/with_lock_retries.rb:172:in `execute'*/
  ↳ lib/gitlab/database/with_lock_retries.rb:172:in `execute'

How to set up and validate locally

Numbered steps to set up and validate the change are strongly suggested.

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

Edited by Marius Bobin

Merge request reports