
Odd .. why is heap reporting 5899, when count() only reports 2951?

globalmatch=# select count(gid) from images;
 count 
-------
  2951
(1 row)

globalmatch=# create index images_gid on images using btree ( gid );
CREATE
globalmatch=# vacuum verbose analyze images;
NOTICE:  --Relation images--
NOTICE:  Pages 56: Changed 0, reaped 0, Empty 0, New 0; Tup 5899: Vac 0, Keep/VTL 2948/0, Crash 0, UnUsed 0, MinLen 51, MaxLen 79; Re-using: Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. CPU 0.04s/0.00u sec.
NOTICE:  Index images_gid: Pages 8; Tuples 2951. CPU 0.00s/0.00u sec.
NOTICE:  Index images_gid: NUMBER OF INDEX' TUPLES (2951) IS NOT THE SAME AS HEAP' (5899).
        Recreate the index.
VACUUM



Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org 


Wed, 12 Jul 2000 20:01:32 -0300 (ADT)
The Hermit Hacker <scrappy@hub.org>
[7.0.2] INDEX' TUPLES != HEAP' ..
The Hermit Hacker <scrappy@hub.org> writes:
> Odd .. why is heap reporting 5899, when count() only reports 2951?

Open transactions preventing recently-dead tuples from being reaped?

			regards, tom lane

Wed, 12 Jul 2000 19:13:51 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [7.0.2] INDEX' TUPLES != HEAP' .. 
On Wed, 12 Jul 2000, Tom Lane wrote:

> The Hermit Hacker <scrappy@hub.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 ... actually, since this database is up, there would have
been zero additions or deletions, as that ability is yet to be re-written,
so other then SELECTs, that table is static/read-only




Wed, 12 Jul 2000 20:17:51 -0300 (ADT)
The Hermit Hacker <scrappy@hub.org>
Re: [7.0.2] INDEX' TUPLES != HEAP' .. 
The Hermit Hacker <scrappy@hub.org> writes:
> On Wed, 12 Jul 2000, Tom Lane wrote:
>> The Hermit Hacker <scrappy@hub.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.

			regards, tom lane

Wed, 12 Jul 2000 19:29:58 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [7.0.2] INDEX' TUPLES != HEAP' .. 
On Wed, 12 Jul 2000, Tom Lane wrote:

> The Hermit Hacker <scrappy@hub.org> writes:
> > On Wed, 12 Jul 2000, Tom Lane wrote:
> >> The Hermit Hacker <scrappy@hub.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?

zip ... only SELECTs right now ... no facility there to do updates,
deletes or inserts ...

> 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.

last email about waiting was a different database ... will *that* affect
this? :(



Wed, 12 Jul 2000 20:46:18 -0300 (ADT)
The Hermit Hacker <scrappy@hub.org>
Re: [7.0.2] INDEX' TUPLES != HEAP' .. 
The Hermit Hacker <scrappy@hub.org> writes:
>> 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.

> last email about waiting was a different database ... will *that* affect
> this? :(

Not directly, but the "oldest open transaction" is across the whole
installation IIRC.  So an old open transaction could prevent VACUUM
from deleting tuples even if the xact is being done in another DB.

			regards, tom lane

Wed, 12 Jul 2000 19:59:46 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [7.0.2] INDEX' TUPLES != HEAP' .. 
> -----Original Message-----
> From: pgsql-hackers-owner@hub.org [mailto:pgsql-hackers-owner@hub.org]On
> Behalf Of Tom Lane
> 
> The Hermit Hacker <scrappy@hub.org> writes:
> > On Wed, 12 Jul 2000, Tom Lane wrote:
> >> The Hermit Hacker <scrappy@hub.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@tpf.co.jp

Thu, 13 Jul 2000 09:43:40 +0900
"Hiroshi Inoue" <Inoue@tpf.co.jp>
RE: [7.0.2] INDEX' TUPLES != HEAP' .. 