Re: Postgresql long transaction support

On Wed, Dec 14, 2022 at 4:46 AM Kirmo Uusitalo <kirmo(dot)uusitalo(at)gmail(dot)com>
wrote:

> Hi Samed,
>
> thanks. The bitemporal tables are somewhat similar except that there is
> not a date range to distinguish between versions of objects. One could use
> this long transaction feature for comparing the results of different plans
> of implementing something - and these could be affecting many objects from
> the parent version (or it's parent and so forth).
>
> Similarly as with bitemporal tables the FK constraints are problematic. If
> an object is to be deleted in parent version, any created child object of
> this in child version cannot be posted to parent version later. That's why
> I believe it is best first merge the changes done in parent version to
> current version before you are allowed to post.
>
> To solve this in application layer for an existing application is quite
> complex and this is why I am looking for a more generic solution within the
> database.
>
>>
>>>
This seems pretty complex to address at any layer! But it would be a useful
feature for some situations, for sure. It seems like the approach taken by
Ruby/Rails and similar frameworks for "db migrations" might have some
useful design patterns for you. Each transaction has a link to its future
and past transaction. If the system wants to traverse from "migration 6 to
migration 3" and the DB is currently at migration 6 (stored a version
number in the db itself), it runs the exit function to downgrade to
migration 5, then runs the logic for migration 5, then runs the downgrade
to 4, etc.

It can be slow, as implemented, but it allows you to attach logic to every
step forward and backward in time that ensures you can handle even
structure changes in the tables, etc (as well as changing lookup values in
tables, etc).

Might be worth considering as a design model?
Steve