Re: REINDEX vs broken HOT chains, redux

Excerpts from Tom Lane's message of mar abr 19 12:29:04 -0300 2011:
> Last week we fixed a problem in which REINDEX could corrupt pg_index's
> own indexes by forbidding it from setting indcheckxmin on a system
> catalog's index. While thinking about bug #5985 I realized that there's
> a better, more general solution. Namely, that when reindexing an
> existing index, there cannot be any need to advance the index's
> indcheckxmin horizon. The existing code just blindly pushes the horizon
> forward to current time if it finds any possibly-broken HOT chains ---
> but if the index existed before, then any HOT chains that are actually
> broken with respect to it must predate its existing horizon.
>
> Therefore, when reindexing an existing index, we should never set
> indcheckxmin if it wasn't set before. In particular, this rule fixes
> the previous issue for system catalogs, which are certain to not have
> had indcheckxmin set when initdb made them.

Interesting.

> In short, the entire update of pg_index in index_build is unwanted when
> reindexing an existing index. index_build doesn't currently know
> whether it's being called for a new index or a reindex operation,
> but it wouldn't be hard to pass down a flag for that.
>
> I'm intending to revert last week's patch in favor of this approach,
> at least in HEAD. It'll be slightly more invasive than the previous
> patch because of the API change for index_build, so I'm not sure whether
> to back-patch or not --- comments?

Maybe add a new function index_build_ext that has the API change, and
keep the existing index_build as a wrapper that keeps the current
behavior. In HEAD just change the API of index_build and make
index_build_ext a macro on top of the function (or just make it
disappear.)

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support