Received: from web804.mail.yahoo.com (web804.mail.yahoo.com [128.11.23.64]) by hub.org (8.10.1/8.10.1) with SMTP id e7HIeHm85721 for ; Thu, 17 Aug 2000 14:40:17 -0400 (EDT) Received: (qmail 19886 invoked by uid 60001); 17 Aug 2000 18:38:02 -0000 Message-ID: <20000817183802.19885.qmail@web804.mail.yahoo.com> Received: from [169.2.229.71] by web804.mail.yahoo.com; Thu, 17 Aug 2000 11:38:02 PDT Date: Thu, 17 Aug 2000 11:38:02 -0700 (PDT) From: Webb Sprague Subject: Re: Continuous inserts... To: Stephan Szabo , Joerg Hessdoerfer Cc: pgsql-sql@postgresql.org MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Archive-Number: 200008/166 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 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/