Thread: function doesn't see change in search_path
I have a behaviour similar to this http://archives.postgresql.org/pgsql-bugs/2007-09/msg00017.php create language plpgsql; create schema test1; create schema test2; create table test1.a(a varchar(3) unique); create table test2.a(a varchar(3) unique); create or replace function test_insert() returns void as $$ begin raise notice 'path %', current_schemas(true); insert into a values('a'); end; $$ language plpgsql volatile; set search_path to 'test1', 'public'; select * from test_insert(); NOTICE: path {pg_catalog,test1,public} test_insert ------------- (1 row) set search_path to 'test2', 'public'; select * from test_insert(); NOTICE: path {pg_catalog,test2,public} ERROR: duplicate key value violates unique constraint "a_a_key" CONTEXT: SQL statement "insert into a values('a')" PL/pgSQL function "test_insert" line 3 at SQL statement PostgreSQL 8.3.14 what's going on? -- Ivan Sergio Borgonovo http://www.webthatworks.it
2011/11/7 Ivan Sergio Borgonovo <mail@webthatworks.it>: > On Mon, 7 Nov 2011 17:55:11 +0100 > Pavel Stehule <pavel.stehule@gmail.com> wrote: > >> Hello >> >> this is know bug/feature based on caching plans > > What puzzled me is I'm operating in a similar way in a different > system and I'm not experiencing the same problem. > > Do different users have different caches? depend on usage - cache is per session > What about different sessions? if you don't change a search_path inside session, then all will works well > >> There is workaround - you can put a copy of test_insert function to >> every schema - no to "public" schema. > > That's pretty ugly. I'll take the chance to refactor everything and > learn. yes, this is workaround - it's not nice > > Where can i learn about creation and invalidation of plans in > postgres documentation? > > BTW it looks to me you just answered to my address and not to the > list. If it was by mistake feel free to repost everything to the > list for other people's reference. > > thanks > > -- > Ivan Sergio Borgonovo > http://www.webthatworks.it > >
On Mon, 7 Nov 2011 19:07:29 +0100 Pavel Stehule <pavel.stehule@gmail.com> wrote: > 2011/11/7 Ivan Sergio Borgonovo <mail@webthatworks.it>: > > On Mon, 7 Nov 2011 17:55:11 +0100 > > Pavel Stehule <pavel.stehule@gmail.com> wrote: > > > >> Hello > >> > >> this is know bug/feature based on caching plans > > > > What puzzled me is I'm operating in a similar way in a different > > system and I'm not experiencing the same problem. > > > > Do different users have different caches? > > depend on usage - cache is per session OK. It is clear it is "per session". Up to my knowledge users can't be changed inside the same session. What are you referring to with "depend on usage". Is there any other thing that can influence cached plans? Right now I just need a workaround and calling the function in different sessions seems cleaner than writing a function for each schema especially since I can use psql \connect. It seems that cache management happens completely behind the scenes and there are no way to control it other than playing tricks as sql := 'select * from ' | sometable |... execute sql; I didn't find anything on cache other than what's written here http://developer.postgresql.org/pgdocs/postgres/plpgsql-implementation.html thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it
On 07/11/11 14:43, Ivan Sergio Borgonovo wrote: > > create or replace function test_insert() returns void as [snip] > $$ language plpgsql volatile; > > set search_path to 'test1', 'public'; > set search_path to 'test2', 'public'; [snip unexpected behaviour] I now try to add a SET search_path to the bottom of all my plpgsql functions. It can get very confusing otherwise, as you've just demonstrated. -- Richard Huxton Archonet Ltd