Capture a changelog for the current transaction
Capture a changelog for the current transaction
От:
Stuart Campbell <stuart.campbell@ridewithvia.com>
Дата:
Hello,
Our data model has some constraints that are not enforceable at the database level. So, we resort to running some checks in application code at the end of each transaction.
By the time the checks run, the app doesn't know exactly which data (i.e. rows) were modified, so it has to check more data than is strictly necessary for a given operation. So I'm wondering about capturing the context of what changed during the DB transaction, and using that to pare down the amount of checking needed.
My question is: is there a way to capture a log of changes (say, table name, primary key value, operation [insert/update/delete] + column[s] updated) only in the context of the current transaction?
One approach I've seen suggested is to create a log table (or tables), and use triggers to populate the log. But it seems to me like those log tables would require frequent pruning to avoid performance issues.
Would using temporary tables be a feasible solution? That seems to be functionally what I want, but maybe it's hard to write triggers that write to temp tables.
Maybe a persistent log table is OK, and each transaction is responsible for deleting its own rows from it? Sounds like a lot of churn though...
Any guidance is appreciated.
Thanks,
Stuart
This communication and any attachments may contain confidential information and are intended to be viewed only by the intended recipients. If you have received this message in error, please notify the sender immediately by replying to the original message and then delete all copies of the email from your systems.
Re: Capture a changelog for the current transaction
От:
Ron Johnson <ronljohnsonjr@gmail.com>
Дата:
On Tue, Oct 6, 2026 at 6:39 AM Stuart Campbell <stuart.campbell@ridewithvia.com> wrote:
[snip]
Would using temporary tables be a feasible solution? That seems to be functionally what I want, but maybe it's hard to write triggers that write to temp tables.Maybe a persistent log table is OK, and each transaction is responsible for deleting its own rows from it? Sounds like a lot of churn though...
A recent email in https://postgrespro.com/list/thread-id/2812560#77826d7580a9488ca28eb4bcfe94aa37@stmuk.bayern.de mentioned this extension to create "persistent" temporary tables (where each connection doesn't need to CREATE them every time.
Maybe that would be useful. I've never used them, though, so don't know any gotchas.
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
Re: Capture a changelog for the current transaction
От:
Laurenz Albe <laurenz.albe@cybertec.at>
Дата:
On Tue, 2026-10-06 at 21:38 +1100, Stuart Campbell wrote: > Our data model has some constraints that are not enforceable at the database level. > So, we resort to running some checks in application code at the end of each transaction. > > By the time the checks run, the app doesn't know exactly which data (i.e. rows) were > modified, so it has to check more data than is strictly necessary for a given operation. > So I'm wondering about capturing the context of what changed during the DB transaction, > and using that to pare down the amount of checking needed. > > My question is: is there a way to capture a log of changes (say, table name, primary > key value, operation [insert/update/delete] + column[s] updated) only in the context > of the current transaction? > > One approach I've seen suggested is to create a log table (or tables), and use triggers > to populate the log. But it seems to me like those log tables would require frequent > pruning to avoid performance issues. > > Would using temporary tables be a feasible solution? That seems to be functionally what > I want, but maybe it's hard to write triggers that write to temp tables. > > Maybe a persistent log table is OK, and each transaction is responsible for deleting > its own rows from it? Sounds like a lot of churn though... > > Any guidance is appreciated. Yes, you can use triggers for that. If you use a temporary table, make sure that you don't drop and re-create it for every operation. That could easily lead to undesirable bloat in system tables like pg_attribute. Deleting the data after use is a better alternative. You could use a temporary table created with ON COMMIT DELETE ROWS, so that PostgreSQL empties it automatically at the end of each transaction. That would save you a certain amount of work. If the constraint can only be verified outside the database, because it needs some information not available in the database, you may have no choice but to do that in the application. If all the information is available inside the database, there may be better alternatives. The SQL standard has the concept of an assertion, which may be a good fit, but is not implemented in PostgreSQL. Perhaps you could use a deferred constraint trigger that uses the data you collected in the temporary table to validate data integrity. The advantage of doing these things in the database is that data manipulations outside of your application also cannot violate your constraint. > This communication and any attachments may contain confidential information and are > intended to be viewed only by the intended recipients. ... which, in this case, is the whole of the internet. Yours, Laurenz Albe
Re: Capture a changelog for the current transaction
От:
Adrian Klaver <adrian.klaver@aklaver.com>
Дата:
On 10/6/26 3:38 AM, Stuart Campbell wrote: > Hello, > > Our data model has some constraints that are not enforceable at the > database level. So, we resort to running some checks in application code > at the end of each transaction. Does this mean the information needed to do the checks is not in the database, but in another data store? > Any guidance is appreciated. > > Thanks, > Stuart > > This communication and any attachments may contain confidential > information and are intended to be viewed only by the intended > recipients. If you have received this message in error, please notify > the sender immediately by replying to the original message and then > delete all copies of the email from your systems. > >
Re: Capture a changelog for the current transaction
От:
Stuart Campbell <stuart.campbell@ridewithvia.com>
Дата:
On Wed, Oct 7, 2026 at 8:00 AM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 10/6/26 3:38 AM, Stuart Campbell wrote:
> Hello,
>
> Our data model has some constraints that are not enforceable at the
> database level. So, we resort to running some checks in application code
> at the end of each transaction.
Does this mean the information needed to do the checks is not in the
database, but in another data store?
Not exactly. Some of our tables hold binary data that can't be inspected from within the databse. Our app has to deserialize the binary data and run checks against it.
This communication and any attachments may contain confidential information and are intended to be viewed only by the intended recipients. If you have received this message in error, please notify the sender immediately by replying to the original message and then delete all copies of the email from your systems.
Re: Capture a changelog for the current transaction
От:
Stuart Campbell <stuart.campbell@ridewithvia.com>
Дата:
On Wed, Oct 7, 2026 at 12:09 AM Ron Johnson <ronljohnsonjr@gmail.com> wrote:
On Tue, Oct 6, 2026 at 6:39 AM Stuart Campbell <stuart.campbell@ridewithvia.com> wrote:[snip]Would using temporary tables be a feasible solution? That seems to be functionally what I want, but maybe it's hard to write triggers that write to temp tables.Maybe a persistent log table is OK, and each transaction is responsible for deleting its own rows from it? Sounds like a lot of churn though...A recent email in https://postgrespro.com/list/thread-id/2812560#77826d7580a9488ca28eb4bcfe94aa37@stmuk.bayern.de mentioned this extension to create "persistent" temporary tables (where each connection doesn't need to CREATE them every time.Maybe that would be useful. I've never used them, though, so don't know any gotchas.
Thanks for the suggestion. That seems semantically close to what I'm looking for. In my case we're working with Amazon RDS, so I don't think ad hoc extensions are permitted.
This communication and any attachments may contain confidential information and are intended to be viewed only by the intended recipients. If you have received this message in error, please notify the sender immediately by replying to the original message and then delete all copies of the email from your systems.
Re: Capture a changelog for the current transaction
От:
Stuart Campbell <stuart.campbell@ridewithvia.com>
Дата:
Thanks, that's helpful guidance.
I wish the system were modeled in a way that let us do all constraint validation within the database, but unfortunately it's not so.
This communication and any attachments may contain confidential information and are intended to be viewed only by the intended recipients. If you have received this message in error, please notify the sender immediately by replying to the original message and then delete all copies of the email from your systems.
Re: Capture a changelog for the current transaction
От:
Ron Johnson <ronljohnsonjr@gmail.com>
Дата:
On Tue, Oct 6, 2026 at 6:39 AM Stuart Campbell <stuart.campbell@ridewithvia.com> wrote:
[snip]
Maybe a persistent log table is OK, and each transaction is responsible for deleting its own rows from it? Sounds like a lot of churn though...
Partitioned persistent log tables wouldn't reduce the total total amount of churn, but they'd distribute it into smaller chunks which autovacuum threads (or cron jobs) could attack in parallel.
That would, of course, increase schema complexity and there would need to be reasonable "static" partition columns in the parent tables.
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
Re: Capture a changelog for the current transaction
От:
shihao zhong <zhong950419@gmail.com>
Дата:
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
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
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,
Thanks,
Shihao