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 existHowever, 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