Drop foreign keys before attaching ci_builds
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
-
GITLAB_SIMULATE_SAAS=1 pgai use -o ci bin/rails c
- connect rails console to a CI thin clone Feature.enable(:attach_ci_builds_partition)
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.
-
I have evaluated the MR acceptance checklist for this MR.
Related to #382033 (closed)
Edited by Marius Bobin