Обсуждение: Schema Search Path Problem

Поиск
Список
Период
Сортировка

Schema Search Path Problem

От
Kalai R
Дата:
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.

Help me to solve this issue.

Thanks.

Re: Schema Search Path Problem

От
Pavel Stehule
Дата:
Hello


2013/10/10 Kalai R <softlinne.kv@gmail.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.