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 fall2006 LOOP 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 is supposed 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! ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq