From: Olivier Jolly <zeograd@gmail.com>
To: Laurenz Albe <laurenz.albe@cybertec.at>
To: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18813: Materialized view creation regression when inlining recursive SQL function
Date: Sun, 16 Feb 2025 18:33:04 +0100
Message-ID: <03bf1dba-cb83-47cb-81b5-f091168e4772@gmail.com> (raw)
In-Reply-To: <d90a451aa1f7af0c4ad8e05f6523f4ed57499c14.camel@cybertec.at>
References: <18813-d5dadfa976a8ee2e@postgresql.org>
<d90a451aa1f7af0c4ad8e05f6523f4ed57499c14.camel@cybertec.at>
Le 16/02/2025 à 17:24, Laurenz Albe a écrit :
> On Sun, 2025-02-16 at 15:49 +0000, PG Bug reporting form wrote:
>> I've encountered an error when creating a materialized view as I updated
>> its body by introducing a recursive function.
>>
>> On postgreSQL 16, it works as expected: the materialized view is created and
>> works as intended.
>> Starting from posgreSQL 17.0, the materialized view created failed with the
>> error message
>>
>> ERROR: function jsonb_recursive_merge(jsonb, jsonb) does not exist
>> LINE 9: ELSE jsonb_recursive_merge(va::jsonb, vb:...
>> ^
>> HINT: No function matches the given name and argument types. You might need
>> to add explicit type casts.
>> CONTEXT: SQL function "jsonb_recursive_merge" during inlining
>>
>> postgreSQL 17.3 also returns the same error.
> That's not a bug, that's expected:
> https://www.postgresql.org/docs/current/release-17.html#RELEASE-17-MIGRATION
>
> Change functions to use a safe search_path during maintenance operations (Jeff Davis)
>
> So that's actually a bug in your function definition.
> Fix it by running
>
> ALTER FUNCTION ... SET search_path = schema_containing_function;
>
> Make sure that the schema does *not* have CREATE privileges for PUBLIC...
>
Many thanks for the fast on point answer.
I had read this part of the release note, but did not think it would
apply to functions defined in PUBLIC (as it was part of the default
search_path)
ALTER FUNCTION ... SET search_path = public;
fixed my script in 17.x, indeed.
Thanks a lot, sorry for not finding out by myself.
Take care,
Olivier
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: zeograd@gmail.com, laurenz.albe@cybertec.at, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18813: Materialized view creation regression when inlining recursive SQL function
In-Reply-To: <03bf1dba-cb83-47cb-81b5-f091168e4772@gmail.com>
* 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