Обсуждение: plpgsql function confusing behaviour

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

plpgsql function confusing behaviour

От
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.

Re: plpgsql function confusing behaviour

От
Merlin Moncure
Дата:
On Mon, Jul 11, 2011 at 3:23 PM, Shianmiin <Shianmiin@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

Re: plpgsql function confusing behaviour

От
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.

Re: plpgsql function confusing behaviour

От
Merlin Moncure
Дата:
On Tue, Jul 12, 2011 at 12:10 PM, Shianmiin <Shianmiin@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