Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p5R9B-0001HZ-6j for pgsql-sql@arkaria.postgresql.org; Wed, 14 Dec 2022 12:46:05 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1p5R99-0003cZ-MB for pgsql-sql@arkaria.postgresql.org; Wed, 14 Dec 2022 12:46:03 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p5R99-0003cQ-Ae for pgsql-sql@lists.postgresql.org; Wed, 14 Dec 2022 12:46:03 +0000 Received: from mail-qv1-xf34.google.com ([2607:f8b0:4864:20::f34]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1p5R96-0000eS-Ni for pgsql-sql@lists.postgresql.org; Wed, 14 Dec 2022 12:46:02 +0000 Received: by mail-qv1-xf34.google.com with SMTP id h10so12434858qvq.7 for ; Wed, 14 Dec 2022 04:46:00 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=yj9/C3aITViLYV0cjqAUyOSur4yYFffitzphH4DBXtY=; b=VHX6v0IClVQeHzlhOmmLFlaONqMRRsKCce3YlBdBYklI3seKdOVOnbONFlW2vL62sD jYCYWig/C4m9MXiLcO9BcOOj6LlL6rQT1CjxKW6xT4MLBoct7+CHcLl3pdjgH+sky+IF QdOXGhtVhmoihZc3QSKvvJ26ux+nuw62F6/OQ8s3Hi6Guo6sh5upAD2XEV84QtHSoIdK 0YFssZkSRL4YIcQxpLIr0SZcCOLP87ny/Ic0ByRft9jd3dFB7nkVSD6XAlu3EER9rJDK f64NUXFjD9Cve9gXvTOIbTSIQX7dLnPmJ/8L7870l/sjsgAMizqaFtGAgEywaT8fJHHZ kGpg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=yj9/C3aITViLYV0cjqAUyOSur4yYFffitzphH4DBXtY=; b=FuIOEOCWXJe7iUjkvlcsaHe5RRhIifGzW2DGjRonKH3y10m/5A7phFA2bxjK04BKl9 t+tAFBEMWDW5rLjAixaQJtpPVlwW531dola5H2oRW9+rHlqLJAIk22VUNeTkc6Lat4Lt H3j3QKQQ7/k7jv9NbWDeIirH/D59Guh5CZJJ7NuKCy/ZL6PQLg7Z62+Uk9RsM7KNvi70 g+XYDQTuxzC1Zq76cUv9XPoZf5YAjAQo6QkRPvjLaTMK8Crfs+WQCbNTr7hmoPMKK1rT Id3Q0r1dUJjjA+zoal1eCu1/jji5JVCW+hoEXerF3cJfxhEug9qZlmK0cPEX3nqRTm6B cJKw== X-Gm-Message-State: ANoB5pm3lJpFRZ7jAgmwOhW58aLPBoVOHEbX/n+ubg5DFV919rkZqPt6 lopflTZAsx13wToYv9O64448wgOOvFRNb9IKsoNKVRnXBJk= X-Google-Smtp-Source: AA0mqf5Sia6ZJL3C5jI2a2a4UIsGUYXPggHaat9qPmY7/YF0s8Pe7++wz9b1DfmGwTsjQEsVGszW2/gq3pGgle/9vEk= X-Received: by 2002:ad4:4683:0:b0:4c6:9b28:e5eb with SMTP id pl3-20020ad44683000000b004c69b28e5ebmr7639498qvb.86.1671021958767; Wed, 14 Dec 2022 04:45:58 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Kirmo Uusitalo Date: Wed, 14 Dec 2022 14:45:47 +0200 Message-ID: Subject: Re: Postgresql long transaction support To: Samed YILDIRIM Cc: pgsql-sql@lists.postgresql.org Content-Type: multipart/alternative; boundary="00000000000043a81d05efc91e06" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000043a81d05efc91e06 Content-Type: text/plain; charset="UTF-8" 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. On Wed, Dec 14, 2022 at 2:02 PM Samed YILDIRIM wrote: > 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 > 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? >> > --00000000000043a81d05efc91e06 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi Samed,

thanks. The bitemp= oral tables are somewhat similar except that there is not a date range to d= istinguish between versions of objects. One could use this long transaction= feature for comparing the results of different plans of implementing somet= hing - and these could be affecting many objects from the parent version (o= r it's parent and so forth).

Similarly as with= bitemporal tables the FK constraints are problematic. If an object is to b= e deleted in parent version, any created child object of this in child vers= ion cannot be posted to parent version later. That's why I believe it i= s best first merge the changes done in parent version to current version be= fore you are allowed to post.

To solve this i= n application layer for an existing application is quite complex and this i= s why I am looking for a more generic solution within the database.

On Wed, Dec 14, 2022 at 2:02 PM Samed YILDIRIM <samed@reddoc.net> wrote:
Hi Kirmo,
=
This is a pretty interesting feature. I'm not sure if I&= #39;ve seen something like that before. I recommend you to check the pg_bit= emporal[1] project. It may be useful for you.

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

<= div>
Best regards.
Samed YI= LDIRIM


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

Hi,

I'm looking for a solution for version managed (or lo= ng transaction) data in PostgreSQL (and Oracle).

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.

I am looking for something which would require minimal change in the app= lication logic, like

    create table my_obj(id serial primary key,prop_1 text);
   =20
    insert into my_obj (prop_1) values ('object 1');
   =20
    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&=
#39;t changed anything yet

1   object 1
2   object 2 (to be deleted)

update my_obj set prop_1=3D'updated in version xx' where id =
=3D 1;
delete from my_obj where id=3D=
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) valu=
es ('object 3 (inserted after creation of xx)')=
;

switch to version 'xx'; //= this functionality i'm looking for

merge; //brings changes made in parent 'top' ve= rsion

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/c= ontent/dam/gepower-new/global/en_US/downloads/gas-new-site/resources/refere= nce/ger-4231-smallworld-4-managing-change-spatial-data.pdf) 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).

Does Postgresql have anything like this?

--00000000000043a81d05efc91e06--