Thread: Unqualified name not resolved in function called from materialized view (17.4)
Unqualified name not resolved in function called from materialized view (17.4)
From
Peter Wright
Date:
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
Re: Unqualified name not resolved in function called from materialized view (17.4)
From
Laurenz Albe
Date:
On Tue, 2025-03-11 at 19:45 +1100, Peter Wright wrote: > 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: > > 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 That is not a bug, it is intentional. See the first entry in the list of incompatibilities https://www.postgresql.org/docs/current/release-17.html#RELEASE-17-MIGRATION You should fix the function. Yours, Laurenz Albe
Re: Unqualified name not resolved in function called from materialized view (17.4)
From
Peter Wright
Date:
On 11/03 12:58:37, Laurenz Albe wrote: > On Tue, 2025-03-11 at 19:45 +1100, Peter Wright wrote: > > 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: > > > > 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 > > That is not a bug, it is intentional. > See the first entry in the list of incompatibilities > https://www.postgresql.org/docs/current/release-17.html#RELEASE-17-MIGRATION Aha! Noted, and thank you. I spent some time looking for evidence that this was an intentional change, but apparently didn't look in the most obvious place. <facepalm> It also seemed counter-intuitive that CREATE MATERIALIZED VIEW would use a different search path to CREATE VIEW - but makes sense now I know that materialized view create/refresh are “maintenance operations”. > You should fix the function. I will. :-) > Yours, > Laurenz Albe Pete. -- If at first you don't succeed, you must be a programmer.