Re: Schema Search Path Problem

Hello

2013/10/10 Kalai R <softlinne(dot)kv(at)gmail(dot)com>

> Hi,
>
> I am developing .Net application using PSQL. I am using npgsql to connect
> PSQL database. In single database I have more than one schemas. After I
> connect database I set search path using "Set Searchpath command" for the
> required schema. It works perfectly.
>
> But after sometime, I need to change schema. so I set again search path
> using "Set Searchpath command" . Now if I execute a single query the
> changed search path works correctly. But if I execute a pl/sql function
> written in background the changed search path does not work.
>

It is expected behave for older releases and it is fixed in 9.3. PL/pgSQL
procedures holds a execution plans in session plan cache and changing
search_path didn't invalidate caches. There are two workarounds: a) using
special instances of functions for schema, b) change search_path only once
after connect, and if you need a new search_path, then disconnect, and
create new connect.

Regards

Pavel Stehule

>
> Help me to solve this issue.
>
> Thanks.
>