`CREATE INDEX CONCURRENTLY` with `GoMigrationNoTx` failing

[masroorhasan]

Hello 👋

I'm trying to run goose migrations to create index on a partitioned postgres table. I'm basically following the Postgres docs example for adding index to an existing partitioned table: https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE-MAINTENANCE

However the migration run keeps failing with error that says CREATE INDEX CONCURRENTLY cannot be run under transaction, despite the golang goose migration using GoMigrationNoTx.

Here is the code (using Postgres docs example) & error for reference:

import (
    "database/sql"
    "log"

    "github.com/pressly/goose/v3"
)

const (
    upSQL1 = `
CREATE INDEX measurement_usls_idx ON ONLY measurement (unitsales);
`

    upSQL2 = `
CREATE INDEX CONCURRENTLY measurement_usls_200602_idx
    ON measurement_y2006m02 (unitsales);
ALTER INDEX measurement_usls_idx
    ATTACH PARTITION measurement_usls_200602_idx;
`

    downSQL = `DROP INDEX measurement_usls_idx`
)

func init() {
    goose.AddMigrationNoTx(up, down)
}

func up(db *sql.DB) error {
        if _, err := db.Exec(upSQL1); err != nil {
        return err
    }
         if _, err := db.Exec(upSQL2); err != nil {
        return err
    }
    return nil
}

func down(db *sql.DB) error {
        if _, err := db.Exec(downSQL); err != nil {
        return err
    }
        return nil
}

Error: 2024/07/19 16:19:07 goose up: ERROR go migration no tx: "20240719161645_add_index_concurrently_example.go": failed to run go migration: ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block (SQLSTATE 25001)

I'm using pressly/goose/v3@v3.11.2.

Any insights here would be appreciated, thanks!

[mfridman]

Can you try pulling the latest version of goose? v3.21.1

Also, which command are you running? goose.Up?

[masroorhasan]

Hi @mfridman

Same issue when I used latest patch v3.21.1.

Using pgx driver, command used for running migration:

goose -dir ./migrations pgx up