Re: Postgresql long transaction support

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(dot)uusitalo(at)gmail(dot)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?
>