{"record":{"id":"798408eb0f4ea89f","repo":"Wei-Shaw/sub2api","slug":"notx-sql-must-not-mix-non-concurrently-sql-state","errorCode":null,"errorMessage":"*_notx.sql must not mix non-CONCURRENTLY SQL statements","messagePattern":"\\*_notx\\.sql must not mix non-CONCURRENTLY SQL statements","errorType":"validation","errorClass":null,"httpStatus":null,"severity":"error","filePath":"backend/internal/repository/migrations_runner.go","lineNumber":523,"sourceCode":"\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}\n\t\t\tif isDropIndex && !strings.Contains(normalizedStmt, \"IF EXISTS\") {\n\t\t\t\treturn false, errors.New(\"DROP INDEX CONCURRENTLY in *_notx.sql must include IF EXISTS for idempotency\")\n\t\t\t}\n\t\t\tcontinue\n\t\t}\n\n\t\treturn false, errors.New(\"*_notx.sql must not mix non-CONCURRENTLY SQL statements\")\n\t}\n\n\treturn true, nil\n}\n\nfunc splitSQLStatements(content string) []string {\n\tparts := strings.Split(content, \";\")\n\tout := make([]string, 0, len(parts))\n\tfor _, part := range parts {\n\t\tif strings.TrimSpace(part) == \"\" {\n\t\t\tcontinue\n\t\t}\n\t\tout = append(out, part)\n\t}\n\treturn out\n}\n\nfunc stripSQLLineComment(s string) string {","sourceCodeStart":505,"sourceCodeEnd":541,"githubUrl":"https://github.com/Wei-Shaw/sub2api/blob/073e92d17178a1ccdb0a27017f572f10c9c7ab62/backend/internal/repository/migrations_runner.go#L505-L541","documentation":"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.","triggerScenarios":"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.","commonSituations":"'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.","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."],"exampleFix":"-- before: 0007_profit_notx.sql (rejected)\nCREATE INDEX CONCURRENTLY IF NOT EXISTS idx_accounts_platform ON accounts(platform);\nUPDATE groups SET profit_control_enabled = false WHERE platform = 'web';\n\n-- after: split into two files\n-- 0007_profit_notx.sql (non-transactional, index only)\nCREATE INDEX CONCURRENTLY IF NOT EXISTS idx_accounts_platform ON accounts(platform);\n-- 0008_disable_web_profit.sql (regular transactional migration)\nUPDATE groups SET profit_control_enabled = false WHERE platform = 'web';","handlingStrategy":"validation","validationCode":"// Pre-flight check: a *_notx.sql file must contain ONLY CONCURRENTLY statements.\nfunc lintNotxPurity(files []string) error {\n\tfor _, f := range files {\n\t\tif !strings.HasSuffix(strings.ToLower(f), \"_notx.sql\") {\n\t\t\tcontinue\n\t\t}\n\t\tb, err := os.ReadFile(f)\n\t\tif err != nil {\n\t\t\treturn err\n\t\t}\n\t\tif strings.Contains(strings.ToUpper(string(b)), \"BEGIN\") || strings.Contains(strings.ToUpper(string(b)), \"COMMIT\") {\n\t\t\treturn fmt.Errorf(\"%s: transaction control in _notx file\", f)\n\t\t}\n\t\tfor _, stmt := range strings.Split(string(b), \";\") {\n\t\t\ts := strings.ToUpper(strings.TrimSpace(stmt))\n\t\t\tif s == \"\" {\n\t\t\t\tcontinue\n\t\t\t}\n\t\t\tif !strings.Contains(s, \"CONCURRENTLY\") {\n\t\t\t\treturn fmt.Errorf(\"%s: non-CONCURRENTLY statement in _notx file: %.40s\", f, s)\n\t\t\t}\n\t}\n\treturn nil\n}","typeGuard":null,"tryCatchPattern":"// Fail the deploy loudly; this error always means the migration file's content is wrong.\nif err := migrationsRunner.Run(ctx); err != nil {\n\tvar msg = err.Error()\n\tif strings.Contains(msg, \"must not mix\") || strings.Contains(msg, \"_notx.sql\") {\n\t\tlog.Fatalf(\"invalid *_notx.sql migration (split statements across tx/_notx files): %v\", err)\n\t}\n\treturn err\n}","preventionTips":["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"],"tags":["postgresql","sql","migrations","go","database","transactions"],"backgroundTag":null,"analyzedSha":"073e92d17178a1ccdb0a27017f572f10c9c7ab62","analyzedAt":"2026-08-15T14:33:00.750Z","schemaVersion":2},"datasetVersion":"2026-08-15T17:31:12.345Z"}