Re: Vacuum only with 20% old tuples

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...

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #