Thread: BUG #18292: Unexpected error: "relation "hobbies_r" does not exist" caused by user-defined functions
BUG #18292: Unexpected error: "relation "hobbies_r" does not exist" caused by user-defined functions
From
PG Bug reporting form
Date:
The following bug has been logged on the website: Bug reference: 18292 Logged by: Zuming Jiang Email address: zuming.jiang@inf.ethz.ch PostgreSQL version: 16.1 Operating system: Ubuntu 20.04 Description: My fuzzer finds a suspicious alarm in Postgres 17devel: "ERROR: relation "hobbies_r" does not exist". I report it as I think it might be an unexpected error. --- Set up database --- create table exeet_t2 (vkey int4); insert into exeet_t2 values (5); CREATE TABLE person (name text); insert into person values ('mike'); CREATE TABLE hobbies_r (name text, person text); CREATE TABLE equipment_r (name text, hobby text); INSERT INTO hobbies_r (name, person) SELECT 'posthacking', p.name FROM person* p; INSERT INTO equipment_r (name, hobby) VALUES ('advil', 'posthacking'); CREATE FUNCTION hobbies(person) RETURNS setof hobbies_r AS 'select * from hobbies_r where person = $1.name' LANGUAGE SQL; CREATE FUNCTION equipment(hobbies_r) RETURNS setof equipment_r AS 'select * from equipment_r where hobby = $1.name' LANGUAGE SQL; create schema simple1; create function simple1.simpletarget(int) returns int language plpgsql as $$begin return $1;end$$; create function simpletarget(int) returns int language plpgsql as $$begin return $1 + 100;end$$; create or replace function simplecaller() returns int language plpgsql as $$ declare sum int := 0;begin for n in 1..10 loop sum := sum + simpletarget(n);if n = 5 then set local search_path = 'simple1';end if;end loop;return sum;end$$; The fuzzer generates a test case: --- Test case --- select (SELECT (p.hobbies).equipment.name FROM ONLY person p order by 1 limit 1) as c_11 from (select 1 as c_0 from exeet_t2 as ref_0 where '555' = ((select cast(simplecaller() as text)))) as subq_0; --- Expected behavior --- The test case should not trigger any error. --- Actual behavior --- The test case trigger an error: ERROR: relation "hobbies_r" does not exist --- Postgres version --- Github commit: 15235abbf34f6b246f7681e88dccf8c2796a245b Version: PostgreSQL 17devel on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.2) 9.4.0, 64-bit --- Platform information --- Platform: Ubuntu 20.04 Kernel: Linux 5.4.0-147-generic --- Note --- The following query can be successfully executed: select (select name from hobbies_r order by 1 limit 1) as c_11 from (select 1 as c_0 from exeet_t2 as ref_0 where '555' = ((select cast(simplecaller() as text)))) as subq_0; --- return {'posthacking'}
Re: BUG #18292: Unexpected error: "relation "hobbies_r" does not exist" caused by user-defined functions
From
Tom Lane
Date:
PG Bug reporting form <noreply@postgresql.org> writes: > My fuzzer finds a suspicious alarm in Postgres 17devel: "ERROR: relation > "hobbies_r" does not exist". I report it as I think it might be an > unexpected error. I don't see anything unexpected here: you made a transaction-local change in search_path that renders that table invisible. It's not instantly obvious how the flow of control gets to a lookup of that table after the SET LOCAL; but evidently that's happening, and I don't feel any urge to work out the details. (Note that if you were expecting SET LOCAL to mean "local to this function call", you're mistaken. You can get that effect with a SET clause attached to the function definition; but this is not that.) If you want people to take this sort of report seriously, you need to analyze the behavior yourself, not expect us to look for a bug that probably doesn't exist. regards, tom lane
Re: BUG #18292: Unexpected error: "relation "hobbies_r" does not exist" caused by user-defined functions
From
Zu-Ming Jiang
Date:
Thanks for your feedback. I will adjust my new fuzzer accordingly. And got it; I will avoid such cases and will report only issues that must be bugs. Best, Zuming