- no data redundancy - in my extension current versions of entries are stored only once in original table (in table_log - entries are inserted to both original and log table)
That's not necessarily a benefit... it makes querying for both history *and* current data a lot more complex. Table inheritance might be an elegant solution to that, but I doubt you could just bolt that on top of what you've created.
Yes, querying for history data is more complex, but i focused on preserving the performance of current queries. That's the reason why I use separate table for old versions.
Table inheritance is very good idea and it will not require so much effort to use it in my solution. Currently, when user queries whole history of entries, table reference in FROM clause is replaced with subselect, which access data in both tables. For example when user executes command:
NONSEQUENCED TRANSACTIONTIME SELECT * FROM person;
The actually executed command is:
SELECT * FROM (SELECT * FROM person UNION ALL SELECT * FROM person_hist) as person
Use of table inheritance can make things simpler and more elegant, but I'm not sure about how it affect performance. Will it cause gain in performance?
The timestamp fields need to have timezone info. If you change the timezone for a connection you will get inconsistent results without it.
_sys_end should either be NULLable or if it's going to have a magic value that magic value should be "Infinity":
Good point. I will use timestamp with timezone and value "Infinity" instead of max timestamp value