Detect and truncate unnecessary data in decomposed database

Overview

A number of us have had hit failures in database migration since the ci decomposed database has stale data. For example:

ci: == 20221107220420 ValidateNotNullConstraintOnMemberNamespaceId: migrating =====
ci: -- current_schema()
ci:    -> 0.0002s
ci: -- execute("ALTER TABLE members VALIDATE CONSTRAINT check_508774aac0;")
rails aborted!
StandardError: An error has occurred, all later migrations canceled:

PG::CheckViolation: ERROR:  check constraint "check_508774aac0" is violated by some row
/Users/stanhu/gdk-ee/gitlab/lib/gitlab/database/migrations/constraints_helpers.rb:115:in `block in validate_check_constraint'
/Users/stanhu/gdk-ee/gitlab/lib/gitlab/database/migrations/timeout_helpers.rb:26:in `disable_statement_timeout'
/Users/stanhu/gdk-ee/gitlab/lib/gitlab/database/migrations/constraints_helpers.rb:112:in `validate_check_constraint'
/Users/stanhu/gdk-ee/gitlab/lib/gitlab/database/migrations/constraints_helpers.rb:228:in `validate_not_null_constraint'
/Users/stanhu/gdk-ee/gitlab/db/post_migrate/20221107220420_validate_not_null_constraint_on_member_namespace_id.rb:9:in `up'
/Users/stanhu/gdk-ee/gitlab/lib/gitlab/database/migration_helpers/restrict_gitlab_schema.rb:33:in `block in exec_migration'
/Users/stanhu/gdk-ee/gitlab/lib/gitlab/database/query_analyzer.rb:37:in `within'
/Users/stanhu/gdk-ee/gitlab/lib/gitlab/database/migration_helpers/restrict_gitlab_schema.rb:30:in `exec_migration'
/Users/stanhu/gdk-ee/gitlab/lib/gitlab/database/migrations/lock_retry_mixin.rb:36:in `ddl_transaction'

Workaround

The current workaround is to drop this data manually:

gdk psql -d gitlabhq_development_ci -c 'TRUNCATE TABLE members CASCADE'
gdk psql -d gitlabhq_development_ci -c 'TRUNCATE TABLE routes CASCADE'
gdk psql -d gitlabhq_development_ci -c 'TRUNCATE TABLE issues CASCADE'
gdk psql -d gitlabhq_development_ci -c 'TRUNCATE TABLE merge_request_user_mentions CASCADE'
gdk psql -d gitlabhq_development_ci -c 'TRUNCATE TABLE merge_request_metrics CASCADE'

Now that gitlab#382130 (closed) has been closed, we should be able to run:

bin/rake gitlab:db:lock_writes
bin/rake gitlab:db:truncate_legacy_tables:main
bin/rake gitlab:db:truncate_legacy_tables:ci
bin/rake gitlab:db:unlock_writes

Impacted categories

The following categories relate to this issue:

• gdk-reliability - e.g. When a GDK action fails to complete.
• gdk-usability - e.g. Improvements or suggestions around how the GDK functions.
• gdk-performance - e.g. When a GDK action is slow or times out.

Steps to replicate (optional)

Proposal (optional)

I wonder if we should:

1. Have gdk doctor detect data present in the CI database (e.g. SELECT COUNT(*) FROM members) (and vice versa with the main database)
2. Mention this workaround if it does detect data present.

Feel free to disregard since gdk reset-data would also fix this. 😄

Environment (optional)

• Operating system name: <!-- output of `uname -a` command -->
• Architecture: <!-- output of `arch` command -->
• The contents of your gdk.yml (if any)
• Ruby version: <!-- output of `ruby --version` command -->
• GDK version: <!-- output of `git rev-parse --short HEAD` command -->