MIME-Version: 1.0
References: 
 <CAH2dGSOY+8amTEE12_WkYkPs17=Y0Cy1dbQ+bsvhmBB6cFSRmw@mail.gmail.com>
In-Reply-To: 
 <CAH2dGSOY+8amTEE12_WkYkPs17=Y0Cy1dbQ+bsvhmBB6cFSRmw@mail.gmail.com>
From: Samed YILDIRIM <samed@reddoc.net>
Date: Wed, 14 Dec 2022 14:02:02 +0200
Message-ID: 
 <CAAo1mb=fgrfDzP-+5-0i7tDQiWUphZc-Pw5eGNzp7mQ+spOsgA@mail.gmail.com>
Subject: Re: Postgresql long transaction support
To: Kirmo Uusitalo <kirmo.uusitalo@gmail.com>
Cc: pgsql-sql@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000cfd75505efc881fd"
Archived-At: 
 <https://www.postgresql.org/message-id/CAAo1mb%3DfgrfDzP-%2B5-0i7tDQiWUphZc-Pw5eGNzp7mQ%2BspOsgA%40mail.gmail.com>
Precedence: bulk

--000000000000cfd75505efc881fd
Content-Type: text/plain; charset="UTF-8"

Hi Kirmo,

This is a pretty interesting feature. I'm not sure if I've seen something
like that before. I recommend you to check the pg_bitemporal[1] project. It
may be useful for you.

[1]: https://github.com/hettie-d/pg_bitemporal

Best regards.
Samed YILDIRIM


On Wed, 14 Dec 2022 at 13:55, Kirmo Uusitalo <kirmo.uusitalo@gmail.com>
wrote:

> Hi,
>
> I'm looking for a solution for version managed (or long transaction) data
> in PostgreSQL (and Oracle).
>
> This means database objects having different properties in different
> versions of the data set. Versions could be organized in tree-like
> hierarchy (each versios can have subversions). Objects can even be deleted
> in some of the versions of the data.
>
> I am looking for something which would require minimal change in the
> application logic, like
>
>     create table my_obj(id serial primary key,prop_1 text);
>
>     insert into my_obj (prop_1) values ('object 1');
>
>     insert into my_obj (prop_1) values ('object 2 (to be deleted)');
>
>
> select * from my_obj;
>
> 1   object 1
> 2   object 2 (to be deleted)
>
> create version 'xx'; //this functionality i'm looking for
> switch to version 'xx'; //this functionality i'm looking for
>
> select * from my_obj; //same data as in top version as we haven't changed anything yet
>
> 1   object 1
> 2   object 2 (to be deleted)
>
> update my_obj set prop_1='updated in version xx' where id = 1;delete from my_obj where id=2;
>
> select * from my_obj;
>
> 1   'updated in version xx'
>
> switch to version 'top'; //let's go back to top version
>
> select * from my_obj; //same data as before as we haven't changed posted
> our changes made in 'xx' yet to top version
>
> 1   object 1
> 2   object 2 (to be deleted)
>
> insert into my_obj (prop_1) values ('object 3 (inserted after creation of xx)');
>
> switch to version 'xx'; //this functionality i'm looking for
>
> merge; //brings changes made in parent 'top' version
>
> 1   updated in version xx
> 3   object 3 (inserted after creation of xx)
>
> post; //sends all changes upwards
>
> This functionality exists in GE Smallworld spatial database (see
> https://www.ge.com/content/dam/gepower-new/global/en_US/downloads/gas-new-site/resources/reference/ger-4231-smallworld-4-managing-change-spatial-data.pdf)
> and the use case is quite common (plan some changes in data which will
> happen in real life in far future, much further away than when current
> database session ends). Also the conflict management should exist (same
> object being changed in multiple versions).
>
> Does Postgresql have anything like this?
>

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

<div dir=3D"ltr"><div>Hi Kirmo,</div><div><br></div><div>This is a pretty i=
nteresting feature. I&#39;m not sure if I&#39;ve seen something like that b=
efore. I recommend you to check the pg_bitemporal[1] project. It may be use=
ful for you. <br></div><div><br></div><div>[1]: <a href=3D"https://github.c=
om/hettie-d/pg_bitemporal">https://github.com/hettie-d/pg_bitemporal</a></d=
iv><div><br></div><div><div><div dir=3D"ltr" class=3D"gmail_signature" data=
-smartmail=3D"gmail_signature"><div dir=3D"ltr"><div>Best regards.</div><di=
v>Samed YILDIRIM<br></div></div></div></div><br></div></div><br><div class=
=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Wed, 14 Dec 2022 =
at 13:55, Kirmo Uusitalo &lt;<a href=3D"mailto:kirmo.uusitalo@gmail.com">ki=
rmo.uusitalo@gmail.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_q=
uote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,2=
04);padding-left:1ex"><div dir=3D"ltr">
<div>
               =20
<p>Hi, <br></p><p>I&#39;m looking for a solution for version managed (or lo=
ng transaction) data in PostgreSQL (and Oracle).</p>
<p>This means database objects having different properties in different=20
versions of the data set. Versions could be organized in tree-like hierarch=
y (each versios can have subversions). Objects can even be deleted in some =
of the=20
versions of the data.</p>
<p>I am looking for something which would require minimal change in the app=
lication logic,
like</p>
<pre><code>    create table my_obj(id serial primary key,prop_1 text);
   =20
    insert into my_obj (prop_1) values (&#39;object 1&#39;);
   =20
    insert into my_obj (prop_1) values (&#39;object 2 (to be deleted)&#39;)=
;


select * from my_obj;

1   object 1
2   object 2 (to be deleted)
</code></pre>
<pre><code><span>create</span> version <span>&#39;xx&#39;</span>; <span>/</=
span><span>/</span>this functionality i<span>&#39;m looking for
switch to version &#39;</span>xx<span>&#39;; //this functionality i&#39;</s=
pan>m looking <span>for</span>
</code></pre>
<pre><code>select * from my_obj; //same data as in top version as we haven&=
#39;t changed anything yet

1   object 1
2   object 2 (to be deleted)
</code></pre>
<pre><code><span>update</span> my_obj <span>set</span> prop_1<span>=3D</spa=
n><span>&#39;updated in version xx&#39;</span> <span>where</span> id <span>=
=3D</span> <span>1</span>;
<span>delete</span> <span>from</span> my_obj <span>where</span> id<span>=3D=
</span><span>2</span>;
</code></pre>
<pre><code>select * from my_obj;

1   &#39;updated in version xx&#39;
</code></pre>
<p><span style=3D"font-family:monospace">switch to version &#39;top&#39;; /=
/let&#39;s go back to top version </span><br></p><p><span style=3D"font-fam=
ily:monospace">select * from my_obj; //same data as before as we haven&#39;=
t changed posted our changes made in &#39;xx&#39; yet to top version</span>=
</p>
<pre><code>1   object 1
2   object 2 (to be deleted)
</code></pre>
<pre><code><span>insert</span> <span>into</span> my_obj (prop_1) <span>valu=
es</span> (<span>&#39;object 3 (inserted after creation of xx)&#39;</span>)=
;
</code></pre>
<p><span style=3D"font-family:monospace">switch to version &#39;xx&#39;; //=
this functionality i&#39;m looking for <br></span></p><p><span style=3D"fon=
t-family:monospace">merge; //brings changes made in parent &#39;top&#39; ve=
rsion</span></p>
<pre><code>1   updated in version xx
3   object 3 (inserted after creation of xx)
</code></pre>
<p>post; //sends all changes upwards</p>
<p>This functionality exists in GE Smallworld spatial database (see <a href=
=3D"https://www.ge.com/content/dam/gepower-new/global/en_US/downloads/gas-n=
ew-site/resources/reference/ger-4231-smallworld-4-managing-change-spatial-d=
ata.pdf" rel=3D"nofollow noreferrer" target=3D"_blank">https://www.ge.com/c=
ontent/dam/gepower-new/global/en_US/downloads/gas-new-site/resources/refere=
nce/ger-4231-smallworld-4-managing-change-spatial-data.pdf</a>)
 and the use case is quite common (plan some changes in data which will=20
happen in real life in far future, much further away than when current=20
database session ends). Also the conflict management should exist (same=20
object being changed in multiple versions).</p>
<p>Does Postgresql have anything like this?</p>
    </div>

</div>
</blockquote></div>

--000000000000cfd75505efc881fd--