Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id BE9509F9B2F for ; Fri, 24 Aug 2007 05:12:53 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 42177-02 for ; Fri, 24 Aug 2007 05:12:44 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from mailgw1.fjfi.cvut.cz (mailgw1.fjfi.cvut.cz [147.32.9.3]) by postgresql.org (Postfix) with ESMTP id 1F4739F9B40 for ; Fri, 24 Aug 2007 05:12:45 -0300 (ADT) Received: from localhost (localhost.localdomain [127.0.0.1]) by mailgw1.fjfi.cvut.cz (Postfix) with ESMTP id 3B66643E33 for ; Fri, 24 Aug 2007 10:12:40 +0200 (CEST) X-CTU-FNSPE-Virus-Scanned: amavisd-new at fjfi.cvut.cz X-CTU-FNSPE: this_is_mailgw_for_ctu_fnspe_header X-CTU-FNSPE-Helo: [10.0.0.1] X-CTU-FNSPE-Client-Address: 85.207.117.155 X-CTU-FNSPE-Client-Name: 155-117-207-85.strcechy.adsl-llu.static.bluetone.cz X-CTU-FNSPE-Sender: konference@brinda.info X-CTU-FNSPE-Recip: pgsql-admin@postgresql.org Received: from mailgw1.fjfi.cvut.cz ([127.0.0.1]) by localhost (mailgw1.fjfi.cvut.cz [127.0.0.1]) (amavisd-new, port 10026) with ESMTP id 7wWCqbE+klir for ; Fri, 24 Aug 2007 10:12:30 +0200 (CEST) Received: from [10.0.0.1] (155-117-207-85.strcechy.adsl-llu.static.bluetone.cz [85.207.117.155]) (using SSLv3 with cipher DES-CBC3-SHA (168/168 bits)) (No client certificate requested) (Authenticated sender: brinda) by mailgw1.fjfi.cvut.cz (Postfix) with ESMTP id 1F2D543E1D for ; Fri, 24 Aug 2007 10:12:30 +0200 (CEST) Subject: Re: Possibilities of PgSQL From: Karel =?UTF-8?Q?B=C5=99inda?= To: pgsql-admin In-Reply-To: <46CD82E7.8070605@Sheeky.Biz> References: <1187816377.4039.44.camel@karkulka> <46CD82E7.8070605@Sheeky.Biz> Content-Type: text/plain; charset=ISO-8859-2 Date: Fri, 24 Aug 2007 10:12:29 +0200 Message-Id: <1187943149.3153.0.camel@karkulka> Mime-Version: 1.0 X-Mailer: Evolution 2.11.90 (2.11.90-2.fc8) Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200708/311 X-Sequence-Number: 26642 > 100MB of text in each row?? is each row a copy of the english dictionary? > No, that was only example. It will be web application (Python + PgSQL). I thought about table with records + table with changes but is it some easy way how to get differences between 2 strings? Shane Ambler píše v Čt 23. 08. 2007 v 22:21 +0930: > 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. > >