Re: [GENERAL] function doesn't see change in search_path

2011-11-07 Thread Richard Huxton 

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] function doesn't see change in search_path

2011-11-07 Thread Ivan Sergio Borgonovo 
On Mon, 7 Nov 2011 19:07:29 +0100
Pavel Stehule  wrote:

> 2011/11/7 Ivan Sergio Borgonovo :
> > On Mon, 7 Nov 2011 17:55:11 +0100
> > Pavel Stehule  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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] function doesn't see change in search_path

2011-11-07 Thread Pavel Stehule 
2011/11/7 Ivan Sergio Borgonovo :
> On Mon, 7 Nov 2011 17:55:11 +0100
> Pavel Stehule  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
>
>

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] function doesn't see change in search_path

2011-11-07 Thread Ivan Sergio Borgonovo 
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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general