polygon insert into r-tree sometimes fails - Mailing list pgsql-bugs

Jim Stone (jmstone@ucdavis.edu) reports a bug with a severity of 1
The lower the number the more severe it is.

Short Description
polygon insert into r-tree sometimes fails

Long Description
I'm always hesitant to submit a bug report--it's usually something I
[did | didn't do]--but I'm beginning to wonder if maybe I have hit
one. If I have, it's a show-stopper for us--we need r-trees and
polygons.

I'm using postgres as the underlying db for a neuroanatomical
application with some functionality similar to a GIS. To provide
lookups for 2d information I'm using r-tree indexes containing
polygon information. Some of the polygons are fairly large, but all
fit well within the 8k page limit.

I'm in the midst of upgrading from 6.3.2 to 7.0.3 and hit a snag:
it seems as if some inserts into the r-tree silently fail. The outputs
from vacuum tells the tale:

1. 145 inserts into both versions
2. 7.0.3 r-tree index (polygonindextest_poly_idx) has only 122
   entries, , but requires 88 pages for them
3. 6.3.2 r-tree index (again, polygonindextest_poly_idx) has all 145
   entries, using just 15 pages

Dropping/recreating the index does not improve matters--same results.
Examining the output during insertion reveals no errors; however, if a
vacuum is done after each insertion (NOT the way I normally do
things), it's obvious that *some* insertions (not all) into the r-tree
fail when an r-tree page splits.

Does anyone have any ideas? Does anyone want more info? I didn't want
to post the script that I used because the first index insertion fails
at the 26th--the script is kinda big, but it is self-contained.

Platforms:
----------
7.0.3: RedHat Linux 6.2; kernel 2.2.16; dual 800 MHz PIII; 256 MB ram
6.3.2: Redhat Linux 5.2; kernel 2.0.35; single 350 MHz PIII; 128 MB ram

Thanks for any help/pointers,

VACUUM output follows:


From 7.0.3 VACUUM:
-----------
NOTICE:  --Relation polygonindextest--
NOTICE:  Pages 15: Changed 1, reaped 0, Empty 0, New 0; Tup 145:
                                                            ^^^
     Vac 0, Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 228,
     MaxLen 2564; Re-using: Free/Avail. Space 0/0;
     EndEmpty/Avail. Pages 0/0. CPU 0.00s/0.00u sec.
NOTICE:  Index polygonindextest_idx: Pages 4; Tuples 145.
     CPU 0.00s/0.00u sec.
NOTICE:  Index polygonindextest_box_idx: Pages 1; Tuples 145.
     CPU 0.00s/0.00u sec.
NOTICE:  Index polygonindextest_poly_idx: Pages 88; Tuples 112.
                                                ^^         ^^^
     CPU 0.00s/0.01u sec.
NOTICE:  Index polygonindextest_poly_idx: NUMBER OF INDEX' TUPLES (112)
     IS NOT THE SAME AS HEAP' (145).
        Recreate the index.
VACUUM


From 6.3.2 VACUUM:
----------------------
NOTICE:  Rel polygonindextest: Pages 15: Changed 1, Reapped 0,
     Empty 0, New 0; Tup 145: Vac 0, Crash 0, UnUsed 0,
                             ^^^
     MinLen 240, MaxLen 2576; Re-using: Free/Avail. Space 0/0;
     EndEmpty/Avail. Pages 0/0. Elapsed 0/0 sec.
NOTICE:  Ind polygonindextest_idx: Pages 4; Tuples 145.
     Elapsed 0/0 sec.
NOTICE:  Ind polygonindextest_box_idx: Pages 1; Tuples 145.
     Elapsed 0/0 sec.
NOTICE:  Ind polygonindextest_poly_idx: Pages 15; Tuples 145.
                                          ^^         ^^^
     Elapsed 0/0 sec.
VACUUM


Sample Code


No file was uploaded with this report