Re: Capture a changelog for the current transaction

Hi Stuart,

I don't know enough about your application to pick one for you, so
here are four options with the trade-offs.

1. Track the changes in the application.

changes = []
with conn.transaction():
cur.execute("insert into tbl(value) values ('text') "
"returning id")
changes.append(("tbl", cur.fetchone()[0], "I"))
verify(changes)

This is the simplest and works if all writes go through your code.
It misses changes made by triggers, stored procedures, cascades or
other clients. Some ORMs already give you this, for example
after_flush in SQLAlchemy.

2. A temp table with ON COMMIT DELETE ROWS.

At the start of each transaction run:

create temp table if not exists txn_changelog
(table_name text, pk text, op char(1))
on commit delete rows;

Then a statement level trigger fills it:

create function tbl_track() returns trigger
language plpgsql as $$
begin
if to_regclass('pg_temp.txn_changelog') is null then
return null;
end if;
insert into pg_temp.txn_changelog
select 'tbl', id::text, 'I' from new_rows;
return null;
end $$;

create trigger tbl_track after insert on tbl
referencing new table as new_rows
for each statement execute function tbl_track();

Read the table before COMMIT. The table is private to the session
and empties itself. If you use RDS Proxy or a pooler in transaction
mode, temp tables can pin the session, so check that first.

3. A regular or unlogged table keyed by transaction id.

create unlogged table txn_changelog
(txid xid8 default pg_current_xact_id(),
table_name text, pk text, op char(1));

Use the same trigger, without the to_regclass check. Before COMMIT:

select * from txn_changelog
where txid = pg_current_xact_id();
delete from txn_changelog
where txid = pg_current_xact_id();

No pooler issue. You pay for the delete, so watch autovacuum and
replication lag.

4. A transaction local setting.

select set_config('app.changes', '[1,2,3]', true);

The last argument true makes it last only for the current
transaction. Read it before COMMIT with:

select current_setting('app.changes', true);

You have to build the string yourself, for example a jsonb array.
Call it once per statement, not once per row, because every call
rewrites the whole string. Fine for small transactions only. This one
have the lightest overhead.

Thanks,
Shihao