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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers