exit code -1073741819 - Mailing list pgsql-bugs

Hi, All,

I'm working on a GIS project using PostgreSQL and PostGIS. In the project I need to find locations of about 12K
addresses(the process is referred to as geocoding). I wrote some script to perform this task by calling a procedure
"tiger_geocoding"that is provided by PostGIS. My script seems to crash the server after a while with the following
message:


server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
connection to server was lost


The log shows the following message:


CurTransactionContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExecutorState: 122880 total in 4 blocks; 1912 free (9 chunks); 120968 used
ExprContext: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
ExprContext: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
ExprContext: 8192 total in 1 blocks; 8000 free (1 chunks); 192 used
ExprContext: 8192 total in 1 blocks; 8000 free (1 chunks); 192 used
ExprContext: 8192 total in 1 blocks; 8096 free (1 chunks); 96 used
SPI Exec: 0 total in 0 blocks; 0 free (0 chunks); 0 used
SPI Proc: 8192 total in 1 blocks; 2616 free (0 chunks); 5576 used
ExecutorState: 57344 total in 3 blocks; 35776 free (7 chunks); 21568 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
2007-07-10 12:25:57 LOG: server process (PID 2004) exited with exit code -1073741819
2007-07-10 12:25:57 LOG: terminating any other active server processes
2007-07-10 12:25:59 WARNING: terminating connection because of crash of another server process


My script is listed below:

CREATE OR REPLACE FUNCTION geocode_fall2006 () RETURNS VOID AS $$
DECLARE
     address VARCHAR(255);
     geom GEOMETRY;
     tmp_geom GEOMETRY;
     counter INTEGER := 0;
     app_id VARCHAR(50);
     st VARCHAR(50);
     f_processed BOOLEAN;
     f_geocoded BOOLEAN;
BEGIN
     FOR app_id, address, st, f_processed, f_geocoded IN SELECT APPLICATION_ID, add, state, geoprocessed, geocoded FROM
fall2006LOOP 
          RAISE NOTICE 'add=%, app_id=%, state=%', address, app_id, st;
          IF upper(st)='OH' AND f_processed='f'
          THEN
               geom := geocode_test(address);
               IF geom IS NOT NULL
               THEN
                    counter := counter + 1;
                    -- TIGER SRID is 32767.
                    tmp_geom := PointFromText('POINT(' || X(geom) || ' ' || Y(geom) || ')',4269);
                    tmp_geom := transform(tmp_geom,4326);
                    -- id := SRID(tmp_geom);
                    -- RAISE NOTICE 'srid=%', id;
                    UPDATE fall2006 SET lat_lon = tmp_geom WHERE APPLICATION_ID = app_id;
                    UPDATE fall2006 SET geocoded = 't' WHERE APPLICATION_ID = app_id;
                    RAISE NOTICE 'UPDATE fall2006 SET lat_lon = % WHERE APPLICATION_ID = %;', AsText(tmp_geom), app_id;
               END IF;
          UPDATE fall2006 SET geoprocessed = 't' WHERE APPLICATION_ID = app_id;
          END IF;
     END LOOP;
     RAISE NOTICE 'counter=%', counter;
END;
$$ LANGUAGE plpgsql


I googled and found a similar bug was reported for version 8.1 and was claimed to be fixed
(http://archives.postgresql.org/pgsql-bugs/2006-12/msg00214.php).However, the PostgreSQL in my machine is 8.2.4, which
issupposed to be free of the bug. Any suggestion will be greatly appreciated. 




_______________________________________________
Join Excite! - http://www.excite.com
The most personalized portal on the Web!