Skip to content
Snippets Groups Projects

Fix the on delete option for namespace_id FK on work_item_dates_sources

Merged Fix the on delete option for namespace_id FK on work_item_dates_sources
fix_work_item_source_dates_cascade_option_on_fk_to_namespace_id into master
Merged Alexandru Croitor requested to merge fix_work_item_source_dates_cascade_option_on_fk_to_namespace_id into master

What does this MR do and why?

This MR fixes the on delete option for the namespace_id foreign key on the work_item_dates_sources table.

Currently, the work_item_dates_sources table has a not null constraint on namespace_id and a foreign key on namespace_id with on delete nullify. These two constraints are conflicting because there is no reason to have a work_item_dates_sources record with a null namespace_id.

To resolve this conflict, this MR updates the on delete option for the namespace_id foreign key to cascade. This means that when a namespace is deleted, all associated work_item_dates_sources records will also be deleted.

re #449048 (closed)

MR acceptance checklist

Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Screenshots or screen recordings

Screenshots are required for UI changes, and strongly recommended for all other merge requests.

Before After

How to set up and validate locally

Numbered steps to set up and validate the change are strongly suggested.

This description was generated for revision 09dc977c using AI

Edited by Alexandru Croitor

Merge request reports

Loading
Loading

Activity

Filter activity
  • Approvals
  • Assignees & reviewers
  • Comments (from bots)
  • Comments (from users)
  • Commits & branches
  • Edits
  • Labels
  • Lock status
  • Mentions
  • Merge request status
  • Tracking
  • Alexandru Croitor assigned to @acroitor

    assigned to @acroitor

  • Alexandru Croitor requested review from @kassio

    requested review from @kassio

  • Alexandru Croitor changed the description

    changed the description

  • Alexandru Croitor added devopsplan groupproduct planning sectiondev typebug labels

    added devopsplan groupproduct planning sectiondev typebug labels

  • Alexandru Croitor changed milestone to %16.11

    changed milestone to %16.11

  • A deleted user added database databasereview pending development guidelines docsimprovement documentation maintenancerefactor typemaintenance labels and removed typebug label

    added database databasereview pending development guidelines docsimprovement documentation maintenancerefactor typemaintenance labels and removed typebug label

  • Ghost User
    Ghost User @ghost1
    Contributor
    2 Messages
    :book: This merge request adds or changes files that require a review from the Database team.
    :book: This MR contains docs in the /doc/development directory, but any Maintainer (other than the author) can merge. You do not need tech writer review.

    This merge request requires a database review. To make sure these changes are reviewed, take the following steps:

    1. Ensure the merge request has database and databasereview pending labels. If the merge request modifies database files, Danger will do this for you.
    2. Prepare your MR for database review according to the docs.
    3. Assign and mention the database reviewer suggested by Reviewer Roulette.

    The following files require a review from the Database team:

    • db/migrate/20240312145200_fix_work_item_source_dates_cascade_option_on_fk_to_namespace_id.rb
    • db/migrate/20240312150341_validate_new_namespace_id_fk_on_work_item_dates_sources.rb
    • db/migrate/20240312150526_remove_old_namespace_id_fk_on_work_item_dates_sources.rb
    • db/schema_migrations/20240312145200
    • db/schema_migrations/20240312150341
    • db/schema_migrations/20240312150526
    • db/structure.sql

    Reviewer roulette

    Category Reviewer Maintainer
    database @mwoolf profile link current availability (UTC+0, 2 hours behind author) @mayra-cabrera profile link current availability (UTC-6, 8 hours behind author)

    Please check reviewer's status!

    • available Reviewer is available!
    • unavailable Reviewer is unavailable!

    Please refer to documentation page for guidance on how you can benefit from the Reviewer Roulette, or use the GitLab Review Workload Dashboard to find other available reviewers.

    If needed, you can retry the :repeat: danger-review job that generated this comment.

    Generated by :no_entry_sign: Danger

    Edited by Ghost User
  • A deleted user added Data WarehouseImpact Check label

    added Data WarehouseImpact Check label

  • Alexandru Croitor added 1 commit

    added 1 commit

    • 07b8da68 - Fix the on delete option for namespace_id FK on work_item_dates_sources

    Compare with previous version

  • Ghost User
    Ghost User @ghost1
    Contributor

    Database migrations (on the main database)

    2 Warnings
    :warning: 20240312150341 - ValidateNewNamespaceIdFkOnWorkItemDatesSources had a query that exceeded
    timing guidelines    . Run time should not exceed 100ms, but it was 323.78ms. Please consider possible
    options to improve the query performance. 
    ALTER TABLE work_item_dates_sources VALIDATE
     CONSTRAINT fk_work_item_dates_sources_on_namespace_id
    :warning: 20240312150341 - ValidateNewNamespaceIdFkOnWorkItemDatesSources had a query that exceeded
    timing guidelines    . Run time should not exceed 100ms, but it was 318.68ms. Please consider possible
    options to improve the query performance. 
    SELECT $1 AS one
     FROM "postgres_foreign_keys" WHERE "postgres_foreign_keys"."constrained_table_name" = $2 AND
     "postgres_foreign_keys"."name" = $3
     LIMIT $4

    Migrations included in this change have been executed on gitlab.com data for testing purposes. For details, please see the migration testing pipeline (limited access).

    Migration Type Total runtime Result DB size change
    20240312145200 - FixWorkItemSourceDatesCascadeOptionOnFkToNamespaceId Regular 2.0 s :white_check_mark: +0.00 B
    20240312150341 - ValidateNewNamespaceIdFkOnWorkItemDatesSources Regular 2.5 s :warning: +0.00 B
    20240312150526 - RemoveOldNamespaceIdFkOnWorkItemDatesSources Regular 1.8 s :white_check_mark: +0.00 B
    Runtime Histogram for all migrations
    Query Runtime Count
    0 seconds - 0.01 seconds 0
    0.01 seconds - 0.1 seconds 12
    0.1 seconds - 1 second 2
    1 second - 5 seconds 0
    5 seconds - 15 seconds 0
    15 seconds - 5 minutes 0
    5 minutes + 0

    Migration: 20240312145200 - FixWorkItemSourceDatesCascadeOptionOnFkToNamespaceId

    • Type: Regular
    • Duration: 2.0 s
    • Database size change: +0.00 B
    Calls Total Time Max Time Mean Time Rows Query
    1 30.9 ms 30.9 ms 30.9 ms 0 
    ALTER TABLE work_item_dates_sources ADD CONSTRAINT fk_work_item_dates_sources_on_namespace_id FOREIGN KEY (namespace_id) REFERENCES namespaces (id) ON DELETE CASCADE NOT VALID
    2 0.4 ms 0.2 ms 0.2 ms 0 
    SELECT "postgres_partitioned_tables".*
      FROM "postgres_partitioned_tables"  WHERE (identifier = concat(current_schema(), $1, $2))
      LIMIT $3
    1 0.0 ms 0.0 ms 0.0 ms 0 
    SELECT $1 AS one
      FROM "postgres_foreign_keys"  WHERE "postgres_foreign_keys"."constrained_table_name" = $2 AND "postgres_foreign_keys"."referenced_table_name" = $3 AND "postgres_foreign_keys"."name" = $4 AND "postgres_foreign_keys"."constrained_columns" = $5 AND "postgres_foreign_keys"."referenced_columns" = $6 AND "postgres_foreign_keys"."on_delete_action" = $7
      LIMIT $8
    2 0.0 ms 0.0 ms 0.0 ms 2 
    SELECT pg_backend_pid()
    Histogram for FixWorkItemSourceDatesCascadeOptionOnFkToNamespaceId
    Query Runtime Count
    0 seconds - 0.01 seconds 0
    0.01 seconds - 0.1 seconds 6
    0.1 seconds - 1 second 0
    1 second - 5 seconds 0
    5 seconds - 15 seconds 0
    15 seconds - 5 minutes 0
    5 minutes + 0

    :warning: Migration: 20240312150341 - ValidateNewNamespaceIdFkOnWorkItemDatesSources

    • Type: Regular
    • Duration: 2.5 s
    • Database size change: +0.00 B
    Calls Total Time Max Time Mean Time Rows Query
    1 323.8 ms 323.8 ms 323.8 ms 0 
    ALTER TABLE work_item_dates_sources VALIDATE CONSTRAINT fk_work_item_dates_sources_on_namespace_id
    1 318.7 ms 318.7 ms 318.7 ms 1 
    SELECT $1 AS one
      FROM "postgres_foreign_keys"  WHERE "postgres_foreign_keys"."constrained_table_name" = $2 AND "postgres_foreign_keys"."name" = $3
      LIMIT $4
    2 0.0 ms 0.0 ms 0.0 ms 2 
    SELECT pg_backend_pid()
    Histogram for ValidateNewNamespaceIdFkOnWorkItemDatesSources
    Query Runtime Count
    0 seconds - 0.01 seconds 0
    0.01 seconds - 0.1 seconds 2
    0.1 seconds - 1 second 2
    1 second - 5 seconds 0
    5 seconds - 15 seconds 0
    15 seconds - 5 minutes 0
    5 minutes + 0

    Migration: 20240312150526 - RemoveOldNamespaceIdFkOnWorkItemDatesSources

    • Type: Regular
    • Duration: 1.8 s
    • Database size change: +0.00 B
    Calls Total Time Max Time Mean Time Rows Query
    1 10.5 ms 10.5 ms 10.5 ms 0 
    ALTER TABLE "work_item_dates_sources" DROP CONSTRAINT "fk_d602f0955d"
    1 9.8 ms 9.8 ms 9.8 ms 1 
    SELECT $1 AS one
      FROM "postgres_foreign_keys"  WHERE "postgres_foreign_keys"."constrained_table_name" = $2 AND "postgres_foreign_keys"."name" = $3 AND "postgres_foreign_keys"."constrained_columns" = $4 AND "postgres_foreign_keys"."on_delete_action" = $5
      LIMIT $6
    2 0.0 ms 0.0 ms 0.0 ms 2 
    SELECT pg_backend_pid()
    Histogram for RemoveOldNamespaceIdFkOnWorkItemDatesSources
    Query Runtime Count
    0 seconds - 0.01 seconds 0
    0.01 seconds - 0.1 seconds 4
    0.1 seconds - 1 second 0
    1 second - 5 seconds 0
    5 seconds - 15 seconds 0
    15 seconds - 5 minutes 0
    5 minutes + 0

    Other information

    Other migrations pending on GitLab.com
    Migration Type Total runtime Result DB size change
    20240301210341 - CreateCatalogResourceComponentUsagesTable Regular 2.6 s :white_check_mark: +8.00 KiB [note]
    20240301210400 - AddComponentFkToCatalogResourceComponentUsages Regular 2.3 s :white_check_mark: +0.00 B
    20240301210420 - AddCatalogResourceFkToCatalogResourceComponentUsages Regular 2.4 s :white_check_mark: +0.00 B
    20240301210440 - AddProjectFkToCatalogResourceComponentUsages Regular 2.2 s :white_check_mark: +0.00 B
    20240305141243 - PrepareIndexOnSbomOccurrencesComponentVersionIdAndTraversalIds Post deploy 2.5 s :white_check_mark: +8.00 KiB [note]
    20240305141244 - PrepareAsyncIndexOnSbomOccurrencesForAggregations Post deploy 2.5 s :white_check_mark: +0.00 B
    20240306153539 - AddNotNullConstraintToVulnerabilityReadsTraversalIds Post deploy 189.7 s :warning: +0.00 B
    Clone details
    Clone ID Clone Created At Clone Data Timestamp Expected Removal Time
    database-testing-2983004-13161980-main 2024-03-12T16:33:06Z 2024-03-11T08:11:23Z 2024-03-13 04:42:24 +0000
    database-testing-2983004-13161980-ci 2024-03-12T16:33:06Z 2024-03-12T12:45:04Z 2024-03-13 04:42:24 +0000

    Job artifacts

    Database migrations (on the ci database)

    1 Warnings
    :warning: 20240312150341 - ValidateNewNamespaceIdFkOnWorkItemDatesSources had a query that exceeded
    timing guidelines    . Run time should not exceed 100ms, but it was 296.45ms. Please consider possible
    options to improve the query performance. 
    SELECT $1 AS one
     FROM "postgres_foreign_keys" WHERE "postgres_foreign_keys"."constrained_table_name" = $2 AND
     "postgres_foreign_keys"."name" = $3
     LIMIT $4

    Migrations included in this change have been executed on gitlab.com data for testing purposes. For details, please see the migration testing pipeline (limited access).

    Migration Type Total runtime Result DB size change
    20240312145200 - FixWorkItemSourceDatesCascadeOptionOnFkToNamespaceId Regular 3.0 s :white_check_mark: +8.00 KiB [note]
    20240312150341 - ValidateNewNamespaceIdFkOnWorkItemDatesSources Regular 2.9 s :warning: +0.00 B
    20240312150526 - RemoveOldNamespaceIdFkOnWorkItemDatesSources Regular 2.7 s :white_check_mark: +0.00 B
    Runtime Histogram for all migrations
    Query Runtime Count
    0 seconds - 0.01 seconds 0
    0.01 seconds - 0.1 seconds 12
    0.1 seconds - 1 second 2
    1 second - 5 seconds 0
    5 seconds - 15 seconds 0
    15 seconds - 5 minutes 0
    5 minutes + 0

    Migration: 20240312145200 - FixWorkItemSourceDatesCascadeOptionOnFkToNamespaceId

    • Type: Regular
    • Duration: 3.0 s
    • Database size change: +8.00 KiB [note]
    Calls Total Time Max Time Mean Time Rows Query
    1 78.8 ms 78.8 ms 78.8 ms 0 
    ALTER TABLE work_item_dates_sources ADD CONSTRAINT fk_work_item_dates_sources_on_namespace_id FOREIGN KEY (namespace_id) REFERENCES namespaces (id) ON DELETE CASCADE NOT VALID
    2 0.4 ms 0.2 ms 0.2 ms 0 
    SELECT "postgres_partitioned_tables".*
      FROM "postgres_partitioned_tables"  WHERE (identifier = concat(current_schema(), $1, $2))
      LIMIT $3
    1 0.0 ms 0.0 ms 0.0 ms 0 
    SELECT $1 AS one
      FROM "postgres_foreign_keys"  WHERE "postgres_foreign_keys"."constrained_table_name" = $2 AND "postgres_foreign_keys"."referenced_table_name" = $3 AND "postgres_foreign_keys"."name" = $4 AND "postgres_foreign_keys"."constrained_columns" = $5 AND "postgres_foreign_keys"."referenced_columns" = $6 AND "postgres_foreign_keys"."on_delete_action" = $7
      LIMIT $8
    2 0.0 ms 0.0 ms 0.0 ms 2 
    SELECT pg_backend_pid()
    Histogram for FixWorkItemSourceDatesCascadeOptionOnFkToNamespaceId
    Query Runtime Count
    0 seconds - 0.01 seconds 0
    0.01 seconds - 0.1 seconds 5
    0.1 seconds - 1 second 1
    1 second - 5 seconds 0
    5 seconds - 15 seconds 0
    15 seconds - 5 minutes 0
    5 minutes + 0

    :warning: Migration: 20240312150341 - ValidateNewNamespaceIdFkOnWorkItemDatesSources

    • Type: Regular
    • Duration: 2.9 s
    • Database size change: +0.00 B
    Calls Total Time Max Time Mean Time Rows Query
    1 296.5 ms 296.5 ms 296.5 ms 1 
    SELECT $1 AS one
      FROM "postgres_foreign_keys"  WHERE "postgres_foreign_keys"."constrained_table_name" = $2 AND "postgres_foreign_keys"."name" = $3
      LIMIT $4
    1 31.1 ms 31.1 ms 31.1 ms 0 
    ALTER TABLE work_item_dates_sources VALIDATE CONSTRAINT fk_work_item_dates_sources_on_namespace_id
    2 0.0 ms 0.0 ms 0.0 ms 2 
    SELECT pg_backend_pid()
    Histogram for ValidateNewNamespaceIdFkOnWorkItemDatesSources
    Query Runtime Count
    0 seconds - 0.01 seconds 0
    0.01 seconds - 0.1 seconds 3
    0.1 seconds - 1 second 1
    1 second - 5 seconds 0
    5 seconds - 15 seconds 0
    15 seconds - 5 minutes 0
    5 minutes + 0

    Migration: 20240312150526 - RemoveOldNamespaceIdFkOnWorkItemDatesSources

    • Type: Regular
    • Duration: 2.7 s
    • Database size change: +0.00 B
    Calls Total Time Max Time Mean Time Rows Query
    1 10.7 ms 10.7 ms 10.7 ms 0 
    ALTER TABLE "work_item_dates_sources" DROP CONSTRAINT "fk_d602f0955d"
    1 8.1 ms 8.1 ms 8.1 ms 1 
    SELECT $1 AS one
      FROM "postgres_foreign_keys"  WHERE "postgres_foreign_keys"."constrained_table_name" = $2 AND "postgres_foreign_keys"."name" = $3 AND "postgres_foreign_keys"."constrained_columns" = $4 AND "postgres_foreign_keys"."on_delete_action" = $5
      LIMIT $6
    2 0.0 ms 0.0 ms 0.0 ms 2 
    SELECT pg_backend_pid()
    Histogram for RemoveOldNamespaceIdFkOnWorkItemDatesSources
    Query Runtime Count
    0 seconds - 0.01 seconds 0
    0.01 seconds - 0.1 seconds 4
    0.1 seconds - 1 second 0
    1 second - 5 seconds 0
    5 seconds - 15 seconds 0
    15 seconds - 5 minutes 0
    5 minutes + 0

    Other information

    Other migrations pending on GitLab.com
    Migration Type Total runtime Result DB size change
    20240301210341 - CreateCatalogResourceComponentUsagesTable Regular 3.4 s :warning: +8.00 KiB [note]
    20240301210400 - AddComponentFkToCatalogResourceComponentUsages Regular 3.1 s :white_check_mark: +0.00 B
    20240301210420 - AddCatalogResourceFkToCatalogResourceComponentUsages Regular 3.0 s :white_check_mark: +0.00 B
    20240301210440 - AddProjectFkToCatalogResourceComponentUsages Regular 3.0 s :warning: +0.00 B
    20240305141243 - PrepareIndexOnSbomOccurrencesComponentVersionIdAndTraversalIds Post deploy 3.3 s :white_check_mark: +8.00 KiB [note]
    20240305141244 - PrepareAsyncIndexOnSbomOccurrencesForAggregations Post deploy 3.3 s :white_check_mark: +0.00 B
    20240306153539 - AddNotNullConstraintToVulnerabilityReadsTraversalIds Post deploy 2.4 s :white_check_mark: +0.00 B
    Clone details
    Clone ID Clone Created At Clone Data Timestamp Expected Removal Time
    database-testing-2983004-13161980-main 2024-03-12T16:33:06Z 2024-03-11T08:11:23Z 2024-03-13 04:42:24 +0000
    database-testing-2983004-13161980-ci 2024-03-12T16:33:06Z 2024-03-12T12:45:04Z 2024-03-13 04:42:24 +0000

    Job artifacts

    Brought to you by gitlab-org/database-team/gitlab-com-database-testing. Epic

    Edited by Ghost User
  • A deleted user added database-testing-automation label

    added database-testing-automation label

  • Alexandru Croitor added 1 commit

    added 1 commit

    • e60e11ef - Fix the on delete option for namespace_id FK on work_item_dates_sources

    Compare with previous version

  • Kassio Borges approved this merge request

    approved this merge request

  • Kassio Borges removed review request for @kassio

    removed review request for @kassio

  • 🤖 GitLab Bot 🤖 added pipeline:mr-approved label

    added pipeline:mr-approved label

  • Désirée Chevalier mentioned in issue gitlab-com/gl-infra/production-engineering#25178 (closed)

    mentioned in issue gitlab-com/gl-infra/production-engineering#25178 (closed)

  • Ghost User
    Ghost User @ghost1
    Contributor

    E2E Test Result Summary

    allure-report-publisher generated test report!

    e2e-test-on-gdk: :white_check_mark: test report for e60e11ef

    expand test summary 
    +------------------------------------------------------------------+
|                          suites summary                          |
+-------------+--------+--------+---------+-------+-------+--------+
|             | passed | failed | skipped | flaky | total | result |
+-------------+--------+--------+---------+-------+-------+--------+
| Create      | 8      | 0      | 3       | 0     | 11    | ✅     |
| Plan        | 51     | 0      | 2       | 0     | 53    | ✅     |
| Govern      | 3      | 0      | 0       | 0     | 3     | ✅     |
| Package     | 0      | 0      | 1       | 0     | 1     | ➖     |
| Monitor     | 4      | 0      | 0       | 0     | 4     | ✅     |
| Data Stores | 2      | 0      | 0       | 0     | 2     | ✅     |
+-------------+--------+--------+---------+-------+-------+--------+
| Total       | 68     | 0      | 6       | 0     | 74    | ✅     |
+-------------+--------+--------+---------+-------+-------+--------+

    e2e-package-and-test: :white_check_mark: test report for e60e11ef

    expand test summary 
    +------------------------------------------------------------------+
|                          suites summary                          |
+-------------+--------+--------+---------+-------+-------+--------+
|             | passed | failed | skipped | flaky | total | result |
+-------------+--------+--------+---------+-------+-------+--------+
| Plan        | 245    | 0      | 19      | 0     | 264   | ✅     |
| Create      | 145    | 0      | 17      | 0     | 162   | ✅     |
| Govern      | 3      | 0      | 0       | 0     | 3     | ✅     |
| Data Stores | 2      | 0      | 0       | 0     | 2     | ✅     |
| Monitor     | 4      | 0      | 0       | 0     | 4     | ✅     |
| Package     | 0      | 0      | 1       | 0     | 1     | ➖     |
+-------------+--------+--------+---------+-------+-------+--------+
| Total       | 399    | 0      | 37      | 0     | 436   | ✅     |
+-------------+--------+--------+---------+-------+-------+--------+
    Edited by Ghost User
  • Snehansh Chaturvedi added Data WarehouseNot Impacted label and removed Data WarehouseImpact Check label

    added Data WarehouseNot Impacted label and removed Data WarehouseImpact Check label

  • Loading
  • Loading
  • Loading
  • Loading
  • Loading
  • Loading
  • Loading
  • Loading
  • Loading
  • Loading
Please register or sign in to reply