Allow a custom sys_period to be specified during INSERT

[MHova]

First of all, thanks for creating this great project!

The versioning() function currently intercepts UPDATE and INSERT operations to overwrite the sys_period column in the main table with [now, NULL). This makes perfect sense for UPDATEs as we want the function to manage sys_period for us. However doing this on INSERT created some problems for my use case:

1. I am trying to migrate existing data from another database into a PSQL-with-temporal-tables database. I would like to retain the original creation dates on the existing data and thus would like to initialize the sys_period to [original_creation_date, NULL) for all the rows. But of course, my efforts are thwarted by the logic in versioning().
2. As a sidenote, this logic also makes moot any defaults for the sys_period column in the main table schema.

I would like to update versioning() to only manage sys_period for UPDATE. For INSERT, the client can either specify a custom value or use the default value, which will likely be [now, NULL) anyway.

I have a PR ready for submission if you agree with this change.

[paolochiodi]

Hi, thanks for getting in touch and sorry for the late reply.

I have three thoughts on this issue:

1. A quick fix for your use case would be to attached the trigger after you ported the original data. The only thing the trigger versioning function does on inserts is to force the sys_period
2. Do you know what is the behaviour of the original c version in this situation? I'm inclined to respect its behaviour
3. I'd prefer for versioning() to make sure we have a valid sys_period. If we want to include this functionality I'd have versioning() modified so that if sys_period is present in the original insert it will check its validity (start should be in the past and end not defined), if not preset versioning() will create it. This means that we can't support default values for sys_period, but I don't see a real use case for that (please let me know if you have one)

[MHova]

1. That wouldn't work in my case because the original database is live and being continually updated by a live service. My plan is to setup the new database with the triggers, hook up the service to write to both databases, then run my migration to backfill all the old data. This will allow me to migrate the data without any gaps and without downtime.
2. I don't know what the behavior of the original extension is. Honestly this is my first time doing any kind of in-depth work with an SQL database.
3. Your suggestion makes sense 👍. Let me see if I can modify versioning() to do this. I don't see a sane use case for supporting default values either.

[nyov]

I just found this and seem to have a similar requirement. I detailed it in an issue at arkhipov/temporal_tables#41
I have done as @paolochiodi said and inserted my initial data with the trigger disabled, but I also need to set sys_period on UPDATEs to backfill my data (and the history table, through the trigger) so I can push multiple in-time snapshots without manually stitching a history table.

As to how the c version handles this currently: it ignores any data passed to the sys_period column in an INSERT or UPDATE.

(Thanks @MHova for the PR code, I believe I can understand this enough to use as a starting point for a simple hackjob - unlike the c code. 👍 )

[paolochiodi]

I'm going to close this, as I'm not convinced about this feature. The use case described can be achieved through populating the tables before adding the versioning trigger (or by temporarily disabling it).

On the other side, I'm afraid giving the user this option may result in unexpected errors and inconsistencies in the history table. I'd prefer to keep the sys_period value under the sole control of the versioning function.

(plus, this is super old)

I'm open to revisit this decision if a new valid use case arise and/or the original extension also adds this feature.