Re: plpgsql function confusing behaviour

On Mon, Jul 11, 2011 at 3:23 PM, Shianmiin <Shianmiin(at)gmail(dot)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