{"record":{"id":"972dc0f7b12d7c4b","repo":"multica-ai/multica","slug":"acquire-migration-advisory-lock-w","errorCode":null,"errorMessage":"acquire migration advisory lock: %w","messagePattern":"acquire migration advisory lock: %w","errorType":"console","errorClass":null,"httpStatus":null,"severity":"error","filePath":"server/cmd/migrate/main.go","lineNumber":375,"sourceCode":"\t// *pgxpool.Conn for the whole run — calling pool.Exec would attach the\n\t// lock to a random connection that pgxpool could hand back out before\n\t// the loop finishes, making the lock effectively a no-op. We use the\n\t// blocking pg_advisory_lock (not pg_try_*) so a late-arriving runner\n\t// queues behind the current one instead of crash-looping; once it\n\t// acquires the lock the EXISTS checks below turn finished migrations\n\t// into no-op skips.\n\t//\n\t// We deliberately do NOT wrap the loop in a single transaction: the\n\t// repo already ships migrations using CREATE INDEX CONCURRENTLY,\n\t// which Postgres rejects inside a transaction block.\n\tconn, err := pool.Acquire(ctx)\n\tif err != nil {\n\t\treturn fmt.Errorf(\"acquire migration connection: %w\", err)\n\t}\n\tdefer conn.Release()\n\n\tif _, err := conn.Exec(ctx, \"SELECT pg_advisory_lock($1)\", lockKey); err != nil {\n\t\treturn fmt.Errorf(\"acquire migration advisory lock: %w\", err)\n\t}\n\t// Best-effort explicit unlock on the success path. On error returns\n\t// the defer still runs; on os.Exit error paths in main() it does not,\n\t// but session-level advisory locks are released automatically when\n\t// the connection closes at process exit, so the next runner is never\n\t// permanently blocked.\n\tdefer func() {\n\t\tif _, err := conn.Exec(ctx, \"SELECT pg_advisory_unlock($1)\", lockKey); err != nil {\n\t\t\tslog.Warn(\"failed to release migration advisory lock\", \"error\", err)\n\t\t}\n\t}()\n\n\t// Create migrations tracking table.\n\tif _, err := conn.Exec(ctx, fmt.Sprintf(`\n\t\tCREATE TABLE IF NOT EXISTS %s (\n\t\t\tversion TEXT PRIMARY KEY,\n\t\t\tapplied_at TIMESTAMPTZ NOT NULL DEFAULT now()\n\t\t)","sourceCodeStart":357,"sourceCodeEnd":393,"githubUrl":"https://github.com/multica-ai/multica/blob/2c0912b6ec764b373d44eeea1e80f0d9f11ab417/server/cmd/migrate/main.go#L357-L393","documentation":"Executing SELECT pg_advisory_lock($1) on the pinned migration connection failed. This is the blocking variant: a second runner (multi-replica Deployment, manual `migrate up` overlapping pod startup) normally queues here without erroring. An error means the session died while acquiring/waiting — server restart, network drop, backend termination, or ctx cancellation while queued.","triggerScenarios":"ctx cancelled while this runner waits behind another holding migrationAdvisoryLockKey; Postgres failover/restart during the wait; operator runs pg_terminate_backend on the queued backend.","commonSituations":"Two replicas starting simultaneously and one's startup probe timeout cancels it while queued; server maintenance mid-migration-start.","solutions":["Re-run migrate — the lock is session-scoped and freed when the dead session ends, and per-version EXISTS checks make re-runs no-ops for applied versions","Check who holds the lock: SELECT pid, ... FROM pg_locks WHERE locktype='advisory' AND objid=<key>","Increase the startup timeout of initContainers/jobs that run migrations so queued runners survive the wait"],"exampleFix":null,"handlingStrategy":"retry","validationCode":null,"typeGuard":null,"tryCatchPattern":"if _, err := conn.Exec(ctx, \"SELECT pg_advisory_lock($1)\", lockKey); err != nil {\n    return fmt.Errorf(\"acquire migration advisory lock: %w\", err)\n}","preventionTips":["Give initContainers/jobs enough timeout to survive queuing behind another runner","Understand this call blocks by design; a failure means the session died, so retry","Session-level locks auto-release at process exit — no stuck-lock remediation needed"],"tags":["go","postgres","advisory-lock","migration","concurrency"],"backgroundTag":null,"analyzedSha":"2c0912b6ec764b373d44eeea1e80f0d9f11ab417","analyzedAt":"2026-08-15T13:25:18.241Z","schemaVersion":2},"datasetVersion":"2026-08-15T17:31:12.345Z"}