Em Quinta 25 Maio 2006 19:27, Jim Nasby escreveu:
>
> Yes. Views essentially end up with schemas hard-coded into them. If
> that doesn't work you should be able to create views on set returning
> functions, though that's obviously more work.
>
> I don't know how hard it would be to allow views to become
> search_path aware on execution, or if such a change would be accepted.
>
> Ultimately though, why is this a problem? Aren't you defining all the
> views in their appropriate schema?
Yes, this is a problem because I have to create the views on each schema. If
I could just use search_path, then I would end up with only one instance of
each view and function and they'd do the right thing using data from that
particular schema.
As they don't respect the search_path, then I have to create n+1 copies of
each view/function, one for each schema and one for the base schema. This
looks like inneficient because if I need to change the view, I'll have to
change n+1 views instead of just one. The same applies to functions :-(
I confess that I expected it to respect the search_path.
> This is due to query plan caching. If you grab a new connection every
> time you switch companies it won't be an issue. There's also been
> talk of adding the ability to 'reset' a connection, but I don't
> remember the status of that or if it would reset the query plan cache.
Making them respect the search_path would be also nice. I thought that a
VOLATILE functions had no cache, even for the query plan.
One last try... Languages other than plpgsql doesn't reuse / save the query
plan by default, right? So if I switch to, e.g., plpythonu I wouldn't,
theoretically, have this problem when running functions, right?
It turns out that this won't work in an easy way in a standard installation of
PostgreSQL... :-( Unfortunately. It would save a lot of code, a lot of
redundant definitions and would make life a lot easier to manage the
database.
If we can solve the problem with functions by using a language other than
plpgsql (and of course sql), then we'd need to profile and try using another
language to write our functions and replace views with set returning
functions... This isn't all that pretty, but instead of changing 1000
instances of each view and each function that needs some modification I
prefer changing one function that doesn't save the query plan (if possible at
all, of course).
Thanks again,
--
Jorge Godoy <jgodoy@gmail.com>