*_notx.sql must not mix non-CONCURRENTLY SQL statements

Error message

*_notx.sql must not mix non-CONCURRENTLY SQL statements

What it means

validateMigrationExecutionMode (backend/internal/repository/migrations_runner.go:523) rejects any *_notx.sql statement that does not contain the CONCURRENTLY token. _notx files exist solely because Postgres cannot run CREATE/DROP INDEX CONCURRENTLY inside a transaction; every other statement must live in a regular transactional migration. Note the parser splits naively on ';' (splitSQLStatements, line 529) and strips only '--' line comments, so a semicolon inside a string literal or a '/* */' block comment can produce a fragment that lands on this error even if the file 'looks' index-only.

Source

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

			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, ";")
	out := make([]string, 0, len(parts))
	for _, part := range parts {
		if strings.TrimSpace(part) == "" {
			continue
		}
		out = append(out, part)
	}
	return out
}

func stripSQLLineComment(s string) string {

View on GitHub (pinned to 073e92d171)

Solutions

Move every non-CONCURRENTLY statement into a separate numbered transactional migration (plain .sql handled inside a transaction).
Leave only CREATE/DROP INDEX CONCURRENTLY ... IF [NOT] EXISTS statements in the *_notx.sql file.
If a ';' inside a string literal or dollar-quoted body caused a bogus split, move that statement to a tx migration or rewrite it without embedded semicolons - splitSQLStatements is a naive split and cannot parse SQL quoting.
Replace '/* */' block comments with '--' line comments so normalization sees the real statement.

Example fix

-- before: 0007_profit_notx.sql (rejected)
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_accounts_platform ON accounts(platform);
UPDATE groups SET profit_control_enabled = false WHERE platform = 'web';

-- after: split into two files
-- 0007_profit_notx.sql (non-transactional, index only)
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_accounts_platform ON accounts(platform);
-- 0008_disable_web_profit.sql (regular transactional migration)
UPDATE groups SET profit_control_enabled = false WHERE platform = 'web';

Defensive patterns

Strategy: validation

Validate before calling

// Pre-flight check: a *_notx.sql file must contain ONLY CONCURRENTLY statements.
func lintNotxPurity(files []string) error {
	for _, f := range files {
		if !strings.HasSuffix(strings.ToLower(f), "_notx.sql") {
			continue
		}
		b, err := os.ReadFile(f)
		if err != nil {
			return err
		}
		if strings.Contains(strings.ToUpper(string(b)), "BEGIN") || strings.Contains(strings.ToUpper(string(b)), "COMMIT") {
			return fmt.Errorf("%s: transaction control in _notx file", f)
		}
		for _, stmt := range strings.Split(string(b), ";") {
			s := strings.ToUpper(strings.TrimSpace(stmt))
			if s == "" {
				continue
			}
			if !strings.Contains(s, "CONCURRENTLY") {
				return fmt.Errorf("%s: non-CONCURRENTLY statement in _notx file: %.40s", f, s)
			}
	}
	return nil
}

Try / catch

// Fail the deploy loudly; this error always means the migration file's content is wrong.
if err := migrationsRunner.Run(ctx); err != nil {
	var msg = err.Error()
	if strings.Contains(msg, "must not mix") || strings.Contains(msg, "_notx.sql") {
		log.Fatalf("invalid *_notx.sql migration (split statements across tx/_notx files): %v", err)
	}
	return err
}

Prevention

One concern per migration file: indexes-with-CONCURRENTLY in _notx, everything else in tx migrations
Avoid semicolons inside string literals in _notx files - the splitter is naive (';' split)
Use '--' comments only; '/* */' blocks are not stripped and can confuse statement classification
CI lint that rejects any _notx.sql statement without the CONCURRENTLY token

When it happens

Trigger: A *_notx.sql file mixes in ALTER TABLE / UPDATE / INSERT / COMMENT ON / ANALYZE, or contains a plain non-concurrent CREATE INDEX; alternatively a string literal or function body containing ';' splits into a fragment without CONCURRENTLY. Also: BEGIN/COMMIT/ROLLBACK are rejected earlier (line 497), everything else non-CONCURRENTLY hits line 523.

Common situations: 'Just one more statement' growth of an existing _notx migration (e.g. a backfill UPDATE after the index); renaming a problematic file to _notx.sql to dodge transaction restrictions; writing ANALYZE or COMMENT ON after the index build; block comments (not stripped by stripSQLLineComment) hiding or introducing keywords.

Related errors

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