BUG #9505: If a function references an unqualified table, it only resolves it from search_path once per session - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      9505
Logged by:          Gordon Shannon
Email address:      gordo169@gmail.com
PostgreSQL version: 9.2.4
Operating system:   Centos
Description:

After session starts, 1st time you run a function with an unqualified
schema, it will use search_path to resolve.  If in the same session you set
search_path to point to a different schema with the same table name, when
you rerun the function it still resolves to the original schema.

** -------- TEST CASE ---------**
set search_path to default;
show search_path;

create or replace function get_count()
 returns int
 language plpgsql
as $$
declare
    v_cnt int := 0;
begin
  select count(*) into v_cnt from junk;
  return v_cnt;
end;
$$;

create schema frick;
create table frick.junk (id int);
insert into frick.junk values (1), (2);

create schema frack;
create table frack.junk (id int);
insert into frack.junk values (1), (2), (3), (4), (5);

set search_path to public, frick;
select * from get_count();
set search_path to public, frack;
select * from get_count();

\c
set search_path to public, frack;
select * from get_count();
set search_path to public, frick;
select * from get_count();


** -------- TEST CASE WITH OUTPUT---------**
[admin@dev1:acct] 15:38:05> set search_path to default;
SET
Time: 20.340 ms
[admin@dev1:acct] 15:39:21> show search_path;
  search_path
----------------
 "$user",public
(1 row)

Time: 17.667 ms
[admin@dev1:acct] 15:39:21>
[admin@dev1:acct] 15:39:21> create or replace function get_count()
>  returns int
>  language plpgsql
> as $$
> declare
>     v_cnt int := 0;
> begin
>   select count(*) into v_cnt from junk;
>   return v_cnt;
> end;
> $$;
CREATE FUNCTION
Time: 21.481 ms
[admin@dev1:acct] 15:39:21>
[admin@dev1:acct] 15:39:21> create schema frick;
CREATE SCHEMA
Time: 25.057 ms
[admin@dev1:acct] 15:39:21> create table frick.junk (id int);
CREATE TABLE
Time: 19.538 ms
[admin@dev1:acct] 15:39:21> insert into frick.junk values (1), (2);
INSERT 0 2
Time: 16.467 ms
[admin@dev1:acct] 15:39:21>
[admin@dev1:acct] 15:39:21> create schema frack;
CREATE SCHEMA
Time: 17.609 ms
[admin@dev1:acct] 15:39:21> create table frack.junk (id int);
CREATE TABLE
Time: 24.746 ms
[admin@dev1:acct] 15:39:21> insert into frack.junk values (1), (2), (3),
(4), (5);
INSERT 0 5
Time: 18.159 ms
[admin@dev1:acct] 15:39:21>
[admin@dev1:acct] 15:39:21> set search_path to public, frick;
SET
Time: 17.433 ms
[admin@dev1:acct] 15:39:21> select * from get_count();
 get_count
-----------
         2
(1 row)

Time: 18.490 ms
[admin@dev1:acct] 15:39:21> set search_path to public, frack;
SET
Time: 17.051 ms
[admin@dev1:acct] 15:39:21> select * from get_count();
 get_count
-----------
         2
(1 row)

Time: 16.365 ms
[admin@dev1:acct] 15:39:21>
[admin@dev1:acct] 15:39:21> \c
psql (9.2.1, server 9.2.6)
You are now connected to database "acct" as user "admin".
[admin@dev1:acct] 15:39:22> set search_path to public, frack;
SET
Time: 18.069 ms
[admin@dev1:acct] 15:39:22> select * from get_count();
 get_count
-----------
         5
(1 row)

Time: 19.769 ms
[admin@dev1:acct] 15:39:22> set search_path to public, frick;
SET
Time: 16.020 ms
[admin@dev1:acct] 15:39:22> select * from get_count();
 get_count
-----------
         5
(1 row)