RE: [HACKERS] Duplicate index check in btbuild

> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
>
> "Hiroshi Inoue" <Inoue(at)tpf(dot)co(dot)jp> writes:
> > In addition I could find no other place to check
> > index uniqueness in tuplesort.c .
> > Seems we have to give up the uniqueness check in comparetup_
> > index() and have to check it in _bt_buildadd().
>
> Checking index uniqueness in tuplesort is pretty much of a hack,
> although kind of cool since it doesn't take any extra comparisons
> to do it there. But if we're doing the wrong thing then it has
> to be removed.
>
> I'm a little unclear on *why* it's wrong though. Don't we grab an
> exclusive lock on a table while building an index for it? (If not,
> shouldn't we be doing so?) I don't see how there can be tuples of
> uncertain commit state that need to be included in the index.
>

It's what you pointed out in [[HACKERS] Reproducible vacuum complaint!]
2 months ago.

> Oh, I think I see --- you mean that CREATE INDEX needs to make index
> entries for tuples that are committed dead but might still be visible
> to some running transaction somewhere. Yes, that seems to fit what
> I was seeing. VACUUM always complained that there were too few
> index entries, never too many.

> It looks like btbuild() only indexes tuples that satisfy SnapshotNow,
> so this is definitely a potential problem for btree indexes. The other
> index types are likely broken in the same way...
>
> Comments anyone? What time qual should btbuild and friends be using,
> if not that?

Same phenomenon was found in Marc's recent posting [HACKERS] [6.5.2]
potentially major bug?]. After changing SnapshotNow -> SnapshotAny I tried
and found that comparetup_index() rejects duplicate index tuples.

Regards.

Hiroshi Inoue
Inoue(at)tpf(dot)co(dot)jp