4 ms·
Better idea: just install the Postgres extension that enables ISO SQL Temporal Tables, just like what MSSQL and MariaDB have had for 8 years now: https://pgxn.o
by DaiPlusPlus 3y ago
Better idea: just install the Postgres extension that enables ISO SQL Temporal Tables, just like what MSSQL and MariaDB have had for 8 years now: https://pgxn.org/dist/temporal_tables/ https://pgxn.org/dist/temporal_tables/
- j45 3y agoGreat tip, thanks
- h1fra 3y agoauthor here: Yes I agree, and I mention it at the end. However it's not possible to install the extension everywhere, for example it's not available in GCP Cloud SQL unfortunately
- DaiPlusPlus 3y agoI feel your pain, except in my case it's the crippling inability to use SQLCLR in Azure SQL, so we can't even do things like use a Regex to clean-up data in-situ. Yeargh.
- l-vincent-l 3y agoIt was reimplemented in pure SQL here https://github.com/nearform/temporal_tables https://github.com/nearform/temporal_tables for this purpose
- btown 3y agoDoes this robustly support altering tables? What happens if a column is deleted - does it delete historical data, or mark the history column as nullable? As the OP article notes/hints, there are all sorts of performance and regulatory considerations around this. Separately though, it's great to see that there's still people trying to solve this problem at the extension level for Postgres. I was sad to discover that Postgres for a very long time had an official extension for time travel - https://www.postgresql.org/docs/11/contrib-spi.html#id-1.11.7.45.6 https://www.postgresql.org/docs/11/contrib-spi.html#id-1.11.... - only to realize that it had been removed in https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=2d10defa7771931295ee804cf096fb9388472a7b https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit... - with a note that "it's easy to implement this separately" but no real hints that anyone was taking up the mantle. As a simpler alternative, if you're only modifying data via Django and can tolerate some unreliability, https://django-simple-history.readthedocs.io/en/latest/ https://django-simple-history.readthedocs.io/en/latest/ can get you much of the way there without a need for extensions. We've built on top of it with some domain-specific modifications to visualize and filter diffs, giving us at least a best-efforts audit-esque log system.
- sjmiller609 3y agoI wrote a guide and simple experimental setup for this extension: https://tembo.io/blog/table-version-history https://tembo.io/blog/table-version-history
- agildehaus 3y agoDoes MySQL have temporal? I thought only MariaDB implemented this. https://mariadb.com/kb/en/temporal-tables/ https://mariadb.com/kb/en/temporal-tables/ A quick Google turns up nothing for MySQL temporal.
- DaiPlusPlus 3y agoMy mistake, sorry; I meant MariaDB. I've edited my post.
- deleted 3y ago[deleted]