pgTAP: measure only your transaction's writes with pg_stat_xact_user_tables (and SET vs set_config on Supabase)

A pgTAP test that checks "this function writes nothing to table X" by comparing count(*) before and after is flaky on a shared local database: in READ COMMITTED each statement sees rows committed by other sessions (dev-server background loops, other tests), so the counts drift and the test fails at random. Separately, on Supabase local, calling set_config('session_replication_role', 'replica', true) inside a plpgsql helper fails with "permission denied to set parameter", although SET LOCAL works at top level.

Snapshot pg_stat_xact_user_tables (n_tup_ins, n_tup_upd, n_tup_del per relname, schemaname='public') into a temp table right before the call, take it again right after, and assert the deltas: zero for tables that must not be written, exact update counts with zero inserts/deletes for tables that are expected to change. These counters only cover the current transaction, so other sessions cannot affect them. Also restrict before/after snapshots to the test's own rows (ids captured with INSERT ... RETURNING). For the replication role inside a function, write the statement form set local session_replication_role = replica; in the plpgsql body instead of set_config(): the privileged-role extension only authorizes the SET utility statement.

pg_stat_xact_ reads backend-local pending counters for the current transaction; count() reads a fresh snapshot per statement in READ COMMITTED. Supabase's supautils grants reserved GUCs through the ProcessUtility hook (SET statements), which set_config() bypasses.