Обсуждение: BUG #19001: PostgreSQL 17: CREATE MATERIALIZED VIEW fails with STABLE function using JSONB LATERAL joins

The following bug has been logged on the website:

Bug reference:      19001
Logged by:          Caleb Gopal
Email address:      cgopal@onezero.com
PostgreSQL version: 17.5
Operating system:   Debian (Docker - postgres:latest) on WSL2 / Window
Description:

In PostgreSQL 17, I’m encountering an error when creating a MATERIALIZED
VIEW that uses a STABLE function returning jsonb, followed by LATERAL joins
using jsonb_each() and jsonb_to_record().

The view creation fails with:
ERROR:  relation "test_table" does not exist

However, the referenced table does exist, and the function used in the view
creation runs fine standalone.

The same view definition works correctly in PostgreSQL 13, and the function
has been explicitly marked STABLE. The function:
- only performs SELECT queries (read-only),
- does not use EXECUTE or dynamic SQL,
- has no side effects,
- returns a JSONB object for a given input string.

It seems PostgreSQL 17 is performing stricter planning or relation
resolution, even for a STABLE function with no volatility concerns.

I have tried:
- declaring the function as STABLE (explicitly, with PARALLEL UNSAFE)
- using WITH NO DATA
- materializing a simplified version
- removing lateral joins from the view definition

All of these continue to fail in PG17, while they work fine in PG13.

The only successful workaround was to precompute the function output in a
real table, then reference that table in the materialized view.
This works, confirming that the planner in PG17 has issues with either:
- nested LATERAL expressions using JSONB functions
- function dependency resolution during materialized view creation

If a function is declared STABLE and performs only read-only, deterministic
operations:
- PostgreSQL should allow it inside materialized views
- the planner should not throw errors about nonexistent relations unless
they're actually unresolved

Please confirm whether this is:
- an intentional change in PG17 planning behavior,
- a planner bug or regression,
- or if there are additional constraints we need to be aware of for STABLE
functions using JSONB + LATERAL in materialized views.

I'm happy to provide a full repro script or test database if helpful.

Kind regards,
Caleb Gopal

PG Bug reporting form <noreply@postgresql.org> writes:
> In PostgreSQL 17, I’m encountering an error when creating a MATERIALIZED
> VIEW that uses a STABLE function returning jsonb, followed by LATERAL joins
> using jsonb_each() and jsonb_to_record().

> The view creation fails with:
> ERROR:  relation "test_table" does not exist

What this looks like is a search_path problem.  Per the first
incompatibility point in the v17 release notes, materialized view
refresh now runs the view query with a "safe" search_path that
doesn't include the public schema.  So you need to schema-qualify
that reference, or else modify the function to use a "SET search_path"
clause.

            regards, tom lane

On Tue, 2025-07-29 at 13:55 +0000, PG Bug reporting form wrote:
> PostgreSQL version: 17.5
> Operating system:   Debian (Docker - postgres:latest) on WSL2 / Window
> Description:       
>
> In PostgreSQL 17, I’m encountering an error when creating a MATERIALIZED
> VIEW that uses a STABLE function returning jsonb, followed by LATERAL joins
> using jsonb_each() and jsonb_to_record().
>
> The view creation fails with:
> ERROR:  relation "test_table" does not exist
>
> However, the referenced table does exist, and the function used in the view
> creation runs fine standalone.
>
> The same view definition works correctly in PostgreSQL 13

That must be a consequence of the tightened security, see the first entry in
https://www.postgresql.org/docs/current/release-17.html#RELEASE-17-MIGRATION

Your function is not defined correctly.  Set a "search_path" like this:

  ALTER FUNCTION yourfunc SET search_path = schema_containing_test_table;

Yours,
Laurenz Albe