{"record":{"id":"a038932f1fd59661","repo":"vxcontrol/pentagi","slug":"failed-to-acquire-database-connection-for-advisory","errorCode":null,"errorMessage":"failed to acquire database connection for advisory lock: %w","messagePattern":"failed to acquire database connection for advisory lock: %w","errorType":"exception","errorClass":null,"httpStatus":null,"severity":"error","filePath":"backend/pkg/database/tenant.go","lineNumber":205,"sourceCode":"// the pre-existing race between two single-instance deployments sharing a\n// database.\nfunc RunMigrations(ctx context.Context, db *sql.DB, cfg *config.Config, up func(*sql.DB) error) error {\n\treturn WithAdvisoryLock(ctx, db, \"pentagi-migrations-\"+cfg.SchemaName(), func(*sql.Conn) error {\n\t\treturn up(db)\n\t})\n}\n\n// WithAdvisoryLock runs fn while holding a PostgreSQL session-level advisory\n// lock derived from key. The lock is taken on a dedicated connection because\n// advisory locks are session-scoped and *sql.DB is a pool.\nfunc WithAdvisoryLock(ctx context.Context, db *sql.DB, key string, fn func(*sql.Conn) error) error {\n\t// crc32 into the signed 32-bit space keeps the key stable and collision-free\n\t// enough for the two distinct locks this application takes.\n\tlockID := int64(int32(crc32.ChecksumIEEE([]byte(key))))\n\n\tconn, err := db.Conn(ctx)\n\tif err != nil {\n\t\treturn fmt.Errorf(\"failed to acquire database connection for advisory lock: %w\", err)\n\t}\n\tdefer conn.Close()\n\n\tif _, err := conn.ExecContext(ctx, \"SELECT pg_advisory_lock($1)\", lockID); err != nil {\n\t\treturn fmt.Errorf(\"failed to acquire advisory lock %q: %w\", key, err)\n\t}\n\tdefer func() {\n\t\t// Best effort: closing the connection releases the lock regardless.\n\t\t_, _ = conn.ExecContext(context.WithoutCancel(ctx), \"SELECT pg_advisory_unlock($1)\", lockID)\n\t}()\n\n\treturn fn(conn)\n}\n\n// withSearchPath returns dsn with the tenant's search_path applied as a\n// PostgreSQL startup parameter (or, with viaOptions, wrapped as\n// options=--search_path=<value> for poolers that need it — see\n// DATABASE_SEARCH_PATH_VIA_OPTIONS in backend/docs/config.md). Supports both","sourceCodeStart":187,"sourceCodeEnd":223,"githubUrl":"https://github.com/vxcontrol/pentagi/blob/ea665308baaff015b226f308438a68d929d0f29b/backend/pkg/database/tenant.go#L187-L223","documentation":"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.","triggerScenarios":"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.","commonSituations":"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.","solutions":["Restore PostgreSQL connectivity and check server logs; test with pg_isready.","Increase pool limits (SetMaxOpenConns) or reduce concurrent booting instances so a connection is free for the lock.","Raise the context/startup timeout if exhaustion is caused by long migrations.","Retry — once a connection is available the lock flow proceeds and is safe to re-run."],"exampleFix":"// before\ndb, _ := sql.Open(\"postgres\", dsn) // MaxOpenConns default; migrations starve the pool\n// after\ndb.SetMaxOpenConns(20)\ndb.SetConnMaxLifetime(5 * time.Minute)","handlingStrategy":"retry","validationCode":"// ensure the DB is reachable and pool capacity exists before bootstrap\nif err := db.PingContext(ctx); err != nil { return err }\ndb.SetMaxOpenConns(25)\ndb.SetMaxIdleConns(5)","typeGuard":null,"tryCatchPattern":"if err := WithAdvisoryLock(ctx, db, key, fn); err != nil {\n    if errors.Is(err, context.DeadlineExceeded) || strings.Contains(err.Error(), \"failed to acquire database connection\") {\n        time.Sleep(2 * time.Second) // pool exhaustion/DB restart: retry with backoff\n        return WithAdvisoryLock(ctx, db, key, fn)\n    }\n    return err\n}","preventionTips":["Size the pool above the number of concurrent booting instances","Add a postgres healthcheck so instances boot only when the DB is ready","Give startup contexts a generous timeout","Watch pg_stat_activity for connections left idle in transaction"],"tags":["postgres","advisory-lock","connection-pool","concurrency"],"backgroundTag":"connection-pool-exhausted","analyzedSha":"ea665308baaff015b226f308438a68d929d0f29b","analyzedAt":"2026-09-01T14:16:31.421Z","contentChangedAt":null,"schemaVersion":2},"datasetVersion":"2026-09-08T15:18:49.778Z"}