Обсуждение: unlimited undo/journaling
hello, i wondered how an unlimited undo feature would be realized in postgresql. example: there is an address book within my application and i want to log each change some user of my system is doing to an entry. but when he updates maybe the adress of a person i want to be able to restore each state which this address ever had as i don't trust the employees of my fictional company. so what i want is basically an unlimited undo or journalling feature for more or less every field. the idea should be to save the data-difference in some journalling table, but i'm not sure how this could be done properly. maybe somebody with some experience can help me here ? thanks in advance carl
On Wed, Jun 26, 2002 at 07:43:19AM +0200, Carl Meyer wrote: > hello, > > i wondered how an unlimited undo feature would be realized in > postgresql. > > example: > there is an address book within my application and i want to log > each change some user of my system is doing to an entry. but > when he updates maybe the adress of a person i want to be able > to restore each state which this address ever had as i don't trust > the employees of my fictional company. > > so what i want is basically an unlimited undo or journalling feature > for more or less every field. > > the idea should be to save the data-difference in some journalling > table, but i'm not sure how this could be done properly. maybe > somebody with some experience can help me here ? What you're looking for seems similar to the time travel feature in the contrib section. Even if you don't use exactly that, the same idea can be used to keep track of every change ever made to a table. Look for time travel in the docs also. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > There are 10 kinds of people in the world, those that can do binary > arithmetic and those that can't.
On Wed, 2002-06-26 at 07:43, Carl Meyer wrote: > hello, > > i wondered how an unlimited undo feature would be realized in > postgresql. > I think Temporal SQL, which is perhaps a standard now, offers this functionnality. Basically, it would allow you to create a table with "transaction time" support and every modification would be stored automatically with your data. To get an idea of how simple it would be then to retrieve your data as stored at a particular date, see the introduction to the (draft?) TSQL standard : ftp://ftp.cs.arizona.edu/tsql/tsql2/sql3/ansi-94-276.pdf I think that the PostgreSQL developpers are very busy, so I wouldn't expect this feature soon :-) Anyway, you have a dream now :-) Cheers, -- Arnaud Le Taillanter - alt@fr.clara.net Claranet Système/Réseau
On Wednesday 26 Jun 2002 6:43 am, Carl Meyer wrote: > so what i want is basically an unlimited undo or journalling feature > for more or less every field. > > the idea should be to save the data-difference in some journalling > table, but i'm not sure how this could be done properly. maybe > somebody with some experience can help me here ? Perhaps the simplest system is to have two tables: address and arc_address. Use "before" triggers on address to copy the old data into arc_address and stamp it with a version number/timestamp. I've done something similar to this using a sequence to generate unique version numbers for me. The other approach is to turn the process around the other way and have separate version codes (e.g. EDITING, SUBMITTED, 2002-06-24 etc) and "publish" the SUBMITTED changes once a day to a "live_address" table. I currently use something similar to this for a directory where they require editorial approval, but the editorial bit isn't required. - Richard Huxton
> > so what i want is basically an unlimited undo or journalling feature > > for more or less every field. > > > > the idea should be to save the data-difference in some journalling > > table, but i'm not sure how this could be done properly. maybe > > somebody with some experience can help me here ? > > Perhaps the simplest system is to have two tables: address > and arc_address. > Use "before" triggers on address to copy the old data into > arc_address and > stamp it with a version number/timestamp. > > I've done something similar to this using a sequence to > generate unique > version numbers for me. I have too, but not using a sequence, but instead another table for version numbers: Create table trans_version (trans_id int4, version int4); Copying the current data into the archive table is quick and efficient with this method: Create table mytrans(trans_id int4, date_modified timestamp, ...); Create table arch_mytrans(version int4, trans_id int4, date_modified timestamp, ...); Then, when modifying a document: Begin; Insert into arch_mytrans select <new version #>, * from mytrans where transi_d = <trans id>; Update trans_version set version=<new version #> where trans_id = <trans id>; Commit; This allows you to add more fields to your tables without messing with your versioning code. Glen Parker glenebob@nwlink.com
On Wed, Jun 26, 2002 at 07:34:14PM +0200, Arnaud Le Taillanter wrote: > On Wed, 2002-06-26 at 07:43, Carl Meyer wrote: > > > hello, > > > > i wondered how an unlimited undo feature would be realized in > > postgresql. > > > > I think Temporal SQL, which is perhaps a standard now, offers this > functionnality. Basically, it would allow you to create a table with > "transaction time" support and every modification would be stored > automatically with your data. It was actually made an SQL standard? Wow. Postgresql used to have this feature a long time ago but it was ripped out because the overhead was considered too high for a feature no-one used anyway. Nowadays with view, triggers and rules you can acheive exactly the same effect except for the nifty syntax. > To get an idea of how simple it would be then to retrieve your data as > stored at a particular date, see the introduction to the (draft?) TSQL > standard : > ftp://ftp.cs.arizona.edu/tsql/tsql2/sql3/ansi-94-276.pdf > > I think that the PostgreSQL developpers are very busy, so I wouldn't > expect this feature soon :-) Anyway, you have a dream now :-) You can see how it used to be at: http://www.us.postgresql.org/users-lounge/docs/6.5/tutorial/x1403.htm For now you can look in the contrib directory for time travel. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > There are 10 kinds of people in the world, those that can do binary > arithmetic and those that can't.
On Wed, 26 Jun 2002 19:59:47 +0000 (UTC), dev@archonet.com (Richard Huxton) wrote: >Perhaps the simplest system is to have two tables: address and arc_address.= >Use "before" triggers on address to copy the old data into arc_address and= >stamp it with a version number/timestamp. > >I've done something similar to this using a sequence to generate unique=20 >version numbers for me. this looks mostly like what i need. however, i really don't like the idea to store the whole data over and over again. i'd rather want a "reverse diff": store the data in it's current form and be able to roll back from there. i fear this can't be done easily. the temporal sql looks brilliant. this is exactly what i need :-) is there any other (maybe non free) database which supports this ? greetings carl