Allow a custom `sys_period` to be specified during `INSERT`

[nearform]

Allow a custom `sys_period` to be specified during `INSERT`

[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]

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.

[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.