Unqualified name not resolved in function called from materialized view (17.4) - Mailing list pgsql-bugs

PostgreSQL version: 17.4
Operating system: Ubuntu 24.04, x86_64, kernel 6.8.0-51-generic
Description:

Given a file "break_it.sql" containing this SQL:

  SELECT version();
  CREATE VIEW things AS SELECT 1 AS id, 'rock' AS thing_name;
  CREATE FUNCTION num_things() RETURNS INTEGER AS $$ SELECT COUNT(1) FROM things; $$ LANGUAGE SQL STABLE;
  CREATE MATERIALIZED VIEW thing_report AS SELECT num_things() AS number_of_things;

Running the following commands with the environment pointing at a PostgreSQL 17.4 server:

  dropdb demo 2> /dev/null ; createdb demo && psql -d demo -f break_it.sql

...produces an error on line 4: relation "things" does not exist.

Full output (indented two spaces):

                                                                version

-----------------------------------------------------------------------------------------------------------------------------------
   PostgreSQL 17.4 (Ubuntu 17.4-1.pgdg24.04+2) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.3.0-6ubuntu2~24.04)
13.3.0,64-bit
 
  (1 row)
  
  CREATE VIEW
  CREATE FUNCTION
  psql:break_it.sql:4: ERROR:  relation "things" does not exist
  LINE 1:  SELECT COUNT(1) FROM things;
                                ^
  QUERY:   SELECT COUNT(1) FROM things;
  CONTEXT:  SQL function "num_things" during inlining


Running the same commands with the environment pointing at a PostgreSQL 16.8
server does *not* report an error (and the materialized view is created):

                                                                version

-----------------------------------------------------------------------------------------------------------------------------------
   PostgreSQL 16.8 (Ubuntu 16.8-1.pgdg24.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.3.0-6ubuntu2~24.04)
13.3.0,64-bit
 
  (1 row)
  
  CREATE VIEW
  CREATE FUNCTION
  SELECT 1


Similarly, the same test on PostgreSQL 15.10 does not report an error
(and the materialized view is created):

                                                                 version

-------------------------------------------------------------------------------------------------------------------------------------
   PostgreSQL 15.10 (Ubuntu 15.10-1.pgdg24.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.3.0-6ubuntu2~24.04)
13.3.0,64-bit
 
  (1 row)
  
  CREATE VIEW
  CREATE FUNCTION
  SELECT 1


But if I change the third line of "break_it.sql" to use the namespace-qualified
name "public.things":

  CREATE FUNCTION num_things() RETURNS INTEGER AS $$ SELECT COUNT(1) FROM public.things; $$ LANGUAGE SQL STABLE;

...then it works as expected under 17.4 without any error:

                                                                version

-----------------------------------------------------------------------------------------------------------------------------------
   PostgreSQL 17.4 (Ubuntu 17.4-1.pgdg24.04+2) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.3.0-6ubuntu2~24.04)
13.3.0,64-bit
 
  (1 row)
  
  CREATE VIEW
  CREATE FUNCTION
  SELECT 1



Pete.
-- 
"I'm an Igor, thur. We don't athk quethtionth."
"Really? Why not?"
"I don't know, thur. I didn't athk."
        -- "Making Money", Terry Pratchett