Open psql session blocks order updates
Your task
You're on call for the order service, which runs on one Postgres primary. At 16:35 an alert fires: 22% of PATCH /orders/:id requests fail with ERROR: canceling statement due to lock timeout, and the label-printing worker that updates order status has stopped making progress. Reads and new orders are fine. Database CPU is 12% and connections are at 80 of 400. Every failing request is for an order assigned to warehouse 3.
In pg_stat_activity you find one session with application_name = psql, user jordan_ops, state = idle in transaction, xact_start = 16:05, and last query:
UPDATE orders SET warehouse_id = 7 WHERE warehouse_id = 3 AND status = 'open';
At 16:04 Jordan from operations posted in the ops channel that warehouse 3 closed early for weather and they were moving its open orders to warehouse 7. Jordan's status says "in a meeting until 17:30". How do you triage and resolve this?
It covers
- Row locks
- Idle in transaction
- Pg stat activity