Thread
-
Unqualified name not resolved in function called from materialized view (17.4)
Peter Wright <pete@flooble.net> — 2025-03-11T08:45:33Z
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)
Laurenz Albe <laurenz.albe@cybertec.at> — 2025-03-11T11:58:37Z
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)
Peter Wright <pete@flooble.net> — 2025-03-11T21:54:03Z
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.