Transaction: false returns error

[victoraugustolls]

Hi!

So I'm guessing that using the option transaction: false only applies to the first command.
When I have multiple CREATE INDEX CONCURRENTLY, it seems like the option only applies to the first operation, and the second fails. Is this expected?

Thanks in advance!

[amacneil]

It should apply to the entire migration. Can you share more about the error?

[victoraugustolls]

Sure can!

I’m using with Postgres 11.

My first try was creating multiple indexes concurrently without transaction false, which was a mistake and obviously gave me an error.

Second try was setting transaction to false and the error was the same, saying concurrently indexes can’t be created inside a transaction.

Then I tried with only one index creation and it worked as expected.

When I tried again with two or more, the same error occurred, saying concurrently indexes can’t be created inside an transaction.

[vrajanap]

I encountered this issue as well - with Postgres 11 on a migration to create multiple indexes.

After a bit of reading the code, found that dbmate correctly detects that this set of statement needs to be outside transaction and uses execMigration and then later uses the Exec routine.

This results in Error: pq: CREATE INDEX CONCURRENTLY cannot run inside a transaction block.

However, when I tried to repro directly on with lib/pq, it looks like a known limitation - lib/pq#820

$ psql -h localhost -U postgres -c "create database db_test"
CREATE DATABASE
$ psql -h localhost -U postgres -d db_test -c "create table users(id serial, name text, address text, phonenum bigint)"
CREATE TABLE
$ cat concurrent_index_creation.go
───────┬──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
       │ File: concurrent_index_creation.go
───────┼──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
   1   │ package main
   2   │
   3   │ import (
   4   │     "database/sql"
   5   │     "fmt"
   6   │
   7   │     _ "github.com/lib/pq"
   8   │ )
   9   │
  10   │ func main() {
  11   │     db, err := sql.Open("postgres", fmt.Sprintf("host=%s user=%s dbname=%s sslmode=disable", "localhost", "postgres", "db_test"))
  12   │     if err != nil {
  13   │         panic(err)
  14   │     }
  15   │     defer db.Close()
  16   │
  17   │     _, err = db.Exec("create index concurrently n1 on tuser(name); create index concurrently n2 on users(address, phonenum);")
  18   │     if err != nil {
  19   │         panic(err)
  20   │     }
  21   │ }
───────┴──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
$ go run concurrent_index_creation.go
panic: pq: CREATE INDEX CONCURRENTLY cannot be executed from a function or multi-command string

goroutine 1 [running]:
main.main()
        /home/rajan/code/private/mybackup/golang/concurrent_index_creation.go:19 +0x1f5
exit status 2

The solution here could be using a different postgres go driver library or splitting block into separate statements when transaction is set to false.

Of course there is also a simple workaround for users - splitting index creation statements into separate migrations.

@amacneil Lemme know if splitting the migration block into separate statements for this case is fine solution here or if there is a better solution.

[amacneil]

Thanks for the reproduction @vrajanap.

It sounds like this is a limitation of the lib/pq driver, and I'm not aware of any other golang drivers for postgresql. Therefore, I will close this with the workaround of putting each concurrent index creation in a separate migration file.