pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedUnqualified name not resolved in function called from materialized view (17.4)
3+ messages / 2 participants
[nested] [flat]
* Unqualified name not resolved in function called from materialized view (17.4)
@ 2025-03-11 08:45 Peter Wright <pete@flooble.net>
0 siblings, 1 reply; 3+ messages in thread
From: Peter Wright @ 2025-03-11 08:45 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Unqualified name not resolved in function called from materialized view (17.4)
@ 2025-03-11 11:58 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Peter Wright <pete@flooble.net>
0 siblings, 1 reply; 3+ messages in thread
From: Laurenz Albe @ 2025-03-11 11:58 UTC (permalink / raw)
To: Peter Wright <pete@flooble.net>; pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Unqualified name not resolved in function called from materialized view (17.4)
@ 2025-03-11 21:54 Peter Wright <pete@flooble.net>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 3+ messages in thread
From: Peter Wright @ 2025-03-11 21:54 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-bugs@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2025-03-11 21:54 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-03-11 08:45 Unqualified name not resolved in function called from materialized view (17.4) Peter Wright <pete@flooble.net>
2025-03-11 11:58 ` Laurenz Albe <laurenz.albe@cybertec.at>
2025-03-11 21:54 ` Peter Wright <pete@flooble.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox