Thread: 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
Gregory Stark <stark@enterprisedb.com> writes:
> 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.
How do you figure that? FrozenXID is certainly in the past from any
vantage point.
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> writes: > Gregory Stark <stark@enterprisedb.com> writes: >> 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. > > How do you figure that? FrozenXID is certainly in the past from any > vantage point. Uhm, I'm not sure what I was thinking. 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. 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? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production Tuning
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.
> 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.
regards, tom lane
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!
Attachment
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.
> 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.
regards, tom lane
On Thu, Mar 26, 2009 at 7:41 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > 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. > >> 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
Greg Stark <stark@enterprisedb.com> writes:
> On Thu, Mar 26, 2009 at 7:41 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> 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.
> 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.
Hmm. You're right, I was thinking about the case of a generic update on
the pg_index row, but this particular patch is focused on REINDEX which
has just rebuilt the index contents. So never mind that.
However, I'm still unconvinced that it's safe to clear indcheckxmin
unconditionally. Isn't it possible for such a REINDEX to encounter
HOT chains that are (still) broken? Consider a REINDEX launched
right after a CREATE INDEX CONCURRENTLY (possibly a failed one),
while there are still open transactions older than the C.I.C.
We could reorganize the function slightly so that the IndexInfo is
still available after the PG_TRY block, and then do something like
"if (!indexInfo->ii_BrokenHotChain) reset indcheckxmin". This would
ensure that we didn't wipe the flag if index_build had just set it.
If I'm wrong and this can never happen, no harm done.
regards, tom lane
I wrote:
> We could reorganize the function slightly so that the IndexInfo is
> still available after the PG_TRY block, and then do something like
> "if (!indexInfo->ii_BrokenHotChain) reset indcheckxmin". This would
> ensure that we didn't wipe the flag if index_build had just set it.
> If I'm wrong and this can never happen, no harm done.
Committed with that change.
regards, tom lane