Re: When/if to Reindex

This has been saved for the 8.4 release:

http://momjian.postgresql.org/cgi-bin/pgpatches_hold

---------------------------------------------------------------------------

Tom Lane wrote:
> "Steven Flatt" <steven(dot)flatt(at)gmail(dot)com> writes:
> > So, can we simply trust what's in pg_class.relpages and ignore looking
> > directly at the index?
>
> No, we can't. In the light of morning I remember more about the reason
> for the aforesaid patch: it's actually unsafe to read the pg_class row
> at all if you have not got lock on the index. We are reading with
> SnapshotNow in order to be sure we see up-to-date info, and that means
> that a concurrent update of the row (eg, for REINDEX to report the new
> relfilenode) can have the following behavior:
>
> 1. REINDEX inserts the new modified version of the index's pg_class row.
>
> 2. Would-be reader process visits the new version of the pg_class row.
> It's not committed yet, so we ignore it and continue scanning.
>
> 3. REINDEX commits.
>
> 4. Reader process visits the old version of the pg_class row. It's
> now committed dead, so we ignore it and continue scanning.
>
> 5. Reader process bombs out with a complaint about no pg_class row for
> the index.
>
> So we really have to have the lock.
>
> > This is a fairly serious concern for us, that
> > reindex is blocking all readers of the parent table.
>
> I'm afraid you're kinda stuck: I don't see any fix that would be
> practical to put into 8.2, or even 8.3 considering that it's way too
> late to be thinking of implementing REINDEX CONCURRENTLY for 8.3.
>
> You might be able to work around it for now by faking such a reindex
> "by hand"; that is, create a duplicate new index under a different
> name using CREATE INDEX CONCURRENTLY, then exclusive-lock the table
> for just long enough to drop the old index and rename the new one
> to match.
>
> It's probably worth asking also how badly you really need routine
> reindexing. Are you certain your app still needs that with 8.2,
> or is it a hangover from a few releases back? Could more aggressive
> (auto)vacuuming provide a better solution?
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +