From: Tom Lane <tgl@sss.pgh.pa.us>
To: cgopal@onezero.com
cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #19001: PostgreSQL 17: CREATE MATERIALIZED VIEW fails with
 STABLE function using JSONB LATERAL joins
In-reply-to: <19001-efdb4ac1eaddc5b8@postgresql.org>
References: <19001-efdb4ac1eaddc5b8@postgresql.org>
Comments: In-reply-to PG Bug reporting form <noreply@postgresql.org>
	message dated "Tue, 29 Jul 2025 13:55:07 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <998271.1753803119.1@sss.pgh.pa.us>
Content-Transfer-Encoding: 8bit
Date: Tue, 29 Jul 2025 11:31:59 -0400
Message-ID: <998272.1753803119@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/998272.1753803119%40sss.pgh.pa.us>
Precedence: bulk

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