Beego迁移:并发创建索引不起作用

Question

package main

import (
    "github.com/beego/beego/v2/client/orm/migration"
)

// DO NOT MODIFY
type AddConstraintToUser_20210928_223906 struct {
    migration.Migration
}

// DO NOT MODIFY
func init() {
    m := &AddConstraintToUser_20210928_223906{}
    m.Created = "20210928_223906"

    migration.Register("AddConstraintToUser_20210928_223906", m)
}

// Run the migrations
func (m *AddConstraintToUser_20210928_223906) Up() {
    // use m.SQL("CREATE TABLE ...") to make schema update
    m.SQL("END;" +
        "CREATE UNIQUE INDEX  CONCURRENTLY IF NOT EXISTS uniq_email ON users (email) WHERE status_id=1;" +
        "CREATE UNIQUE INDEX  CONCURRENTLY IF NOT EXISTS uniq_name ON users (name) WHERE status_id=1;" +
        "BEGIN;")
}

// Reverse the migrations
func (m *AddConstraintToUser_20210928_223906) Down() {
    // use m.SQL("DROP TABLE ...") to reverse schema update
    m.SQL("END;" +
        "DROP INDEX CONCURRENTLY IF EXISTS uniq_email;" +
        "DROP INDEX CONCURRENTLY IF EXISTS  uniq_name;" +
        "BEGIN;")
}

这是我的迁移文件。在运行bee migrate时，它抛出了一个错误：

2021/09/29 04:26:03 INFO     ▶ 0001 Using 'postgres' as 'driver'
2021/09/29 04:26:03 INFO     ▶ 0002 Using '/Users/aashishpassrija/go/src/users/database/migrations' as 'dir'
2021/09/29 04:26:03 INFO     ▶ 0003 Running all outstanding migrations
2021/09/29 04:26:08 INFO     ▶ 0004 |> 2021/09/29 04:26:06.018 [I]  start upgrade AddConstraintToUser_20210928_223906
2021/09/29 04:26:08 INFO     ▶ 0005 |> 2021/09/29 04:26:06.018 [I]  exec sql: END;CREATE UNIQUE INDEX  CONCURRENTLY IF NOT EXISTS uniq_email ON users (email) WHERE status_id=1;CREATE UNIQUE INDEX  CONCURRENTLY IF NOT EXISTS uniq_name ON users (name) WHERE status_id=1;BEGIN;
2021/09/29 04:26:08 INFO     ▶ 0006 |> 2021/09/29 04:26:06.018 [E]  execute error: pq: CREATE INDEX CONCURRENTLY cannot run inside a transaction block
2021/09/29 04:26:08 ERROR    ▶ 0007 Could not run migration binary: exit status 2

现在我知道索引不能在事务内并发创建。但考虑到我的数据库很大。我该如何实现这一点？是否有用于beego的等价物disable_ddl_transaction

Answer 1

如果您向服务器发送多个用分号分隔的语句，则这些语句都将在单个事务中运行。但是您不能在多语句事务中运行CREATE INDEX CONCURRENTLY。

启用自动提交(如果禁用)，并单独运行每个CREATE INDEX CONCURRENTLY。