Skip to content

Add not null constraint on routes#namespace_id column

Alexandru Croitorrequested to merge
347400_namespace_id_constraint_to_routes_table into master

What does this MR do and why?

After backfilling namespace_id on routes table it is quite easy to detect orphaned routes, which was much harder with the polymorphic source_id, source_type association.

This migration adds a NOT NULL constraint check on routes#namespace_id column. It is set to validate: false because there still can be some records with NULL values as we remove the values as part of a background migration. We will finalize the background migration in next release and validate the constraint.

This migration also adds the ON DELETE CASCADE FK instead of a ON DELETE SET NULL re #347400 (closed)

re #347400 (closed)

Database Run

UP

main: == 20220606060825 SetOnDeleteCascadeOnNamespaceIdOnRoutesTable: migrating =====
main: -- transaction_open?()
main:    -> 0.0000s
main: -- foreign_keys(:routes)
main:    -> 0.0054s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- execute("ALTER TABLE routes\nADD CONSTRAINT fk_bb2e5b8968\nFOREIGN KEY (namespace_id)\nREFERENCES namespaces (id)\nON DELETE CASCADE\nNOT VALID;\n")
main:    -> 0.0020s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0006s
main: -- execute("ALTER TABLE routes VALIDATE CONSTRAINT fk_bb2e5b8968;")
main:    -> 0.0048s
main: -- execute("RESET statement_timeout")
main:    -> 0.0007s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- foreign_keys(:routes)
main:    -> 0.0027s
main: -- remove_foreign_key(:routes, {:column=>:namespace_id, :name=>"fk_679ff8213d"})
main:    -> 0.0036s
main: == 20220606060825 SetOnDeleteCascadeOnNamespaceIdOnRoutesTable: migrated (0.0263s)

main: == 20220606060850 AddNotNullConstraintOnRoutesNamespaceId: migrating ==========
main: -- current_schema()
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 routes\nADD CONSTRAINT check_af84c6c93f\nCHECK ( namespace_id IS NOT NULL )\nNOT VALID;\n")
main:    -> 0.0010s
main: == 20220606060850 AddNotNullConstraintOnRoutesNamespaceId: migrated (0.0120s) =

ci: == 20220606060825 SetOnDeleteCascadeOnNamespaceIdOnRoutesTable: migrating =====
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- foreign_keys(:routes)
ci:    -> 0.0041s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- execute("ALTER TABLE routes\nADD CONSTRAINT fk_bb2e5b8968\nFOREIGN KEY (namespace_id)\nREFERENCES namespaces (id)\nON DELETE CASCADE\nNOT VALID;\n")
ci:    -> 0.0023s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0005s
ci: -- execute("ALTER TABLE routes VALIDATE CONSTRAINT fk_bb2e5b8968;")
ci:    -> 0.0028s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0007s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- foreign_keys(:routes)
ci:    -> 0.0031s
ci: -- remove_foreign_key(:routes, {:column=>:namespace_id, :name=>"fk_679ff8213d"})
ci:    -> 0.0035s
ci: == 20220606060825 SetOnDeleteCascadeOnNamespaceIdOnRoutesTable: migrated (0.0292s)

ci: == 20220606060850 AddNotNullConstraintOnRoutesNamespaceId: migrating ==========
ci: -- current_schema()
ci:    -> 0.0003s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- current_schema()
ci:    -> 0.0002s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- execute("ALTER TABLE routes\nADD CONSTRAINT check_af84c6c93f\nCHECK ( namespace_id IS NOT NULL )\nNOT VALID;\n")
ci:    -> 0.0009s
ci: == 20220606060850 AddNotNullConstraintOnRoutesNamespaceId: migrated (0.0106s) =

DOWN

main: == 20220606060850 AddNotNullConstraintOnRoutesNamespaceId: reverting ==========
main: -- transaction_open?()
main:    -> 0.0000s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- execute("ALTER TABLE routes\nDROP CONSTRAINT IF EXISTS check_af84c6c93f\n")
main:    -> 0.0007s
main: == 20220606060850 AddNotNullConstraintOnRoutesNamespaceId: reverted (0.0058s) =

main: == 20220606060825 SetOnDeleteCascadeOnNamespaceIdOnRoutesTable: reverting =====
main: -- transaction_open?()
main:    -> 0.0000s
main: -- foreign_keys(:routes)
main:    -> 0.0051s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- execute("ALTER TABLE routes\nADD CONSTRAINT fk_679ff8213d\nFOREIGN KEY (namespace_id)\nREFERENCES namespaces (id)\nON DELETE SET NULL\nNOT VALID;\n")
main:    -> 0.0021s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0002s
main: -- execute("ALTER TABLE routes VALIDATE CONSTRAINT fk_679ff8213d;")
main:    -> 0.0069s
main: -- execute("RESET statement_timeout")
main:    -> 0.0004s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- foreign_keys(:routes)
main:    -> 0.0026s
main: -- remove_foreign_key(:routes, {:column=>:namespace_id, :name=>"fk_bb2e5b8968"})
main:    -> 0.0029s
main: == 20220606060825 SetOnDeleteCascadeOnNamespaceIdOnRoutesTable: reverted (0.0241s)

ci: == 20220606060850 AddNotNullConstraintOnRoutesNamespaceId: reverting ==========
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- execute("ALTER TABLE routes\nDROP CONSTRAINT IF EXISTS check_af84c6c93f\n")
ci:    -> 0.0004s
ci: == 20220606060850 AddNotNullConstraintOnRoutesNamespaceId: reverted (0.0020s) =

ci: == 20220606060825 SetOnDeleteCascadeOnNamespaceIdOnRoutesTable: reverting =====
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- foreign_keys(:routes)
ci:    -> 0.0052s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- execute("ALTER TABLE routes\nADD CONSTRAINT fk_679ff8213d\nFOREIGN KEY (namespace_id)\nREFERENCES namespaces (id)\nON DELETE SET NULL\nNOT VALID;\n")
ci:    -> 0.0064s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0002s
ci: -- execute("ALTER TABLE routes VALIDATE CONSTRAINT fk_679ff8213d;")
ci:    -> 0.0194s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0006s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- foreign_keys(:routes)
ci:    -> 0.0026s
ci: -- remove_foreign_key(:routes, {:column=>:namespace_id, :name=>"fk_bb2e5b8968"})
ci:    -> 0.0036s
ci: == 20220606060825 SetOnDeleteCascadeOnNamespaceIdOnRoutesTable: reverted (0.0424s)

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.

Edited by Alexandru Croitor

Merge request reports