SQL Migration error: `CREATE INDEX CONCURRENTLY` cannot run inside a transaction block

[i0tool5]

Preflight checklist

• [X] I could not find a solution in the existing issues, docs, nor discussions.
• [X] I agree to follow this project's Code of Conduct.
• [X] I have read and am following this repository's Contribution Guidelines.
• [ ] This issue affects my Ory Network project.
• [ ] I have joined the Ory Community Slack.
• [ ] I am signed up to the Ory Security Patch Newsletter.

Describe the bug

I am adding new migration file with concurrent query, and migrations are faiing with error

Reproducing the bug

1. Create migration file inside persistence/sql/migrations/
2. Insert concurrent query (concurrent index in my case) CREATE INDEX CONCURRENTLY name_idx ON table_name(column1, column2);
3. Run make migrate
4. Fails with error.

Relevant log output

rm -f package.json package-lock.json
packr2
go version
go version go1.20.4 linux/amd64
go env
GO111MODULE="on"
GOARCH="amd64"
GOBIN=""
GOCACHE="/home/user/.cache/go-build"
GOENV="/home/user/.config/go/env"
GOEXE=""
GOEXPERIMENT=""
GOFLAGS=""
GOHOSTARCH="amd64"
GOHOSTOS="linux"
GOINSECURE=""
GOMODCACHE="/home/user/go/pkg/mod"
GOOS="linux"
GOPATH="/home/user/go"
GOROOT="/usr/local/go"
GOSUMDB="sum.golang.org"
GOTMPDIR=""
GOTOOLDIR="/usr/local/go/pkg/tool/linux_amd64"
GOVCS=""
GOVERSION="go1.20.4"
GCCGO="gccgo"
GOAMD64="v1"
AR="ar"
CC="gcc"
CXX="g++"
CGO_ENABLED="1"
GOMOD="/home/user/directory/auth/hydra/go.mod"
GOWORK=""
CGO_CFLAGS="-O2 -g"
CGO_CPPFLAGS=""
CGO_CXXFLAGS="-O2 -g"
CGO_FFLAGS="-O2 -g"
CGO_LDFLAGS="-O2 -g"
PKG_CONFIG="pkg-config"
GOGCCFLAGS="-fPIC -m64 -pthread -Wl,--no-gc-sections -fmessage-length=0 -fdebug-prefix-map=/tmp/go-build3548924247=/tmp/go-build -gno-record-gcc-switches"
go build -ldflags "-X github.com/ory/hydra/cmd.Version=v3.47.6+1-1-g65016-dirty" -o auth-hydra
echo "CREATE SCHEMA IF NOT EXISTS \"auth\";" | psql "postgres://login:password@localhost:5432?sslmode=disable";
CREATE SCHEMA
SUBSYSTEM="auth" \
APP_NAME="hydra" \
CONFIG_DSN="postgres://login:password:5432/auth?search_path=auth&sslmode=disable" \
    ./auth-hydra migrate sql -y -e --config ./config.yml
The following migration is planned:

Version          Name                                              Status    
20190100000001   client                                            Applied   
20190100000001   client                                            Applied    
20190100000007   client                                            Applied   
20190100000010   client                                            Applied   
20190100000010   client                                            Applied   
20190100000011   client                                            Applied   
20190100000011   client                                            Applied   
20190100000011   client                                            Applied   
20190100000012   client                                            Applied   
20190100000012   client                                            Applied   
20190100000013   client                                            Applied   
20190100000013   client                                            Applied   
20190100000013   client                                            Applied   
20190100000014   client                                            Applied   
20190100000014   client                                            Applied   
20190100000014   client                                            Applied   
20190300000004   consent                                           Applied   
20190300000010   consent                                           Applied   
20190300000011   consent                                           Applied   
20190300000011   consent                                           Applied   
20190300000012   consent                                           Applied   
20190300000012   consent                                           Applied   
20190300000012   consent                                           Applied   
20190300000013   consent                                           Applied   
20190300000013   consent                                           Applied   
20190300000013   consent                                           Applied   
20190300000014   consent                                           Applied   
20190400000003   oauth2                                            Applied   
20190400000003   oauth2                                            Applied   
20190400000006   oauth2                                            Applied   
20190400000006   oauth2                                            Applied   
20190400000007   oauth2                                            Applied   
20190400000007   oauth2                                            Applied   
20190400000007   oauth2                                            Applied   
20190400000008   oauth2                                            Applied   
20190400000010   oauth2                                            Applied   
20190400000011   oauth2                                            Applied   
20190400000011   oauth2                                            Applied   
20200521071434   consent                                           Applied   
20200521071434   consent                                           Applied   
20200521071434   consent                                           Applied   
20200527215731   client                                            Applied   
20200527215732   client                                            Applied   
20200819163013   add_client_id_subject_idx_to_access_and_refresh   Applied   
20200819163013   add_client_id_subject_idx_to_access_and_refresh   Applied   
20200819163013   add_client_id_subject_idx_to_access_and_refresh   Applied   
20202809000000   expired_at                                        Applied   
20202809000000   expired_at                                        Applied   
20211011000001   roles                                             Applied   
20211011000002   hydra_client_role_relations                       Applied   
20221301000000   add_column_tenant_code                            Applied   
20221301000001   hydra_role_relations_version                      Applied   
20221301000002   remove_roles                                      Applied   
20221301000003   hydra_client_tenant_profiles                      Applied   
20221301000004   hydra_role_relations_version_fix_clients_fk       Applied   
20221301000005   hydra_role_relations_tenant_code                  Applied   
20221301000006   truncate_tokens                                   Applied   
20221301000007   truncate_tokens                                   Applied   
20221301000008   truncate_hydra_client_role_relations_version      Applied   
20221301000009   add_hydra_client_jwks_meta                        Applied   
20230522000001   create-index-hydra-jwk                            Pending   
[POP] 2023/05/22 12:32:22 info - 0.0187 seconds
Could not apply migrations:
ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block (SQLSTATE 25001)
error executing 20230522000001_create-index-hydra-jwk.up.sql, sql:

CREATE INDEX CONCURRENTLY
    hydra_jwk__sid_and_created_at_idx 
ON
    auth.hydra_jwk (sid, created_at);

github.com/gobuffalo/pop/v5.NewMigrationBox.func1.1
    /home/user/go/pkg/mod/github.com/gobuffalo/pop/v5@v5.3.1/migration_box.go:37
github.com/gobuffalo/pop/v5.Migration.Run
    /home/user/go/pkg/mod/github.com/gobuffalo/pop/v5@v5.3.1/migration_info.go:29
github.com/gobuffalo/pop/v5.Migrator.UpTo.func1.2
    /home/user/go/pkg/mod/github.com/gobuffalo/pop/v5@v5.3.1/migrator.go:106
github.com/gobuffalo/pop/v5.(*Connection).Transaction.func1
    /home/user/go/pkg/mod/github.com/gobuffalo/pop/v5@v5.3.1/connection.go:139
github.com/gobuffalo/pop/v5.commonDialect.Lock
    /home/user/go/pkg/mod/github.com/gobuffalo/pop/v5@v5.3.1/dialect_common.go:29
github.com/gobuffalo/pop/v5.(*Connection).Transaction
    /home/user/go/pkg/mod/github.com/gobuffalo/pop/v5@v5.3.1/connection.go:133
github.com/gobuffalo/pop/v5.Migrator.UpTo.func1
    /home/user/go/pkg/mod/github.com/gobuffalo/pop/v5@v5.3.1/migrator.go:105
github.com/gobuffalo/pop/v5.Migrator.exec
    /home/user/go/pkg/mod/github.com/gobuffalo/pop/v5@v5.3.1/migrator.go:279
github.com/gobuffalo/pop/v5.Migrator.UpTo
    /home/user/go/pkg/mod/github.com/gobuffalo/pop/v5@v5.3.1/migrator.go:90
github.com/gobuffalo/pop/v5.Migrator.Up
    /home/user/go/pkg/mod/github.com/gobuffalo/pop/v5@v5.3.1/migrator.go:82
github.com/ory/hydra/persistence/sql.(*Persister).MigrateUp
    /home/user/directory/auth/hydra/persistence/sql/persister.go:54
github.com/ory/hydra/cmd/cli.(*MigrateHandler).MigrateSQL
    /home/user/directory/auth/hydra/cmd/cli/handler_migrate.go:95
github.com/spf13/cobra.(*Command).execute
    /home/user/go/pkg/mod/github.com/spf13/cobra@v1.2.1/command.go:860
github.com/spf13/cobra.(*Command).ExecuteC
    /home/user/go/pkg/mod/github.com/spf13/cobra@v1.2.1/command.go:974
github.com/spf13/cobra.(*Command).Execute
    /home/user/go/pkg/mod/github.com/spf13/cobra@v1.2.1/command.go:902
github.com/ory/hydra/cmd.Execute
    /home/user/directory/auth/hydra/cmd/root.go:70
main.main
    /home/user/directory/auth/hydra/main.go:33
runtime.main
    /usr/local/go/src/runtime/proc.go:250
runtime.goexit
    /usr/local/go/src/runtime/asm_amd64.s:1598
github.com/ory/hydra/persistence/sql.(*Persister).MigrateUp
    /home/user/directory/auth/hydra/persistence/sql/persister.go:54
github.com/ory/hydra/cmd/cli.(*MigrateHandler).MigrateSQL
    /home/user/directory/auth/hydra/cmd/cli/handler_migrate.go:95
github.com/spf13/cobra.(*Command).execute
    /home/user/go/pkg/mod/github.com/spf13/cobra@v1.2.1/command.go:860
github.com/spf13/cobra.(*Command).ExecuteC
    /home/user/go/pkg/mod/github.com/spf13/cobra@v1.2.1/command.go:974
github.com/spf13/cobra.(*Command).Execute
    /home/user/go/pkg/mod/github.com/spf13/cobra@v1.2.1/command.go:902
github.com/ory/hydra/cmd.Execute
    /home/user/directory/auth/hydra/cmd/root.go:70
main.main
    /home/user/directory/auth/hydra/main.go:33
runtime.main
    /usr/local/go/src/runtime/proc.go:250
runtime.goexit
    /usr/local/go/src/runtime/asm_amd64.s:1598
github.com/ory/hydra/cmd/cli.(*MigrateHandler).MigrateSQL
    /home/user/directory/auth/hydra/cmd/cli/handler_migrate.go:96
github.com/spf13/cobra.(*Command).execute
    /home/user/go/pkg/mod/github.com/spf13/cobra@v1.2.1/command.go:860
github.com/spf13/cobra.(*Command).ExecuteC
    /home/user/go/pkg/mod/github.com/spf13/cobra@v1.2.1/command.go:974
github.com/spf13/cobra.(*Command).Execute
    /home/user/go/pkg/mod/github.com/spf13/cobra@v1.2.1/command.go:902
github.com/ory/hydra/cmd.Execute
    /home/user/directory/auth/hydra/cmd/root.go:70
main.main
    /home/user/directory/auth/hydra/main.go:33
runtime.main
    /usr/local/go/src/runtime/proc.go:250
runtime.goexit
    /usr/local/go/src/runtime/asm_amd64.s:1598

Relevant configuration

No response

Version

v3.47.6

On which operating system are you observing this issue?

Linux

In which environment are you deploying?

Other

Additional Context

No response

[aeneasr]

What Postgres version?

[i0tool5]

What Postgres version?

11 and 12