RE: [7.0.2] INDEX' TUPLES != HEAP' ..

> -----Original Message-----
> From: pgsql-hackers-owner(at)hub(dot)org [mailto:pgsql-hackers-owner(at)hub(dot)org]On
> Behalf Of Tom Lane
>
> The Hermit Hacker <scrappy(at)hub(dot)org> writes:
> > On Wed, 12 Jul 2000, Tom Lane wrote:
> >> The Hermit Hacker <scrappy(at)hub(dot)org> writes:
> >>>> Odd .. why is heap reporting 5899, when count() only reports 2951?
> >>
> >> Open transactions preventing recently-dead tuples from being reaped?
>
> > nope ... I've tried recreating the indices, no change ... and
> no change in
> > number of tuples ...
>
> That would fit right in: a newly-created index will only index the
> tuples that are currently live. (OK, since an old transaction that
> could still see the dead tuples couldn't see the index anyway.)
>
> > actually, since this database is up, there would have
> > been zero additions or deletions,
>
> What about UPDATEs?
>
> Given your other comment about a bunch of waiting backends, it sure
> sounds like you've got some backend that's sitting on an old open
> transaction.
>

I've mentioned I have a fix for this case.
But I've hesitated to commit it for a while.

It has a performance problem for unique indexes.
I moved the place of duplicate check from tuplesort()
to btbuild() in my fix. So it may take long time to check
the uniqueness of indexes when there are many updated
-dead-but-cannot-be-discarded tuples(maybe Marc's
case is so).
.
In addtion it recently caused the fail of initdb in my
local branch. I don't think my fix is beautiful and am
suspicious if my fix would be robust for related changes.

Comments ?

Regards.

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