<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.2900.2604" name=GENERATOR></HEAD>
<BODY>
<DIV><SPAN class=076470510-09032005><FONT face=Arial size=2>Hi
folks,</FONT></SPAN></DIV>
<DIV><SPAN class=076470510-09032005><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=076470510-09032005><FONT face=Arial size=2>We have a problem
when running pgsql2shp on a large dataset via the following query (as
a view):</FONT></SPAN></DIV>
<DIV><SPAN class=076470510-09032005><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=076470510-09032005><FONT face=Arial size=2> SELECT
crs_street_address.house_number AS house_no, crs_street_address.status,
crs_road_name."type", crs_road_name.name, crs_road_name."location",
transform(translate(crs_street_address.shape, 160::double precision, 0::double
precision, 0::double precision), 27200) AS point<BR> FROM
crs_street_address<BR> JOIN crs_road_name ON
crs_street_address.rna_id = crs_road_name.id;<BR></FONT></SPAN></DIV>
<DIV><SPAN class=076470510-09032005><FONT face=Arial size=2>QUERY PLAN<BR>Merge
Join (cost=0.00..83997.59 rows=1506893 width=86)<BR> Merge Cond:
("outer".id = "inner".rna_id)<BR> -> Index Scan using
crs_road_name_id_idx on crs_road_name (cost=0.00..1854.48 rows=68168
width=51)<BR> -> Index Scan using crs_street_address_rna_id_idx
on crs_street_address (cost=0.00..55602.24 rows=1506893
width=43)<BR></FONT></SPAN></DIV>
<DIV><SPAN class=076470510-09032005><FONT face=Arial size=2>The job runs for
quite some time, but eventually will output the following:</FONT></SPAN></DIV>
<DIV><SPAN class=076470510-09032005><FONT face=Arial size=2>Initializing...
GeometryType: ERROR: out of memory<BR>DETAIL: Failed on request of
size 63.<BR>CONTEXT: SQL function "get_proj4_from_srid" statement
1<BR>PL/pgSQL function "transform" line 2 at return<BR></FONT></SPAN></DIV>
<DIV><SPAN class=076470510-09032005><FONT face=Arial size=2>This is running on
FreeBSD 5.3 with 2Gb ram and as I watch memory utilisation, it is not using any
swap at all. So it doesn't appear to be a traditional memory leak.
Also we have other querys that work find on a smaller number of
rows.</FONT></SPAN></DIV>
<DIV><SPAN class=076470510-09032005><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=076470510-09032005><FONT face=Arial size=2>Any suggestions
appreciated.</FONT></SPAN></DIV>
<DIV><SPAN class=076470510-09032005><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=076470510-09032005><FONT face=Arial
size=2>Bryce.</DIV></FONT></SPAN></BODY></HTML>