Thread: [7.0.2] INDEX' TUPLES != HEAP' ..
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,
MaxLen79; 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
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
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
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
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? :(
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
> -----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