{"record":{"id":"2d3cc0d1c9242095","repo":"Wei-Shaw/sub2api","slug":"drop-index-concurrently-in-notx-sql-must-include","errorCode":null,"errorMessage":"DROP INDEX CONCURRENTLY in *_notx.sql must include IF EXISTS for idempotency","messagePattern":"DROP INDEX CONCURRENTLY in \\*_notx\\.sql must include IF EXISTS for idempotency","errorType":"validation","errorClass":null,"httpStatus":null,"severity":"error","filePath":"backend/internal/repository/migrations_runner.go","lineNumber":518,"sourceCode":"\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) == \"\" {\n\t\t\tcontinue\n\t\t}\n\t\tout = append(out, part)","sourceCodeStart":500,"sourceCodeEnd":536,"githubUrl":"https://github.com/Wei-Shaw/sub2api/blob/073e92d17178a1ccdb0a27017f572f10c9c7ab62/backend/internal/repository/migrations_runner.go#L500-L536","documentation":"validateMigrationExecutionMode (backend/internal/repository/migrations_runner.go:517-518) runs at migration time over each *_notx.sql file, splits it into statements, upper-cases them and strips '--' line comments. When a statement contains CONCURRENTLY and matches DROP + INDEX, it must also contain IF EXISTS, because a failed/half-finished prior deploy may have already dropped (or never created) the index and the runner re-executes the whole file outside a transaction.","triggerScenarios":"A *_notx.sql migration contains 'DROP INDEX CONCURRENTLY idx_foo;' without IF EXISTS. Raised during the pre-execution validation pass, so the statement never reaches Postgres.","commonSituations":"Rollback/cleanup migrations written by hand without the guard; statements copied from psql sessions where the index was already dropped; replacing a plain DROP INDEX with the CONCURRENTLY variant while forgetting the idempotency token required by this runner's policy.","solutions":["Change the statement to DROP INDEX CONCURRENTLY IF EXISTS idx_name; in the offending *_notx.sql file.","Re-run migrations; the validator passes once the token is present.","Keep a file/CI template for _notx migrations so both CREATE (IF NOT EXISTS) and DROP (IF EXISTS) variants always carry their guard."],"exampleFix":"-- before (0012_drop_old_index_notx.sql)\nDROP INDEX CONCURRENTLY idx_groups_legacy;\n\n-- after\nDROP INDEX CONCURRENTLY IF EXISTS idx_groups_legacy;","handlingStrategy":"validation","validationCode":"// Pre-deploy lint: mirror the runner's DROP INDEX rule.\nfunc lintNotxDropIndexes(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 strings.Contains(s, \"CONCURRENTLY\") && strings.Contains(s, \"DROP\") && strings.Contains(s, \"INDEX\") && !strings.Contains(s, \"IF EXISTS\") {\n\t\t\t\treturn fmt.Errorf(\"%s: DROP INDEX CONCURRENTLY missing IF EXISTS\", f)\n\t\t\t}\n\t}\n\treturn nil\n}","typeGuard":null,"tryCatchPattern":"// Validator error => edit the migration file; never catch-and-continue.\nif err := migrationsRunner.Run(ctx); err != nil {\n\tif strings.Contains(err.Error(), \"IF EXISTS\") || strings.Contains(err.Error(), \"_notx.sql\") {\n\t\tlog.Fatalf(\"fix migration file: %v\", err)\n\t}\n\treturn err\n}","preventionTips":["Treat DROP INDEX CONCURRENTLY IF EXISTS as the only sanctioned form in _notx files","Keep rollback migrations in the same reviewed template as forward ones","Run the migration validator as a CI gate on every PR touching migrations/"],"tags":["postgresql","sql","migrations","go","database","idempotency"],"backgroundTag":null,"analyzedSha":"073e92d17178a1ccdb0a27017f572f10c9c7ab62","analyzedAt":"2026-08-15T14:33:00.750Z","schemaVersion":2},"datasetVersion":"2026-08-15T17:31:12.345Z"}