TIL that CREATE TEMPORARY TABLE in Postgres takes an ON COMMIT clause:

CREATE TEMP TABLE staging (id bigint, payload jsonb) ON COMMIT DROP;

Three settings. PRESERVE ROWS is the default and keeps the table around for the whole session. DELETE ROWS truncates it at every commit. DROP bins the table itself at the end of the transaction that created it, which is almost always what I actually wanted — a scratch table for one bulk load or one gnarly multi-step query, gone the moment I’m done with it. No cleanup step, no DROP TABLE IF EXISTS at the top guarding against whatever the last run left lying around.

Postgres deviates from the standard here, incidentally: SQL says the default should be DELETE ROWS, and Postgres went with PRESERVE ROWS.

The thing that made it click was PgBouncer’s feature map, which lists ON COMMIT DROP temp tables as working under transaction pooling and PRESERVE/DELETE ROWS as “Never”. Of course it does — in transaction pooling the server connection goes back in the pool after each commit, so anything session-scoped either leaks into someone else’s transaction or vanishes. Scope the table to the transaction and there’s nothing left to leak.