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
