// advent-of-postgres/day-9
The Blast Radius
If the DNS service goes down, what else breaks? Walk the dependency graph with a recursive query, and mind the loop.
The DNS servers for chimney lookups are getting a risky upgrade tonight. Before Alabaster signs off, she wants to know how many services could go down with it.
Every call between services is in dependencies:
| Column | Type | Notes |
|---|---|---|
service |
text |
This service |
depends_on |
text |
A service it calls |
If a service goes down, every service that calls it goes down too, and so on up the chain. Somewhere in the table there is an old loop that nobody cleaned up.
Your task
If chimney-dns goes down, how many other services go down with it, directly or through other services?
Submit the number.
Stuck? Show a hint
A recursive CTE with UNION, not UNION ALL, stops when it finds no new rows. That is what keeps the loop from running forever.
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