> > some times can be pretty ineffective to have database per customer - more > connect, disconnect in postgres is much more expensive than SET search_path > TO .. and maybe RESET plans;
I guess that means there is a practical application where search_path MAY change at runtime IF done in different sessions or if the cache is reset using the DISCARD command:
I assume DISCARD PLANS would be the right command?
that depends. plan inside plan cache is invalidated when search_path is different. You use RESET plans because you want to release all plans quickly.
Unfortunately, the types assigned to plpgsql variables are not invalidated. This is the source of problems. It is a classical problem - it is hard to say when you should invalidate cache.
Current design is not ideal - but it is almost a good enough compromise between correctness and performance. It is true, so nobody did some work to fix it. So maybe the impact to performance should not be too bad, but it is not an easy issue. plans are isolated - and the impact of one plan to the second plan is zero. For variables it is exactly opposite.
This seems to be a very special case though. I think there should be a warning in the documentation of CREATE FUNCTION with regard to schemas anyway, though.
I am not sure. If you want to use this warning, then it should be everywhere where any non-qualified identifier can be used. Maybe in plpgsql can be more accented so almost everything in plpgsql depends on the current setting of search_path. Lot of people don't understand, so every expression in plpgsql is SQL and every expression is executed like part of a query. And unfortunately there are some different caches - plpgsql cache and plan cache and both caches are invalidated at different times (I think so plpgsql cache is not resetted by RESET PLANS). Maybe it is better to explain how plpgsql works. It is a little bit different from well known interpreted languages.
