plpgsql function confusing behaviour

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.