// advent-of-postgres/day-18
The Lock Pile-Up
Thirteen sessions are stuck waiting on locks. Follow the blocking chains back to the session that is really holding everyone up.
At 03:12 the gift database froze. Pepper caught a snapshot of who was waiting on whom before restarting anything, and wants to know which session to cancel first.
The snapshot is in lock_snapshot, built from pg_blocking_pids():
| Column | Type | Notes |
|---|---|---|
pid |
int |
Primary key: the session |
query |
text |
What it was running |
blocked_by |
int[] |
The sessions it was waiting for (can be empty) |
If session A waits for B, and B waits for C, then C is holding up both B and A. A session can wait for more than one other session.
Your task
Which session holds up the most other sessions, directly or through a chain?
Submit its pid.
Stuck? Show a hint
unnest(blocked_by) turns the array into one row per blocker. Then walk the edges with a recursive CTE and count everyone each root is holding up.
Download today's data as SQL to solve it on your own Postgres (14 or later).
Solved this puzzle? Share it.
Tag @thedevopsdaily and share it with the community.
We earn commissions when you shop through the links below.
Bitrise
Managed runners for GitHub Actions
Bitrise Build Hub runs your GitHub Actions jobs on M4 Macs and AMD Linux machines. Change runs-on and keep your workflows, secrets and logs on GitHub.
Atomsized
AWS platform engineering and GitOps
Design and automation for reliable AWS and Kubernetes platforms, safer delivery workflows, and preview and UAT environments your engineers can understand and own.
DigitalOcean
Cloud infrastructure for developers
Simple, reliable cloud computing designed for developers
DevDojo
Developer community & tools
Join a community of developers sharing knowledge and tools
SMTPfast
Developer-first email API
Send transactional and marketing email through a clean REST API. Detailed logs, webhooks, and embeddable signup forms in one dashboard.
QuizAPI
Developer-first quiz platform
Build, generate, and embed quizzes with a powerful REST API. AI-powered question generation and live multiplayer.
Want to support DevOps Daily and reach thousands of developers?
Become a Sponsor