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 e7I7xjm88255 for ; Fri, 18 Aug 2000 03:59:45 -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 e7I7xhU38458; Fri, 18 Aug 2000 09:59:43 +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 JAA18736; Fri, 18 Aug 2000 09:59:42 +0200 Message-Id: <4.3.2.7.0.20000818095440.00af6770@192.168.0.1> X-Sender: joe@192.168.0.1 X-Mailer: QUALCOMM Windows Eudora Version 4.3.2 Date: Fri, 18 Aug 2000 09:59:42 +0200 To: Stephan Szabo From: Joerg Hessdoerfer Subject: Re: Continuous inserts... Cc: pgsql-sql@postgresql.org In-Reply-To: References: <4.3.2.7.0.20000817162006.00af3db0@192.168.0.1> Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii"; format=flowed X-Archive-Number: 200008/169 Hi! Thanks all for your input... At 09:15 17.08.00 -0700, you wrote: [...] > > 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 [...] Good idea - I immediately tested it - rules rule! That seems to work perfectly, and the client doesn't even see it happen (except for 'selects', one would have to setup a rule to return something meaningful then...). I did: Two tables, a and b. Normally, insert into a. When Vacuuming starts, create rule on a to insert into b Vacuum a drop rule copy records from b to a vacuum b Why did you suppose three tables? Did I overlook something? 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 | +---------------------------------------------------------------+