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).

You'll need to make that a schema-qualified select public.myfunc($1,$2); because the matview query loses sight of the public schema: "while the query is running, the search_path is temporarily changed to pg_catalog, pg_temp." — Zegarek
– Zegarek, Commented Jun 26 at 21:24

Nimantha · Accepted Answer · 2025-07-01 08:23:11Z

3

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.

edited Jul 1 at 8:23

Nimantha

6,5376 gold badges32 silver badges78 bronze badges

answered Jun 26 at 21:33

Adrian Klaver

20.3k3 gold badges24 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

David M. Kaplan Jul 1 at 7:36

Perhaps you could explicitly add @Zegarek suggestion to your answer (i.e., include select public.myfunc($1,$2); in your answer for completeness).

Nimantha · Accepted Answer · 2025-07-01 08:21:28Z

2

Adrian's answer is probably the best, most secure way to fix the problem, but I found another solution that avoids explicitly citing a schema (one reason I wanted this is that I wanted code that would install the functions in the current user's schema without explicitly stating who that user is):

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
SET search_path FROM CURRENT;

This explicitly sets the search_path to be the same as the one at the time of function creation when running the function.

edited Jul 1 at 8:21

Nimantha

6,5376 gold badges32 silver badges78 bronze badges

answered Jul 1 at 7:31

David M. Kaplan

8851 gold badge8 silver badges17 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related