From: | gordo169(at)gmail(dot)com |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #9505: If a function references an unqualified table, it only resolves it from search_path once per session |
Date: | 2014-03-09 21:45:04 |
Message-ID: | 20140309214504.1230.51420@wrigleys.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 9505
Logged by: Gordon Shannon
Email address: gordo169(at)gmail(dot)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(at)dev1:acct] 15:38:05> set search_path to default;
SET
Time: 20.340 ms
[admin(at)dev1:acct] 15:39:21> show search_path;
search_path
----------------
"$user",public
(1 row)
Time: 17.667 ms
[admin(at)dev1:acct] 15:39:21>
[admin(at)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(at)dev1:acct] 15:39:21>
[admin(at)dev1:acct] 15:39:21> create schema frick;
CREATE SCHEMA
Time: 25.057 ms
[admin(at)dev1:acct] 15:39:21> create table frick.junk (id int);
CREATE TABLE
Time: 19.538 ms
[admin(at)dev1:acct] 15:39:21> insert into frick.junk values (1), (2);
INSERT 0 2
Time: 16.467 ms
[admin(at)dev1:acct] 15:39:21>
[admin(at)dev1:acct] 15:39:21> create schema frack;
CREATE SCHEMA
Time: 17.609 ms
[admin(at)dev1:acct] 15:39:21> create table frack.junk (id int);
CREATE TABLE
Time: 24.746 ms
[admin(at)dev1:acct] 15:39:21> insert into frack.junk values (1), (2), (3),
(4), (5);
INSERT 0 5
Time: 18.159 ms
[admin(at)dev1:acct] 15:39:21>
[admin(at)dev1:acct] 15:39:21> set search_path to public, frick;
SET
Time: 17.433 ms
[admin(at)dev1:acct] 15:39:21> select * from get_count();
get_count
-----------
2
(1 row)
Time: 18.490 ms
[admin(at)dev1:acct] 15:39:21> set search_path to public, frack;
SET
Time: 17.051 ms
[admin(at)dev1:acct] 15:39:21> select * from get_count();
get_count
-----------
2
(1 row)
Time: 16.365 ms
[admin(at)dev1:acct] 15:39:21>
[admin(at)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(at)dev1:acct] 15:39:22> set search_path to public, frack;
SET
Time: 18.069 ms
[admin(at)dev1:acct] 15:39:22> select * from get_count();
get_count
-----------
5
(1 row)
Time: 19.769 ms
[admin(at)dev1:acct] 15:39:22> set search_path to public, frick;
SET
Time: 16.020 ms
[admin(at)dev1:acct] 15:39:22> select * from get_count();
get_count
-----------
5
(1 row)
From | Date | Subject | |
---|---|---|---|
Next Message | Vik Fearing | 2014-03-09 21:57:20 | Re: BUG #9505: If a function references an unqualified table, it only resolves it from search_path once per session |
Previous Message | richardozsvald | 2014-03-09 07:57:51 | BUG #9500: problem with password |