MIME-Version: 1.0
References: 
 <CAH2dGSOY+8amTEE12_WkYkPs17=Y0Cy1dbQ+bsvhmBB6cFSRmw@mail.gmail.com>
 <CAAo1mb=fgrfDzP-+5-0i7tDQiWUphZc-Pw5eGNzp7mQ+spOsgA@mail.gmail.com>
 <CAH2dGSPW4jVzB1fLwNpZiaL3u1BBsZYACTRzerMy-RnpprMk-A@mail.gmail.com>
In-Reply-To: 
 <CAH2dGSPW4jVzB1fLwNpZiaL3u1BBsZYACTRzerMy-RnpprMk-A@mail.gmail.com>
From: Steve Midgley <science@misuse.org>
Date: Wed, 14 Dec 2022 11:59:44 -0800
Message-ID: 
 <CAJexoSJ2M=S5p21jzzxyy97yhNFLOmqjZaR3G+6Dp5iZpA9ang@mail.gmail.com>
Subject: Re: Postgresql long transaction support
To: Kirmo Uusitalo <kirmo.uusitalo@gmail.com>
Cc: Samed YILDIRIM <samed@reddoc.net>, pgsql-sql@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000003f046205efcf2e4b"
Archived-At: 
 <https://www.postgresql.org/message-id/CAJexoSJ2M%3DS5p21jzzxyy97yhNFLOmqjZaR3G%2B6Dp5iZpA9ang%40mail.gmail.com>
Precedence: bulk

--0000000000003f046205efcf2e4b
Content-Type: text/plain; charset="UTF-8"

On Wed, Dec 14, 2022 at 4:46 AM Kirmo Uusitalo <kirmo.uusitalo@gmail.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

--0000000000003f046205efcf2e4b
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote">=
<div dir=3D"ltr" class=3D"gmail_attr">On Wed, Dec 14, 2022 at 4:46 AM Kirmo=
 Uusitalo &lt;<a href=3D"mailto:kirmo.uusitalo@gmail.com">kirmo.uusitalo@gm=
ail.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"=
margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-lef=
t:1ex"><div dir=3D"ltr"><div>Hi Samed,</div><div><br></div><div>thanks. The=
 bitemporal tables are somewhat similar except that there is not a date ran=
ge to distinguish between versions of objects. One could use this long tran=
saction feature for comparing the results of different plans of implementin=
g something - and these could be affecting many objects from the parent ver=
sion (or it&#39;s parent and so forth).</div><div><br></div><div>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 chi=
ld version cannot be posted to parent version later. That&#39;s why I belie=
ve it is best first merge the changes done in parent version to current ver=
sion before you are allowed to post. <br></div><div><br></div><div>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. =
<br></div></div><div class=3D"gmail_quote"><blockquote class=3D"gmail_quote=
" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);=
padding-left:1ex"><div class=3D"gmail_quote"><blockquote class=3D"gmail_quo=
te" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204=
);padding-left:1ex"><div dir=3D"ltr"><div><br></div></div></blockquote></di=
v></blockquote></div></blockquote><div><br></div><div>This seems pretty com=
plex to address at any layer! But it would be a useful feature for some sit=
uations, for sure. It seems like the approach taken by Ruby/Rails and simil=
ar frameworks for &quot;db migrations&quot; might have some useful design p=
atterns for you. Each transaction has a link to its future and past transac=
tion. If the system wants to traverse from &quot;migration 6 to migration 3=
&quot; and the DB is currently at migration 6 (stored a version number in t=
he 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.</div><di=
v><br></div><div>It can be slow, as implemented, but it allows you to attac=
h logic to every step forward and backward in time that ensures you can han=
dle even structure changes in the tables, etc (as well as changing lookup v=
alues in tables, etc).</div><div><br></div><div>Might be worth considering =
as a design model?</div><div>Steve</div></div></div>

--0000000000003f046205efcf2e4b--