{"record":{"id":"296d7a3b24f0bb1b","repo":"multica-ai/multica","slug":"drop-invalid-concurrent-index-s-w","errorCode":null,"errorMessage":"drop invalid concurrent index %s: %w","messagePattern":"drop invalid concurrent index (.+?): %w","errorType":"console","errorClass":null,"httpStatus":null,"severity":"error","filePath":"server/cmd/migrate/main.go","lineNumber":188,"sourceCode":"\t\t\tLEFT JOIN pg_index i ON i.indexrelid = c.oid\n\t\t\tWHERE c.oid = to_regclass($1)\n\t\t`, indexRegclass).Scan(&schemaName, &relationName, &isIndex, &isValid)\n\t\tif errors.Is(err, pgx.ErrNoRows) {\n\t\t\treturn nil\n\t\t}\n\t\tif err != nil {\n\t\t\treturn fmt.Errorf(\"inspect concurrent index %q: %w\", indexRegclass, err)\n\t\t}\n\t\tif !isIndex {\n\t\t\treturn fmt.Errorf(\"relation %q exists but is not an index\", indexRegclass)\n\t\t}\n\t\tif isValid {\n\t\t\treturn nil\n\t\t}\n\n\t\tqualifiedName := pgx.Identifier{schemaName, relationName}.Sanitize()\n\t\tif _, err := pool.Exec(ctx, \"DROP INDEX CONCURRENTLY IF EXISTS \"+qualifiedName); err != nil {\n\t\t\treturn fmt.Errorf(\"drop invalid concurrent index %s: %w\", qualifiedName, err)\n\t\t}\n\t\tslog.Warn(\"removed invalid index before migration retry\", \"index\", qualifiedName)\n\t\treturn nil\n\t}\n}\n\nfunc runTaskUsageHourlyHook(ctx context.Context, pool *pgxpool.Pool) error {\n\tres, err := taskusagebackfill.Hook(ctx, pool, taskusagebackfill.HookOptions{})\n\tif err != nil {\n\t\treturn fmt.Errorf(\"task_usage_hourly pre-103 hook: %w\", err)\n\t}\n\tif res.Skipped != \"\" {\n\t\tslog.Info(\"task_usage hourly rollup hook: skipped\",\n\t\t\t\"reason\", res.Skipped,\n\t\t\t\"watermark_stamped\", res.WatermarkStamped)\n\t\treturn nil\n\t}\n\tslog.Info(\"task_usage hourly rollup hook: backfill complete\",","sourceCodeStart":170,"sourceCodeEnd":206,"githubUrl":"https://github.com/multica-ai/multica/blob/2c0912b6ec764b373d44eeea1e80f0d9f11ab417/server/cmd/migrate/main.go#L170-L206","documentation":"DROP INDEX CONCURRENTLY IF EXISTS <qualified> failed while removing an INVALID index left behind by a previously interrupted CREATE INDEX CONCURRENTLY, so the migration can rebuild it. CIC/DCIC cannot run inside a transaction block, need to take locks, and can hit lock timeouts — the wrapped error states which.","triggerScenarios":"statement_timeout/lock_timeout firing while DCIC waits for ACCESS EXCLUSIVE on the index; another session holding long transactions on the table (common in production with idle-in-transaction sessions); running under a driver/wrapper that auto-wraps statements in a transaction.","commonSituations":"Retrying a failed concurrent-index migration on a busy production DB; a stuck transaction from an app pool blocks DCIC until timeout.","solutions":["Identify blockers: SELECT pid, state, xact_start FROM pg_stat_activity WHERE state <> 'idle'; terminate idle-in-transaction sessions","Set generous timeouts for the run: SET lock_timeout=0; SET statement_timeout=0; in the migrate session/config","Re-run `migrate up` after clearing blockers — the drop+recreate is retried automatically"],"exampleFix":"-- before: DCIC times out behind an idle transaction\n\n-- after: clear blockers, then retry\nSELECT pg_terminate_backend(<pid>);  -- the idle-in-transaction session\n-- re-run: migrate up","handlingStrategy":"retry","validationCode":"-- check for INVALID indexes left by interrupted CIC before migrating\nSELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;","typeGuard":null,"tryCatchPattern":"if _, err := pool.Exec(ctx, \"DROP INDEX CONCURRENTLY IF EXISTS \"+qualifiedName); err != nil {\n    return fmt.Errorf(\"drop invalid concurrent index %s: %w\", qualifiedName, err)\n}","preventionTips":["Clear idle-in-transaction sessions before running concurrent-index migrations","Set lock_timeout/statement_timeout generously (or 0) for the migration session","Never wrap CIC/DCIC in a transaction — run migrations with this tool, not inside app transactions"],"tags":["go","postgres","migration","concurrent-index","locks"],"backgroundTag":null,"analyzedSha":"2c0912b6ec764b373d44eeea1e80f0d9f11ab417","analyzedAt":"2026-08-15T13:25:18.241Z","schemaVersion":2},"datasetVersion":"2026-08-15T22:17:37.221Z"}