From: Laurenz Albe <laurenz.albe@cybertec.at>
To: cgopal@onezero.com
To: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #19001: PostgreSQL 17: CREATE MATERIALIZED VIEW fails with STABLE function using JSONB LATERAL joins
Date: Tue, 29 Jul 2025 17:34:21 +0200
Message-ID: <713b3a6c6bbac7329b7d58038d47b5361d3ff2e4.camel@cybertec.at> (raw)
In-Reply-To: <19001-efdb4ac1eaddc5b8@postgresql.org>
References: <19001-efdb4ac1eaddc5b8@postgresql.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: laurenz.albe@cybertec.at, cgopal@onezero.com, 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: <713b3a6c6bbac7329b7d58038d47b5361d3ff2e4.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox