[postgis-devel] STABLE functions

[strk at refractions.net]

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