Search Postgresql Archives

Re: plpgsql function confusing behaviour

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Mon, Jul 11, 2011 at 3:23 PM, Shianmiin <Shianmiin@xxxxxxxxx> 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux