Postgresql long transaction support

Поиск
Список
Период
Сортировка
От Kirmo Uusitalo
Тема Postgresql long transaction support
Дата
Msg-id CAH2dGSOY+8amTEE12_WkYkPs17=Y0Cy1dbQ+bsvhmBB6cFSRmw@mail.gmail.com
обсуждение исходный текст
Ответы Re: Postgresql long transaction support  (Samed YILDIRIM <samed@reddoc.net>)
Список pgsql-sql

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?

В списке pgsql-sql по дате отправления:

Предыдущее
От: Shaozhong SHI
Дата:
Сообщение: Re: Tom changed his job role many times
Следующее
От: Samed YILDIRIM
Дата:
Сообщение: Re: Postgresql long transaction support