From: | Shianmiin <Shianmiin(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | plpgsql function confusing behaviour |
Date: | 2011-07-11 20:23:49 |
Message-ID: | 1310415829837-4576354.post@n5.nabble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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.
From | Date | Subject | |
---|---|---|---|
Next Message | Darren Duncan | 2011-07-11 20:47:03 | Re: [HACKERS] Creating temp tables inside read only transactions |
Previous Message | Raymond O'Donnell | 2011-07-11 20:00:57 | Re: sql or pgsql question, accessing a created value |