Re: Capture a changelog for the current transaction

[shihao zhong]

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