search

arkhipov / temporal_tables

Temporal Tables PostgreSQL Extension
BSD 2-Clause "Simplified" License
937 stars 47 forks source link

Question about README section Update conflicts and time adjustment #9

Closed omikronsc closed 9 years ago

omikronsc commented 9 years ago

Hi!

I can't understand this part:

At time T4 the trigger must set the start of sys_period column of the row to T1 and insert the following row into the history table:

name department salary sys_period
Lenina Crowne Hatchery and Conditioning Centre 7000 [T2, T1)

As I see it, at time T4, employee record about Lenina should have sys_period set to [T4,) and employee_history record about Lenina should appear with sys_period [T2, T4).

Where this T1 in Lenina's record come from?

Why there is said that "set the start of sys_period column of the row to T1" but you are setting end not start?

What am I missing?

Regards Sebastian

arkhipov commented 9 years ago

Hello, @omikronsc

Since transactions are atomic, all changes that were made by a transaction within an interval of activity are considered to happen at the same time. The extension uses the transaction start time as the timestamp for these changes.

As for the second question, the T1 time refers to the history table, not to the main one. There is already a record with sys_period = [T2, ), so the extension must close the previous period (set its sys_period to [T2, T1)) and add a new one with sys_period = [T1, ).

omikronsc commented 9 years ago

I didn't know that transaction has timestamp set to beginning of transaction. Now everything is clear. Thanks @arkhipov