[postgis-devel] error with GIST indexes with NULLs

[Mark Cave-Ayland]

On Thu, 2006-12-14 at 10:59 -0800, Kevin Neufeld wrote:
> 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


Hi Kevin,

Confirmed. I've had a quick look at this with a debugger, and it appears
that the culprit is BOX2D_union calling PG_RETURN_NULL, which causes
DirectFunctionCall2 to error out in fmgr.c.


Here's the backtrace from my system below:

(gdb) bt
#0  errstart (elevel=20, filename=0x1 <Address 0x1 out of bounds>,
lineno=1,
    funcname=0x82e17ae "DirectFunctionCall2") at elog.c:251
#1  0x0823d865 in elog_finish (elevel=1,
    fmt=0x82e9cae "function %p returned NULL") at elog.c:920
#2  0x0823ff7b in DirectFunctionCall2 (func=0x41ec96d7 <BOX2D_union>,
arg1=1,
    arg2=1) at fmgr.c:884
#3  0x41ec53ed in LWGEOM_gist_penalty (fcinfo=0x1) at lwgeom_gist.c:955
#4  0x08240761 in FunctionCall3 (flinfo=0x0, arg1=1, arg2=1, arg3=1)
    at fmgr.c:1179
#5  0x0808105e in gistpenalty (giststate=0xbfd62f50, attno=1, orig=0x1,
    isNullOrig=24 '\030', add=0x1, isNullAdd=1 '\001') at gistutil.c:534
#6  0x08081425 in gistchoose (r=0x41e8e040, p=0x4060f200 "", it=0x1,
    giststate=0xbfd62f50) at gistutil.c:420
#7  0x080804a0 in gistdoinsert (r=0x41e8e040, itup=<value optimized
out>,
    freespace=819, giststate=0xbfd62f50) at gist.c:579
#8  0x08080a9a in gistbuildCallback (index=0x41e8e040, htup=0x83ec7f0,
    values=0x1, isnull=0x1 <Address 0x1 out of bounds>, tupleIsAlive=1
'\001',
    state=0xbfd62f50) at gist.c:201
#9  0x080c8f5d in IndexBuildHeapScan (heapRelation=0x41e8c930,
    indexRelation=0x41e8e040, indexInfo=0x83ddee0,
    callback=0x8080a03 <gistbuildCallback>, callback_state=0xbfd62f50)
    at index.c:1535
#10 0x080808f9 in gistbuild (fcinfo=0x1) at gist.c:150
#11 0x08240f0b in OidFunctionCall3 (functionId=1, arg1=1, arg2=1,
arg3=1)
    at fmgr.c:1460
#12 0x080c74e0 in index_build (heapRelation=0x41e8c930,
    indexRelation=0x41e8e040, indexInfo=0x1, isprimary=0 '\0') at
index.c:1283
#13 0x080c826e in index_create (heapRelationId=16803,
    indexRelationName=0x83b5688 "test2_geom_idx", indexRelationId=16813,
    indexInfo=0x83ddee0, accessMethodObjectId=1, tableSpaceId=0,
    classObjectId=0x83dded0, reloptions=1, isprimary=0 '\0',
    isconstraint=0 '\0', allow_system_table_mods=0 '\0', skip_build=0
'\0',
    concurrent=0 '\0') at index.c:784
#14 0x0811778c in DefineIndex (heapRelation=0x83b56b0,
    indexRelationName=0x83b5688 "test2_geom_idx", indexRelationId=0,
    accessMethodName=0x83b56d8 "gist", tableSpaceName=0x0,
    attributeList=0x83b5728, predicate=0x0, rangetable=0x0, options=0x1,
    unique=0 '\0', primary=0 '\0', isconstraint=0 '\0', is_alter_table=0
'\0',
    check_rights=1 '\001', skip_build=0 '\0', quiet=0 '\0', concurrent=0
'\0')
    at indexcmds.c:439
#15 0x081caeb4 in ProcessUtility (parsetree=0x83b5740, params=0x0,
    dest=0x83b55b8, completionTag=0xbfd64b0a "") at utility.c:789
#16 0x081c8a71 in PortalRunUtility (portal=0x83d3e88, query=0x83b57b0,
    dest=0x83b55b8, completionTag=0xbfd64b0a "") at pquery.c:1063
#17 0x081c90cf in PortalRunMulti (portal=0x83d3e88, dest=0xbfd64a18,
    altdest=0x83b55b8, completionTag=0xbfd64b0a "") at pquery.c:1131
#18 0x081c97e7 in PortalRun (portal=0x83d3e88, count=2147483647,
    dest=0x83b55b8, altdest=0x83b55b8, completionTag=0xbfd64b0a "")
    at pquery.c:700
#19 0x081c51e9 in exec_simple_query (
    query_string=0x83b53a8 "CREATE index test2_geom_idx ON test2 USING
gist (the_Geom);") at postgres.c:939
#20 0x081c5de1 in PostgresMain (argc=4, argv=<value optimized out>,
    username=0x835bf60 "pg82") at postgres.c:3419
#21 0x0819f777 in ServerLoop () at postmaster.c:2926
#22 0x081a04cb in PostmasterMain (argc=3, argv=0x8344288) at
postmaster.c:958
#23 0x08162215 in main (argc=3, argv=0xbfd66004) at main.c:188
(gdb)


I have no idea why the behaviour should be different under PostgreSQL
8.1 but unfortunately I'm not going to be able to have the time to look
at this for another week or so due to other commitments...


Kind regards,

Mark.