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 1p5QLw-0007Vu-51 for pgsql-sql@arkaria.postgresql.org; Wed, 14 Dec 2022 11:55:12 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1p5QLt-0004gO-F9 for pgsql-sql@arkaria.postgresql.org; Wed, 14 Dec 2022 11:55:09 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p5QLt-0004gD-0G for pgsql-sql@lists.postgresql.org; Wed, 14 Dec 2022 11:55:09 +0000 Received: from mail-qk1-x729.google.com ([2607:f8b0:4864:20::729]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1p5QLq-0004K1-0M for pgsql-sql@lists.postgresql.org; Wed, 14 Dec 2022 11:55:07 +0000 Received: by mail-qk1-x729.google.com with SMTP id k2so1130138qkk.7 for ; Wed, 14 Dec 2022 03:55:05 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=27W6QMo3P6v3eGRGJ+YOOi9PB2OeAFiIUvkQYffdQm8=; b=k56Hsm86GspM0wiRJZfotdONIwIL9nV56SDMRJX8ZrI47gQMnY6SjITimrju0Yjn/P FiBMhei5dBU/ZHptflR4p0wrZmZOLHgwtrdOBNELCWK8forFjvtZI7Cm/7Pr3Tk0fIkn DnPOT7o73wmc7VZtwOGW/G5bO3+iqxsne5sQbVUcslxs88vP3/qE884qQZ8NxdUnQ/oU ygVbp530zbSFU5gi/c9e3H2O6Ok95TVm8pKS58uL3J8vvR5NFnQTJQ2gpHLx5JjW8oXJ TnrHGV+sGHhJOb4P9efK611dI2GBE1mDAV8W9N85c5dggmg+wsfFFIlDL/oiFC4DKge1 1i5A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=to:subject:message-id:date:from:mime-version:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=27W6QMo3P6v3eGRGJ+YOOi9PB2OeAFiIUvkQYffdQm8=; b=MeZM84Vep1Syu1aF7am+nTUkvcHKOrY5NywFp4Y3SnXC8YydfndKhahSVETeP0A9A2 nEF0tOTyYcRWkGvzYsfnwIG/RwAUsC61jesEuYcvTq9f97eD3wwFoN/6LlSW2EiGz9VN CUB5C2YbVN8aZwm45+GCWskicoUjWxinAJjIEzNubcOSmPkTabsmcKvIqthUSYHGg0MD 8N2lvXcgUgr7SpPNZZDWKpDlQdMGwEloKc4obSdp7/RnlRoTnl3Onv7wMFazwUtY0LiI nEL3NpN68FMscKLiQrBET7HH0vxocUI/7ZP6SB3D0mJEhrJcI43sG9NvyLClUlnbKR4P odiQ== X-Gm-Message-State: ANoB5pmf5uDG5TtJIGa9+R/7JRsHHhaysKlcTF2prWNDxN1M1qx17CJ+ 6ZWdxU4IPCoLkxe8zJWyvl1cmny8v39Eb9D9ivPV8qBDpQ5BXQ== X-Google-Smtp-Source: AA0mqf47pWxWjIbshCZ8FR1NFoJIWC6bLR1u51B6R6ITh2PNUbfKNZN/jk9BC7FRK2e3kf/vY2ZpEWZOvwoSBi7czs4= X-Received: by 2002:a05:620a:2e7:b0:6fc:9a48:eddf with SMTP id a7-20020a05620a02e700b006fc9a48eddfmr11491624qko.458.1671018904918; Wed, 14 Dec 2022 03:55:04 -0800 (PST) MIME-Version: 1.0 From: Kirmo Uusitalo Date: Wed, 14 Dec 2022 13:54:54 +0200 Message-ID: Subject: Postgresql long transaction support To: pgsql-sql@lists.postgresql.org Content-Type: multipart/alternative; boundary="0000000000003d9eea05efc86830" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000003d9eea05efc86830 Content-Type: text/plain; charset="UTF-8" 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? --0000000000003d9eea05efc86830 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
=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 versio=
n 'xx'; //this =
functionality i'm looking for
switch to version 'xx'; //=
this functionality i'm looking for

select * from my_ob=
j; //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=3D'updated in v=
ersion xx' where id =3D 1;
delete from my_obj where=
 id=3D2;

select * from my_ob=
j;

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 ('obje=
ct 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 vers=
ion 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-small= world-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?

--0000000000003d9eea05efc86830--