Thread

1. BUG #19001: PostgreSQL 17: CREATE MATERIALIZED VIEW fails with STABLE function using JSONB LATERAL joins

   PG Bug reporting form <noreply@postgresql.org> — 2025-07-29T13:55:07Z

   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
   
   

2. Re: BUG #19001: PostgreSQL 17: CREATE MATERIALIZED VIEW fails with STABLE function using JSONB LATERAL joins

   Tom Lane <tgl@sss.pgh.pa.us> — 2025-07-29T15:31:59Z

   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
   
   
   
   

3. Re: BUG #19001: PostgreSQL 17: CREATE MATERIALIZED VIEW fails with STABLE function using JSONB LATERAL joins

   Laurenz Albe <laurenz.albe@cybertec.at> — 2025-07-29T15:34:21Z

   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