[postgis-devel] error with GIST indexes with NULLs

[Kevin Neufeld]

Hi all,

Apparently, in PostgreSQL 8.2, one cannot have more than 459 null 
geometries in a spatial table before the gist index creation fails. Has 
anyone else seen this?
Note: this test works fine with 8.1.

-- Successful test USING GIST
mydb=# CREATE TABLE test1 (the_geom geometry);
CREATE TABLE
mydb=# INSERT INTO test1 SELECT null FROM generate_series(1,459);
INSERT 0 459
mydb=# CREATE index test1_geom_idx ON test1 USING gist (the_Geom);
CREATE INDEX

-- NON successful test USING GIST
mydb=# CREATE TABLE test2 (the_geom geometry);
CREATE TABLE
mydb=# INSERT INTO test2 SELECT null FROM generate_series(1,460);
INSERT 0 460
mydb=# CREATE index test2_geom_idx ON test2 USING gist (the_Geom);
ERROR:  function 0x71be24 returned NULL


-- Successful test USING BTREE GIST
mydb=# CREATE TABLE test3 (i integer);
CREATE TABLE
mydb=# INSERT INTO test3 SELECT null FROM generate_series(1, 460);
INSERT 0 460
mydb=# CREATE index test3_idx ON test3 USING gist (i);
CREATE INDEX


mydb=# select version();
                                              
version                                             
---------------------------------------------------------------------------------------------------
 PostgreSQL 8.2.0 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.4 
20050721 (Red Hat 3.4.4-2)
(1 row)

mydb=# select postgis_full_version();
                               
postgis_full_version                              
----------------------------------------------------------------------------------
 POSTGIS="1.1.6" GEOS="3.0.0-CAPI-1.0.1" PROJ="Rel. 4.4.9, 29 Oct 2004" 
USE_STATS
(1 row)



Cheers,
Kevin