I'm not familiar with rules. Could you please post the SQL for creating the rule
that you've created?
I going to make such a setup in the near future and this seems to .
Joerg Hessdoerfer wrote:
> 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 |
> +---------------------------------------------------------------+
