Received: from dv.kp.dlr.de (dv.kp.dlr.de [129.247.97.1]) by hub.org (8.10.1/8.10.1) with ESMTP id e7HEVMm84104 for ; Thu, 17 Aug 2000 10:31:22 -0400 (EDT) Received: from sea-gw.sea.com (yyy.kp.dlr.de [129.247.113.223]) by dv.kp.dlr.de (8.10.1/8.10.1) with ESMTP id e7HEVKU46332 for ; Thu, 17 Aug 2000 16:31:20 +0200 Received: from joebox.sea-gmbh.com (joebox.sea.com [192.168.0.20]) by sea-gw.sea.com (8.7.4/8.7.1) with ESMTP id QAA13925 for ; Thu, 17 Aug 2000 16:31:17 +0200 Message-Id: <4.3.2.7.0.20000817162006.00af3db0@192.168.0.1> X-Sender: joe@192.168.0.1 X-Mailer: QUALCOMM Windows Eudora Version 4.3.2 Date: Thu, 17 Aug 2000 16:30:30 +0200 To: pgsql-sql@postgresql.org From: Joerg Hessdoerfer Subject: Continuous inserts... Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii"; format=flowed X-Archive-Number: 200008/158 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? I know, I could simulate such functionality in the client (inserter). But it seems more elegant this way... 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 | +---------------------------------------------------------------+