RE: Vacuum only with 20% old tuples

> -----Original Message-----
> From: Jan Wieck [mailto:JanWieck(at)t-online(dot)de]
>
> Hiroshi Inoue wrote:
> > > -----Original Message-----
> > > From: pgsql-hackers-owner(at)hub(dot)org
> [mailto:pgsql-hackers-owner(at)hub(dot)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(at)tpf(dot)co(dot)jp