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
