{"record":{"id":"9a49d2553285dccd","repo":"Wei-Shaw/sub2api","slug":"create-index-concurrently-in-notx-sql-must-inclu","errorCode":null,"errorMessage":"CREATE INDEX CONCURRENTLY in *_notx.sql must include IF NOT EXISTS for idempotency","messagePattern":"CREATE INDEX CONCURRENTLY in \\*_notx\\.sql must include IF NOT EXISTS for idempotency","errorType":"validation","errorClass":null,"httpStatus":null,"severity":"error","filePath":"backend/internal/repository/migrations_runner.go","lineNumber":515,"sourceCode":"\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, \";\")\n\tout := make([]string, 0, len(parts))\n\tfor _, part := range parts {\n\t\tif strings.TrimSpace(part) == \"\" {","sourceCodeStart":497,"sourceCodeEnd":533,"githubUrl":"https://github.com/Wei-Shaw/sub2api/blob/073e92d17178a1ccdb0a27017f572f10c9c7ab62/backend/internal/repository/migrations_runner.go#L497-L533","documentation":"validateMigrationExecutionMode (backend/internal/repository/migrations_runner.go:514-515) statically inspects every *_notx.sql migration before the runner executes anything. Non-transactional files are restricted to CREATE/DROP INDEX CONCURRENTLY statements, and every CREATE INDEX CONCURRENTLY must contain IF NOT EXISTS so a partially applied migration can be re-run safely (CONCURRENTLY cannot run inside a transaction, hence no atomic apply). The error fires when a normalized upper-case statement matches CREATE + INDEX + CONCURRENTLY but lacks the IF NOT EXISTS token.","triggerScenarios":"A migration file whose name ends in _notx.sql contains a statement like 'CREATE INDEX CONCURRENTLY idx_groups_platform ON groups(platform);' without IF NOT EXISTS. It triggers at runner startup (app boot or migrate command), before any SQL reaches Postgres.","commonSituations":"Developer copies an index statement from psql history or another project that omits IF NOT EXISTS; an existing transactional migration is renamed to _notx.sql to get CONCURRENTLY support without editing the statement; habits from Postgres < 9.5 where IF NOT EXISTS on indexes did not exist.","solutions":["Open the offending *_notx.sql and make the statement idempotent: CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_name ON table(col);","Re-run the migration command; the runner re-validates the file content at startup and will now accept it.","If the statement genuinely cannot be idempotent, move it out of _notx.sql or reconsider whether it needs CONCURRENTLY at all."],"exampleFix":"-- before (0007_index_notx.sql)\nCREATE INDEX CONCURRENTLY idx_accounts_platform ON accounts(platform);\n\n-- after\nCREATE INDEX CONCURRENTLY IF NOT EXISTS idx_accounts_platform ON accounts(platform);","handlingStrategy":"validation","validationCode":"// Run in CI / pre-deploy over the migrations dir before the app boots.\nfunc lintNotxCreateIndexes(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\tfor _, stmt := range strings.Split(string(b), \";\") {\n\t\t\ts := strings.ToUpper(strings.TrimSpace(stmt))\n\t\t\tif s == \"\" || !strings.Contains(s, \"CONCURRENTLY\") {\n\t\t\t\tcontinue\n\t\t\t}\n\t\t\tif strings.Contains(s, \"CREATE\") && strings.Contains(s, \"INDEX\") && !strings.Contains(s, \"IF NOT EXISTS\") {\n\t\t\t\treturn fmt.Errorf(\"%s: CREATE INDEX CONCURRENTLY missing IF NOT EXISTS\", f)\n\t\t\t}\n\t}\n\treturn nil\n}","typeGuard":null,"tryCatchPattern":"// The runner already validates before executing; treat any error mentioning _notx.sql as a file-content bug, not a DB failure.\nif err := migrationsRunner.Run(ctx); err != nil {\n\tif strings.Contains(err.Error(), \"_notx.sql\") {\n\t\tlog.Fatalf(\"migration file rejected by validator: %v\", err) // fix the .sql file; do not retry\n\t}\n\treturn fmt.Errorf(\"run migrations: %w\", err)\n}","preventionTips":["Use a snippet/template for new *_notx.sql files that always includes IF NOT EXISTS on CREATE INDEX CONCURRENTLY","Add a CI step that lints migrations/*.sql with the same rules before merge","In code review, reject any _notx.sql statement lacking IF NOT EXISTS/IF EXISTS"],"tags":["postgresql","sql","migrations","go","database","idempotency"],"backgroundTag":null,"analyzedSha":"073e92d17178a1ccdb0a27017f572f10c9c7ab62","analyzedAt":"2026-08-15T14:33:00.750Z","schemaVersion":2},"datasetVersion":"2026-08-15T22:17:37.221Z"}