Re: Vacuum only with 20% old tuples

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


Jan

--

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