vxcontrol/pentagi · error

failed to acquire database connection for advisory lock: %w

Error message

failed to acquire database connection for advisory lock: %w

What it means

Wraps the error from db.Conn(ctx) inside WithAdvisoryLock (backend/pkg/database/tenant.go), which acquires a dedicated connection to take a PostgreSQL session-level advisory lock (e.g. for migrations). Typically caused by pool exhaustion, invalid DSN, or context cancellation/deadline while waiting for a connection.

Source

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

// the pre-existing race between two single-instance deployments sharing a
// database.
func RunMigrations(ctx context.Context, db *sql.DB, cfg *config.Config, up func(*sql.DB) error) error {
	return WithAdvisoryLock(ctx, db, "pentagi-migrations-"+cfg.SchemaName(), func(*sql.Conn) error {
		return up(db)
	})
}

// 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

View on GitHub (pinned to ea665308ba)

Solutions

1. Restore PostgreSQL connectivity and check server logs; test with pg_isready.
2. Increase pool limits (SetMaxOpenConns) or reduce concurrent booting instances so a connection is free for the lock.
3. Raise the context/startup timeout if exhaustion is caused by long migrations.
4. Retry — once a connection is available the lock flow proceeds and is safe to re-run.

Example fix

// before
db, _ := sql.Open("postgres", dsn) // MaxOpenConns default; migrations starve the pool
// after
db.SetMaxOpenConns(20)
db.SetConnMaxLifetime(5 * time.Minute)

// ensure the DB is reachable and pool capacity exists before bootstrap
if err := db.PingContext(ctx); err != nil { return err }
db.SetMaxOpenConns(25)
db.SetMaxIdleConns(5)

if err := WithAdvisoryLock(ctx, db, key, fn); err != nil {
    if errors.Is(err, context.DeadlineExceeded) || strings.Contains(err.Error(), "failed to acquire database connection") {
        time.Sleep(2 * time.Second) // pool exhaustion/DB restart: retry with backoff
        return WithAdvisoryLock(ctx, db, key, fn)
    }
    return err
}

When it happens

Trigger: EnsureTenantSchema or RunMigrations calls WithAdvisoryLock and db.Conn(ctx) fails: DB down, all pool connections busy (pool exhaustion), context deadline exceeded while waiting in the pool's queue, or the DSN is invalid at dial time.

Common situations: Two instances booting simultaneously saturating a small max-open-connections pool; long migrations holding all connections; DB restarted during deploy; context timeout shorter than migration runtime.

Related errors

• failed to acquire advisory lock %q: %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/a038932f1fd59661. Report an issue: GitHub.