Re: Potential problem with HOT and indexes?

On Thu, Mar 26, 2009 at 7:41 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Gregory Stark <stark(at)enterprisedb(dot)com> writes:
>>> Another thought now though. What if someone updates the pg_index entry --
>>> since we never reset indcheckxmin then the new tuple will have a new xmin and
>>> will suddenly become invisible again for no reason.
>
>> Fixing this for REINDEX is fairly straightforward I think. It already updates
>> the pg_index line to fix indisvalid and indisready. see:
>
> I realized what was bothering me about that patch: it could reset
> indcheckxmin too soon, ie, while there are still transactions that
> shouldn't use the index.
>
> I propose that we modify it slightly: if we are updating a pg_index
> row, and indcheckxmin is set, *and the old xmin is below the GlobalXmin
> horizon*, then reset indcheckxmin.  Otherwise leave it set, which will
> mean that we postpone the time when the index becomes usable to
> everyone, but it won't risk breaking anything.

That doesn't sound like the right solution. What we want to do is use
the indcheckxmin for the newly built index if any -- ignoring any
indcheckxmin from the previous index. The old value is completely
irrelevant.

What you describe would be right for the ALTER INDEX commands like
RENAME or SET/RESET which might update the xmin without rebuilding the
index contents. Likewise for CLUSTER/SET WITHOUT CLUSTER.

--
greg