Re: temporal support patch

2012/5/30 Jim Nasby <jim(at)nasby(dot)net>

> On 5/18/12 2:06 AM, Miroslav Šimulčík wrote:
>
>> - 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