Hello! Does someone knows if it's possible to disable transactions for a particular migration? I would like to build an index concurrently, but it's not possible to build it inside a transaction on PostgreSQL.

Database error code: 25001

Database error:
ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block

Hey, what does you migration sql file look like besides the

CREATE INDEX CONCURRENTLY

?

it only contains a couple of

CREATE INDEX CONCURRENTLY

statements:

CREATE INDEX CONCURRENTLY "UserEventFlags_eventId_idx" ON "UserEventFlags"("eventId");
...

I’m curious about this as well

Generally PostgreSQL migrations should not add a transaction around the transaction itself automatically (Afaik).

I’m running into this as well

I think someone opened an issue about this later, searching for "INDEX CONCURRENTLY" in the repo might help you find it.

I found a discussion referencing this thread and this issue that suggests running the migration manually and editing the checksum. Is this the preferred way of creating indices concurrently? It’d be great if I could run these migrations directly with Prisma so I can make a change to an index without incurring downtime in a production service.

It seems we are missing an actual feature request for

CREATE INDEX CONCURRENTLY

- so that would be great if you could create it.

Thanks! I did try adding a

COMMIT

to the beginning of my migration yesterday, but it failed with the same error. Will create a feature request for this today.

Sorry for bringing up such an old discussion (I can start a new one if that’s preferred).