On 12 March 2012 16:32, Robert Haas <robertmh...@gmail.com> wrote: > On Mon, Mar 12, 2012 at 11:16 AM, Tom Lane <t...@sss.pgh.pa.us> wrote: > > Robert Haas <robertmh...@gmail.com> writes: > >> On Mon, Mar 5, 2012 at 6:52 AM, <rene.vanpaas...@gmail.com> wrote: > >>> I found some unexpected behaviour when changing the schema search path > in > >>> combination with plpgsql functions (may be true for other function > types > >>> too, did not check). This occurs both in 9.1.2 (on Fedora, 64 bit) and > 8.4.9 > >>> (Centos 6, 32 bit). I created a small example run with psql, to > demonstrate > >>> this. > > > >> I have a vague feeling this is a known issue. It sure seems like we > >> should handle it better, but I'm not sure how hard that would be to > >> implement. > > > > plpgsql intentionally caches the plan for the query as it was built with > > the original search_path. There's been talk of adjusting that behavior > > but I'm worried that we might break as many cases as we fix ... > > IMHO, the problem with the current behavior is that it's neither all > one thing nor all the other. Using the definition-time search_path > seems defensible, and using the run-time search_path does, too. But > we're not consistently doing either one, which doesn't seem good. > > Isn't this what the VOLATILE, STABLE and IMMUTABLE keywords should be for? I don't like the current behaviour, because now VOLATILE is not volatile, unless you close and re-open the database connection. There should at least be a big fat warning about combining functions with changing search path somewhere in the documentation.
Implementation-wise (but I have to admit I don't know the underlying code at all), would it be possible to cache with the search_path as an index? > -- > Robert Haas > EnterpriseDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company > -- René van Paassen <rene.vanpaas...@gmail.com>
