RE: Vacuum only with 20% old tuples

> -----Original Message-----
> From: Jan Wieck [mailto:JanWieck@t-online.de]
> 
> Hiroshi Inoue wrote:
> > > -----Original Message-----
> > > From: pgsql-hackers-owner@hub.org 
> [mailto:pgsql-hackers-owner@hub.org]On
> > > Behalf Of The Hermit Hacker
> > >
> > > how about leaving vacuum as is, but extend REINDEX so that it
> > > drops/rebuilds all indices on a TABLE | DATABASE?  Or does it do that
> > > now?  From reading \h REINDEX, my thought is that it doesn't, but ...
> > >
> >
> > As for user tables,REINDEX could do it already,i.e
> >    REINDEX TABLE table_name FORCE;  is possible under psql.
> > If REINDEX fails,PostgreSQL just ignores the indexes of the table
> > (i.e Indexscan is never applied) and REINDEX/VACUUM would
> > recover the state. Yes,VACUUM already has a hidden functionality
> > to reindex.
> 
>     Sorry, but there seem to be problems with that.
> 
>         pgsql=# delete from t2;
>         DELETE 0
>         pgsql=# vacuum;
>         VACUUM
>         pgsql=# reindex table t2 force;
>         REINDEX
>         pgsql=# \c
>         You are now connected to database pgsql as user pgsql.
>         pgsql=# insert into t2 select * from t1;
>         FATAL 1:  btree: failed to add item to the page
>         pqReadData() -- backend closed the channel unexpectedly.
>                 This probably means the backend terminated abnormally
>                 before or while processing the request.
> 
>     Happens  too  if  I  don't  reconnect to the database between
>     REINDEX and INSERT. Also if I  drop  connection  and  restart
>     postmaster,  so  it  shouldn't  belong  to old blocks hanging
>     aroung in the cache.
> 
>     The interesting thing is that the btree index get's reset  to
>     2 blocks.  Need to dive into...
>

Hmm,couldn't reproduce it here.
What kind of indexes t2 have ?

Anyway the index get's reset to 2 blocks seems reasonable because
t2 is empty.

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp