Date: Wed, 12 Mar 2025 08:54:03 +1100
From: Peter Wright <pete@flooble.net>
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: Unqualified name not resolved in function called from
 materialized view (17.4)
Message-ID: <Z9Cw-_ikmCua_gQD@flooble.net>
References: <Z8_4Lf5EKH4ZDwvg@flooble.net>
 <df50dda5bad084ae0e8d8a8077d015a86625396a.camel@cybertec.at>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable
In-Reply-To: <df50dda5bad084ae0e8d8a8077d015a86625396a.camel@cybertec.at>
Sender: Peter Wright <pete@flooble.net>
Archived-At: 
 <https://www.postgresql.org/message-id/Z9Cw-_ikmCua_gQD%40flooble.net>
Precedence: bulk

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:
> >=20
> > Given a file "break_it.sql" containing this SQL:
> >=20
> >   SELECT version();
> >   CREATE VIEW things AS SELECT 1 AS id, 'rock' AS thing_name;
> >   CREATE FUNCTION num_things() RETURNS INTEGER AS $$ SELECT COUNT(1) FR=
OM things; $$ LANGUAGE SQL STABLE;
> >   CREATE MATERIALIZED VIEW thing_report AS SELECT num_things() AS numbe=
r_of_things;
> >=20
> > Running the following commands with the environment pointing at a Postg=
reSQL 17.4 server:
> >=20
> >   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
> >=20
> >=20
> > Running the same commands with the environment pointing at a PostgreSQL=
 16.8
> > server does *not* report an error
>=20
> 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-MIGRAT=
ION

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 =E2=80=9Cmaintenance operations=
=E2=80=9D.

> You should fix the function.

I will. :-)

> Yours,
> Laurenz Albe

Pete.
--=20
If at first you don't succeed, you must be a programmer.