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
