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.
