public inbox for [email protected]
help / color / mirror / Atom feedFrom: Steve Midgley <[email protected]>
To: Kirmo Uusitalo <[email protected]>
Cc: Samed YILDIRIM <[email protected]>
Cc: [email protected]
Subject: Re: Postgresql long transaction support
Date: Wed, 14 Dec 2022 11:59:44 -0800
Message-ID: <CAJexoSJ2M=S5p21jzzxyy97yhNFLOmqjZaR3G+6Dp5iZpA9ang@mail.gmail.com> (raw)
In-Reply-To: <CAH2dGSPW4jVzB1fLwNpZiaL3u1BBsZYACTRzerMy-RnpprMk-A@mail.gmail.com>
References: <CAH2dGSOY+8amTEE12_WkYkPs17=Y0Cy1dbQ+bsvhmBB6cFSRmw@mail.gmail.com>
<CAAo1mb=fgrfDzP-+5-0i7tDQiWUphZc-Pw5eGNzp7mQ+spOsgA@mail.gmail.com>
<CAH2dGSPW4jVzB1fLwNpZiaL3u1BBsZYACTRzerMy-RnpprMk-A@mail.gmail.com>
On Wed, Dec 14, 2022 at 4:46 AM Kirmo Uusitalo <[email protected]>
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
view thread (5+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: Postgresql long transaction support
In-Reply-To: <CAJexoSJ2M=S5p21jzzxyy97yhNFLOmqjZaR3G+6Dp5iZpA9ang@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox