vxcontrol/pentagi · error

failed to acquire advisory lock %q: %w

Error message

failed to acquire advisory lock %q: %w

What it means

After obtaining a dedicated connection, WithAdvisoryLock runs SELECT pg_advisory_lock($1) with a lock ID derived from crc32(key). This error means PostgreSQL rejected or could not complete the lock call itself — typically because the session died mid-call, the context was canceled while blocked waiting on a lock another session holds, or the statement was terminated. Note pg_advisory_lock blocks indefinitely, so context cancellation while queued behind another holder is the most common wrapped cause.

Source

Thrown at backend/pkg/database/tenant.go:210

	})
}

// WithAdvisoryLock runs fn while holding a PostgreSQL session-level advisory
// lock derived from key. The lock is taken on a dedicated connection because
// advisory locks are session-scoped and *sql.DB is a pool.
func WithAdvisoryLock(ctx context.Context, db *sql.DB, key string, fn func(*sql.Conn) error) error {
	// crc32 into the signed 32-bit space keeps the key stable and collision-free
	// enough for the two distinct locks this application takes.
	lockID := int64(int32(crc32.ChecksumIEEE([]byte(key))))

	conn, err := db.Conn(ctx)
	if err != nil {
		return fmt.Errorf("failed to acquire database connection for advisory lock: %w", err)
	}
	defer conn.Close()

	if _, err := conn.ExecContext(ctx, "SELECT pg_advisory_lock($1)", lockID); err != nil {
		return fmt.Errorf("failed to acquire advisory lock %q: %w", key, err)
	}
	defer func() {
		// Best effort: closing the connection releases the lock regardless.
		_, _ = conn.ExecContext(context.WithoutCancel(ctx), "SELECT pg_advisory_unlock($1)", lockID)
	}()

	return fn(conn)
}

// withSearchPath returns dsn with the tenant's search_path applied as a
// PostgreSQL startup parameter (or, with viaOptions, wrapped as
// options=--search_path=<value> for poolers that need it — see
// DATABASE_SEARCH_PATH_VIA_OPTIONS in backend/docs/config.md). Supports both
// URL-style DSNs and libpq keyword strings.
func withSearchPath(dsn, searchPath string, viaOptions bool) (string, error) {
	key, value := "search_path", searchPath
	if viaOptions {
		key, value = "options", "--search_path="+searchPath

View on GitHub (pinned to ea665308ba)

Solutions

1. Retry the start once the current lock holder finishes — check holders via SELECT pid, granted FROM pg_locks WHERE locktype='advisory'; and pg_stat_activity.
2. Increase the startup context timeout so concurrent boots serialize instead of failing.
3. If a stale session holds the lock, terminate it: SELECT pg_terminate_backend(<pid>);
4. Avoid tiny statement_timeout settings on the migration/bootstrap role.

Example fix

// before
ctx := context.Background() // then caller's 5s timeout expires waiting on pg_advisory_lock
// after
ctx, cancel := context.WithTimeout(context.Background(), 5*time.Minute) // allow lock serialization

// check current advisory-lock holders before deploying a second instance
SELECT pid, granted, ((payload::bit(32))::bigint) FROM pg_locks WHERE locktype='advisory';

err := RunMigrations(ctx, db, cfg, up)
if err != nil && strings.Contains(err.Error(), "failed to acquire advisory lock") {
    // wait for the other holder to finish, then retry once
    time.Sleep(10 * time.Second)
    return RunMigrations(ctx, db, cfg, up)
}

When it happens

Trigger: EnsureTenantSchema or RunMigrations waits on pg_advisory_lock while another instance/boot holds the same lock and ctx expires or the connection drops; server shutdown (admin shutdown / crash) kills the session; statement timeout fires on the lock wait.

Common situations: Two replicas deployed simultaneously with a short startup timeout — the second times out waiting; a crashed instance's session lingering and holding the lock until the server reaps it; statement_timeout set globally on the DB killing long lock waits during big migrations.

Related errors

• failed to acquire database connection for advisory lock: %w
• failed to create flow in DB: %w
• failed to finish assistant %d: %w
• failed to delete assistant %d: %w
• flow %d stopped: %w

AI-assisted analysis of vxcontrol/pentagi@ea665308ba (2026-09-01). Data as JSON: /api/errors/aa3b4b8e4b16026b. Report an issue: GitHub.