Add support for cursor based batched background migrations
What does this MR do and why?
Add support for cursor based batched background migrations
Related to #452258 (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.
Migration output
Up
main: == [advisory_lock_connection] object_id: 118900, pg_backend_pid: 95731
main: == 20241030182743 AddCursorsToBatchedBackgroundMigrationJobs: migrating =======
main: -- add_column(:batched_background_migration_jobs, :min_cursor, :jsonb, {:null=>true})
main: -> 0.0025s
main: -- add_column(:batched_background_migration_jobs, :max_cursor, :jsonb, {:null=>true})
main: -> 0.0005s
main: == 20241030182743 AddCursorsToBatchedBackgroundMigrationJobs: migrated (0.0068s)
main: == [advisory_lock_connection] object_id: 118900, pg_backend_pid: 95731
ci: == [advisory_lock_connection] object_id: 119560, pg_backend_pid: 95735
ci: == 20241030182743 AddCursorsToBatchedBackgroundMigrationJobs: migrating =======
ci: -- add_column(:batched_background_migration_jobs, :min_cursor, :jsonb, {:null=>true})
ci: -> 0.0010s
ci: -- add_column(:batched_background_migration_jobs, :max_cursor, :jsonb, {:null=>true})
ci: -> 0.0005s
ci: == 20241030182743 AddCursorsToBatchedBackgroundMigrationJobs: migrated (0.0085s)
ci: == [advisory_lock_connection] object_id: 119560, pg_backend_pid: 95735
main: == [advisory_lock_connection] object_id: 119860, pg_backend_pid: 95740
main: == 20241030182745 AddCursorsToBatchedBackgroundMigrations: migrating ==========
main: -- add_column(:batched_background_migrations, :min_cursor, :jsonb, {:null=>true})
main: -> 0.0009s
main: -- add_column(:batched_background_migrations, :max_cursor, :jsonb, {:null=>true})
main: -> 0.0005s
main: == 20241030182745 AddCursorsToBatchedBackgroundMigrations: migrated (0.0046s) =
main: == [advisory_lock_connection] object_id: 119860, pg_backend_pid: 95740
ci: == [advisory_lock_connection] object_id: 120380, pg_backend_pid: 95744
ci: == 20241030182745 AddCursorsToBatchedBackgroundMigrations: migrating ==========
ci: -- add_column(:batched_background_migrations, :min_cursor, :jsonb, {:null=>true})
ci: -> 0.0009s
ci: -- add_column(:batched_background_migrations, :max_cursor, :jsonb, {:null=>true})
ci: -> 0.0004s
ci: == 20241030182745 AddCursorsToBatchedBackgroundMigrations: migrated (0.0083s) =
ci: == [advisory_lock_connection] object_id: 120380, pg_backend_pid: 95744
main: == [advisory_lock_connection] object_id: 121000, pg_backend_pid: 95749
main: == 20241030182747 AddIdsOrCursorsConstraintToBatchedBackgroundMigrationJobs: migrating
main: -- transaction_open?(nil)
main: -> 0.0000s
main: -- transaction_open?(nil)
main: -> 0.0000s
main: -- execute("ALTER TABLE batched_background_migration_jobs\nADD CONSTRAINT check_c1ce96fe3b\nCHECK ( num_nonnulls(min_value, max_value) = 2 OR num_nonnulls(min_cursor, max_cursor) = 2 )\nNOT VALID;\n")
main: -> 0.0009s
main: -- execute("SET statement_timeout TO 0")
main: -> 0.0003s
main: -- execute("ALTER TABLE batched_background_migration_jobs VALIDATE CONSTRAINT check_c1ce96fe3b;")
main: -> 0.0008s
main: -- execute("RESET statement_timeout")
main: -> 0.0003s
main: == 20241030182747 AddIdsOrCursorsConstraintToBatchedBackgroundMigrationJobs: migrated (0.0268s)
main: == [advisory_lock_connection] object_id: 121000, pg_backend_pid: 95749
ci: == [advisory_lock_connection] object_id: 121500, pg_backend_pid: 95753
ci: == 20241030182747 AddIdsOrCursorsConstraintToBatchedBackgroundMigrationJobs: migrating
ci: -- transaction_open?(nil)
ci: -> 0.0000s
ci: -- transaction_open?(nil)
ci: -> 0.0000s
ci: -- execute("ALTER TABLE batched_background_migration_jobs\nADD CONSTRAINT check_c1ce96fe3b\nCHECK ( num_nonnulls(min_value, max_value) = 2 OR num_nonnulls(min_cursor, max_cursor) = 2 )\nNOT VALID;\n")
ci: -> 0.0010s
ci: -- execute("SET statement_timeout TO 0")
ci: -> 0.0003s
ci: -- execute("ALTER TABLE batched_background_migration_jobs VALIDATE CONSTRAINT check_c1ce96fe3b;")
ci: -> 0.0009s
ci: -- execute("RESET statement_timeout")
ci: -> 0.0003s
ci: == 20241030182747 AddIdsOrCursorsConstraintToBatchedBackgroundMigrationJobs: migrated (0.0141s)
ci: == [advisory_lock_connection] object_id: 121500, pg_backend_pid: 95753
main: == [advisory_lock_connection] object_id: 125680, pg_backend_pid: 95756
main: == 20241030182751 AddIdsOrCursorsConstraintToBatchedBackgroundMigrations: migrating
main: -- transaction_open?(nil)
main: -> 0.0000s
main: -- transaction_open?(nil)
main: -> 0.0000s
main: -- execute("ALTER TABLE batched_background_migrations\nADD CONSTRAINT check_f5158baa12\nCHECK ( num_nonnulls(min_value, max_value) = 2 OR num_nonnulls(min_cursor, max_cursor) = 2 )\nNOT VALID;\n")
main: -> 0.0008s
main: -- execute("SET statement_timeout TO 0")
main: -> 0.0003s
main: -- execute("ALTER TABLE batched_background_migrations VALIDATE CONSTRAINT check_f5158baa12;")
main: -> 0.0005s
main: -- execute("RESET statement_timeout")
main: -> 0.0003s
main: == 20241030182751 AddIdsOrCursorsConstraintToBatchedBackgroundMigrations: migrated (0.0091s)
main: == [advisory_lock_connection] object_id: 125680, pg_backend_pid: 95756
ci: == [advisory_lock_connection] object_id: 129760, pg_backend_pid: 95759
ci: == 20241030182751 AddIdsOrCursorsConstraintToBatchedBackgroundMigrations: migrating
ci: -- transaction_open?(nil)
ci: -> 0.0000s
ci: -- transaction_open?(nil)
ci: -> 0.0000s
ci: -- execute("ALTER TABLE batched_background_migrations\nADD CONSTRAINT check_f5158baa12\nCHECK ( num_nonnulls(min_value, max_value) = 2 OR num_nonnulls(min_cursor, max_cursor) = 2 )\nNOT VALID;\n")
ci: -> 0.0009s
ci: -- execute("SET statement_timeout TO 0")
ci: -> 0.0003s
ci: -- execute("ALTER TABLE batched_background_migrations VALIDATE CONSTRAINT check_f5158baa12;")
ci: -> 0.0005s
ci: -- execute("RESET statement_timeout")
ci: -> 0.0003s
ci: == 20241030182751 AddIdsOrCursorsConstraintToBatchedBackgroundMigrations: migrated (0.0129s)
ci: == [advisory_lock_connection] object_id: 129760, pg_backend_pid: 95759
main: == [advisory_lock_connection] object_id: 133940, pg_backend_pid: 95762
main: == 20241030182755 RemoveValuesNotNullConstraintsFromBatchedBackgroundMigrationJobs: migrating
main: -- change_column_null(:batched_background_migration_jobs, :max_value, true)
main: -> 0.0008s
main: -- change_column_null(:batched_background_migration_jobs, :min_value, true)
main: -> 0.0006s
main: == 20241030182755 RemoveValuesNotNullConstraintsFromBatchedBackgroundMigrationJobs: migrated (0.0048s)
main: == [advisory_lock_connection] object_id: 133940, pg_backend_pid: 95762
ci: == [advisory_lock_connection] object_id: 137980, pg_backend_pid: 95766
ci: == 20241030182755 RemoveValuesNotNullConstraintsFromBatchedBackgroundMigrationJobs: migrating
ci: -- change_column_null(:batched_background_migration_jobs, :max_value, true)
ci: -> 0.0008s
ci: -- change_column_null(:batched_background_migration_jobs, :min_value, true)
ci: -> 0.0004s
ci: == 20241030182755 RemoveValuesNotNullConstraintsFromBatchedBackgroundMigrationJobs: migrated (0.0080s)
ci: == [advisory_lock_connection] object_id: 137980, pg_backend_pid: 95766
main: == [advisory_lock_connection] object_id: 138540, pg_backend_pid: 95769
main: == 20241030182758 RemoveValuesNotNullConstraintsFromBatchedBackgroundMigrations: migrating
main: -- change_column_null(:batched_background_migrations, :max_value, true)
main: -> 0.0008s
main: -- change_column_null(:batched_background_migrations, :min_value, true)
main: -> 0.0005s
main: == 20241030182758 RemoveValuesNotNullConstraintsFromBatchedBackgroundMigrations: migrated (0.0044s)
main: == [advisory_lock_connection] object_id: 138540, pg_backend_pid: 95769
ci: == [advisory_lock_connection] object_id: 139000, pg_backend_pid: 95771
ci: == 20241030182758 RemoveValuesNotNullConstraintsFromBatchedBackgroundMigrations: migrating
ci: -- change_column_null(:batched_background_migrations, :max_value, true)
ci: -> 0.0007s
ci: -- change_column_null(:batched_background_migrations, :min_value, true)
ci: -> 0.0005s
ci: == 20241030182758 RemoveValuesNotNullConstraintsFromBatchedBackgroundMigrations: migrated (0.0079s)
ci: == [advisory_lock_connection] object_id: 139000, pg_backend_pid: 95771
main: == [advisory_lock_connection] object_id: 139560, pg_backend_pid: 95774
main: == 20241030182749 AddJsonbArrayConstraintsToBatchedBackgroundMigrationJobs: migrating
main: -- transaction_open?(nil)
main: -> 0.0000s
main: -- transaction_open?(nil)
main: -> 0.0000s
main: -- execute("ALTER TABLE batched_background_migration_jobs\nADD CONSTRAINT check_18d498ea58\nCHECK ( jsonb_typeof(min_cursor) = 'array' AND jsonb_typeof(max_cursor) = 'array' )\nNOT VALID;\n")
main: -> 0.0008s
main: -- execute("SET statement_timeout TO 0")
main: -> 0.0003s
main: -- execute("ALTER TABLE batched_background_migration_jobs VALIDATE CONSTRAINT check_18d498ea58;")
main: -> 0.0006s
main: -- execute("RESET statement_timeout")
main: -> 0.0003s
main: == 20241030182749 AddJsonbArrayConstraintsToBatchedBackgroundMigrationJobs: migrated (0.0094s)
main: == [advisory_lock_connection] object_id: 139560, pg_backend_pid: 95774
ci: == [advisory_lock_connection] object_id: 140060, pg_backend_pid: 95776
ci: == 20241030182749 AddJsonbArrayConstraintsToBatchedBackgroundMigrationJobs: migrating
ci: -- transaction_open?(nil)
ci: -> 0.0000s
ci: -- transaction_open?(nil)
ci: -> 0.0000s
ci: -- execute("ALTER TABLE batched_background_migration_jobs\nADD CONSTRAINT check_18d498ea58\nCHECK ( jsonb_typeof(min_cursor) = 'array' AND jsonb_typeof(max_cursor) = 'array' )\nNOT VALID;\n")
ci: -> 0.0008s
ci: -- execute("SET statement_timeout TO 0")
ci: -> 0.0003s
ci: -- execute("ALTER TABLE batched_background_migration_jobs VALIDATE CONSTRAINT check_18d498ea58;")
ci: -> 0.0005s
ci: -- execute("RESET statement_timeout")
ci: -> 0.0003s
ci: == 20241030182749 AddJsonbArrayConstraintsToBatchedBackgroundMigrationJobs: migrated (0.0130s)
ci: == [advisory_lock_connection] object_id: 140060, pg_backend_pid: 95776
main: == [advisory_lock_connection] object_id: 144240, pg_backend_pid: 95779
main: == 20241030182753 AddJsonbArrayConstraintsToBatchedBackgroundMigrations: migrating
main: -- transaction_open?(nil)
main: -> 0.0000s
main: -- transaction_open?(nil)
main: -> 0.0000s
main: -- execute("ALTER TABLE batched_background_migrations\nADD CONSTRAINT check_122750e705\nCHECK ( jsonb_typeof(min_cursor) = 'array' AND jsonb_typeof(max_cursor) = 'array' )\nNOT VALID;\n")
main: -> 0.0008s
main: -- execute("SET statement_timeout TO 0")
main: -> 0.0003s
main: -- execute("ALTER TABLE batched_background_migrations VALIDATE CONSTRAINT check_122750e705;")
main: -> 0.0005s
main: -- execute("RESET statement_timeout")
main: -> 0.0003s
main: == 20241030182753 AddJsonbArrayConstraintsToBatchedBackgroundMigrations: migrated (0.0095s)
main: == [advisory_lock_connection] object_id: 144240, pg_backend_pid: 95779
ci: == [advisory_lock_connection] object_id: 148320, pg_backend_pid: 95782
ci: == 20241030182753 AddJsonbArrayConstraintsToBatchedBackgroundMigrations: migrating
ci: -- transaction_open?(nil)
ci: -> 0.0000s
ci: -- transaction_open?(nil)
ci: -> 0.0000s
ci: -- execute("ALTER TABLE batched_background_migrations\nADD CONSTRAINT check_122750e705\nCHECK ( jsonb_typeof(min_cursor) = 'array' AND jsonb_typeof(max_cursor) = 'array' )\nNOT VALID;\n")
ci: -> 0.0009s
ci: -- execute("SET statement_timeout TO 0")
ci: -> 0.0003s
ci: -- execute("ALTER TABLE batched_background_migrations VALIDATE CONSTRAINT check_122750e705;")
ci: -> 0.0005s
ci: -- execute("RESET statement_timeout")
ci: -> 0.0004s
ci: == 20241030182753 AddJsonbArrayConstraintsToBatchedBackgroundMigrations: migrated (0.0141s)
ci: == [advisory_lock_connection] object_id: 148320, pg_backend_pid: 95782
main: == [advisory_lock_connection] object_id: 152500, pg_backend_pid: 95789
main: == 20241030182800 IndexBackgroundMigrationJobsOnMigrationIdAndMaxCursor: migrating
main: -- transaction_open?(nil)
main: -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main: -> 0.0006s
main: -- index_exists?(:batched_background_migration_jobs, "batched_background_migration_id, max_cursor", {:where=>"max_cursor is not null", :name=>"index_migration_jobs_on_migration_id_and_cursor_max_value", :algorithm=>:concurrently})
main: -> 0.0029s
main: -- execute("SET statement_timeout TO 0")
main: -> 0.0003s
main: -- add_index(:batched_background_migration_jobs, "batched_background_migration_id, max_cursor", {:where=>"max_cursor is not null", :name=>"index_migration_jobs_on_migration_id_and_cursor_max_value", :algorithm=>:concurrently})
main: -> 0.0024s
main: -- execute("RESET statement_timeout")
main: -> 0.0003s
main: == 20241030182800 IndexBackgroundMigrationJobsOnMigrationIdAndMaxCursor: migrated (0.0200s)
main: == [advisory_lock_connection] object_id: 152500, pg_backend_pid: 95789
ci: == [advisory_lock_connection] object_id: 156580, pg_backend_pid: 95792
ci: == 20241030182800 IndexBackgroundMigrationJobsOnMigrationIdAndMaxCursor: migrating
ci: -- transaction_open?(nil)
ci: -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci: -> 0.0004s
ci: -- index_exists?(:batched_background_migration_jobs, "batched_background_migration_id, max_cursor", {:where=>"max_cursor is not null", :name=>"index_migration_jobs_on_migration_id_and_cursor_max_value", :algorithm=>:concurrently})
ci: -> 0.0023s
ci: -- execute("SET statement_timeout TO 0")
ci: -> 0.0003s
ci: -- add_index(:batched_background_migration_jobs, "batched_background_migration_id, max_cursor", {:where=>"max_cursor is not null", :name=>"index_migration_jobs_on_migration_id_and_cursor_max_value", :algorithm=>:concurrently})
ci: -> 0.0029s
ci: -- execute("RESET statement_timeout")
ci: -> 0.0003s
ci: == 20241030182800 IndexBackgroundMigrationJobsOnMigrationIdAndMaxCursor: migrated (0.0193s)
ci: == [advisory_lock_connection] object_id: 156580, pg_backend_pid: 95792Edited by Matt Kasa