Re: Improving REINDEX for system indexes (long)

I've just put back your previous change, sorry.
As I already mentioned many times it must be the first thing.

Though I don't remenber my code completely yet, I would
reply to some points.
Unfortunately REINDEX wasn't a eagerly wanted command when
I implemented it. Though I wanted to introduce a per index
flag, I unwillingly used an existent per table flag(relhasindex)
instead. Because it was impossible to make REINDEX transaction-safe
then, such flag was needed to suppress inconsistency as less
as possible.
I also unwillingly introduced the ReindexProcessing mode(flag)
because I didn't think of other quick solutions.

IIRC the main reason why I gave up the REINDEX functionality
on nailed relations was the difficulty of reindexing pg_class
and the handling of relcache overflow. I didn't have much time
to test it. In addtion REINDEX wasn't a recognized command
then and I found no one to discuss the situation.

Tom Lane wrote:
>
> I've been looking at the issues involved in reindexing system tables,
> and I now have what I think is a fairly defensible set of proposals.
>
> We should whenever possible use the same reindexing technique used by
> CLUSTER:

REINDEX was the first command which used the pg_class.relfilenode
functionality. The pg_class.relfilenode was essentially my proposal.

> 1. There is a problem for a shared index, because we have no way to
> update pg_class.relfilenode in all the other databases besides ours.
> I see no good way around this problem.

So the current REINDEX disallows on-line reindex on shared relations.

> 2. There is a problem for a nailed-in-cache index, because the relcache
> isn't prepared to cope with relfilenode updates for such indexes.
> However, that is fixable.

My code works pretty good with nailed relations except pg_class
#if defined (ENABLE_REINDEX_NAILED_RELATIONS).

> 3. There is a problem for an index on pg_class itself: doing heap_update
> on a pg_class row must make new index entries. We have to be careful
> that the new row does appear in the updated index, while not making
> entries in old-and-possibly-broken indexes. This is doable.

Yes.

Sorry I have no time to continue the discussion now.

regards,
Hiroshi Inoue
http://www.geocities.jp/inocchichichi/psqlodbc/