plpgsql function confusing behaviour

On Mon, Jul 11, 2011 at 3:23 PM, Shianmiin <Shianmiin@gmail.com> 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

Merlin Moncure-2 wrote:

One proposed solution is to cache plpgsql plans around the search path.

I like the proposed solution, since search_path plays a part when generating
plpgsql plan, it make sense to be part of the cache.

Merlin Moncure-2 wrote:

*) use sql functions for portions that float across schemas

Just to clarify, does this mean the sql functions doesn't cache plans like
plpgsql functions do?

--
View this message in context: http://postgresql.1045698.n5.nabble.com/plpgsql-function-confusing-behaviour-tp4576354p4579619.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On Tue, Jul 12, 2011 at 12:10 PM, Shianmiin <Shianmiin@gmail.com> wrote:

Merlin Moncure-2 wrote:

One proposed solution is to cache plpgsql plans around the search path.

I like the proposed solution, since search_path plays a part when generating
plpgsql plan, it make sense to be part of the cache.

Merlin Moncure-2 wrote:

*) use sql functions for portions that float across schemas

Just to clarify, does this mean the sql functions doesn't cache plans like
plpgsql functions do?

correct. so you could wrap schema dependent bits inside set returning
sql functions.

merlin