[postgis-devel] Query causes Postgres to crash

[collin]

 >
 > Also, a wrong estimation should no justify the crash.
 > I suggest Collin estimates a single field update and show us
 > the result.
 >
 > --strk;


Hi strk, Mark,

I have installed postgresql 8beta5 on my laptop to compare with 
postgresql 8beta4 on my server and have loaded the same tables. 
Unfortunately, the crash seems to have corrupted my PG8,0beta4 database, 
so it is going to take me a little while to recreate the database and 
load the tables.

I recompiled postgis with debug defined on my server, but I am not sure 
how to take advantage of that (my programming skills aren't extensive).

I performed the following query on thePG8beta5 version. I'll post the 
beta4 as soon as I have it.

EXPLAIN ANALYZE UPDATE grid10m SET
lzmin = (SELECT min(lz) from unfilteredPTS WHERE lreturn  && the_geom);

result from PG8.0beta5: 
                    QUERY PLAN
---------------------------------------------------------------------
  Seq Scan on grid10m_box1x1  (cost=0.00..54224.49 rows=1000 width=210) 
(actual time=188.663..1229350.209 rows=11236 loops=1)
    SubPlan
      ->  Aggregate  (cost=54.20..54.20 rows=1 width=8) (actual 
time=109.262..109.263 rows=1 loops=11236)
            ->  Index Scan using gist_unfiltered_box1x1 on 
unfiltered_box1x1  (cost=0.00..54.17 rows=13 width=8) (actual 
time=8.587..108.870 rows=263 loops=11236)
                  Index Cond: (lreturn && $0)
  Total runtime: 1232608.251 ms
(6 rows)