multica-ai/multica · error

drop invalid concurrent index %s: %w

Error message

drop invalid concurrent index %s: %w

What it means

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.

Source

Thrown at server/cmd/migrate/main.go:188

			LEFT JOIN pg_index i ON i.indexrelid = c.oid
			WHERE c.oid = to_regclass($1)
		`, indexRegclass).Scan(&schemaName, &relationName, &isIndex, &isValid)
		if errors.Is(err, pgx.ErrNoRows) {
			return nil
		}
		if err != nil {
			return fmt.Errorf("inspect concurrent index %q: %w", indexRegclass, err)
		}
		if !isIndex {
			return fmt.Errorf("relation %q exists but is not an index", indexRegclass)
		}
		if isValid {
			return nil
		}

		qualifiedName := pgx.Identifier{schemaName, relationName}.Sanitize()
		if _, err := pool.Exec(ctx, "DROP INDEX CONCURRENTLY IF EXISTS "+qualifiedName); err != nil {
			return fmt.Errorf("drop invalid concurrent index %s: %w", qualifiedName, err)
		}
		slog.Warn("removed invalid index before migration retry", "index", qualifiedName)
		return nil
	}
}

func runTaskUsageHourlyHook(ctx context.Context, pool *pgxpool.Pool) error {
	res, err := taskusagebackfill.Hook(ctx, pool, taskusagebackfill.HookOptions{})
	if err != nil {
		return fmt.Errorf("task_usage_hourly pre-103 hook: %w", err)
	}
	if res.Skipped != "" {
		slog.Info("task_usage hourly rollup hook: skipped",
			"reason", res.Skipped,
			"watermark_stamped", res.WatermarkStamped)
		return nil
	}
	slog.Info("task_usage hourly rollup hook: backfill complete",

View on GitHub (pinned to 2c0912b6ec)

Solutions

1. Identify blockers: SELECT pid, state, xact_start FROM pg_stat_activity WHERE state <> 'idle'; terminate idle-in-transaction sessions
2. Set generous timeouts for the run: SET lock_timeout=0; SET statement_timeout=0; in the migrate session/config
3. Re-run `migrate up` after clearing blockers — the drop+recreate is retried automatically

Example fix

-- before: DCIC times out behind an idle transaction

-- after: clear blockers, then retry
SELECT pg_terminate_backend(<pid>);  -- the idle-in-transaction session
-- re-run: migrate up

-- check for INVALID indexes left by interrupted CIC before migrating
SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;

if _, err := pool.Exec(ctx, "DROP INDEX CONCURRENTLY IF EXISTS "+qualifiedName); err != nil {
    return fmt.Errorf("drop invalid concurrent index %s: %w", qualifiedName, err)
}

When it happens

Trigger: 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.

Common situations: Retrying a failed concurrent-index migration on a busy production DB; a stuck transaction from an app pool blocks DCIC until timeout.

Related errors

• inspect concurrent index %q: %w
• relation %q exists but is not an index
• task_usage_hourly pre-103 hook: %w
• attribution strict-constraint pre-198 hook: %w
• invalid schema migrations table %q: %w

AI-assisted analysis of multica-ai/multica@2c0912b6ec (2026-08-15). Data as JSON: /api/errors/296d7a3b24f0bb1b. Report an issue: GitHub.