Adding history items

[suricactus]

Hello. I found your extension and at first glance it seemed to be exactly what I need.

But I have a trouble when I need to add truly historical rows (that have been active decades ago). How I can set sys_period to be timestamp in the past?

Here is the proble:

nsi=# INSERT INTO subscriptions (id, sys_period) VALUES (100, tstzrange('1900-01-01', 'infinity'));                                                                                                                                           
INSERT 0 1
nsi=# select * from subscriptions where id = 100;
 id  |             sys_period             
-----+------------------------------------
 100 | ["2016-04-11 15:27:10.533796+03",)
nsi=# update subscriptions set sys_period = tstzrange('1940-01-01', 'infinity') where id = 100;
UPDATE 1
nsi=# select * from subscriptions where id = 100;
 id  |             sys_period             
-----+------------------------------------
 100 | ["2016-04-11 15:28:33.569874+03",)
(1 row)

This is my schema:

CREATE TABLE subscriptions                                                                                        
(                                          
  id SERIAL PRIMARY KEY
);
ALTER TABLE subscriptions  ADD COLUMN sys_period tstzrange NOT NULL;
CREATE TABLE subscriptions_history (LIKE subscriptions); 
CREATE TRIGGER versioning_trigger                                                                                                                                                                                
BEFORE INSERT OR UPDATE OR DELETE ON subscriptions
FOR EACH ROW EXECUTE PROCEDURE versioning(
  'sys_period', 'subscriptions_history', true
);

[arkhipov]

Hello @suricactus,

What you are trying to achieve is called application-time period tables and is not currently supported by the extension. In fact, you do not even need an extension to implement them since the application-time period is controlled by the application, not by the system (that is why the other type of temporal tables is called system-time period tables).

I have been thinking about implementing application-time period tables, but I am not sure if I will find enough time to tackle it. The most painful part is versioned FKs and PKs.

[suricactus]

Thank you! Sorry I misunderstood the purpose of your extension. I just thought I might be reinventing the wheel. Hope you have enough time to implement another plugin for application-time period tables one day. Best wishes!

[rbygrave]

Sorry, I might be totally misunderstanding but I wondered ... it seems in this case these old historical rows have no history yet, then you might really be simply wanting the sys_period start timestamp to match some prior timestamp?

It seems to me that if you don't have the trigger active you can update that start timestamp. So you could only create the trigger after the start timestamps are what you want.

Sorry, I just thought I'd add that in case you really are looking for that option.