Change owner of security_findings before rename
What does this MR do and why?
This MR makes sure to change the owner of security_findings
table and security_findings_id_seq
before renaming them
Related to gitlab-com/gl-infra/production#7769 (closed)
Database review
UP
gitlab on mzajac/incident-7769 [$✘!] via ⬢ v16.15.0 via 💎 ruby ➜ dbup db/post_migrate/20220902204048_move_security_findings_table_to_gitlab_partitions_dynamic_schema.rb
main: == 20220902204048 MoveSecurityFindingsTableToGitlabPartitionsDynamicSchema: migrating
main: -- transaction_open?()
main: -> 0.0000s
main: -- execute("LOCK TABLE vulnerability_scanners, security_scans, security_findings IN ACCESS EXCLUSIVE MODE\n")
main: -> 0.0004s
main: -- execute("SELECT tableowner FROM pg_tables WHERE tablename = 'security_findings';\n")
main: -> 0.0123s
main: -- execute("SELECT\n pg_get_constraintdef(pg_catalog.pg_constraint.oid)\nFROM\n pg_catalog.pg_constraint\nINNER JOIN pg_class ON pg_class.oid = pg_catalog.pg_constraint.conrelid\nWHERE\n conname = 'check_partition_number' AND\n pg_class.relname = 'security_findings'\n")
main: -> 0.0022s
main: -- execute("ALTER TABLE security_findings RENAME TO security_findings_1;\n")
main: -> 0.0010s
main: -- execute("ALTER INDEX security_findings_pkey RENAME TO security_findings_1_pkey;\n")
main: -> 0.0009s
main: -- execute("CREATE TABLE security_findings (\n LIKE security_findings_1 INCLUDING ALL\n) PARTITION BY LIST (partition_number);\n")
main: -> 0.0051s
main: -- execute("ALTER TABLE security_findings OWNER TO quintasan;\n")
main: -> 0.0006s
main: -- execute("ALTER SEQUENCE security_findings_id_seq OWNER TO quintasan;\n")
main: -> 0.0007s
main: -- execute("ALTER SEQUENCE security_findings_id_seq OWNED BY public.security_findings.id;\n")
main: -> 0.0007s
main: -- execute("ALTER TABLE security_findings\nADD CONSTRAINT fk_rails_729b763a54 FOREIGN KEY (scanner_id) REFERENCES vulnerability_scanners(id) ON DELETE CASCADE;\n")
main: -> 0.0035s
main: -- execute("ALTER TABLE security_findings\nADD CONSTRAINT fk_rails_bb63863cf1 FOREIGN KEY (scan_id) REFERENCES security_scans(id) ON DELETE CASCADE;\n")
main: -> 0.0021s
main: -- execute("ALTER TABLE security_findings_1 SET SCHEMA gitlab_partitions_dynamic;\n")
main: -> 0.0037s
main: -- execute("ALTER TABLE security_findings ATTACH PARTITION gitlab_partitions_dynamic.security_findings_1 FOR VALUES IN (1);\n")
main: -> 0.0074s
main: -- execute("ALTER TABLE security_findings DROP CONSTRAINT check_partition_number;\n")
main: -> 0.0017s
main: -- execute("ALTER INDEX gitlab_partitions_dynamic.index_security_findings_on_unique_columns RENAME TO security_findings_1_uuid_scan_id_partition_number_idx;\n")
main: -> 0.0013s
main: -- execute("ALTER INDEX gitlab_partitions_dynamic.index_security_findings_on_confidence RENAME TO security_findings_1_confidence_idx;\n")
main: -> 0.0013s
main: -- execute("ALTER INDEX gitlab_partitions_dynamic.index_security_findings_on_project_fingerprint RENAME TO security_findings_1_project_fingerprint_idx;\n")
main: -> 0.0013s
main: -- execute("ALTER INDEX gitlab_partitions_dynamic.index_security_findings_on_scan_id_and_deduplicated RENAME TO security_findings_1_scan_id_deduplicated_idx;\n")
main: -> 0.0013s
main: -- execute("ALTER INDEX gitlab_partitions_dynamic.index_security_findings_on_scan_id_and_id RENAME TO security_findings_1_scan_id_id_idx;\n")
main: -> 0.0012s
main: -- execute("ALTER INDEX gitlab_partitions_dynamic.index_security_findings_on_scanner_id RENAME TO security_findings_1_scanner_id_idx;\n")
main: -> 0.0013s
main: -- execute("ALTER INDEX gitlab_partitions_dynamic.index_security_findings_on_severity RENAME TO security_findings_1_severity_idx;\n")
main: -> 0.0013s
main: == 20220902204048 MoveSecurityFindingsTableToGitlabPartitionsDynamicSchema: migrated (0.0635s)
ci: == 20220902204048 MoveSecurityFindingsTableToGitlabPartitionsDynamicSchema: migrating
ci: -- transaction_open?()
ci: -> 0.0000s
ci: -- execute("LOCK TABLE vulnerability_scanners, security_scans, security_findings IN ACCESS EXCLUSIVE MODE\n")
ci: -> 0.0004s
ci: -- execute("SELECT tableowner FROM pg_tables WHERE tablename = 'security_findings';\n")
ci: -> 0.0013s
ci: -- execute("SELECT\n pg_get_constraintdef(pg_catalog.pg_constraint.oid)\nFROM\n pg_catalog.pg_constraint\nINNER JOIN pg_class ON pg_class.oid = pg_catalog.pg_constraint.conrelid\nWHERE\n conname = 'check_partition_number' AND\n pg_class.relname = 'security_findings'\n")
ci: -> 0.0008s
ci: -- execute("ALTER TABLE security_findings RENAME TO security_findings_1;\n")
ci: -> 0.0004s
ci: -- execute("ALTER INDEX security_findings_pkey RENAME TO security_findings_1_pkey;\n")
ci: -> 0.0005s
ci: -- execute("CREATE TABLE security_findings (\n LIKE security_findings_1 INCLUDING ALL\n) PARTITION BY LIST (partition_number);\n")
ci: -> 0.0022s
ci: -- execute("ALTER TABLE security_findings OWNER TO quintasan;\n")
ci: -> 0.0003s
ci: -- execute("ALTER SEQUENCE security_findings_id_seq OWNER TO quintasan;\n")
ci: -> 0.0003s
ci: -- execute("ALTER SEQUENCE security_findings_id_seq OWNED BY public.security_findings.id;\n")
ci: -> 0.0003s
ci: -- execute("ALTER TABLE security_findings\nADD CONSTRAINT fk_rails_729b763a54 FOREIGN KEY (scanner_id) REFERENCES vulnerability_scanners(id) ON DELETE CASCADE;\n")
ci: -> 0.0022s
ci: -- execute("ALTER TABLE security_findings\nADD CONSTRAINT fk_rails_bb63863cf1 FOREIGN KEY (scan_id) REFERENCES security_scans(id) ON DELETE CASCADE;\n")
ci: -> 0.0007s
ci: -- execute("ALTER TABLE security_findings_1 SET SCHEMA gitlab_partitions_dynamic;\n")
ci: -> 0.0005s
ci: -- execute("ALTER TABLE security_findings ATTACH PARTITION gitlab_partitions_dynamic.security_findings_1 FOR VALUES IN (1);\n")
ci: -> 0.0034s
ci: -- execute("ALTER TABLE security_findings DROP CONSTRAINT check_partition_number;\n")
ci: -> 0.0005s
ci: -- execute("ALTER INDEX gitlab_partitions_dynamic.index_security_findings_on_unique_columns RENAME TO security_findings_1_uuid_scan_id_partition_number_idx;\n")
ci: -> 0.0003s
ci: -- execute("ALTER INDEX gitlab_partitions_dynamic.index_security_findings_on_confidence RENAME TO security_findings_1_confidence_idx;\n")
ci: -> 0.0004s
ci: -- execute("ALTER INDEX gitlab_partitions_dynamic.index_security_findings_on_project_fingerprint RENAME TO security_findings_1_project_fingerprint_idx;\n")
ci: -> 0.0003s
ci: -- execute("ALTER INDEX gitlab_partitions_dynamic.index_security_findings_on_scan_id_and_deduplicated RENAME TO security_findings_1_scan_id_deduplicated_idx;\n")
ci: -> 0.0003s
ci: -- execute("ALTER INDEX gitlab_partitions_dynamic.index_security_findings_on_scan_id_and_id RENAME TO security_findings_1_scan_id_id_idx;\n")
ci: -> 0.0003s
ci: -- execute("ALTER INDEX gitlab_partitions_dynamic.index_security_findings_on_scanner_id RENAME TO security_findings_1_scanner_id_idx;\n")
ci: -> 0.0003s
ci: -- execute("ALTER INDEX gitlab_partitions_dynamic.index_security_findings_on_severity RENAME TO security_findings_1_severity_idx;\n")
ci: -> 0.0003s
ci: == 20220902204048 MoveSecurityFindingsTableToGitlabPartitionsDynamicSchema: migrated (0.0184s)
DOWN
gitlab on mzajac/incident-7769 [$!] via ⬢ v16.15.0 via 💎 ruby ➜ dbdown db/post_migrate/20220902204048_move_security_findings_table_to_gitlab_partitions_dynamic_schema.rb
main: == 20220902204048 MoveSecurityFindingsTableToGitlabPartitionsDynamicSchema: reverting
main: -- execute("SELECT\n partitions.relname AS partition_name\nFROM pg_inherits\nJOIN pg_class parent ON pg_inherits.inhparent = parent.oid\nJOIN pg_class partitions ON pg_inherits.inhrelid = partitions.oid\nWHERE\n parent.relname = 'security_findings'\nORDER BY (regexp_matches(partitions.relname, 'security_findings_(\\d+)'))[1]::int DESC\nLIMIT 1\n")
main: -> 0.0044s
main: -- transaction_open?()
main: -> 0.0000s
main: -- execute("LOCK TABLE vulnerability_scanners, security_scans, security_findings IN ACCESS EXCLUSIVE MODE\n")
main: -> 0.0004s
main: -- execute("ALTER TABLE security_findings DETACH PARTITION gitlab_partitions_dynamic.security_findings_1;\n")
main: -> 0.0022s
main: -- execute("ALTER TABLE gitlab_partitions_dynamic.security_findings_1 SET SCHEMA public;\n")
main: -> 0.0011s
main: -- execute("ALTER SEQUENCE security_findings_id_seq OWNED BY security_findings_1.id;\n")
main: -> 0.0005s
main: -- execute("DROP TABLE security_findings;\n")
main: -> 0.0015s
main: -- execute("ALTER TABLE security_findings_1 RENAME TO security_findings;\n")
main: -> 0.0005s
main: -- execute("ALTER INDEX public.security_findings_1_pkey RENAME TO security_findings_pkey;\n")
main: -> 0.0008s
main: -- execute("ALTER INDEX public.security_findings_1_uuid_scan_id_partition_number_idx RENAME TO index_security_findings_on_unique_columns;\n")
main: -> 0.0004s
main: -- execute("ALTER INDEX public.security_findings_1_confidence_idx RENAME TO index_security_findings_on_confidence;\n")
main: -> 0.0006s
main: -- execute("ALTER INDEX public.security_findings_1_project_fingerprint_idx RENAME TO index_security_findings_on_project_fingerprint;\n")
main: -> 0.0003s
main: -- execute("ALTER INDEX public.security_findings_1_scan_id_deduplicated_idx RENAME TO index_security_findings_on_scan_id_and_deduplicated;\n")
main: -> 0.0008s
main: -- execute("ALTER INDEX public.security_findings_1_scan_id_id_idx RENAME TO index_security_findings_on_scan_id_and_id;\n")
main: -> 0.0003s
main: -- execute("ALTER INDEX public.security_findings_1_scanner_id_idx RENAME TO index_security_findings_on_scanner_id;\n")
main: -> 0.0003s
main: -- execute("ALTER INDEX public.security_findings_1_severity_idx RENAME TO index_security_findings_on_severity;\n")
main: -> 0.0004s
main: -- transaction_open?()
main: -> 0.0000s
main: -- current_schema()
main: -> 0.0002s
main: -- transaction_open?()
main: -> 0.0000s
main: -- execute("ALTER TABLE security_findings\nADD CONSTRAINT check_partition_number\nCHECK ( (partition_number = 1) )\nNOT VALID;\n")
main: -> 0.0004s
main: -- current_schema()
main: -> 0.0005s
main: -- execute("SET statement_timeout TO 0")
main: -> 0.0007s
main: -- execute("ALTER TABLE security_findings VALIDATE CONSTRAINT check_partition_number;")
main: -> 0.0004s
main: -- execute("RESET statement_timeout")
main: -> 0.0006s
main: == 20220902204048 MoveSecurityFindingsTableToGitlabPartitionsDynamicSchema: reverted (0.0377s)
ci: == 20220902204048 MoveSecurityFindingsTableToGitlabPartitionsDynamicSchema: reverting
ci: -- execute("SELECT\n partitions.relname AS partition_name\nFROM pg_inherits\nJOIN pg_class parent ON pg_inherits.inhparent = parent.oid\nJOIN pg_class partitions ON pg_inherits.inhrelid = partitions.oid\nWHERE\n parent.relname = 'security_findings'\nORDER BY (regexp_matches(partitions.relname, 'security_findings_(\\d+)'))[1]::int DESC\nLIMIT 1\n")
ci: -> 0.0011s
ci: -- transaction_open?()
ci: -> 0.0000s
ci: -- execute("LOCK TABLE vulnerability_scanners, security_scans, security_findings IN ACCESS EXCLUSIVE MODE\n")
ci: -> 0.0004s
ci: -- execute("ALTER TABLE security_findings DETACH PARTITION gitlab_partitions_dynamic.security_findings_1;\n")
ci: -> 0.0020s
ci: -- execute("ALTER TABLE gitlab_partitions_dynamic.security_findings_1 SET SCHEMA public;\n")
ci: -> 0.0008s
ci: -- execute("ALTER SEQUENCE security_findings_id_seq OWNED BY security_findings_1.id;\n")
ci: -> 0.0004s
ci: -- execute("DROP TABLE security_findings;\n")
ci: -> 0.0013s
ci: -- execute("ALTER TABLE security_findings_1 RENAME TO security_findings;\n")
ci: -> 0.0004s
ci: -- execute("ALTER INDEX public.security_findings_1_pkey RENAME TO security_findings_pkey;\n")
ci: -> 0.0004s
ci: -- execute("ALTER INDEX public.security_findings_1_uuid_scan_id_partition_number_idx RENAME TO index_security_findings_on_unique_columns;\n")
ci: -> 0.0004s
ci: -- execute("ALTER INDEX public.security_findings_1_confidence_idx RENAME TO index_security_findings_on_confidence;\n")
ci: -> 0.0003s
ci: -- execute("ALTER INDEX public.security_findings_1_project_fingerprint_idx RENAME TO index_security_findings_on_project_fingerprint;\n")
ci: -> 0.0003s
ci: -- execute("ALTER INDEX public.security_findings_1_scan_id_deduplicated_idx RENAME TO index_security_findings_on_scan_id_and_deduplicated;\n")
ci: -> 0.0003s
ci: -- execute("ALTER INDEX public.security_findings_1_scan_id_id_idx RENAME TO index_security_findings_on_scan_id_and_id;\n")
ci: -> 0.0004s
ci: -- execute("ALTER INDEX public.security_findings_1_scanner_id_idx RENAME TO index_security_findings_on_scanner_id;\n")
ci: -> 0.0003s
ci: -- execute("ALTER INDEX public.security_findings_1_severity_idx RENAME TO index_security_findings_on_severity;\n")
ci: -> 0.0004s
ci: -- transaction_open?()
ci: -> 0.0000s
ci: -- current_schema()
ci: -> 0.0002s
ci: -- transaction_open?()
ci: -> 0.0000s
ci: -- execute("ALTER TABLE security_findings\nADD CONSTRAINT check_partition_number\nCHECK ( (partition_number = 1) )\nNOT VALID;\n")
ci: -> 0.0005s
ci: -- current_schema()
ci: -> 0.0002s
ci: -- execute("SET statement_timeout TO 0")
ci: -> 0.0003s
ci: -- execute("ALTER TABLE security_findings VALIDATE CONSTRAINT check_partition_number;")
ci: -> 0.0004s
ci: -- execute("RESET statement_timeout")
ci: -> 0.0003s
ci: == 20220902204048 MoveSecurityFindingsTableToGitlabPartitionsDynamicSchema: reverted (0.0175s)
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.
Edited by Michał Zając