Add DB not null constraint for releases table author_id column
What does this MR do and why?
As per the plan #386089 (closed) we have completed backfill migration in !106813 (merged) and the production data in gitlab.com
was successfully verified that it does not have any releases with empty author_id today(11 Jan 2022).
[ gprd ] production> Release.where(:author_id => nil).count
=> 0
So proceeding with
- Introducing overlapping foreign key for
author_id
on releases table withusers.id
. Since Postgres accepts overlapping foreign keys and uses the latest constraint. Removal of the old foreign key will be covered in follow-up #388064 (closed). - Also adding DB not null constraint without validation as per guidelines.
Database
See !108709 (comment 1235965456) for DB testing.
Up
> bundle exec rake db:migrate:up:main VERSION=20230112132117
main: == 20230112132117 ReplaceFkOnReleasesAuthorId: migrating ======================
main: -- transaction_open?()
main: -> 0.0001s
main: -- transaction_open?()
main: -> 0.0003s
main: -- execute("ALTER TABLE releases\nADD CONSTRAINT fk_releases_author_id_new\nFOREIGN KEY (author_id)\nREFERENCES users (id)\nON DELETE CASCADE\nNOT VALID;\n")
main: -> 0.0074s
main: == 20230112132117 ReplaceFkOnReleasesAuthorId: migrated (0.7345s) =============
> bundle exec rake db:migrate:up:main VERSION=20230112143600
main: == 20230112143600 ValidateFkOnReleasesAuthorId: migrating =====================
main: -- execute("SET statement_timeout TO 0")
main: -> 0.0011s
main: -- execute("ALTER TABLE releases VALIDATE CONSTRAINT fk_releases_author_id_new;")
main: -> 0.0319s
main: -- execute("RESET statement_timeout")
main: -> 0.0009s
main: == 20230112143600 ValidateFkOnReleasesAuthorId: migrated (0.7375s) ============
> bundle exec rake db:migrate:up VERSION=20230112162331
main: == 20230112162331 AddNotNullConstraintToReleaseAuthorId: migrating ============
main: -- current_schema()
main: -> 0.0079s
main: -- transaction_open?()
main: -> 0.0000s
main: -- current_schema()
main: -> 0.0012s
main: -- transaction_open?()
main: -> 0.0000s
main: -- execute("ALTER TABLE releases\nADD CONSTRAINT check_394554d3fa\nCHECK ( author_id IS NOT NULL )\nNOT VALID;\n")
main: -> 0.0022s
main: == 20230111150903 AddNotNullConstraintToReleaseAuthorId: migrated (1.3233s) ===
Down
> bundle exec rake db:migrate:down:main VERSION=20230112132117
main: == 20230112132117 ReplaceFkOnReleasesAuthorId: reverting ======================
main: -- transaction_open?()
main: -> 0.0000s
main: -- remove_foreign_key(:releases, {:column=>:author_id, :on_delete=>:cascade, :name=>"fk_releases_author_id_new"})
main: -> 0.0106s
main: == 20230112132117 ReplaceFkOnReleasesAuthorId: reverted (1.3511s) =============
> bundle exec rake db:migrate:down:main VERSION=20230112143600
main: == 20230112143600 ValidateFkOnReleasesAuthorId: reverting =====================
main: == 20230112143600 ValidateFkOnReleasesAuthorId: reverted (0.0034s) ============
> bundle exec rake db:migrate:down:main VERSION=20230112162331
main: == 20230112162331 AddNotNullConstraintToReleaseAuthorId: reverting ============
main: -- transaction_open?()
main: -> 0.0000s
main: -- transaction_open?()
main: -> 0.0000s
main: -- execute(" ALTER TABLE releases\n DROP CONSTRAINT IF EXISTS check_394554d3fa\n")
main: -> 0.0035s
main: == 20230111150903 AddNotNullConstraintToReleaseAuthorId: reverted (0.0554s) ===
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 #386089 (closed)
Edited by Bala Kumar