Re: search_path for PL/pgSQL functions partially cached?

Hi

> >
> > 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:
>
> https://www.postgresql.org/docs/17/sql-discard.html
>
> 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.

> Regards,
> Jan
>