[postgis-devel] STABLE functions
strk at refractions.net
strk at refractions.net
Tue Feb 1 09:30:02 PST 2005
On Tue, Feb 01, 2005 at 09:18:13AM -0800, Chris Hodgson wrote:
> 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...
Actually exploiting the problem is harder then that.
It takes a plpgsql function using 'find_srid()'.
PostGIS currently provides none.
--strk;
>
> 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
> >
> >
>
> _______________________________________________
> 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