// advent-of-postgres/day-21
The Deleted Gifts
A cleanup job deleted every wrapped gift three seconds after the data loaded. Go back in time with a Neon branch and count what was lost.
Three days before the big night, a cleanup job ran against the gift table. It was meant to remove test rows. Its filter was wrong, and it deleted every gift that was already wrapped. The deploy log says exactly when it ran.
Neon keeps the history of every change for a while (6 hours on the Free plan), so you can create a branch from any moment in that window. A branch from just before the job ran has all the gifts.
Today's data loads in three steps when you first run a query: the gifts, a pause of a few seconds, then the bad cleanup job. The tables:
| Table | Columns | Notes |
|---|---|---|
gifts |
id, child_id, item, value, wrapped |
value is in candy canes |
deploy_log |
deployed_at, release, note |
When the cleanup job ran, and what it was |
The steps, in this order:
- If your database is not in your Neon account yet, press "Keep it for the month" and claim it. Neon resets the password during the move, so paste the new connection string when the page asks.
- Run the starter query. Today's data loads, and a few seconds later the cleanup job runs.
- Note the
deployed_attime fromdeploy_log. - In the Neon Console, create a branch from a point in time one second before
deployed_at, within the 6-hour history window.neon branches createwith that timestamp as the parent does the same. - Point this page at the branch with "Use another connection string", or query the branch in the Neon SQL Editor.
Your task
On a branch from just before the cleanup job, what was the total value of all wrapped gifts?
Submit the number of candy canes.
Stuck? Show a hint
Create the branch from a point in time one or two seconds before deploy_log.deployed_at. Then use that branch's connection string here, or query it in the Neon SQL Editor.
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