Potential problem with HOT and indexes?

In answering the recent question on -general I reread README.HOT and had a
couple thoughts:

> Practically, we prevent certain transactions from using the new index by
> setting pg_index.indcheckxmin to TRUE. Transactions are allowed to use
> such an index only after pg_index.xmin is below their TransactionXmin
> horizon, thereby ensuring that any incompatible rows in HOT chains are
> dead to them. (pg_index.xmin will be the XID of the CREATE INDEX
> transaction. The reason for using xmin rather than a normal column is
> that the regular vacuum freezing mechanism will take care of converting
> xmin to FrozenTransactionId before it can wrap around.)

So it occurs to me that freezing xmin won't actually do what we want for
indexcheckxmin. Namely it'll make the index *never* be used.

I'm not sure what we would want to happen in this admittedly pretty unlikely
scenario. We don't actually have anything protecting against having
transactions with xmin much older than freeze_threshold still hanging around.

> This means in particular that the transaction creating the index will be
> unable to use the index if the transaction has old snapshots. We
> alleviate that problem somewhat by not setting indcheckxmin unless the
> table actually contains HOT chains with RECENTLY_DEAD members.

In index.c:

> * A side effect is to set indexInfo->ii_BrokenHotChain to true if we detect
> * any potentially broken HOT chains. Currently, we set this if there are
> * any RECENTLY_DEAD entries in a HOT chain, without trying very hard to
> * detect whether they're really incompatible with the chain tip.

I wonder if this particular case is good evidence that we need to be cleverer
about checking if the indexed fields have actually changed.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's On-Demand Production Tuning