[postgis-devel] Re: [postgis-users] Memory leak in translate function in RC3

[strk at refractions.net]

This seems to fix the problem:

---------------------------------------------------------------

CREATE OR REPLACE FUNCTION get_proj4_from_srid(integer) RETURNS text AS
'
BEGIN
        RETURN proj4text::text FROM spatial_ref_sys WHERE srid = $1;
END
'
LANGUAGE 'plpgsql' IMMUTABLE STRICT; 

---------------------------------------------------------------

Urgh! I didn't suspect these ugly effects of 'SQL' functions
compared to 'PL/PGSQL' ones... I'll ask on pgsql-hackers..

For the short term I guess we should make the change of all 'SQL'
functions into 'PL/PGSQL' ... changing SCRIPT_VERSION from 0.2.1
to 0.3.0.

Comments ?

--strk;


On Wed, Mar 16, 2005 at 12:46:07PM +0100, strk at refractions.net wrote:
> Please let's move this discussion on postgis-devel.
> 
> I've found a solution and what seems to be a postgresql bug.
> 
> Our transform() function is a plpgsql wrapper to transform_geometry()
> which is a C function.
> 
> The C function (transform_geometry) takes 4 args:
> 	geometry
> 	input_proj4_text
> 	output_proj4_text
> 	output_srid
> 
> The wrapper extracts *_proj4_text invoking another "internal" function:
> get_proj4_from_srid(). This is an 'sql' function:
> 
>   CREATE OR REPLACE FUNCTION get_proj4_from_srid(integer) RETURNS text AS
>      'SELECT proj4text::text FROM spatial_ref_sys WHERE srid= $1'
>      LANGUAGE 'sql' IMMUTABLE STRICT; 
> 
> If I replace:
> 	 transform(setSRID(geom, X), Y)
> 
> with:
> 
> 	transform_geometry(geom, get_proj4_from_srid(X),
> 		get_proj4_from_srid(Y), Y)
> 
> The size keeps growing as before. Buf if I replace get_proj4_from_srid()
> calls with their output memory keeps low:
> 
> 	transform_geometry(geom, X_PROJ4_TEXT, Y_PROJ4_TEXT, Y)
> 
> Bryce, can you confirm this ?
> 
> --strk;
> 
> 	
> On Wed, Mar 16, 2005 at 12:28:12PM +0100, strk at refractions.net wrote:
> > Bryce, I've gone through the code and enforced some other early memory
> > release. I can't see any real leak though...
> > 
> > Can you send the output of explain analyze for the query ?
> > 
> > Also, 268435456 is a BIG number for a single alloc...
> > maybe it's the postgres buffer growing ?
> > 
> > Can you also check sum(mem_size(shape)) from crs_feature_name; ?
> > 
> > --strk;
> > 
> > On Wed, Mar 16, 2005 at 05:42:54PM +1300, Bryce Watkins wrote:
> > > Hi strk,
> > >  
> > > I have defiantly found a leak this time :-) in that if I run the following
> > > query I get out of memory error, and also watching the postgresql process on
> > > the box shows the memory consumption continually increasing over time,
> > > whilst the query is running.
> > >  
> > > SELECT crs_feature_name.id, crs_feature_name."type", crs_feature_name.name,
> > > crs_feature_name.status, crs_feature_name.other_details,
> > > astext(translate(crs_feature_name.shape, 160, 0, 0)) AS shape
> > > FROM crs_feature_name
> > > inner join crs_parcel on crs_feature_name.id = crs_parcel.fen_id
> > > limit 100;
> > >  
> > > Translate seems to work fine if it's used on its own, but when combining
> > > with astext or transform then this is the output (from astext combination
> > > anyway).
> > > ERROR:  out of memory
> > > DETAIL:  Failed on request of size 268435456.
> > > 
> > > This has only shown up since RC2 as far as I can tell.
> > >  
> > > Cheers,
> > > Bryce.
> > 
> > > _______________________________________________
> > > postgis-users mailing list
> > > postgis-users at postgis.refractions.net
> > > http://postgis.refractions.net/mailman/listinfo/postgis-users
> > 
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-devel