Thread: BUG #9505: If a function references an unqualified table, it only resolves it from search_path once per session
BUG #9505: If a function references an unqualified table, it only resolves it from search_path once per session
From
gordo169@gmail.com
Date:
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)
Re: BUG #9505: If a function references an unqualified table, it only resolves it from search_path once per session
From
Vik Fearing
Date:
On 03/09/2014 10:45 PM, gordo169@gmail.com wrote: > 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. This was fixed in 9.3. http://www.postgresql.org/docs/9.3/static/release-9-3.html#AEN114856 -- Vik