Thread: BUG #18292: Unexpected error: "relation "hobbies_r" does not exist" caused by user-defined functions

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'}

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

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