public inbox for [email protected]  

help / color / mirror / Atom feed

From: Samed YILDIRIM <[email protected]>
To: Kirmo Uusitalo <[email protected]>
Cc: [email protected]
Subject: Re: Postgresql long transaction support
Date: Wed, 14 Dec 2022 14:02:02 +0200
Message-ID: <CAAo1mb=fgrfDzP-+5-0i7tDQiWUphZc-Pw5eGNzp7mQ+spOsgA@mail.gmail.com> (raw)
In-Reply-To: <CAH2dGSOY+8amTEE12_WkYkPs17=Y0Cy1dbQ+bsvhmBB6cFSRmw@mail.gmail.com>
References: <CAH2dGSOY+8amTEE12_WkYkPs17=Y0Cy1dbQ+bsvhmBB6cFSRmw@mail.gmail.com>

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 <[email protected]>
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/g...)
> 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?
>

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]
  Subject: Re: Postgresql long transaction support
  In-Reply-To: <CAAo1mb=fgrfDzP-+5-0i7tDQiWUphZc-Pw5eGNzp7mQ+spOsgA@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