Date: Tue, 11 Mar 2025 19:45:33 +1100
From: Peter Wright <pete@flooble.net>
To: pgsql-bugs@lists.postgresql.org
Subject: Unqualified name not resolved in function called from materialized
 view (17.4)
Message-ID: <Z8_4Lf5EKH4ZDwvg@flooble.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Sender: Peter Wright <pete@flooble.net>
Archived-At: 
 <https://www.postgresql.org/message-id/Z8_4Lf5EKH4ZDwvg%40flooble.net>
Precedence: bulk

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