Hi all,

Currently btbuild() rejects duplicate index in comparetup_index().
But there are some cases that index tuples for already deleted
heap tuples should be made. comparetup_index() doesn't work
well in such cases because comparetup_index() couldn't ignore
deleted tuples. 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().
We would be able to take the visibility of heap tuples into acount
in _bt_buildadd(). 

Comments ?

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp

Mon, 31 Jan 2000 23:46:09 +0900
"Hiroshi Inoue" <Inoue@tpf.co.jp>
Duplicate index check in btbuild
"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.

			regards, tom lane

Mon, 31 Jan 2000 11:00:57 -0500
Tom Lane <tgl@sss.pgh.pa.us>
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  




Tue, 1 Feb 2000 08:55:25 +0900
"Hiroshi Inoue" <Inoue@tpf.co.jp>
RE: [HACKERS] Duplicate index check in btbuild 