Re: Continuous inserts... - Mailing list pgsql-sql
From | Webb Sprague |
---|---|
Subject | Re: Continuous inserts... |
Date | |
Msg-id | 20000817183802.19885.qmail@web804.mail.yahoo.com Whole thread Raw |
In response to | Continuous inserts... (Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com>) |
Responses |
Re: Continuous inserts...
(Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com>)
|
List | pgsql-sql |
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/