Thread

1. Re: Continuous inserts...

   Webb Sprague <wsprague100@yahoo.com> — 2000-08-17T18:38:02Z

   Hi All.
   
   Shouldn't Postgres block while vacuuming, and then
   continue inserting starting where it left off?  Is the
   time lag too much?
   
   I am curious because I am going to build a similar app
   soon, basically parsing and inserting log file
   entries.
   
   W 
   --- Stephan Szabo <sszabo@megazone23.bigpanda.com>
   wrote:
   > 
   > On Thu, 17 Aug 2000, Joerg Hessdoerfer wrote:
   > 
   > > Hi!
   > > 
   > > I have an application, where I have to insert data
   > into a table at several
   > > rows per second, 24 hours a day, 365 days a year.
   > > 
   > > After some period (a week, maybe a month) the data
   > will be reducted to some
   > > degree and deleted from the table.
   > > 
   > > As far as I understood, I would have to use VACUUM
   > to really free the table
   > > from deleted rows - but VACUUM (esp. on a table
   > with several million rows)
   > > takes some time and prevents me from inserting new
   > data.
   > > 
   > > Now, I thought I could just rename the table,
   > inserting into a temp table, and
   > > switch the tables back after VACUUMing. Ideally,
   > this should work unnoticed
   > > (and thus without prog. effort) on the client
   > (inserter) side.
   > > 
   > > Question: would it work to use a transaction to
   > perform the rename?
   > > 
   > > i.e.: continuous insert into table 'main' from
   > client.
   > > 
   > >  From somewhere else, execute:
   > > 
   > > begin;
   > > alter table main rename to vac_main;
   > > create table main (...);
   > > end;
   > > 
   > > would the inserter notice this? Read: would ALL
   > inserts AT ANY TIME succeed?
   > 
   > Unfortunately -- no.  Also, bad things can happen if
   > the transaction
   > errors since the rename happens immediately. 
   > There's been talk on 
   > -hackers about this subject in the past.
   > 
   > However, you might be able to do something like
   > this, but
   > I'm not sure it'll work and it's rather wierd:
   > 
   > Have three tables you work with, a and b and c
   > 
   > Set up rule on a to change insert to insert on b.
   > Insert into a.
   > When you want to vacuum, change the rule to insert
   > to c.
   > Vacuum b
   > Change rule back
   > move rows from a and c into b
   > vacuum c
   > 
   > [you will slowly lose space in a, but it should only
   > be an occasional row since you should only insert
   > into
   > a while you've deleted the insert rule to b, but
   > haven't yet added the insert rule to c -- not too
   > many
   > rows here]
   > 
   > 
   
   
   __________________________________________________
   Do You Yahoo!?
   Send instant messages & get email alerts with Yahoo! Messenger.
   http://im.yahoo.com/
   
   

2. Re: Continuous inserts...

   Joerg Hessdoerfer <joerg.hessdoerfer@sea-gmbh.com> — 2000-08-18T08:03:48Z

   hi!
   
   At 11:38 17.08.00 -0700, you wrote:
   >Hi All.
   >
   >Shouldn't Postgres block while vacuuming, and then
   >continue inserting starting where it left off?  Is the
   >time lag too much?
   
   For me - yes. My app can accept some hundredes of ms time lag - not
   seconds or, like with a VACUUM, minutes (I've seen it taking hours,
   but that was a _LARGE_ table on 6.5.x).
   
   >I am curious because I am going to build a similar app
   >soon, basically parsing and inserting log file
   >entries.
   
   Rules do the trick - insert into a second table unnoticed by the inserter
   client. See my previous posting for slightly more detail.
   
   Greetings,
            Joerg
   +------****  Science & Engineering Applications GmbH  ****------+
   |                                                               |
   | Joerg Hessdoerfer                                             |
   | Leading SW developer Phone:    +49 (0)2203-962211             |
   | S.E.A GmbH           Fax:                 -962212             |
   | D-51147 Koeln        Internet: joerg.hessdoerfer@sea-gmbh.com |
   |                                http://www.sea-gmbh.com        |
   +---------------------------------------------------------------+