This has been saved for the 8.4 release: http://momjian.postgresql.org/cgi-bin/pgpatches_hold --------------------------------------------------------------------------- Tom Lane wrote: > "Steven Flatt" <steven.flatt@xxxxxxxxx> 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@xxxxxxxxxxxxxx so that your > message can get through to the mailing list cleanly -- Bruce Momjian <bruce@xxxxxxxxxx> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
