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
