*_notx.sql must not contain transaction control statements (

Error message

*_notx.sql must not contain transaction control statements (BEGIN/COMMIT/ROLLBACK)

What it means

For *_notx.sql migrations (executed without a wrapping transaction), the runner forbids explicit transaction control: any occurrence of BEGIN, COMMIT, or ROLLBACK as substrings of the uppercased content is rejected. Since the runner itself controls transactionality, embedded control statements could commit half-applied state or conflict with the non-transactional execution mode.

Source

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

	}
	_, 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")
			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")
			}

View on GitHub (pinned to 073e92d171)

Solutions

Remove BEGIN/COMMIT/ROLLBACK lines from the *_notx.sql file; the runner manages execution mode.
Keep the statements bare: CREATE INDEX CONCURRENTLY IF NOT EXISTS ...; one per statement.
Avoid those keywords even in comments inside _notx files (validation is a raw substring match on uppercased content).

Example fix

-- file: migrations/0007_add_idx_notx.sql (rejected)
BEGIN;
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_e ON events(a);
COMMIT;

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

Defensive patterns

Strategy: validation

Validate before calling

func checkNotxContent(name, content string) error {
    if !strings.HasSuffix(strings.ToLower(name), "_notx.sql") { return nil }
    upper := strings.ToUpper(content)
    for _, kw := range []string{"BEGIN", "COMMIT", "ROLLBACK"} {
        if strings.Contains(upper, kw) {
            return fmt.Errorf("%s: _notx migration must not contain %s (even in comments)", name, kw)
        }
    }
    return nil
}

Prevention

Keep _notx files to bare DDL statements
Avoid BEGIN/COMMIT/ROLLBACK words even in SQL comments (substring match)
Let the runner own transaction control

When it happens

Trigger: Authoring a *_notx.sql that opens its own transaction for safety ('BEGIN; CREATE INDEX CONCURRENTLY ...; COMMIT;') — exactly the instinct the runner prohibits; or including a COMMENT containing those words in prose (substring matching is literal, so even comments can trip it).

Common situations: Developers porting psql scripts that wrap statements in BEGIN/COMMIT; comments like '-- do not COMMIT early' triggering the substring check; generated SQL from ORMs emitting transaction wrappers.

Related errors

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