plpgsql function confusing behaviour
От | Shianmiin |
---|---|
Тема | plpgsql function confusing behaviour |
Дата | |
Msg-id | 1310415829837-4576354.post@n5.nabble.com обсуждение исходный текст |
Ответы |
Re: plpgsql function confusing behaviour
|
Список | pgsql-general |
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.
В списке pgsql-general по дате отправления: