Re: Potential problem with HOT and indexes?

Tom Lane <tgl@sss.pgh.pa.us> writes:

> Gregory Stark <stark@enterprisedb.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.
>
> Hmm ... if updates to pg_index entries were common then I could get
> worried about that, but they really aren't.

Fixing this for REINDEX is fairly straightforward I think. It already updates
the pg_index line to fix indisvalid and indisready. see:


>> Couldn't this happen if you set a table WITHOUT CLUSTER for example? Or if
>> --as possibly happened in the user's case-- you reindex the table and don't
>> find any HOT update chains but the old pg_index entry had indcheckxmin set
>> already?
>
> This is all useless guesswork until we find out whether he was using
> REINDEX or CREATE INDEX CONCURRENTLY.

Well he said he had a nightly REINDEX script. What's unknown is whether the
index was originally built with CREATE INDEX CONCURRENTLY. But I don't know
any other reason for a newly built index to go unused when the query is very
selective and then to suddenly start being used after a restart.

--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's PostGIS support!