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"  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@postgresql.org so that your
>        message can get through to the mailing list cleanly

-- 
  Bruce Momjian            http://momjian.us
  EnterpriseDB                               http://www.enterprisedb.com

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