*_notx.sql currently only supports CREATE/DROP INDEX CONCURR

Error message

*_notx.sql currently only supports CREATE/DROP INDEX CONCURRENTLY statements

What it means

Inside a *_notx.sql, every statement must be a CREATE/DROP INDEX CONCURRENTLY (with the matching IF [NOT] EXISTS guard, enforced by sibling checks). A statement containing CONCURRENTLY that is not CREATE INDEX or DROP INDEX — e.g. REINDEX CONCURRENTLY, CREATE MATERIALIZED VIEW ... , or ALTER TABLE ... with the word CONCURRENTLY — is rejected; a statement without CONCURRENTLY at all falls through to the 'must not mix' rejection.

Source

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

		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")
			if !isCreateIndex && !isDropIndex {
				return false, errors.New("*_notx.sql currently only supports CREATE/DROP INDEX CONCURRENTLY statements")
			}
			if isCreateIndex && !strings.Contains(normalizedStmt, "IF NOT EXISTS") {
				return false, errors.New("CREATE INDEX CONCURRENTLY in *_notx.sql must include IF NOT EXISTS for idempotency")
			}
			if isDropIndex && !strings.Contains(normalizedStmt, "IF EXISTS") {
				return false, errors.New("DROP INDEX CONCURRENTLY in *_notx.sql must include IF EXISTS for idempotency")
			}
			continue
		}

		return false, errors.New("*_notx.sql must not mix non-CONCURRENTLY SQL statements")
	}

	return true, nil
}

func splitSQLStatements(content string) []string {
	parts := strings.Split(content, ";")

View on GitHub (pinned to 073e92d171)

Solutions

Split the file: keep only CREATE/DROP INDEX CONCURRENTLY statements in the _notx migration; move REINDEX/DML/GRANT into a regular transactional migration.
Replace REINDEX CONCURRENTLY x with the pair DROP INDEX CONCURRENTLY IF EXISTS + CREATE INDEX CONCURRENTLY IF NOT EXISTS, which the runner supports.
Re-run the migration runner; validation failure is pre-execution, so nothing partially applied.

Example fix

-- file: migrations/0008_rebuild_notx.sql (rejected)
REINDEX CONCURRENTLY idx_events;

-- file: migrations/0008_rebuild_notx.sql (accepted)
DROP INDEX CONCURRENTLY IF EXISTS idx_events;
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_events ON events(account_id, created_at);

Defensive patterns

Strategy: validation

Validate before calling

func checkNotxStatements(name, content string) error {
    if !strings.HasSuffix(strings.ToLower(name), "_notx.sql") { return nil }
    for _, stmt := range splitSQLStatements(content) {
        s := strings.ToUpper(strings.TrimSpace(stmt))
        if s == "" { continue }
        isIdx := strings.Contains(s, "CREATE") && strings.Contains(s, "INDEX") && strings.Contains(s, "CONCURRENTLY")
        isDrop := strings.Contains(s, "DROP") && strings.Contains(s, "INDEX") && strings.Contains(s, "CONCURRENTLY")
        if !isIdx && !isDrop {
            return fmt.Errorf("%s: only CREATE/DROP INDEX CONCURRENTLY allowed in _notx, got: %.60s", name, stmt)
        }
    }
    return nil
}

Prevention

One concern per migration file: index DDL in _notx, everything else in normal migrations
Express REINDEX as DROP+CREATE CONCURRENTLY
Run the migration runner in CI before deploy to catch validation failures early

When it happens

Trigger: Putting REINDEX CONCURRENTLY t; into a _notx file (contains CONCURRENTLY, but neither CREATE+INDEX nor DROP+INDEX); or appending a plain UPDATE/INSERT statement to a _notx file alongside the index DDL (hits the non-CONCURRENTLY mixing branch at the trailing return).

Common situations: Trying to rebuild an index with REINDEX CONCURRENTLY; attempting backfill DML in the same non-transactional file; combining GRANT/COMMENT statements with the index creation.

Related errors

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