PostgreSQL create materialized view fails when create table with same code works

Question

I am using PostgreSQL 17 (with PostGIS extension) installed on a Ubuntu 24.04 system. I have a very strange problem where a certain query works as a straight query (or to create a table), but the same exact query fails when I try to place it in a MATERIALIZED VIEW. The problem seems to be related to using a function inside another function, but I am not sure how. Here is a simplified code to reproduce the problem:

CREATE OR REPLACE FUNCTION myfunc(
       ts_array TIMESTAMP WITHOUT TIME ZONE[],tz text)
       RETURNS float[] AS
$BODY$
SELECT array_agg(a) FROM
       (SELECT extract( epoch FROM unnest($1) AT TIME ZONE $2 ) AS a) t;
$BODY$ LANGUAGE 'sql' IMMUTABLE;

CREATE OR REPLACE FUNCTION nest_myfunc(
       ts_array TIMESTAMP WITHOUT TIME ZONE[],tz text)
       RETURNS float[] AS
$BODY$
SELECT myfunc($1,$2);
$BODY$ LANGUAGE 'sql' IMMUTABLE;

CREATE TEMP TABLE blah AS
SELECT nest_myfunc(ARRAY['2021-01-01']::timestamp without time zone[],'GMT');

CREATE MATERIALIZED VIEW blah_mv AS
SELECT nest_myfunc(ARRAY['2021-01-01']::timestamp without time zone[],'GMT'::text);

The instruction to CREATE TEMP TABLE works without a problem, but CREATE MATERIALIZED VIEW returns:

ERROR:  function myfunc(timestamp without time zone[], text) does not exist
LINE 2: SELECT myfunc($1,$2);
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
QUERY:  
SELECT myfunc($1,$2);

CONTEXT:  SQL function "nest_myfunc" during startup
Time: 6.025 ms

What is going on here and how do I fix it?

(NOTE: This code used to work in older versions of PostgreSQL, but not in 17).

Answer 1

The section @Zegarek quoted in his comment comes from here CREATE MATERIALIZED VIEW in description for the parameter query:

A SELECT, TABLE, or VALUES command. This query will run within a security-restricted operation; in particular, calls to functions that themselves create temporary tables will fail. Also, while the query is running, the search_path is temporarily changed to pg_catalog, pg_temp.

The reason it worked before is that the server code was more relaxed about search_path. In newer versions the code around search_path has become more restrictive.

See PG 17 Release Notes Migration:

Observe the following incompatibilities

Change functions to use a safe search_path during maintenance operations (Jeff Davis) This prevents maintenance operations (ANALYZE, CLUSTER, CREATE INDEX, CREATE MATERIALIZED VIEW, REFRESH MATERIALIZED VIEW, REINDEX, or VACUUM) from performing unsafe access.