Skip to content
Snippets Groups Projects

Add and backfill group_id for packages_debian_group_architectures

Merged Add and backfill group_id for packages_debian_group_architectures
backfill-desired-sharding-key-small-table-packages_debian_group_architectures into master
Merged Bojan Marjanovic requested to merge backfill-desired-sharding-key-small-table-packages_debian_group_architectures into master

What does this MR do and why?

Add and backfill group_id for packages_debian_group_architectures.

This table has a desired sharding key configured (view configuration).

This merge request is the first step towards transforming the desired sharding key into a sharding key.

This involves three changes:

  • Adding a new column that will serve as the sharding key (along with the relevant index and foreign key).
  • Populating the sharding key when new records are created by adding a database function and trigger.
  • Scheduling a batched background migration to set the sharding key for existing records.

Once the background migration has completed, a second merge request will be created to finalize the background migration and validate the not null constraint.

How to verify

We have assigned a random backend engineer from grouppackage registry to review these changes. Please review this merge request from a backend perspective. The main thing we are looking to verify is that the added column and association match the values specified by the desired sharding key configuration and that backfilling the column from this other table makes sense in the context of this feature.

When you are finished, please:

  1. Trigger the database testing pipeline as instructed by Danger.
  2. Request a review from the backend maintainer and database reviewer suggested by Danger.

If you have any questions or concerns, reach out to @tigerwnz or @manojmj.

This merge request was generated by a once off keep implemented in !143774

This change was generated by gitlab-housekeeper using the Keeps::BackfillDesiredShardingKeySmallTable keep.

To provide feedback on your experience with gitlab-housekeeper please comment in #442003 (closed).

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
  • Bojan Marjanovic added automation:gitlab-housekeeper-authored database grouppackage registry maintenancescalability typemaintenance labels

    added automation:gitlab-housekeeper-authored database grouppackage registry maintenancescalability typemaintenance labels

  • 🤖 GitLab Bot 🤖 added devopspackage sectionci labels

    added devopspackage sectionci labels

  • Bojan Marjanovic added 1 commit

    added 1 commit

    • 38dba13a - Add and backfill group_id for packages_debian_group_architectures

    Compare with previous version

  • 🤖 GitLab Bot 🤖 added pipelinetier-3 label

    added pipelinetier-3 label

  • A deleted user added backend databasereview pending labels

    added backend databasereview pending labels

  • Ghost User
    Ghost User @ghost1
    Contributor
    2 Warnings
    :warning: 38dba13a: The commit body should not contain more than 72 characters per line. For more information, take a look at our Commit message guidelines.
    :warning: The master pipeline status page reported failures in

    If these jobs fail in your merge request with the same errors, then they are not caused by your changes.
    Please check for any on-going incidents in the incident issue tracker or in the #master-broken Slack channel.

    Reviewer roulette

    Category Reviewer Maintainer
    backend @jwanjohi profile link current availability (UTC+1, 1 hour behind author) @alipniagov profile link current availability (UTC+2, same timezone as author)
    database @ifarkas profile link current availability (UTC+2, same timezone as author) @praba.m7n profile link current availability (UTC+5.5, 3.5 hours ahead of author)

    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
  • Bojan Marjanovic assigned to @bmarjanovic

    assigned to @bmarjanovic

  • Bojan Marjanovic changed milestone to %17.2

    changed milestone to %17.2

  • Bojan Marjanovic mentioned in epic &13678

    mentioned in epic &13678

  • Ghost User
    Ghost User @ghost1
    Contributor

    Database migrations (on the main database)

    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
    20240702072515 - AddGroupIdToPackagesDebianGroupArchitectures Regular 4.2 s :white_check_mark: +0.00 B
    20240702072516 - IndexPackagesDebianGroupArchitecturesOnGroupId Post deploy 4.9 s :white_check_mark: +8.00 KiB [note]
    20240702072517 - AddPackagesDebianGroupArchitecturesGroupIdFk Post deploy 5.2 s :white_check_mark: +8.00 KiB [note]
    20240702072518 - AddPackagesDebianGroupArchitecturesGroupIdTrigger Post deploy 4.5 s :white_check_mark: +8.00 KiB [note]
    20240702072519 - QueueBackfillPackagesDebianGroupArchitecturesGroupId Post deploy 5.0 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 0
    0.1 seconds - 1 second 24
    1 second - 5 seconds 0
    5 seconds - 15 seconds 0
    15 seconds - 5 minutes 0
    5 minutes + 0

    Migration: 20240702072515 - AddGroupIdToPackagesDebianGroupArchitectures

    • Type: Regular
    • Duration: 4.2 s
    • Database size change: +0.00 B
    Calls Total Time Max Time Mean Time Rows Query
    1 3.5 ms 3.5 ms 3.5 ms 0 
    ALTER TABLE "packages_debian_group_architectures" ADD "group_id" bigint
    2 0.0 ms 0.0 ms 0.0 ms 2 
    SELECT pg_backend_pid()
    Histogram for AddGroupIdToPackagesDebianGroupArchitectures
    Query Runtime Count
    0 seconds - 0.01 seconds 0
    0.01 seconds - 0.1 seconds 0
    0.1 seconds - 1 second 3
    1 second - 5 seconds 0
    5 seconds - 15 seconds 0
    15 seconds - 5 minutes 0
    5 minutes + 0

    Migration: 20240702072516 - IndexPackagesDebianGroupArchitecturesOnGroupId

    • Type: Post deploy
    • Duration: 4.9 s
    • Database size change: +8.00 KiB [note]
    Calls Total Time Max Time Mean Time Rows Query
    1 3.9 ms 3.9 ms 3.9 ms 0 
    CREATE INDEX CONCURRENTLY "index_packages_debian_group_architectures_on_group_id" ON "packages_debian_group_architectures" ("group_id")
    2 0.0 ms 0.0 ms 0.0 ms 2 
    SELECT pg_backend_pid()
    Histogram for IndexPackagesDebianGroupArchitecturesOnGroupId
    Query Runtime Count
    0 seconds - 0.01 seconds 0
    0.01 seconds - 0.1 seconds 0
    0.1 seconds - 1 second 3
    1 second - 5 seconds 0
    5 seconds - 15 seconds 0
    15 seconds - 5 minutes 0
    5 minutes + 0

    Migration: 20240702072517 - AddPackagesDebianGroupArchitecturesGroupIdFk

    • Type: Post deploy
    • Duration: 5.2 s
    • Database size change: +8.00 KiB [note]
    Calls Total Time Max Time Mean Time Rows Query
    1 2.7 ms 2.7 ms 2.7 ms 0 
    ALTER TABLE packages_debian_group_architectures ADD CONSTRAINT fk_92714bcab1 FOREIGN KEY (group_id) REFERENCES namespaces (id) ON DELETE CASCADE NOT VALID
    1 1.5 ms 1.5 ms 1.5 ms 0 
    ALTER TABLE packages_debian_group_architectures VALIDATE CONSTRAINT fk_92714bcab1
    2 0.5 ms 0.3 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 AddPackagesDebianGroupArchitecturesGroupIdFk
    Query Runtime Count
    0 seconds - 0.01 seconds 0
    0.01 seconds - 0.1 seconds 0
    0.1 seconds - 1 second 7
    1 second - 5 seconds 0
    5 seconds - 15 seconds 0
    15 seconds - 5 minutes 0
    5 minutes + 0

    Migration: 20240702072518 - AddPackagesDebianGroupArchitecturesGroupIdTrigger

    • Type: Post deploy
    • Duration: 4.5 s
    • Database size change: +8.00 KiB [note]
    Calls Total Time Max Time Mean Time Rows Query
    1 1.2 ms 1.2 ms 1.2 ms 0 
    CREATE OR REPLACE FUNCTION trigger_e0864d1cff37() RETURNS TRIGGER AS $1 LANGUAGE PLPGSQL
    1 1.2 ms 1.2 ms 1.2 ms 0 
    CREATE TRIGGER trigger_e0864d1cff37 BEFORE INSERT OR UPDATE ON "packages_debian_group_architectures" FOR EACH ROW EXECUTE FUNCTION trigger_e0864d1cff37()
    1 0.2 ms 0.2 ms 0.2 ms 0 
    DROP TRIGGER IF EXISTS trigger_e0864d1cff37 ON "packages_debian_group_architectures"
    2 0.0 ms 0.0 ms 0.0 ms 2 
    SELECT pg_backend_pid()
    Histogram for AddPackagesDebianGroupArchitecturesGroupIdTrigger
    Query Runtime Count
    0 seconds - 0.01 seconds 0
    0.01 seconds - 0.1 seconds 0
    0.1 seconds - 1 second 5
    1 second - 5 seconds 0
    5 seconds - 15 seconds 0
    15 seconds - 5 minutes 0
    5 minutes + 0

    Migration: 20240702072519 - QueueBackfillPackagesDebianGroupArchitecturesGroupId

    • Type: Post deploy
    • Duration: 5.0 s
    • Database size change: +0.00 B
    Calls Total Time Max Time Mean Time Rows Query
    1 0.1 ms 0.1 ms 0.1 ms 1 
    INSERT INTO "batched_background_migrations" ("created_at", "updated_at", "max_value", "batch_size", "sub_batch_size", "interval", "status", "job_class_name", "table_name", "column_name", "job_arguments", "gitlab_schema", "finished_at", "queued_migration_version") VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14) RETURNING "id"
    1 0.0 ms 0.0 ms 0.0 ms 0 
    SELECT $1 AS one
      FROM "batched_background_migrations"  WHERE "batched_background_migrations"."job_class_name" = $2 AND "batched_background_migrations"."table_name" = $3 AND "batched_background_migrations"."column_name" = $4 AND (job_arguments = $5) AND "batched_background_migrations"."gitlab_schema" = $6
      LIMIT $7
    1 0.0 ms 0.0 ms 0.0 ms 0 
    SELECT $1 AS one
      FROM "batched_background_migrations"  WHERE "batched_background_migrations"."job_arguments" = $2 AND "batched_background_migrations"."job_class_name" = $3 AND "batched_background_migrations"."table_name" = $4 AND "batched_background_migrations"."column_name" = $5
      LIMIT $6
    2 0.0 ms 0.0 ms 0.0 ms 2 
    SELECT pg_backend_pid()
    1 0.0 ms 0.0 ms 0.0 ms 1 
    SELECT MAX("id")
      FROM "packages_debian_group_architectures"
    Histogram for QueueBackfillPackagesDebianGroupArchitecturesGroupId
    Query Runtime Count
    0 seconds - 0.01 seconds 0
    0.01 seconds - 0.1 seconds 0
    0.1 seconds - 1 second 6
    1 second - 5 seconds 0
    5 seconds - 15 seconds 0
    15 seconds - 5 minutes 0
    5 minutes + 0

    Background Migration: BackfillPackagesDebianProjectArchitecturesProjectId

    Sampled 1 batches. Estimated Time to complete: 0 seconds
    • Interval: 120s
    • Total tuple count: 2
    • Max batch size: 0
    • Estimated seconds to complete: 0s
    • Estimated number of batches: 0
    • Average batch time: 2.17s
    • Batch size: 1000
    • N. of batches sampled: 1
    • N. of failed batches: 0

    Time estimation is conservative and based on sampling production data in a test environment. It represents the max time that migration could take. The actual time may differ from this estimation.

    _Consider changing max_batch_size and interval if this estimate is unacceptable._

    Calls Total Time Max Time Mean Time Rows Query
    1 16.7 ms 16.7 ms 16.7 ms 2 
    UPDATE packages_debian_project_architectures
      SET project_id = packages_debian_project_distributions.project_id
      FROM packages_debian_project_distributions
      WHERE packages_debian_project_distributions.id = packages_debian_project_architectures.distribution_id AND packages_debian_project_architectures.id IN (
        SELECT packages_debian_project_architectures.id
        FROM packages_debian_project_architectures
        WHERE packages_debian_project_architectures.id BETWEEN $1 AND $2 AND packages_debian_project_architectures.project_id IS NULL AND packages_debian_project_architectures.id >= $3
      )
    2 6.8 ms 6.6 ms 3.4 ms 2 
    INSERT INTO batched_background_migration_job_transition_logs (batched_background_migration_job_id, created_at, updated_at, previous_status, next_status) VALUES ($1, $2, $3, $4, $5) RETURNING id
    1 0.2 ms 0.2 ms 0.2 ms 1 
    UPDATE batched_background_migration_jobs
      SET updated_at = $1, started_at = $2, status = $3, attempts = $4
      WHERE batched_background_migration_jobs.id = $5
    1 0.1 ms 0.1 ms 0.1 ms 1 
    UPDATE batched_background_migration_jobs
      SET updated_at = $1, finished_at = $2, status = $3, metrics = $4
      WHERE batched_background_migration_jobs.id = $5
    2 0.1 ms 0.1 ms 0.0 ms 2 
    SELECT batched_background_migration_jobs.*
      FROM batched_background_migration_jobs
      WHERE batched_background_migration_jobs.id = $1
      LIMIT $2
    1 0.1 ms 0.1 ms 0.1 ms 0 
    SELECT packages_debian_project_architectures.id
      FROM packages_debian_project_architectures
      WHERE packages_debian_project_architectures.id BETWEEN $1 AND $2 AND packages_debian_project_architectures.project_id IS NULL AND packages_debian_project_architectures.id >= $3
      ORDER BY packages_debian_project_architectures.id ASC
      LIMIT $4
      OFFSET $5
    1 0.0 ms 0.0 ms 0.0 ms 1 
    SELECT batched_background_migrations.*
      FROM batched_background_migrations
      WHERE batched_background_migrations.id = $1
      LIMIT $2
    1 0.0 ms 0.0 ms 0.0 ms 1 
    SELECT sum(batched_background_migration_jobs.batch_size)
      FROM batched_background_migration_jobs
      WHERE batched_background_migration_jobs.batched_background_migration_id = $1 AND batched_background_migration_jobs.status IN ($2)
    1 0.0 ms 0.0 ms 0.0 ms 1 
    SELECT packages_debian_project_architectures.id
      FROM packages_debian_project_architectures
      WHERE packages_debian_project_architectures.id BETWEEN $1 AND $2 AND packages_debian_project_architectures.project_id IS NULL
      ORDER BY packages_debian_project_architectures.id ASC
      LIMIT $3
    Histogram of batch runtimes for BackfillPackagesDebianProjectArchitecturesProjectId
    Batch Runtime Count
    0 seconds - 10 seconds 1
    10 seconds - 1 minute 0
    1 minute - 2 minutes 0
    2 minutes - 3 minutes 0
    3 minutes - 5 minutes 0
    5 minutes + 0
    Histogram across all sampled batches of BackfillPackagesDebianProjectArchitecturesProjectId
    Query Runtime Count
    0 seconds - 0.1 seconds 0
    0.1 seconds - 0.5 seconds 11
    0.5 seconds - 1 second 0
    1 second - 2 seconds 0
    2 seconds - 5 seconds 0
    5 seconds + 0

    Other information

    Other migrations pending on GitLab.com
    Migration Type Total runtime Result DB size change
    20240515022318 - CreateDuoWorkflowsTables Regular 5.9 s :white_check_mark: +104.00 KiB
    20240515022319 - CreateDuoWorkflowsUserForeignKey Regular 5.5 s :white_check_mark: +0.00 B
    20240515022320 - CreateDuoWorkflowsProjectForeignKey Regular 5.3 s :warning: +0.00 B
    20240515022321 - CreateDuoWorkflowCheckpointsProjectForeignKey Regular 5.2 s :white_check_mark: +0.00 B
    20240618081110 - AddOrganizationIdColumnToStageEventHashes Regular 4.4 s :white_check_mark: +0.00 B
    20240618081525 - AddStageEventHashesOrganizationIdIndex Regular 5.0 s :white_check_mark: +312.00 KiB
    20240701074848 - AddGroupIdToPackagesDebianGroupComponents Regular 4.3 s :white_check_mark: +0.00 B
    20240611121818 - RemoveProjectStatisticsPackagesSizeAndProjectIdIndex Post deploy 5.2 s :white_check_mark: +0.00 B
    20240618091008 - RemoveStageEventHashesUniqueIndex Post deploy 4.9 s :white_check_mark: -448.00 KiB
    20240627122810 - QueueQueueBackfillAutocancelPartitionIdOnCiPipelines Post deploy 4.2 s :white_check_mark: +0.00 B
    20240628105731 - FkToCiPipelinesFromPCiBuildsOnPartitionIdAndUpstreamPipelineId Post deploy 7.5 s :white_check_mark: +0.00 B
    20240628105732 - ValidateAsyncFkOnPCiBuildsPartitionIdAndUpstreamPipelineId Post deploy 6.9 s :white_check_mark: +8.00 KiB [note]
    20240628105816 - FkToCiPipelinesFromPCiBuildsOnPartitionIdAndCommitId Post deploy 7.6 s :white_check_mark: +0.00 B
    20240628105817 - ValidateAsyncFkOnPCiBuildsPartitionIdAndCommitId Post deploy 6.7 s :white_check_mark: +0.00 B
    20240628105903 - FkToCiPipelinesFromPCiBuildsOnAutoCanceledByPartitionId Post deploy 7.6 s :white_check_mark: +0.00 B
    20240628105904 - ValidateAsyncFkOnPCiBuildsAutoCanceledByPartitionId Post deploy 6.7 s :white_check_mark: +0.00 B
    20240628133955 - IndexPackagesDebianProjectArchitecturesOnProjectId Post deploy 4.9 s :white_check_mark: +16.00 KiB
    20240628133956 - AddPackagesDebianProjectArchitecturesProjectIdFk Post deploy 5.1 s :white_check_mark: +0.00 B
    20240628133957 - AddPackagesDebianProjectArchitecturesProjectIdTrigger Post deploy 4.5 s :white_check_mark: +0.00 B
    20240628133958 - QueueBackfillPackagesDebianProjectArchitecturesProjectId Post deploy 5.3 s :white_check_mark: +0.00 B
    20240630231802 - FinalizeConvertCreditCardValidationDataToHashes Post deploy 4.7 s :white_check_mark: +0.00 B
    20240701074849 - IndexPackagesDebianGroupComponentsOnGroupId Post deploy 4.9 s :white_check_mark: +8.00 KiB [note]
    20240701074850 - AddPackagesDebianGroupComponentsGroupIdFk Post deploy 5.3 s :white_check_mark: +0.00 B
    20240701074851 - AddPackagesDebianGroupComponentsGroupIdTrigger Post deploy 4.5 s :white_check_mark: +8.00 KiB [note]
    20240701074852 - QueueBackfillPackagesDebianGroupComponentsGroupId Post deploy 5.1 s :white_check_mark: +0.00 B
    20240701083851 - RemovePartitionIdDefaultValueForCiSourcesProjects Post deploy 4.4 s :white_check_mark: +0.00 B
    Clone details
    Clone ID Clone Created At Clone Data Timestamp Expected Removal Time
    database-testing-3465978-14515583-main 2024-07-02T07:36:26Z 2024-07-02T04:09:58Z 2024-07-02 20:25:00 +0000
    database-testing-3465978-14515583-ci 2024-07-02T07:36:26Z 2024-07-02T04:44:57Z 2024-07-02 20:25:00 +0000

    Job artifacts

    Database migrations (on the ci database)

    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
    20240702072515 - AddGroupIdToPackagesDebianGroupArchitectures Regular 6.9 s :white_check_mark: +0.00 B
    20240702072516 - IndexPackagesDebianGroupArchitecturesOnGroupId Post deploy 7.0 s :white_check_mark: +8.00 KiB [note]
    20240702072517 - AddPackagesDebianGroupArchitecturesGroupIdFk Post deploy 7.1 s :white_check_mark: +0.00 B
    20240702072518 - AddPackagesDebianGroupArchitecturesGroupIdTrigger Post deploy 6.6 s :white_check_mark: +0.00 B
    20240702072519 - QueueBackfillPackagesDebianGroupArchitecturesGroupId Post deploy 6.2 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 6
    0.1 seconds - 1 second 14
    1 second - 5 seconds 0
    5 seconds - 15 seconds 0
    15 seconds - 5 minutes 0
    5 minutes + 0

    Migration: 20240702072515 - AddGroupIdToPackagesDebianGroupArchitectures

    • Type: Regular
    • Duration: 6.9 s
    • Database size change: +0.00 B
    Calls Total Time Max Time Mean Time Rows Query
    1 5.4 ms 5.4 ms 5.4 ms 0 
    ALTER TABLE "packages_debian_group_architectures" ADD "group_id" bigint
    2 0.0 ms 0.0 ms 0.0 ms 2 
    SELECT pg_backend_pid()
    Histogram for AddGroupIdToPackagesDebianGroupArchitectures
    Query Runtime Count
    0 seconds - 0.01 seconds 0
    0.01 seconds - 0.1 seconds 1
    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: 20240702072516 - IndexPackagesDebianGroupArchitecturesOnGroupId

    • Type: Post deploy
    • Duration: 7.0 s
    • Database size change: +8.00 KiB [note]
    Calls Total Time Max Time Mean Time Rows Query
    1 4.8 ms 4.8 ms 4.8 ms 0 
    CREATE INDEX CONCURRENTLY "index_packages_debian_group_architectures_on_group_id" ON "packages_debian_group_architectures" ("group_id")
    2 0.0 ms 0.0 ms 0.0 ms 2 
    SELECT pg_backend_pid()
    Histogram for IndexPackagesDebianGroupArchitecturesOnGroupId
    Query Runtime Count
    0 seconds - 0.01 seconds 0
    0.01 seconds - 0.1 seconds 1
    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: 20240702072517 - AddPackagesDebianGroupArchitecturesGroupIdFk

    • Type: Post deploy
    • Duration: 7.1 s
    • Database size change: +0.00 B
    Calls Total Time Max Time Mean Time Rows Query
    1 3.5 ms 3.5 ms 3.5 ms 0 
    ALTER TABLE packages_debian_group_architectures ADD CONSTRAINT fk_92714bcab1 FOREIGN KEY (group_id) REFERENCES namespaces (id) ON DELETE CASCADE NOT VALID
    1 1.6 ms 1.6 ms 1.6 ms 0 
    ALTER TABLE packages_debian_group_architectures VALIDATE CONSTRAINT fk_92714bcab1
    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 AddPackagesDebianGroupArchitecturesGroupIdFk
    Query Runtime Count
    0 seconds - 0.01 seconds 0
    0.01 seconds - 0.1 seconds 1
    0.1 seconds - 1 second 6
    1 second - 5 seconds 0
    5 seconds - 15 seconds 0
    15 seconds - 5 minutes 0
    5 minutes + 0

    Migration: 20240702072518 - AddPackagesDebianGroupArchitecturesGroupIdTrigger

    • Type: Post deploy
    • Duration: 6.6 s
    • Database size change: +0.00 B
    Calls Total Time Max Time Mean Time Rows Query
    1 2.0 ms 2.0 ms 2.0 ms 0 
    CREATE OR REPLACE FUNCTION trigger_e0864d1cff37() RETURNS TRIGGER AS $1 LANGUAGE PLPGSQL
    1 1.3 ms 1.3 ms 1.3 ms 0 
    CREATE TRIGGER trigger_e0864d1cff37 BEFORE INSERT OR UPDATE ON "packages_debian_group_architectures" FOR EACH ROW EXECUTE FUNCTION trigger_e0864d1cff37()
    1 0.2 ms 0.2 ms 0.2 ms 0 
    DROP TRIGGER IF EXISTS trigger_e0864d1cff37 ON "packages_debian_group_architectures"
    2 0.0 ms 0.0 ms 0.0 ms 2 
    SELECT pg_backend_pid()
    Histogram for AddPackagesDebianGroupArchitecturesGroupIdTrigger
    Query Runtime Count
    0 seconds - 0.01 seconds 0
    0.01 seconds - 0.1 seconds 1
    0.1 seconds - 1 second 4
    1 second - 5 seconds 0
    5 seconds - 15 seconds 0
    15 seconds - 5 minutes 0
    5 minutes + 0

    Migration: 20240702072519 - QueueBackfillPackagesDebianGroupArchitecturesGroupId

    • Type: Post deploy
    • Duration: 6.2 s
    • Database size change: +0.00 B
    Calls Total Time Max Time Mean Time Rows Query
    2 0.0 ms 0.0 ms 0.0 ms 2 
    SELECT pg_backend_pid()
    Histogram for QueueBackfillPackagesDebianGroupArchitecturesGroupId
    Query Runtime Count
    0 seconds - 0.01 seconds 0
    0.01 seconds - 0.1 seconds 2
    0.1 seconds - 1 second 0
    1 second - 5 seconds 0
    5 seconds - 15 seconds 0
    15 seconds - 5 minutes 0
    5 minutes + 0

    Background Migration: QueueBackfillAutocancelPartitionIdOnCiPipelines

    Sampled 28 batches. Estimated Time to complete: 3 weeks, 1 day, 13 hours, and 22 minutes
    • Interval: 120s
    • Total tuple count: 1218086800
    • Max batch size: 0
    • Estimated seconds to complete: 1948920s
    • Estimated number of batches: 16241
    • Average batch time: 62.56s
    • Batch size: 75000
    • N. of batches sampled: 28
    • N. of failed batches: 0

    Time estimation is conservative and based on sampling production data in a test environment. It represents the max time that migration could take. The actual time may differ from this estimation.

    _Consider changing max_batch_size and interval if this estimate is unacceptable._

    Calls Total Time Max Time Mean Time Rows Query
    2772 825237.3 ms 1751.2 ms 297.7 ms 33316 
    UPDATE ci_pipelines
      SET auto_canceled_by_partition_id = canceling_pipelines.partition_id
      FROM ci_pipelines canceling_pipelines
      WHERE ci_pipelines.id BETWEEN $1 AND $2 AND ci_pipelines.id >= $3 AND ci_pipelines.id < $4 AND ci_pipelines.auto_canceled_by_id IS NOT NULL AND ci_pipelines.auto_canceled_by_id = canceling_pipelines.id
    28 7119.7 ms 905.4 ms 254.3 ms 312 
    UPDATE ci_pipelines
      SET auto_canceled_by_partition_id = canceling_pipelines.partition_id
      FROM ci_pipelines canceling_pipelines
      WHERE ci_pipelines.id BETWEEN $1 AND $2 AND ci_pipelines.id >= $3 AND ci_pipelines.auto_canceled_by_id IS NOT NULL AND ci_pipelines.auto_canceled_by_id = canceling_pipelines.id
    2800 986.2 ms 4.1 ms 0.4 ms 2772 
    SELECT ci_pipelines.id
      FROM ci_pipelines
      WHERE ci_pipelines.id BETWEEN $1 AND $2 AND ci_pipelines.id >= $3
      ORDER BY ci_pipelines.id ASC
      LIMIT $4
      OFFSET $5
    28 5.5 ms 2.4 ms 0.2 ms 28 
    UPDATE batched_background_migration_jobs
      SET updated_at = $1, started_at = $2, status = $3, attempts = $4
      WHERE batched_background_migration_jobs.id = $5
    28 10.1 ms 1.3 ms 0.4 ms 28 
    UPDATE batched_background_migration_jobs
      SET updated_at = $1, finished_at = $2, status = $3, metrics = $4
      WHERE batched_background_migration_jobs.id = $5
    56 9.6 ms 0.8 ms 0.2 ms 56 
    INSERT INTO batched_background_migration_job_transition_logs (batched_background_migration_job_id, created_at, updated_at, previous_status, next_status) VALUES ($1, $2, $3, $4, $5) RETURNING id
    56 3.6 ms 0.1 ms 0.1 ms 56 
    SELECT batched_background_migration_jobs.*
      FROM batched_background_migration_jobs
      WHERE batched_background_migration_jobs.id = $1
      LIMIT $2
    28 1.5 ms 0.1 ms 0.1 ms 28 
    SELECT sum(batched_background_migration_jobs.batch_size)
      FROM batched_background_migration_jobs
      WHERE batched_background_migration_jobs.batched_background_migration_id = $1 AND batched_background_migration_jobs.status IN ($2)
    28 1.1 ms 0.1 ms 0.0 ms 28 
    SELECT ci_pipelines.id
      FROM ci_pipelines
      WHERE ci_pipelines.id BETWEEN $1 AND $2
      ORDER BY ci_pipelines.id ASC
      LIMIT $3
    28 1.1 ms 0.1 ms 0.0 ms 28 
    SELECT batched_background_migrations.*
      FROM batched_background_migrations
      WHERE batched_background_migrations.id = $1
      LIMIT $2
    Histogram of batch runtimes for QueueBackfillAutocancelPartitionIdOnCiPipelines
    Batch Runtime Count
    0 seconds - 10 seconds 0
    10 seconds - 1 minute 14
    1 minute - 2 minutes 14
    2 minutes - 3 minutes 0
    3 minutes - 5 minutes 0
    5 minutes + 0
    Histogram across all sampled batches of QueueBackfillAutocancelPartitionIdOnCiPipelines
    Query Runtime Count
    0 seconds - 0.1 seconds 1
    0.1 seconds - 0.5 seconds 5054
    0.5 seconds - 1 second 768
    1 second - 2 seconds 29
    2 seconds - 5 seconds 0
    5 seconds + 0

    Other information

    Other migrations pending on GitLab.com
    Migration Type Total runtime Result DB size change
    20240515022318 - CreateDuoWorkflowsTables Regular 8.3 s :white_check_mark: +80.00 KiB
    20240515022319 - CreateDuoWorkflowsUserForeignKey Regular 7.6 s :white_check_mark: +0.00 B
    20240515022320 - CreateDuoWorkflowsProjectForeignKey Regular 7.2 s :white_check_mark: +0.00 B
    20240515022321 - CreateDuoWorkflowCheckpointsProjectForeignKey Regular 7.1 s :white_check_mark: +0.00 B
    20240618081110 - AddOrganizationIdColumnToStageEventHashes Regular 6.4 s :white_check_mark: +0.00 B
    20240618081525 - AddStageEventHashesOrganizationIdIndex Regular 7.1 s :white_check_mark: +8.00 KiB [note]
    20240701074848 - AddGroupIdToPackagesDebianGroupComponents Regular 6.9 s :white_check_mark: +0.00 B
    20240611121818 - RemoveProjectStatisticsPackagesSizeAndProjectIdIndex Post deploy 7.2 s :white_check_mark: +0.00 B
    20240618091008 - RemoveStageEventHashesUniqueIndex Post deploy 6.9 s :white_check_mark: -8.00 KiB
    20240627122810 - QueueQueueBackfillAutocancelPartitionIdOnCiPipelines Post deploy 7.4 s :white_check_mark: +0.00 B
    20240628105731 - FkToCiPipelinesFromPCiBuildsOnPartitionIdAndUpstreamPipelineId Post deploy 9.5 s :white_check_mark: +0.00 B
    20240628105732 - ValidateAsyncFkOnPCiBuildsPartitionIdAndUpstreamPipelineId Post deploy 9.0 s :white_check_mark: +8.00 KiB [note]
    20240628105816 - FkToCiPipelinesFromPCiBuildsOnPartitionIdAndCommitId Post deploy 9.5 s :white_check_mark: +8.00 KiB [note]
    20240628105817 - ValidateAsyncFkOnPCiBuildsPartitionIdAndCommitId Post deploy 8.7 s :white_check_mark: +0.00 B
    20240628105903 - FkToCiPipelinesFromPCiBuildsOnAutoCanceledByPartitionId Post deploy 9.5 s :white_check_mark: +16.00 KiB
    20240628105904 - ValidateAsyncFkOnPCiBuildsAutoCanceledByPartitionId Post deploy 8.7 s :white_check_mark: +0.00 B
    20240628133955 - IndexPackagesDebianProjectArchitecturesOnProjectId Post deploy 7.0 s :white_check_mark: +8.00 KiB [note]
    20240628133956 - AddPackagesDebianProjectArchitecturesProjectIdFk Post deploy 7.2 s :white_check_mark: +8.00 KiB [note]
    20240628133957 - AddPackagesDebianProjectArchitecturesProjectIdTrigger Post deploy 6.5 s :white_check_mark: +0.00 B
    20240628133958 - QueueBackfillPackagesDebianProjectArchitecturesProjectId Post deploy 6.2 s :white_check_mark: +0.00 B
    20240630231802 - FinalizeConvertCreditCardValidationDataToHashes Post deploy 6.1 s :white_check_mark: +0.00 B
    20240701074849 - IndexPackagesDebianGroupComponentsOnGroupId Post deploy 6.9 s :white_check_mark: +8.00 KiB [note]
    20240701074850 - AddPackagesDebianGroupComponentsGroupIdFk Post deploy 7.3 s :white_check_mark: +0.00 B
    20240701074851 - AddPackagesDebianGroupComponentsGroupIdTrigger Post deploy 6.5 s :white_check_mark: +0.00 B
    20240701074852 - QueueBackfillPackagesDebianGroupComponentsGroupId Post deploy 6.3 s :white_check_mark: +0.00 B
    20240701083851 - RemovePartitionIdDefaultValueForCiSourcesProjects Post deploy 6.4 s :white_check_mark: +0.00 B
    Clone details
    Clone ID Clone Created At Clone Data Timestamp Expected Removal Time
    database-testing-3465978-14515583-main 2024-07-02T07:36:26Z 2024-07-02T04:09:58Z 2024-07-02 20:25:00 +0000
    database-testing-3465978-14515583-ci 2024-07-02T07:36:26Z 2024-07-02T04:44:57Z 2024-07-02 20:25:00 +0000

    Job artifacts

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

    Edited by ****
  • 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 38dba13a

    expand test summary 
    +------------------------------------------------------------------+
|                          suites summary                          |
+-------------+--------+--------+---------+-------+-------+--------+
|             | passed | failed | skipped | flaky | total | result |
+-------------+--------+--------+---------+-------+-------+--------+
| Plan        | 60     | 0      | 2       | 0     | 62    | ✅     |
| Create      | 126    | 0      | 12      | 0     | 138   | ✅     |
| Package     | 19     | 0      | 12      | 0     | 31    | ✅     |
| Verify      | 43     | 0      | 3       | 0     | 46    | ✅     |
| Govern      | 65     | 0      | 0       | 0     | 65    | ✅     |
| Monitor     | 8      | 0      | 0       | 0     | 8     | ✅     |
| Data Stores | 27     | 0      | 4       | 0     | 31    | ✅     |
| Release     | 5      | 0      | 0       | 0     | 5     | ✅     |
| Analytics   | 2      | 0      | 0       | 0     | 2     | ✅     |
| Manage      | 0      | 0      | 1       | 0     | 1     | ➖     |
| Fulfillment | 1      | 0      | 0       | 0     | 1     | ✅     |
+-------------+--------+--------+---------+-------+-------+--------+
| Total       | 356    | 0      | 34      | 0     | 390   | ✅     |
+-------------+--------+--------+---------+-------+-------+--------+
    **e2e-package-and-test**: :x: test report for 38dba13a
    expand test summary 
    +--------------------------------------------------------------+
|                        suites summary                        |
+---------+--------+--------+---------+-------+-------+--------+
|         | passed | failed | skipped | flaky | total | result |
+---------+--------+--------+---------+-------+-------+--------+
| Package | 31     | 0      | 14      | 0     | 45    | ✅     |
| Create  | 277    | 1      | 32      | 6     | 310   | ❌     |
| Govern  | 4      | 0      | 0       | 0     | 4     | ✅     |
| Plan    | 8      | 0      | 0       | 0     | 8     | ✅     |
+---------+--------+--------+---------+-------+-------+--------+
| Total   | 320    | 1      | 46      | 6     | 367   | ❌     |
+---------+--------+--------+---------+-------+-------+--------+
    **e2e-test-on-cng**: :white_check_mark: test report for 38dba13a
    expand test summary 
    +------------------------------------------------------------------+
|                          suites summary                          |
+-------------+--------+--------+---------+-------+-------+--------+
|             | passed | failed | skipped | flaky | total | result |
+-------------+--------+--------+---------+-------+-------+--------+
| Manage      | 2      | 0      | 8       | 0     | 10    | ✅     |
| Create      | 138    | 0      | 16      | 0     | 154   | ✅     |
| Govern      | 79     | 0      | 9       | 0     | 88    | ✅     |
| Plan        | 83     | 0      | 11      | 0     | 94    | ✅     |
| Package     | 23     | 0      | 15      | 0     | 38    | ✅     |
| Growth      | 0      | 0      | 2       | 0     | 2     | ➖     |
| Fulfillment | 2      | 0      | 24      | 0     | 26    | ✅     |
| Data Stores | 30     | 0      | 13      | 0     | 43    | ✅     |
| Verify      | 52     | 0      | 14      | 0     | 66    | ✅     |
| ModelOps    | 0      | 0      | 1       | 0     | 1     | ➖     |
| Monitor     | 8      | 0      | 12      | 0     | 20    | ✅     |
| Secure      | 3      | 0      | 2       | 0     | 5     | ✅     |
| Configure   | 0      | 0      | 3       | 0     | 3     | ➖     |
| Ai-powered  | 0      | 0      | 1       | 0     | 1     | ➖     |
| Release     | 5      | 0      | 1       | 0     | 6     | ✅     |
| Analytics   | 2      | 0      | 0       | 0     | 2     | ✅     |
+-------------+--------+--------+---------+-------+-------+--------+
| Total       | 427    | 0      | 132     | 0     | 559   | ✅     |
+-------------+--------+--------+---------+-------+-------+--------+
    Edited by Ghost User
  • **** added database-testing-automation label

    added database-testing-automation label

  • Bojan Marjanovic requested review from @ahegyi

    requested review from @ahegyi

  • Adam Hegyi resolved all threads

    resolved all threads

  • Adam Hegyi approved this merge request

    approved this merge request

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

    added pipeline:mr-approved label

  • Adam Hegyi added databaseapproved label and removed databasereview pending pipeline:mr-approved labels

    added databaseapproved label and removed databasereview pending pipeline:mr-approved labels

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

    added pipeline:mr-approved label

  • Adam Hegyi enabled automatic add to merge train when the pipeline for e81d8e73 succeeds

    enabled automatic add to merge train when the pipeline for e81d8e73 succeeds

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