RE: [HACKERS] Index recreation in vacuum

> -----Original Message-----
> From: Bruce Momjian [mailto:pgman(at)candle(dot)pha(dot)pa(dot)us]
>
> > Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > > The beauty of doing a temp index while keeping the old one is that you
> > > can recover right away, and maybe allow the old index to be used while
> > > you vacuum?
> >
> > Huh? You've got the whole table locked exclusively for the duration
> > of the vacuum anyway.
> >
> > In fact, the instant that vacuum does its internal commit, the old index
> > contents are actually *wrong*, and there is no possible value in keeping
> > them after that. Might as well blow them away and recover the disk
> > space for use in constructing the new indexes.
>
> Oh, I thought the vacuum itself would use the index during processing.
>

It's a big charge for vacuum to keep consistency between heap table and
indexes. The main point of index recreation in vacuum is to invalidate the
indexes of the target table. Temp indexes or renaming technique is no
longer needed once indexes are invalidated.

Once again,how to invalidate/validate indexes ?

I want to avoid dropping indexes because the definition is lost and
'commit' is needed internally.

My proposal is to use relhasindex of pg_class.
How about ?
relhasindex is true -- all indexes of the table are valid if the table has
indexes.
relhasindex is false -- either the table has no indexes or all indexes
of the table are invalid

CREATE INDEX/DROP INDEX/DROP TABLE/VACUUM/REINDEX
would be able to ignore relhasindex.

Am I misusing relhasindex ?

If reindexing vacuum crashes,indexes of the target table would be invalid.
To recover indexes there would be 2 ways.
1) vacuum again
2) reindex the table

Note that we would be able to REINDEX user tables under postmaster.

Comments ?

Regards.

Hiroshi Inoue
Inoue(at)tpf(dot)co(dot)jp