Set idle_in_transaction_session_timeout on TACC's Postgres

[mosoriob]

Problem

A session was idle in transaction for 100 days on TACC's Hasura database. It held locks from an abandoned COPY public.dataslice.

Found on 2026-08-10 while applying the migration in #116. The hasura migrate apply stalled for five minutes. ALTER TABLE ... DROP CONSTRAINT needs an ACCESS EXCLUSIVE lock. It waited behind that session. App traffic then queued behind the waiting lock request.

pg_terminate_backend on the session cleared it. The migration committed at once.

Why it matters

Two costs, and the second is the larger one:

1. Any DDL can stall behind such a session. A waiting ACCESS EXCLUSIVE request also blocks every later reader, so the app stalls with it.
2. An open transaction pins the xmin horizon. Vacuum could not clean dead rows for 100 days. Expect table and index bloat on the busy tables.

What to do

1. Set idle_in_transaction_session_timeout on the database. Ten minutes is a common value.
2. Find what left the transaction open. The query was a COPY public.dataslice, so look at the ETL pipeline and any restore script.
3. Check bloat on the large tables. Consider VACUUM (ANALYZE) now that the horizon has moved.

Evidence

pid     state                 age                     query
362211  idle in transaction   100 days 09:20:29.388   COPY public.dataslice (id, name, dataset_id, start_date, end...

The runbook records the incident: docs/runbook-116-cascade-migration.md (#118).