{"record":{"id":"6c2609f621e52161","repo":"Wei-Shaw/sub2api","slug":"notx-sql-currently-only-supports-create-drop-ind","errorCode":null,"errorMessage":"*_notx.sql currently only supports CREATE/DROP INDEX CONCURRENTLY statements","messagePattern":"\\*_notx\\.sql currently only supports CREATE/DROP INDEX CONCURRENTLY statements","errorType":"validation","errorClass":null,"httpStatus":null,"severity":"error","filePath":"backend/internal/repository/migrations_runner.go","lineNumber":512,"sourceCode":"\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}\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, \";\")","sourceCodeStart":494,"sourceCodeEnd":530,"githubUrl":"https://github.com/Wei-Shaw/sub2api/blob/073e92d17178a1ccdb0a27017f572f10c9c7ab62/backend/internal/repository/migrations_runner.go#L494-L530","documentation":"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.","triggerScenarios":"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).","commonSituations":"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.","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."],"exampleFix":"-- file: migrations/0008_rebuild_notx.sql (rejected)\nREINDEX CONCURRENTLY idx_events;\n\n-- file: migrations/0008_rebuild_notx.sql (accepted)\nDROP INDEX CONCURRENTLY IF EXISTS idx_events;\nCREATE INDEX CONCURRENTLY IF NOT EXISTS idx_events ON events(account_id, created_at);","handlingStrategy":"validation","validationCode":"func checkNotxStatements(name, content string) error {\n    if !strings.HasSuffix(strings.ToLower(name), \"_notx.sql\") { return nil }\n    for _, stmt := range splitSQLStatements(content) {\n        s := strings.ToUpper(strings.TrimSpace(stmt))\n        if s == \"\" { continue }\n        isIdx := strings.Contains(s, \"CREATE\") && strings.Contains(s, \"INDEX\") && strings.Contains(s, \"CONCURRENTLY\")\n        isDrop := strings.Contains(s, \"DROP\") && strings.Contains(s, \"INDEX\") && strings.Contains(s, \"CONCURRENTLY\")\n        if !isIdx && !isDrop {\n            return fmt.Errorf(\"%s: only CREATE/DROP INDEX CONCURRENTLY allowed in _notx, got: %.60s\", name, stmt)\n        }\n    }\n    return nil\n}","typeGuard":null,"tryCatchPattern":null,"preventionTips":["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"],"tags":["database","migrations","postgres","validation"],"backgroundTag":null,"analyzedSha":"073e92d17178a1ccdb0a27017f572f10c9c7ab62","analyzedAt":"2026-08-15T14:33:00.750Z","schemaVersion":2},"datasetVersion":"2026-08-15T22:17:37.221Z"}