{"record":{"id":"996055612c4cff01","repo":"Wei-Shaw/sub2api","slug":"notx-sql-must-not-contain-transaction-control-st","errorCode":null,"errorMessage":"*_notx.sql must not contain transaction control statements (BEGIN/COMMIT/ROLLBACK)","messagePattern":"\\*_notx\\.sql must not contain transaction control statements \\(BEGIN/COMMIT/ROLLBACK\\)","errorType":"validation","errorClass":null,"httpStatus":null,"severity":"error","filePath":"backend/internal/repository/migrations_runner.go","lineNumber":498,"sourceCode":"\t}\n\t_, fileOK := rule.acceptedChecksums[fileChecksum]\n\treturn fileOK\n}\n\nfunc validateMigrationExecutionMode(name, content string) (bool, error) {\n\tnormalizedName := strings.ToLower(strings.TrimSpace(name))\n\tupperContent := strings.ToUpper(content)\n\tnonTx := strings.HasSuffix(normalizedName, nonTransactionalMigrationSuffix)\n\n\tif !nonTx {\n\t\tif strings.Contains(upperContent, \"CONCURRENTLY\") {\n\t\t\treturn false, errors.New(\"CONCURRENTLY statements must be placed in *_notx.sql migrations\")\n\t\t}\n\t\treturn false, nil\n\t}\n\n\tif strings.Contains(upperContent, \"BEGIN\") || strings.Contains(upperContent, \"COMMIT\") || strings.Contains(upperContent, \"ROLLBACK\") {\n\t\treturn false, errors.New(\"*_notx.sql must not contain transaction control statements (BEGIN/COMMIT/ROLLBACK)\")\n\t}\n\n\tstatements := splitSQLStatements(content)\n\tfor _, stmt := range statements {\n\t\tnormalizedStmt := strings.ToUpper(stripSQLLineComment(strings.TrimSpace(stmt)))\n\t\tif normalizedStmt == \"\" {\n\t\t\tcontinue\n\t\t}\n\n\t\tif strings.Contains(normalizedStmt, \"CONCURRENTLY\") {\n\t\t\tisCreateIndex := strings.Contains(normalizedStmt, \"CREATE\") && strings.Contains(normalizedStmt, \"INDEX\")\n\t\t\tisDropIndex := strings.Contains(normalizedStmt, \"DROP\") && strings.Contains(normalizedStmt, \"INDEX\")\n\t\t\tif !isCreateIndex && !isDropIndex {\n\t\t\t\treturn false, errors.New(\"*_notx.sql currently only supports CREATE/DROP INDEX CONCURRENTLY statements\")\n\t\t\t}\n\t\t\tif isCreateIndex && !strings.Contains(normalizedStmt, \"IF NOT EXISTS\") {\n\t\t\t\treturn false, errors.New(\"CREATE INDEX CONCURRENTLY in *_notx.sql must include IF NOT EXISTS for idempotency\")\n\t\t\t}","sourceCodeStart":480,"sourceCodeEnd":516,"githubUrl":"https://github.com/Wei-Shaw/sub2api/blob/073e92d17178a1ccdb0a27017f572f10c9c7ab62/backend/internal/repository/migrations_runner.go#L480-L516","documentation":"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.","triggerScenarios":"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).","commonSituations":"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.","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)."],"exampleFix":"-- file: migrations/0007_add_idx_notx.sql (rejected)\nBEGIN;\nCREATE INDEX CONCURRENTLY IF NOT EXISTS idx_e ON events(a);\nCOMMIT;\n\n-- file: migrations/0007_add_idx_notx.sql (accepted)\nCREATE INDEX CONCURRENTLY IF NOT EXISTS idx_e ON events(a);","handlingStrategy":"validation","validationCode":"func checkNotxContent(name, content string) error {\n    if !strings.HasSuffix(strings.ToLower(name), \"_notx.sql\") { return nil }\n    upper := strings.ToUpper(content)\n    for _, kw := range []string{\"BEGIN\", \"COMMIT\", \"ROLLBACK\"} {\n        if strings.Contains(upper, kw) {\n            return fmt.Errorf(\"%s: _notx migration must not contain %s (even in comments)\", name, kw)\n        }\n    }\n    return nil\n}","typeGuard":null,"tryCatchPattern":null,"preventionTips":["Keep _notx files to bare DDL statements","Avoid BEGIN/COMMIT/ROLLBACK words even in SQL comments (substring match)","Let the runner own transaction control"],"tags":["database","migrations","postgres","validation"],"backgroundTag":null,"analyzedSha":"073e92d17178a1ccdb0a27017f572f10c9c7ab62","analyzedAt":"2026-08-15T14:33:00.750Z","schemaVersion":2},"datasetVersion":"2026-08-15T17:31:12.345Z"}