{"record":{"id":"a276bc4adff8bd25","repo":"multica-ai/multica","slug":"acquire-advisory-lock-connection-w-a276bc","errorCode":null,"errorMessage":"acquire advisory-lock connection: %w","messagePattern":"acquire advisory-lock connection: %w","errorType":"console","errorClass":null,"httpStatus":null,"severity":"error","filePath":"server/cmd/backfill_task_usage_hourly/main.go","lineNumber":104,"sourceCode":"\tpool, err := pgxpool.New(ctx, dbURL)\n\tif err != nil {\n\t\treturn fmt.Errorf(\"connect to database: %w\", err)\n\t}\n\tdefer pool.Close()\n\n\tif err := pool.Ping(ctx); err != nil {\n\t\treturn fmt.Errorf(\"ping database: %w\", err)\n\t}\n\n\t// Serialise against the cron rollup and any other backfill run via\n\t// advisory lock 4246 — the same id the cron entry checks with\n\t// pg_try_advisory_lock. While this backfill holds it, the cron tick\n\t// no-ops instead of racing on task_usage_hourly row locks; a second\n\t// concurrent backfill blocks here until this one finishes. The lock\n\t// is held on a dedicated session connection for the whole run.\n\tlockConn, err := pool.Acquire(ctx)\n\tif err != nil {\n\t\treturn fmt.Errorf(\"acquire advisory-lock connection: %w\", err)\n\t}\n\tdefer lockConn.Release()\n\tif _, err := lockConn.Exec(ctx, `SELECT pg_advisory_lock(4246)`); err != nil {\n\t\treturn fmt.Errorf(\"acquire advisory lock 4246: %w\", err)\n\t}\n\tdefer func() {\n\t\t// Unlock on a fresh context so a cancelled ctx (SIGINT) does not\n\t\t// skip the release. Releasing the connection afterwards would end\n\t\t// the session anyway, but an explicit unlock frees it immediately.\n\t\t_, _ = lockConn.Exec(context.Background(), `SELECT pg_advisory_unlock(4246)`)\n\t}()\n\n\tvar minTS, maxTS pgtype.Timestamptz\n\tif err := pool.QueryRow(ctx, `SELECT MIN(created_at), MAX(created_at) FROM task_usage`).Scan(&minTS, &maxTS); err != nil {\n\t\treturn fmt.Errorf(\"scan task_usage time range: %w\", err)\n\t}\n\tif !minTS.Valid {\n\t\tslog.Info(\"task_usage is empty; nothing to backfill\")","sourceCodeStart":86,"sourceCodeEnd":122,"githubUrl":"https://github.com/multica-ai/multica/blob/2c0912b6ec764b373d44eeea1e80f0d9f11ab417/server/cmd/backfill_task_usage_hourly/main.go#L86-L122","documentation":"pool.Acquire(ctx) failed when the backfill tried to reserve a dedicated session connection to hold advisory lock 4246 for the whole run. Acquire fails when the context is done, the pool is closed, or the max connection lifetime/idle settings cause a acquire-time failure (e.g. pool already at max_conns and acquire deadline exceeded).","triggerScenarios":"ctx cancelled by SIGINT/SIGTERM before acquisition; pool.Close() racing the Acquire; pool_max_conns exhausted by other long-lived acquires so Acquire blocks past its context deadline.","commonSituations":"Sending Ctrl+C immediately at startup; running the backfill against a pool shared with a busy worker that saturates connections (rare here since the tool owns its pool).","solutions":["If ctx.Err() is context.Canceled, this is expected on interrupt — just re-run later; the idempotent design resumes","Check whether another process exhausted Postgres max_connections so the pool cannot open new sessions","Verify the process is not double-running with a shared pool that was closed"],"exampleFix":null,"handlingStrategy":"retry","validationCode":null,"typeGuard":null,"tryCatchPattern":"lockConn, err := pool.Acquire(ctx)\nif err != nil {\n    return fmt.Errorf(\"acquire advisory-lock connection: %w\", err)\n}","preventionTips":["Run one backfill at a time (the advisory lock also enforces this)","Size pool_max_conns above the number of long-lived acquires","Treat context.Canceled on interrupt as a clean stop, not a defect"],"tags":["go","pgx","connection-pool","concurrency"],"backgroundTag":null,"analyzedSha":"2c0912b6ec764b373d44eeea1e80f0d9f11ab417","analyzedAt":"2026-08-15T13:25:18.241Z","schemaVersion":2},"datasetVersion":"2026-08-15T17:31:12.345Z"}