DROP INDEX CONCURRENTLY cannot run inside a transaction block

[nikita-davydov]

Hello, hope you're all doing well!

I stuck with the problem of updating indexes using aerich, even with --in-transaction false, I'm getting the exception

asyncpg.exceptions.ActiveSQLTransactionError: DROP INDEX CONCURRENTLY cannot run inside a transaction block
tortoise.exceptions.TransactionManagementError: DROP INDEX CONCURRENTLY cannot run inside a transaction block

I see in the trace that it happens on the line aerich/init.py:190 - so it's for sure out of transaction block, how it could be solved?

[waketzheng]

Upgrade aerich to the latest version(pip install --upgrade "aerich>=0.9.1"), then try again.

[Pljopi]

Any news on this?
I am on 0.9.1 and have the same issue?

[waketzheng]

Try dev branch from github:

uv pip install git+https://github.com/tortoise/aerich

If not fixed, tell me how to reproduce this issue.

[Pljopi]

Try dev branch from github:

uv pip install git+https://github.com/tortoise/aerich
If not fixed, tell me how to reproduce this issue.

Okay so it does not work on dev branch either :/

Basically if there is a single CONCURRENTLY statement it works with the -i flag, if there is more than 1 it does not work
tortoise.exceptions.TransactionManagementError: DROP INDEX CONCURRENTLY cannot run inside a transaction block
Dropping this into the upgrade function in migration file works with -i flag

statements = [
'CREATE CONCURRENTLY INDEX IF NOT EXISTS "idx_xxx_event_xxx" ON "xxx" ("event", "order_index");',
'CREATE CONCURRENTLY INDEX IF NOT EXISTS "idx_xxx_source_xxx" ON "xxx" ("source", "order_index");',
'CREATE CONCURRENTLY INDEX IF NOT EXISTS "idx_xxx_event_xxx" ON "xxx" ("event", "order_index");',
'CREATE CONCURRENTLY INDEX IF NOT EXISTS "idx_xxx_source_xxx" ON "xxx" ("source", "order_index");',
'CREATE CONCURRENTLY INDEX IF NOT EXISTS "idx_xxx_event_xxx" ON "xxx" ("event", "order_index");',
'CREATE CONCURRENTLY INDEX IF NOT EXISTS "idx_xxx_source_xxx" ON "xxx" ("source", "order_index");',
'CREATE CONCURRENTLY INDEX IF NOT EXISTS "idx_xxx_event_xxx" ON "xxx" ("event", "order_index");',
'CREATE CONCURRENTLY INDEX IF NOT EXISTS "idx_xxx_source_xxx" ON "xxx" ("source", "order_index");',
'CREATE CONCURRENTLY INDEX IF NOT EXISTS "idx_xxx_event_xxx" ON "xxx" ("event", "order_index");',
'CREATE CONCURRENTLY INDEX IF NOT EXISTS "idx_xxx_source_xxx" ON "xxx" ("source", "order_index");',
'CREATE CONCURRENTLY INDEX IF NOT EXISTS "idx_xxx_event_xxx" ON "xxx" ("event", "order_index");',
'CREATE CONCURRENTLY INDEX IF NOT EXISTS "idx_xxx_source_xxx" ON "xxx" ("source", "order_index");',
'CREATE CONCURRENTLY INDEX IF NOT EXISTS "idx_xxx_event_xxx" ON "xxx" ("event", "order_index");',
'CREATE CONCURRENTLY INDEX IF NOT EXISTS "idx_xxx_source_xxx" ON "xxx" ("source", "order_index");',
]
for statement in statements:
await db.execute_query(statement)

return "SELECT 1;"