`in_threads` option for `add_index_on_all_partitions` fails to migrate in the latest versions

[berniechiu]

Hi @rkrage, thank you for the wonderful gem! We've encountered a few bugs when using the in_threads option for add_index_on_all_partitions, which is causing migration issues.

Current environment:

Postgres Version: 17.5
Rails: 7.1

pg (1.6.2)
pg_party (1.9.0)
parallel (1.27.0)

Recently, I believe we've upgraded a few gems included in Rails from 7.0 to 7.1. The add_index_on_all_partitions with the in_threads option doesn't seem to work quite correctly in these latest versions and appears to have some connection leaks or race conditions.

   (0.5ms)  SELECT pg_try_advisory_lock(1845250994169059400)
Migrating to AddOrdersCountIndexToCustomers (20250911132302)
== 20250911132302 AddOrdersCountIndexToCustomers: migrating ===================
   (0.3ms)  SHOW server_version_num
-- add_index_on_all_partitions(:customers, [:orders_count], {:algorithm=>:concurrently, :in_threads=>4})
   (3.5ms)  CREATE  INDEX "index_customers_on_orders_count" ON ONLY "customers"  ("orders_count")
   (0.2ms)  DROP INDEX IF EXISTS "index_customers_on_orders_count"
   (0.1ms)  CREATE  INDEX CONCURRENTLY "index_customers_on_orders_count_b8f7c8c" ON "customers_shard_0"  ("orders_count")
   (1.8ms)  ALTER INDEX "index_customers_on_orders_count" ATTACH PARTITION "index_customers_on_orders_count_b8f7c8c"
   (0.2ms)  DROP INDEX IF EXISTS "index_customers_on_orders_count"
   (2.7ms)  DROP INDEX IF EXISTS "index_customers_on_orders_count_b8f7c8c"
   (0.1ms)  DROP INDEX IF EXISTS "index_customers_on_orders_count"
   (0.0ms)  DROP INDEX IF EXISTS "index_customers_on_orders_count_b8f7c8c"
   (0.1ms)  SELECT pg_advisory_unlock(1845250994169059400)
Not notifying due to an invalid api_key
rake aborted!
ActiveRecord::ConcurrentMigrationError:  (ActiveRecord::ConcurrentMigrationError)

Failed to release advisory lock

<...installs/ruby/3.2.7/lib/ruby/site_ruby/3.2.0/rubygems/core_ext/kernel_require.rb>:139:in `require'
<...asdf/installs/ruby/3.2.7/lib/ruby/site_ruby/3.2.0/rubygems/core_ext/kernel_require.rb>:139:in `require'
-e:1:in `<main>'

Caused by:
StandardError: An error has occurred, all later migrations canceled: (StandardError)

PG::UndefinedTable: ERROR:  relation "index_customers_on_orders_count" does not exist


Caused by:
ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR:  relation "index_customers_on_orders_count" does not exist (ActiveRecord::StatementInvalid)


Caused by:
PG::UndefinedTable: ERROR:  relation "index_customers_on_orders_count" does not exist (PG::UndefinedTable)

However, removing the in_threads option makes the migration look good instead. Thank you for your help and investigation!