Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 360DC9F9737 for ; Thu, 23 Aug 2007 09:52:04 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 82399-05 for ; Thu, 23 Aug 2007 09:51:54 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from ipmail02.adl2.internode.on.net (ipmail02.adl2.internode.on.net [203.16.214.141]) by postgresql.org (Postfix) with ESMTP id 698E69F96FE for ; Thu, 23 Aug 2007 09:51:59 -0300 (ADT) X-IronPort-Anti-Spam-Filtered: true X-IronPort-Anti-Spam-Result: Aj0KAHYdzUbLevdH/2dsb2JhbAAs X-IronPort-AV: E=Sophos;i="4.19,300,1183300200"; d="scan'208";a="174175819" Received: from ppp247-71.static.internode.on.net (HELO DevBox.local) ([203.122.247.71]) by ipmail02.adl2.internode.on.net with ESMTP; 23 Aug 2007 22:21:54 +0930 Message-ID: <46CD82E7.8070605@Sheeky.Biz> Date: Thu, 23 Aug 2007 22:21:51 +0930 From: Shane Ambler User-Agent: Thunderbird 2.0.0.5 (Macintosh/20070716) MIME-Version: 1.0 To: =?UTF-8?B?S2FyZWwgQsWZaW5kYQ==?= CC: pgsql-admin@postgresql.org Subject: Re: Possibilities of PgSQL References: <1187816377.4039.44.camel@karkulka> In-Reply-To: <1187816377.4039.44.camel@karkulka> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.221 tagged_above=0 required=5 tests=AWL=-1.957, BAYES_20=-0.74, DNS_FROM_DOB=0.732, RCVD_IN_DOB=1.103, URIBL_RHS_DOB=1.083 X-Spam-Level: X-Archive-Number: 200708/286 X-Sequence-Number: 26617 Karel Břinda wrote: > Hi, > > I have a question about PgSQL. I am working at some project and I want > to have few tables with special properties: > > There would be many rows and these would be changed every day for many > times. I want to be able to get know how did the row looked in given > time (f.e. 2 day ago, 6 minutes ago, 2nd Sept. 2002,...). Nevertheless > it should work with diffs (f.e. if I had a row with 100MB string and I > changed only 2 chars it should not require on disk 200MB but only 100MB > + few (kilo)Bytes). 100MB of text in each row?? is each row a copy of the english dictionary? My thoughts are to have a table that records the changes, something along the lines of a timestamp of the change with substring start and end positions of the change with the before and after text that has changed that can be used to 'replay' the changes. When you want to rewind to a certain time you find all entries after the given time and undo the changes to get back to where it was. > Is there any possibility how to solve it? I have heard that I can do it > with triggers (but the worst thing is how to implement diffs) but I hope > that there is any other (easier) way. Triggers are the most reliable way to implement this, I would think that pl/perl may be the fastest way to implement it as a trigger. Basically you would need to compare before and after as the record was saved - doing that with 100MB of data will make saving changes somewhat slower. What sort of client is updating this data? I would think that the client could keep track of changes as they are typed and save this info with the updated data thus removing the time to compare the before and after data at the DB end. -- Shane Ambler pgSQL@Sheeky.Biz Get Sheeky @ http://Sheeky.Biz