Enable non-transactional migrations

[avimoondra]

For DB safety reasons, sometimes it is necessary to add an index concurrently. With the CONCURRENTLY option:

PostgreSQL will build the index without taking any locks that prevent concurrent inserts, updates, or deletes on the table; whereas a standard index build locks out writes (but not reads) on the table until it's done.

But this parameter cannot be used in migrations because Hasura runs the migration in a transaction when applying. But concurrent index creation cannot be wrapped in a transaction block:

Another difference is that a regular CREATE INDEX command can be performed within a transaction block, but CREATE INDEX CONCURRENTLY cannot.

There don't seem to be any workarounds or escape hatches in this case, beyond not tracking the migration at all and running directly on the db instance.

Some solutions might be to include an option in the apply api, or encoding not wrapping a transaction in the yml for the migration file itself.

Original request in Discord: https://discordapp.com/channels/407792526867693568/535727661167673364/626175223724769295

Postgres documentation: https://www.postgresql.org/docs/9.1/sql-createindex.html

[0x777]

We can add this is a query parameter to /v1/query, maybe as /v1/query?tx_isolation=none. This should also be allowed only for admin users.

[avimoondra]

@0x777 Is it possible to add this as a parameter or arg within the up and down migration files? as an alternate method.

Sometimes we do want transactions, and sometimes we don't. The default can be with transactions, and with an arg they can be turned off. And then we continue to use hasura migrate apply without any modification.

[avimoondra]

@0x777 Ah I see a miscommunication on my end - the request was to not have transactions when doing migrations specifically, not really queries.

[haphut]

@avimoondra Thanks for bringing this up. Could you clarify the title of the issue, please? Maybe something like "Enable non-transactional migrations".

Without the ability to CREATE INDEX CONCURRENTLY, we will have to stop using Hasura for migrations. The feature seems otherwise clean so it's a bummer to have to switch.

[ledburyb]

I'm looking for this too. Migrating from Django where non-atomic migrations are standard.

[HoyaBoya]

This would be a much appreciate feature.

We have this in Ruby on Rails which allows us to do CONCURRENTLY index creation: https://www.rubydoc.info/docs/rails/ActiveRecord%2FMigration:disable_ddl_transaction

[creatorrr]

This is also needed for any DDL actions for timescaledb for creating continuous aggregates etc because it uses two separate transactions internally. For now, a workaround is to manually apply the up.sql file and then running hasura migrate apply --up 1 --skip-execution to mark the migration applied in the tracker.

But it'd be really nice to have a CLI flag like --dangerous-manual-transaction that tells the engine to skip creating a transaction automatically and trust that the person running apply is taking care of the transactions manually.

[ThHareau]

Hi, is there any news on this topic?

Concurrent index creations and deletions would be very nice to have! Otherwise, any updates on the linked tables would be locked during the entire duration of the migration (shameless plug of an article I've written on this topic).

The downside is that concurrent index management would take more time to finish (two parses of the table are needed instead of one, and PG would wait for any transaction to finish between these steps). It can be relatively scary, even if harmless.

[EricMeuse-HenryMeds]

I definitely would love to see this added. The more our database grows, the more we need to have indexes added concurrently which cannot be done through migrations.