Thread: Index tuple count != heap tuple count problem identified
You'll probably recall reports of messages like this out of VACUUM:
NOTICE: Index ind1: NUMBER OF INDEX' TUPLES (2002) IS NOT THE SAME AS HEAP' (3003).
I've figured out the cause (or at least a cause) of this condition.
Consider a table having some data and indices, eg "onek" from the
regression tests:
regression=# vacuum verbose analyze onek;
NOTICE: --Relation onek--
NOTICE: Pages 24: Changed 0, reaped 1, Empty 0, New 0; Tup 1000: Vac 0, Keep/VTL 0/0, Crash 0, UnUsed 32, MinLen 180,
MaxLen180; Re-using: Free/Avail. Space 5988/0; EndEmpty/Avail. Pages 0/0. CPU 0.00s/0.11u sec.
NOTICE: Index onek_stringu1: Pages 28; Tuples 1000: Deleted 0. CPU 0.00s/0.01u sec.
NOTICE: Index onek_hundred: Pages 12; Tuples 1000: Deleted 0. CPU 0.00s/0.01u sec.
NOTICE: Index onek_unique2: Pages 18; Tuples 1000: Deleted 0. CPU 0.00s/0.02u sec.
NOTICE: Index onek_unique1: Pages 17; Tuples 1000: Deleted 0. CPU 0.00s/0.01u sec.
VACUUM
In a second psql, start up a transaction and leave it open:
regression=# begin;
BEGIN
regression=# select 1;?column?
---------- 1
(1 row)
regression=#
(It's necessary to actually select something so that the transaction
will get assigned an ID; "begin" alone won't do anything.)
Now return to the first psql and modify the table, doesn't matter how:
regression=# update onek set odd = odd+0;
UPDATE 1000
regression=#
At this point, onek contains 1000 committed updated tuples and 1000 dead
but not yet deleted tuples. Moreover, because we have an open
transaction that should see those dead tuples if it looks at the table
(at least if it's in SERIALIZABLE mode), VACUUM knows it should not
delete those tuples:
regression=# vacuum verbose analyze onek;
NOTICE: --Relation onek--
NOTICE: Pages 47: Changed 47, reaped 0, Empty 0, New 0; Tup 2000: Vac 0, Keep/VTL 1000/0, Crash 0, UnUsed 0, MinLen
180,MaxLen 180; Re-using: Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. CPU 0.00s/0.22u sec.
NOTICE: Index onek_stringu1: Pages 28; Tuples 2000. CPU 0.01s/0.02u sec.
NOTICE: Index onek_hundred: Pages 12; Tuples 2000. CPU 0.00s/0.02u sec.
NOTICE: Index onek_unique2: Pages 18; Tuples 2000. CPU 0.00s/0.01u sec.
NOTICE: Index onek_unique1: Pages 17; Tuples 2000. CPU 0.00s/0.01u sec.
VACUUM
But what if we create a new index while in this state?
regression=# create index toolate on onek(unique1);
CREATE
regression=# vacuum verbose analyze onek;
NOTICE: --Relation onek--
NOTICE: Pages 47: Changed 0, reaped 0, Empty 0, New 0; Tup 2000: Vac 0, Keep/VTL 1000/0, Crash 0, UnUsed 0, MinLen
180,MaxLen 180; Re-using: Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. CPU 0.01s/0.22u sec.
NOTICE: Index toolate: Pages 5; Tuples 1000. CPU 0.00s/0.01u sec.
NOTICE: Index toolate: NUMBER OF INDEX' TUPLES (1000) IS NOT THE SAME AS HEAP' (2000). Recreate the index.
NOTICE: Index onek_stringu1: Pages 28; Tuples 2000. CPU 0.00s/0.02u sec.
NOTICE: Index onek_hundred: Pages 12; Tuples 2000. CPU 0.00s/0.02u sec.
NOTICE: Index onek_unique2: Pages 18; Tuples 2000. CPU 0.01s/0.02u sec.
NOTICE: Index onek_unique1: Pages 17; Tuples 2000. CPU 0.00s/0.02u sec.
VACUUM
The CREATE INDEX operation has only bothered to index the non-dead
tuples. So, VACUUM's little sanity check fails.
I believe that this is not really a bug. If that old transaction came
along and tried to use the index to scan for tuples, then we'd have a
problem, because it'd fail to find tuples that it should have found.
BUT: if that old transaction is serializable, it won't even believe that
the index exists, not so? It can't see the index's entry in pg_class.
So I think CREATE INDEX's behavior is OK, and we just have an
insufficiently smart cross-check in VACUUM.
I am not sure if it is possible to make an exact cross-check at
reasonable cost. A recently created index might contain entries for
all, none, or just some of the committed-dead tuples in its table.
Depending on how old the oldest open transaction is, VACUUM might be
able to remove some but not all of those dead tuples. So in general I
don't see an easy way to cross-check the number of index tuples against
the number of table tuples exactly.
I am inclined to change the check to complain if there are more index
tuples than table tuples (that's surely wrong), or if there are fewer
index tuples than committed-live table tuples (ditto), but not to
complain if it's in between those limits. Comments?
regards, tom lane
> You'll probably recall reports of messages like this out of VACUUM: > NOTICE: Index ind1: NUMBER OF INDEX' TUPLES (2002) IS NOT THE SAME AS HEAP' (3003). > I've figured out the cause (or at least a cause) of this condition. > > I am inclined to change the check to complain if there are more index > tuples than table tuples (that's surely wrong), or if there are fewer > index tuples than committed-live table tuples (ditto), but not to > complain if it's in between those limits. Comments? Sounds good to me. I know I never considered such an interaction. -- Bruce Momjian | http://www.op.net/~candle pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
> -----Original Message----- > From: pgsql-hackers-owner@hub.org [mailto:pgsql-hackers-owner@hub.org]On > Behalf Of Tom Lane > > You'll probably recall reports of messages like this out of VACUUM: > NOTICE: Index ind1: NUMBER OF INDEX' TUPLES (2002) IS NOT THE > SAME AS HEAP' (3003). > I've figured out the cause (or at least a cause) of this condition. > > The CREATE INDEX operation has only bothered to index the non-dead > tuples. So, VACUUM's little sanity check fails. > Is it wrong to change the implementation of CREATE INDEX ? I have a fix. It needs the change of duplicate check(tuplesort->btbuild) and I've thougth that it would be better to change it after the release of 7.0. Regards. Hiroshi Inoue Inoue@tpf.co.jp
> > -----Original Message----- > > From: pgsql-hackers-owner@hub.org [mailto:pgsql-hackers-owner@hub.org]On > > Behalf Of Tom Lane > > > > You'll probably recall reports of messages like this out of VACUUM: > > NOTICE: Index ind1: NUMBER OF INDEX' TUPLES (2002) IS NOT THE > > SAME AS HEAP' (3003). > > I've figured out the cause (or at least a cause) of this condition. > > > > The CREATE INDEX operation has only bothered to index the non-dead > > tuples. So, VACUUM's little sanity check fails. > > > Is it wrong to change the implementation of CREATE INDEX ? > I have a fix. > It needs the change of duplicate check(tuplesort->btbuild) and > I've thougth that it would be better to change it after the release > of 7.0. Well, it seems we better do something about it before 7.0 is released. Now it seems we have to decide to change CREATE INDEX, or modify VACUUM. -- Bruce Momjian | http://www.op.net/~candle pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
> -----Original Message----- > From: Bruce Momjian [mailto:pgman@candle.pha.pa.us] > > > > -----Original Message----- > > > From: pgsql-hackers-owner@hub.org > [mailto:pgsql-hackers-owner@hub.org]On > > > Behalf Of Tom Lane > > > > > > You'll probably recall reports of messages like this out of VACUUM: > > > NOTICE: Index ind1: NUMBER OF INDEX' TUPLES (2002) IS NOT THE > > > SAME AS HEAP' (3003). > > > I've figured out the cause (or at least a cause) of this condition. > > > > > > The CREATE INDEX operation has only bothered to index the non-dead > > > tuples. So, VACUUM's little sanity check fails. > > > > > > Is it wrong to change the implementation of CREATE INDEX ? > > I have a fix. > > It needs the change of duplicate check(tuplesort->btbuild) and > > I've thougth that it would be better to change it after the release > > of 7.0. > > Well, it seems we better do something about it before 7.0 is released. > Now it seems we have to decide to change CREATE INDEX, or modify VACUUM. > It's difficult for me to provide a fix for CREATE INDEX before 7.0 is released. It's not sufficiently checked and I don't remember details now. I'm a little busy now and don't have enough time to look at it again. Regards. Hiroshi Inoue Inoue@tpf.co.jp
"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
>>>> Is it wrong to change the implementation of CREATE INDEX ?
>>>> I have a fix.
>>>> It needs the change of duplicate check(tuplesort->btbuild) and
>>>> I've thougth that it would be better to change it after the release
>>>> of 7.0.
>>
>> Well, it seems we better do something about it before 7.0 is released.
>> Now it seems we have to decide to change CREATE INDEX, or modify VACUUM.
> It's difficult for me to provide a fix for CREATE INDEX before 7.0 is
> released.
> It's not sufficiently checked and I don't remember details now.
Also, we'd need to change the other index access methods too. That
doesn't seem to me like a good thing to tackle a week before release...
regards, tom lane