Re: [GENERAL] plpgsql function confusing behaviour

2011-07-13 Thread Shianmiin 

Merlin Moncure-2 wrote:
 
 One proposed solution is to cache plpgsql plans around the search path.  
 

I like the proposed solution, since search_path plays a part when generating
plpgsql plan, it make sense to be part of the cache.


Merlin Moncure-2 wrote:
 
 *) use sql functions for portions that float across schemas 
 

Just to clarify, does this mean the sql functions doesn't cache plans like
plpgsql functions do?



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/plpgsql-function-confusing-behaviour-tp4576354p4579619.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

-- 
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] plpgsql function confusing behaviour

2011-07-13 Thread Merlin Moncure 
On Tue, Jul 12, 2011 at 12:10 PM, Shianmiin shianm...@gmail.com wrote:

 Merlin Moncure-2 wrote:

 One proposed solution is to cache plpgsql plans around the search path.


 I like the proposed solution, since search_path plays a part when generating
 plpgsql plan, it make sense to be part of the cache.


 Merlin Moncure-2 wrote:

 *) use sql functions for portions that float across schemas


 Just to clarify, does this mean the sql functions doesn't cache plans like
 plpgsql functions do?

correct. so you could wrap schema dependent bits inside set returning
sql functions.

merlin

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

[GENERAL] plpgsql function confusing behaviour

2011-07-12 Thread Shianmiin 
We have recently gone thru an unexpected behavior of PostgreSQL function
written in plpgsql.
I wonder if anyone can help explain the ideas behind the design.

Test scenario:
1. create two identical schemas, let's call them tenant1 and tenant2

-- set up tenant1
create schema tenant1;
set search_path to tenant1;

create table t1 (f1 int);
insert into t1 (f1) values (100);

create function f1() returns integer as $$
begin
   return (select count(*) from t1);
end;
$$ language plpgsql;

-- set up tenant2
create schema tenant2;
set search_path to tenant2;

create table t1 (f1 int);
insert into t1 (f1) values (100), (200);

create function f1() returns integer as $$
begin
   return (select count(*) from t1);
end;
$$ language plpgsql;


2. Run the following script in two new separate sessions:

script 1 (session 1)

set search_path to tenant1;
select * From tenant1.f1();  -- returns 1 ok
select * From tenant2.f1();  -- returns 1 ? but understandable
set search_path to tenant2;
select * from tenant1.f1();  -- returns 1 ok
select * From tenant2.f1();  -- returns 1 !!! wrong/confusing

script 2 (session 2)

set search_path to tenant2;
select * From tenant1.f1();  -- returns 2 ? but understandable
select * From tenant2.f1();  -- returns 2 ok
set search_path to tenant1;
select * from tenant1.f1();  -- returns 2 !!! wrong/confusing
select * From tenant2.f1();  -- returns 2 ok

Depends on the statement sequence, we could get different results.




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/plpgsql-function-confusing-behaviour-tp4576354p4576354.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

-- 
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] plpgsql function confusing behaviour

2011-07-12 Thread Merlin Moncure 
On Mon, Jul 11, 2011 at 3:23 PM, Shianmiin shianm...@gmail.com wrote:
 We have recently gone thru an unexpected behavior of PostgreSQL function
 written in plpgsql.
 I wonder if anyone can help explain the ideas behind the design.

 Test scenario:
 1. create two identical schemas, let's call them tenant1 and tenant2

 -- set up tenant1
 create schema tenant1;
 set search_path to tenant1;

 create table t1 (f1 int);
 insert into t1 (f1) values (100);

 create function f1() returns integer as $$
 begin
   return (select count(*) from t1);
 end;
 $$ language plpgsql;

 -- set up tenant2
 create schema tenant2;
 set search_path to tenant2;

 create table t1 (f1 int);
 insert into t1 (f1) values (100), (200);

 create function f1() returns integer as $$
 begin
   return (select count(*) from t1);
 end;
 $$ language plpgsql;


 2. Run the following script in two new separate sessions:

 script 1 (session 1)
 
 set search_path to tenant1;
 select * From tenant1.f1();  -- returns 1 ok
 select * From tenant2.f1();  -- returns 1 ? but understandable
 set search_path to tenant2;
 select * from tenant1.f1();  -- returns 1 ok
 select * From tenant2.f1();  -- returns 1 !!! wrong/confusing

 script 2 (session 2)
 
 set search_path to tenant2;
 select * From tenant1.f1();  -- returns 2 ? but understandable
 select * From tenant2.f1();  -- returns 2 ok
 set search_path to tenant1;
 select * from tenant1.f1();  -- returns 2 !!! wrong/confusing
 select * From tenant2.f1();  -- returns 2 ok

 Depends on the statement sequence, we could get different results.

This is unfortunately a known issue with plpgsql.  Se extensive recent
discussion in the archives.  One proposed solution is to cache plpgsql
plans around the search path.  Right now, you can do one of:

*) keep a copy of your function in each schema
*) use dynamic sql
*) use sql functions for portions that float across schemas

merlin

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