Re: HOT breaks CLUSTER, a bit

Tom Lane wrote:
> While editing Bruce's README.HOT documentation I ran into a problem.
> I had idly added the following text after thinking about the different
> sorts of snapshots we use:
>
> ---
> Another unpleasant consequence is that it is no longer very meaningful to
> use SnapshotAny in an index scan: if the index was created more recently
> than the last vacuum, it's possible that some of the visited tuples do not
> match the index entry they are linked to. This does not seem to be a
> fatal objection in practice, since there are few users of SnapshotAny and
> they all use seqscans.
> ---
>
> However, a quick grep to confirm that turned up a problem: CLUSTER uses
> SnapshotAny with an indexscan. This essentially means that CLUSTER
> might fetch recently-dead tuples out of order, because it finds them
> attached to an index HOT chain that's for a different index key value.
>
> I think that this is not a fatal objection; the out-of-order-ness is
> limited and won't be seen at all by transactions with snapshots
> postdating the CLUSTER, and CLUSTER can't guarantee the ordering will
> stay pristine for long anyway. But it's a bit worrisome. Does anyone
> see a bigger problem here than I do?

Hmm. Normally all tuples in a HOT chain have the same index key, but
right after CREATE INDEX that might indeed not be true.

I think a warning in the comments for SnapshotAny and index_getnext to
not use SnapshotAny with index scans (except for CLUSTER) is enough.

> BTW, the proposed HOT code in indexam.c that special-cases SnapshotAny
> is a crock ...

It was written under the assumption that all tuples in a HOT chain have
the same index key, which isn't true after CREATE INDEX as you pointed
out. Is there something else wrong with it?

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com