Support creating multiple indexes concurrently in a single migration postgresql

[julieqiu]

Describe the Bug
I'd like to add multiple indexes to my postgresql schema in a single migration file.

Steps to Reproduce

1. My migrations look like '...'

CREATE INDEX CONCURRENTLY idx_my_table_col1 ON my_table(col1);
CREATE INDEX CONCURRENTLY idx_my_table_col2 ON my_table(col2);

2. I ran migrate with the following options '....'

migrate -source file:migrations -database "postgres://localhost:5432/my-database?sslmode=disable" up

3. See error

 (details: pq: CREATE INDEX CONCURRENTLY cannot run inside a transaction block)

Expected Behavior
I expected the migration to succeed and add both indexes. This migration does succeed if I only try to create one index at a time and have two sequential migration files with the following contents:

CREATE INDEX CONCURRENTLY idx_my_table_col1 ON my_table(col1);
```
```
CREATE INDEX CONCURRENTLY idx_my_table_col2 ON my_table(col2);
```

**Migrate Version**
v4.6.2

**Loaded Source Drivers**
Source drivers: github, github-ee, gitlab, go-bindata, godoc-vfs, gcs, file, s3

**Loaded Database Drivers**
Database drivers: cockroachdb, crdb-postgres, postgres, redshift, postgresql, spanner, sqlserver, cassandra, clickhouse, cockroach, mongodb, mysql, stub


**Go Version**
go version go1.13 darwin/amd64

[roopakv]

So psql is able to do what you expect above since they have autocommit turned on. You can repro that this wont work with psql by setting autocommit off (/set AUTOCOMMIT off)

To make this ^ work we would need to figure out how to turn on autocommit.

[psuresh309]

Note this happens with CREATE INDEX CONCURRENTLY and any other sql command and not just another CREATE INDEX CONCURRENTLY.

[dhui]

Closing as this is not a restriction of migrate but instead what postgres allows in a single execute statement. Another otion would be to add support for multiple statements using multistmt

[frederikhors]

Is there any news on this? May I ask you how did you solve, @julieqiu?

[frederikhors]

@dhui I'm new to golang-migrate, can you please explain what to do? I'm in the same situation as @julieqiu...

[dhui]

For now, I'd break up your statements into separate migration files until the postgres driver supports multiple statements in a single migration file.

[frederikhors]

If I have 30 tables and I need many indexes on each of them how many files will i need? 30 * 3~? Really?

[julieqiu]

For now, I'd break up your statements into separate migration files until the postgres driver supports multiple statements in a single migration file.

This is what I ended up doing. Alternatively, I would just run CREATE INDEX without CONCURRENTLY.

[dhui]

@frederikhors It may be easier to add support for multiple statements than to write 90+ migrations
Feel free to open a PR. For example usage, see the neo4j, cassandra, and clickhouse db drivers.

[frederikhors]

I would like to be able to do that.

[AnatolyRugalev]

Gonna work on it