Programmatically construct a migration?

[quasi-coherent]

Hi, I have a particular use case where it would be very nice to do something like what the goose migration tool permits, namely you can freely use a database connection in constructing the embedded migration. That's nice and all, but I think I would practically do anything to avoid writing Go (I'm allergic).

The sqlx CLI doesn't support migrations in Rust, and never plans to. It also doesn't support separate migration tables, e.g., one migration table per service per schema. It doesn't even let you rename the migration table. So refinery was exciting at first glance because it lets you rename the migration history table and says things about embedding migrations in Rust. But now it seems like it's not maybe meant to solve my problem? I just want to get confirmation of that or maybe someone has experience.

In my case, I have a partitioned table in postgres and I want to add an index to it. But in postgres you can't CONCURRENTLY build indices and I need that. The table is being written to a great many times per second forever, so a standard index creation with a long-running lock is just out of the question. But there's a way to do it, and the procedure is:

1. Create the index ON ONLY the partitioned table (the "parent"), which has the effect of "creating" an index that is invalid at first. So we try to make it valid.
2. You do that by recursing over every child partition and performing CREATE INDEX CONCURRENTLY ... ON each of them.
3. Then you ALTER INDEX created_in_step_one_ix ATTACH PARTITION created_in_two_for_child_123_ix.
4. Do that for every partition and now the index on the whole table is valid, and everything's good.

The clear problem is iterating over all the child partitions. They don't have any predictable name and I imagine a pure-SQL migration to do this would be quite complicated. So it would be nice if I could query a system table, get this list of child partitions, and very straightforwardly do steps 2-4 in Rust.

I can build this query in sqlx and then turn it into String like what migration needs, but that being the end of the type signature is pretty limiting, and it's hard to imagine how it could possibly permit this. I haven't found any related issues, and there's only one example that is simple and also three years old, so I've yet to see something like my use case, which is not really interested in embedded migrations only for the sake of having Rust types to add a column. Rather I'm interested in just writing SQL for the simple migrations and Rust for ones like this.

Any advice, or discussion or example, someone is aware of? Is there any general approach that comes to mind to maybe build on refinery to add this capability?