[postgis-devel] STABLE functions

Chris Hodgson chodgson at refractions.net
Tue Feb 1 09:18:13 PST 2005


Yes, mapserver does use find_srid(), unless you specify the srid to use 
with the "USING SRID=#" clause at the end of the "DATA" statement in the 
mapfile. So, I guess that means that if you had mapserver running in 
fastcgi mode with a cached postgres connection, and updated an SRID in 
the geometry_columns table, then the old one would be cached and used 
instead of the new? I think that is a rare enough use case to be 
acceptable...

Chris

strk at refractions.net wrote:

>I've understood that functions marked STABLE will cache
>the PLAN but not the OUTPUT. STABLE functions are those
>which needs to be run for their side-effects.
>
>IMMUTABLE functions, on the other end, will cache the
>OUTPUT but only as long as the PLAN is alive.
>Between queries IMMUTABLE output cache dies, unless
>a plpgsql function invokes those functions. In this
>case the plpgsql engine will keep the PLAN alive
>thus keeping the output alive as well.
>
>I've checked and we have no plpgsql function directly
>invoking the find_srid function.
>
>Altought the correct form would be using STABLE, to
>conform with PG<800 and not loose performance I've
>made find_srid back IMMUTABLE, and also did this
>for transform().
>
>The effects of IMMUTABLE transform() could be tricky
>as transform() would call  get_proj4_from_srid() thus
>caching its output (it is also be made IMMUTABLE).
>This means that if you change a proj text of a SRID
>you'll need to open a new session or you'll get any
>cached previous output from transform() calls.
>
>Note that this behaviour is NOT correct, so would be better
>to avoid it. But we got used to it as it has been incorrect
>for a long time [ with(isstict) ].
>
>Does mapserver use find_srid() call in its fetch ?
>
>--strk;
>
>
>On Tue, Feb 01, 2005 at 12:38:11PM +0100, strk wrote:
>  
>
>>Hello all, I saw that STABLE-defined functions
>>don't get replaced by their output, shoudn't they ?
>>
>>Following shows that VOLATILE and STABLE functions outputs
>>won't get into the Filter, while IMMUTABLE will.
>>
>>Documentation says that STABLE is the modifier to use for functions
>>which don't change output within a single query, isn't the shown one
>>a "single" query ?
>>
>>Thanks in advance
>>
>>--strk;
>>
>>-- VOLATILE
>>update pg_proc set provolatile = 'v' where proname = 'find_srid';
>>UPDATE 1
>>explain analyze select * from input.geobit_5 where the_geom && setsrid('BOX3D(.7e+06 4.6e+06, .8e+06 4.83e+06)'::box3d, find_srid('input','geobit_5','the_geom'));
>>                                                                                                                                                                  QUERY PLAN                                                                                                                                                                  
>>----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>> Seq Scan on geobit_5  (cost=0.00..1708.79 rows=1 width=379) (actual time=586.979..1099.565 rows=255 loops=1)
>>   Filter: (the_geom && setsrid('0103000000010000000500000000000000C05C254100000000308C514100000000C05C254100000000CC6C524100000000006A284100000000CC6C524100000000006A284100000000308C514100000000C05C254100000000308C5141'::geometry, find_srid('input'::character varying, 'geobit_5'::character varying, 'the_geom'::character varying)))
>> Total runtime: 1099.989 ms
>>(3 rows)
>>
>>-- STABLE
>>update pg_proc set provolatile = 's' where proname = 'find_srid';
>>UPDATE 1
>>explain analyze select * from input.geobit_5 where the_geom && setsrid('BOX3D(.7e+06 4.6e+06, .8e+06 4.83e+06)'::box3d, find_srid('input','geobit_5','the_geom'));
>>                                                                                                                                                                    QUERY PLAN                                                                                                                                                                    
>>--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>> Index Scan using geobit_5_gist on geobit_5  (cost=0.00..6.02 rows=1 width=379) (actual time=2.084..42.157 rows=255 loops=1)
>>   Index Cond: (the_geom && setsrid('0103000000010000000500000000000000C05C254100000000308C514100000000C05C254100000000CC6C524100000000006A284100000000CC6C524100000000006A284100000000308C514100000000C05C254100000000308C5141'::geometry, find_srid('input'::character varying, 'geobit_5'::character varying, 'the_geom'::character varying)))
>> Total runtime: 42.835 ms
>>(3 rows)
>>
>>-- IMMUTABLE
>>update pg_proc set provolatile = 'i' where proname = 'find_srid';
>>UPDATE 1
>>explain analyze select * from input.geobit_5 where the_geom && setsrid('BOX3D(.7e+06 4.6e+06, .8e+06 4.83e+06)'::box3d, find_srid('input','geobit_5','the_geom'));
>>                                                                                                                 QUERY PLAN                                                                                                                 
>>--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>> Index Scan using geobit_5_gist on geobit_5  (cost=0.00..1095.52 rows=298 width=379) (actual time=0.127..18.010 rows=255 loops=1)
>>   Index Cond: (the_geom && '0103000020787F0000010000000500000000000000C05C254100000000308C514100000000C05C254100000000CC6C524100000000006A284100000000CC6C524100000000006A284100000000308C514100000000C05C254100000000308C5141'::geometry)
>> Total runtime: 18.276 ms
>>(3 rows)
>>_______________________________________________
>>postgis-devel mailing list
>>postgis-devel at postgis.refractions.net
>>http://postgis.refractions.net/mailman/listinfo/postgis-devel
>>    
>>
>_______________________________________________
>postgis-devel mailing list
>postgis-devel at postgis.refractions.net
>http://postgis.refractions.net/mailman/listinfo/postgis-devel
>  
>




More information about the postgis-devel mailing list