What causes "acquire advisory-lock connection: %w" in multica-ai/multica?

If you interrupted the tool intentionally, just re-run with --execute — the advisory lock design makes re-running safe.

acquire advisory-lock connection: %w

Error message

acquire advisory-lock connection: %w

What it means

Returned when pool.Acquire fails while the backfill tries to dedicate one pooled connection to hold a pg advisory lock for the update phase. In practice this fires only after --execute is set and eligible rows exist, and almost always means ctx was cancelled (SIGINT/SIGTERM via signal.NotifyContext) or the pool hit its max size / was closed — not a database protocol error.

Source

Thrown at server/cmd/backfill_codex_usage_cache/main.go:115

	}

	rows, total, err := loadDryRunSummary(ctx, pool, cfg)
	if err != nil {
		return err
	}
	logSummary(cfg, rows, total)
	if total.Rows == 0 {
		slog.Info("no eligible Codex task_usage rows found")
		return nil
	}
	if !cfg.execute {
		slog.Info("dry-run complete; review the summary, then re-run with --execute to apply the backfill")
		return nil
	}

	lockConn, err := pool.Acquire(ctx)
	if err != nil {
		return fmt.Errorf("acquire advisory-lock connection: %w", err)
	}
	defer lockConn.Release()
	if _, err := lockConn.Exec(ctx, `SELECT pg_advisory_lock($1)`, rollupAdvisoryLockID); err != nil {
		return fmt.Errorf("acquire advisory lock %d: %w", rollupAdvisoryLockID, err)
	}
	defer func() {
		_, _ = lockConn.Exec(context.Background(), `SELECT pg_advisory_unlock($1)`, rollupAdvisoryLockID)
	}()

	updateStartedAt, err := databaseClock(ctx, pool)
	if err != nil {
		return err
	}

	updatedRows, removedTokens, err := executeBackfill(ctx, pool, cfg)
	if err != nil {
		return err
	}

View on GitHub (pinned to 2c0912b6ec)

Solutions

If you interrupted the tool intentionally, just re-run with --execute — the advisory lock design makes re-running safe.
Remove pool-limiting params from the DSN or raise max_conns so one connection is free.
Run the tool under a longer timeout / with nohup so phase transitions are not killed mid-acquire.

Example fix

# before
./backfill_codex_usage_cache --cutoff ... --execute &  # killed by 30s supervisor timeout
# acquire advisory-lock connection: context canceled

# after
nohup ./backfill_codex_usage_cache --cutoff 2026-01-01T00:00:00Z --execute >backfill.log 2>&1 &

Defensive patterns

Strategy: retry

Prevention

Run the --execute phase under nohup/tmux with generous timeouts so phase transitions are not killed.
Keep at least one spare pool connection (avoid tiny max_conns in the DSN).
Know that a re-run after cancellation is safe — the tool's eligibility predicate self-excludes corrected rows.

When it happens

Trigger: Sending SIGINT/SIGTERM to a running --execute backfill just as it moves from the dry-run summary into the locked update phase; pool exhaustion because max_conns is tiny and other sessions hold them; pool already closed.

Common situations: Operator Ctrl-C's the tool during a long run; a supervisor with a short timeout kills it at phase transition; DATABASE_URL embeds pool limiting parameters that leave fewer than 1 free connection.

Related errors

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