Skip to content

Drop foreign keys before attaching ci_builds

Marius Bobin requested to merge 382033-drop-fks-before-attaching-partition into master

What does this MR do and why?

Follow-up from !121554 (merged) to attach ci_builds to p_ci_builds during the week.

Screenshots or screen recordings

[5] pry(main)> Gitlab::Database::CiBuildsPartitioning.new.execute
  Gitlab::Database::PostgresPartition Exists? (221.4ms)  SELECT 1 AS one FROM "postgres_partitions" WHERE "postgres_partitions"."parent_identifier" = 'public.p_ci_builds' AND "postgres_partitions"."identifier" = 'public.ci_builds' LIMIT 1 /*application:console,db_config_name:ci,console_hostname:rocket-sled.local,console_username:marius,line:/lib/gitlab/database/ci_builds_partitioning.rb:81:in `block in already_attached?'*/
  Gitlab::Database::PostgresAutovacuumActivity Exists? (315.9ms)  SELECT 1 AS one FROM "postgres_autovacuum_activity" WHERE (schema = current_schema()) AND "postgres_autovacuum_activity"."table" IN ('ci_pipelines', 'ci_stages', 'ci_builds', 'ci_resource_groups') LIMIT 1 /*application:console,db_config_name:ci,console_hostname:rocket-sled.local,console_username:marius,line:/lib/gitlab/database/ci_builds_partitioning.rb:89:in `block in vacuum_running?'*/
  Gitlab::Database::PostgresForeignKey Load (290.9ms)  SELECT "postgres_foreign_keys".* FROM "postgres_foreign_keys" WHERE "postgres_foreign_keys"."constrained_table_name" = 'p_ci_builds' /*application:console,db_config_name:ci,console_hostname:rocket-sled.local,console_username:marius,line:/lib/gitlab/database/ci_builds_partitioning.rb:58:in `block in drop_foreign_keys'*/
  TRANSACTION (297.6ms)  BEGIN /*application:console,db_config_name:ci,console_hostname:rocket-sled.local,console_username:marius,line:/lib/gitlab/database/with_lock_retries.rb:172:in `execute'*/
   (210.1ms)  SET LOCAL lock_timeout TO '10000ms' /*application:console,db_config_name:ci,console_hostname:rocket-sled.local,console_username:marius,line:/lib/gitlab/database/with_lock_retries.rb:172:in `execute'*/
   (240.0ms)  ALTER TABLE "p_ci_builds" DROP CONSTRAINT "fk_3a9eaa254d" /*application:console,db_config_name:ci,console_hostname:rocket-sled.local,console_username:marius,line:/lib/gitlab/database/ci_builds_partitioning.rb:60:in `block (3 levels) in drop_foreign_keys'*/
  TRANSACTION (330.5ms)  COMMIT /*application:console,db_config_name:ci,console_hostname:rocket-sled.local,console_username:marius,line:/lib/gitlab/database.rb:418:in `commit'*/
   (240.0ms)  RESET idle_in_transaction_session_timeout; RESET lock_timeout /*application:console,db_config_name:ci,console_hostname:rocket-sled.local,console_username:marius,line:/lib/gitlab/database/with_lock_retries.rb:172:in `execute'*/
  TRANSACTION (209.9ms)  BEGIN /*application:console,db_config_name:ci,console_hostname:rocket-sled.local,console_username:marius,line:/lib/gitlab/database/with_lock_retries.rb:172:in `execute'*/
   (260.4ms)  SET LOCAL lock_timeout TO '10000ms' /*application:console,db_config_name:ci,console_hostname:rocket-sled.local,console_username:marius,line:/lib/gitlab/database/with_lock_retries.rb:172:in `execute'*/
   (223.2ms)  ALTER TABLE "p_ci_builds" DROP CONSTRAINT "fk_6661f4f0e8" /*application:console,db_config_name:ci,console_hostname:rocket-sled.local,console_username:marius,line:/lib/gitlab/database/ci_builds_partitioning.rb:60:in `block (3 levels) in drop_foreign_keys'*/
  TRANSACTION (258.9ms)  COMMIT /*application:console,db_config_name:ci,console_hostname:rocket-sled.local,console_username:marius,line:/lib/gitlab/database.rb:418:in `commit'*/
   (247.5ms)  RESET idle_in_transaction_session_timeout; RESET lock_timeout /*application:console,db_config_name:ci,console_hostname:rocket-sled.local,console_username:marius,line:/lib/gitlab/database/with_lock_retries.rb:172:in `execute'*/
  TRANSACTION (209.9ms)  BEGIN /*application:console,db_config_name:ci,console_hostname:rocket-sled.local,console_username:marius,line:/lib/gitlab/database/with_lock_retries.rb:172:in `execute'*/
   (211.0ms)  SET LOCAL lock_timeout TO '10000ms' /*application:console,db_config_name:ci,console_hostname:rocket-sled.local,console_username:marius,line:/lib/gitlab/database/with_lock_retries.rb:172:in `execute'*/
   (223.0ms)  ALTER TABLE "p_ci_builds" DROP CONSTRAINT "fk_87f4cefcda" /*application:console,db_config_name:ci,console_hostname:rocket-sled.local,console_username:marius,line:/lib/gitlab/database/ci_builds_partitioning.rb:60:in `block (3 levels) in drop_foreign_keys'*/
  TRANSACTION (209.1ms)  COMMIT /*application:console,db_config_name:ci,console_hostname:rocket-sled.local,console_username:marius,line:/lib/gitlab/database.rb:418:in `commit'*/
   (210.3ms)  RESET idle_in_transaction_session_timeout; RESET lock_timeout /*application:console,db_config_name:ci,console_hostname:rocket-sled.local,console_username:marius,line:/lib/gitlab/database/with_lock_retries.rb:172:in `execute'*/
  TRANSACTION (320.1ms)  BEGIN /*application:console,db_config_name:ci,console_hostname:rocket-sled.local,console_username:marius,line:/lib/gitlab/database/with_lock_retries.rb:172:in `execute'*/
   (304.9ms)  SET LOCAL lock_timeout TO '10000ms' /*application:console,db_config_name:ci,console_hostname:rocket-sled.local,console_username:marius,line:/lib/gitlab/database/with_lock_retries.rb:172:in `execute'*/
   (211.4ms)  ALTER TABLE "p_ci_builds" DROP CONSTRAINT "fk_a2141b1522" /*application:console,db_config_name:ci,console_hostname:rocket-sled.local,console_username:marius,line:/lib/gitlab/database/ci_builds_partitioning.rb:60:in `block (3 levels) in drop_foreign_keys'*/
  TRANSACTION (217.4ms)  COMMIT /*application:console,db_config_name:ci,console_hostname:rocket-sled.local,console_username:marius,line:/lib/gitlab/database.rb:418:in `commit'*/
   (212.5ms)  RESET idle_in_transaction_session_timeout; RESET lock_timeout /*application:console,db_config_name:ci,console_hostname:rocket-sled.local,console_username:marius,line:/lib/gitlab/database/with_lock_retries.rb:172:in `execute'*/
  TRANSACTION (339.5ms)  BEGIN /*application:console,db_config_name:ci,console_hostname:rocket-sled.local,console_username:marius,line:/lib/gitlab/database/with_lock_retries.rb:172:in `execute'*/
   (228.1ms)  SET LOCAL lock_timeout TO '10000ms' /*application:console,db_config_name:ci,console_hostname:rocket-sled.local,console_username:marius,line:/lib/gitlab/database/with_lock_retries.rb:172:in `execute'*/
   (208.3ms)  ALTER TABLE "p_ci_builds" DROP CONSTRAINT "fk_d3130c9a7f" /*application:console,db_config_name:ci,console_hostname:rocket-sled.local,console_username:marius,line:/lib/gitlab/database/ci_builds_partitioning.rb:60:in `block (3 levels) in drop_foreign_keys'*/
  TRANSACTION (207.4ms)  COMMIT /*application:console,db_config_name:ci,console_hostname:rocket-sled.local,console_username:marius,line:/lib/gitlab/database.rb:418:in `commit'*/
   (210.4ms)  RESET idle_in_transaction_session_timeout; RESET lock_timeout /*application:console,db_config_name:ci,console_hostname:rocket-sled.local,console_username:marius,line:/lib/gitlab/database/with_lock_retries.rb:172:in `execute'*/
  TRANSACTION (208.3ms)  BEGIN /*application:console,db_config_name:ci,console_hostname:rocket-sled.local,console_username:marius,line:/lib/gitlab/database/with_lock_retries.rb:172:in `execute'*/
   (218.1ms)  SET LOCAL lock_timeout TO '10000ms' /*application:console,db_config_name:ci,console_hostname:rocket-sled.local,console_username:marius,line:/lib/gitlab/database/with_lock_retries.rb:172:in `execute'*/
   (323.3ms)  LOCK ci_builds IN ACCESS EXCLUSIVE MODE; DROP TRIGGER IF EXISTS ci_builds_loose_fk_trigger ON ci_builds; ALTER TABLE p_ci_builds ATTACH PARTITION ci_builds FOR VALUES IN (100); ALTER SEQUENCE ci_builds_id_seq OWNED BY p_ci_builds.id; ALTER TABLE p_ci_builds DROP CONSTRAINT partitioning_constraint; CREATE TRIGGER ci_builds_loose_fk_trigger AFTER DELETE ON ci_builds REFERENCING OLD TABLE AS old_table FOR EACH STATEMENT EXECUTE FUNCTION insert_into_loose_foreign_keys_deleted_records(); CREATE TRIGGER p_ci_builds_loose_fk_trigger AFTER DELETE ON p_ci_builds REFERENCING OLD TABLE AS old_table FOR EACH STATEMENT EXECUTE FUNCTION insert_into_loose_foreign_keys_deleted_records(); /*application:console,db_config_name:ci,console_hostname:rocket-sled.local,console_username:marius,line:/lib/gitlab/database/ci_builds_partitioning.rb:68:in `block in attach_partition'*/
  TRANSACTION (208.1ms)  COMMIT /*application:console,db_config_name:ci,console_hostname:rocket-sled.local,console_username:marius,line:/lib/gitlab/database.rb:418:in `commit'*/
   (217.2ms)  RESET idle_in_transaction_session_timeout; RESET lock_timeout /*application:console,db_config_name:ci,console_hostname:rocket-sled.local,console_username:marius,line:/lib/gitlab/database/with_lock_retries.rb:172:in `execute'*/

How to set up and validate locally

  1. GITLAB_SIMULATE_SAAS=1 pgai use -o ci bin/rails c - connect rails console to a CI thin clone
  2. Feature.enable(:attach_ci_builds_partition)
  3. Gitlab::Database::CiBuildsPartitioning.new.execute

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

Edited by Marius Bobin

Merge request reports