|
|
|
|
|
|
by mamcx
4 days ago
|
|
|
Ok this looks like interesting, and now rebuilding my ERP it sounds the kind stuff I need to look at. Question: Could this be useful to store history of changes? One pesky trouble is that after you close an invoice all the data there becomes immutable but you need to continue change the base tables. So, I have a convoluted way of double write rows and keep the "current" as main. All for "just" the case that an invoice need to see the data as was when finalized. Is this feature for this case too? |
|
|
That's exactly why I started learning about temporal tables. I had a customer whose app used questionnaires to measure the effectiveness of government social services, and they let people change the questions (and multiple choice options) even after there were answers! Obviously the data was trashed.
I've seen this bug over & over again, where you have a foreign key relationship, and the referenced table changes, but the referencing table needs the old data. Another example is a sale that doesn't capture the product's current price.
Temporal tables mean that you can run your 2022 financial reports and get the same answer you got before.
My own time-tracking and invoicing app has to solve this problem. I also "copy everything" when something changes. I just gave a talk about migrating it to temporal tables: https://illuminatedcomputing.com/pages/pgdata2026-migrating-...
You'll have to decide if your use-case is more "system time" (history of the database) or "application time" (history of the entities). The features here are for application time. I want to make sure Postgres gets system time too, but it's not in v19.
If you want system time today, there are several widely-adopted Postgres extensions that can do that already. I cover them here: https://illuminatedcomputing.com/posts/2017/12/temporal-data...
And actually, application time is managed by you (while system time is managed automatically by the database), so really you can use it for whatever you want.