CONCURRENTLY statements must be placed in *_notx.sql migrati

Error message

CONCURRENTLY statements must be placed in *_notx.sql migrations

What it means

The migration runner validates each .sql file's execution mode. Any migration NOT ending in the *_notx.sql suffix that contains the keyword CONCURRENTLY (case-insensitive substring on uppercased content) is rejected, because CREATE/DROP INDEX CONCURRENTLY cannot run inside a transaction and regular migrations are wrapped in one.

Source

Thrown at backend/internal/repository/migrations_runner.go:492

	if !ok {
		return false
	}
	_, dbOK := rule.acceptedChecksums[dbChecksum]
	if !dbOK {
		return false
	}
	_, fileOK := rule.acceptedChecksums[fileChecksum]
	return fileOK
}

func validateMigrationExecutionMode(name, content string) (bool, error) {
	normalizedName := strings.ToLower(strings.TrimSpace(name))
	upperContent := strings.ToUpper(content)
	nonTx := strings.HasSuffix(normalizedName, nonTransactionalMigrationSuffix)

	if !nonTx {
		if strings.Contains(upperContent, "CONCURRENTLY") {
			return false, errors.New("CONCURRENTLY statements must be placed in *_notx.sql migrations")
		}
		return false, nil
	}

	if strings.Contains(upperContent, "BEGIN") || strings.Contains(upperContent, "COMMIT") || strings.Contains(upperContent, "ROLLBACK") {
		return false, errors.New("*_notx.sql must not contain transaction control statements (BEGIN/COMMIT/ROLLBACK)")
	}

	statements := splitSQLStatements(content)
	for _, stmt := range statements {
		normalizedStmt := strings.ToUpper(stripSQLLineComment(strings.TrimSpace(stmt)))
		if normalizedStmt == "" {
			continue
		}

		if strings.Contains(normalizedStmt, "CONCURRENTLY") {
			isCreateIndex := strings.Contains(normalizedStmt, "CREATE") && strings.Contains(normalizedStmt, "INDEX")
			isDropIndex := strings.Contains(normalizedStmt, "DROP") && strings.Contains(normalizedStmt, "INDEX")

View on GitHub (pinned to 073e92d171)

Solutions

Rename the file to end with the non-transactional suffix (e.g. 0007_add_idx_notx.sql) so the runner executes it outside a transaction.
If the index is small/table is new, drop CONCURRENTLY and keep it a normal transactional migration.
Re-run migrations after fixing; validation happens pre-execution so no partial state results.

Example fix

-- file: migrations/0007_add_idx.sql  (rejected)
CREATE INDEX CONCURRENTLY idx_events_account ON events(account_id);

-- file: migrations/0007_add_idx_notx.sql  (accepted)
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_events_account ON events(account_id);

Defensive patterns

Strategy: validation

Validate before calling

// pre-commit / CI check for migration files
func checkMigration(name, content string) error {
    nonTx := strings.HasSuffix(strings.ToLower(name), "_notx.sql")
    hasConc := strings.Contains(strings.ToUpper(content), "CONCURRENTLY")
    if hasConc && !nonTx {
        return fmt.Errorf("%s: rename to *_notx.sql or drop CONCURRENTLY", name)
    }
    return nil
}

Prevention

Add a CI lint for migration naming vs CONCURRENTLY usage
Template new index migrations as _notx from the start
Remember CONCURRENTLY cannot run inside the default transactional runner

When it happens

Trigger: Adding a migration like 0007_add_idx.sql containing 'CREATE INDEX CONCURRENTLY ...' — the runner detects CONCURRENTLY, sees the filename lacks the _notx suffix, and fails before executing anything.

Common situations: Developers copying index migrations from other projects that use CONCURRENTLY by default; adding REINDEX ... or COMMENT ... CONCURRENTLY-adjacent syntax; renaming a _notx file and dropping the suffix while keeping the content.

Related errors

AI-assisted analysis of Wei-Shaw/sub2api@073e92d171 (2026-08-15). Data as JSON: /api/errors/3a2b80b15f80bc71. Report an issue: GitHub.