Re: Capture a changelog for the current transaction

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