RE: [HACKERS] Duplicate index check in btbuild

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> 
> "Hiroshi Inoue" <Inoue@tpf.co.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@tpf.co.jp