Message-ID: <03bf1dba-cb83-47cb-81b5-f091168e4772@gmail.com>
Date: Sun, 16 Feb 2025 18:33:04 +0100
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: BUG #18813: Materialized view creation regression when inlining
 recursive SQL function
To: Laurenz Albe <laurenz.albe@cybertec.at>, pgsql-bugs@lists.postgresql.org
References: <18813-d5dadfa976a8ee2e@postgresql.org>
 <d90a451aa1f7af0c4ad8e05f6523f4ed57499c14.camel@cybertec.at>
Content-Language: en-GB
From: Olivier Jolly <zeograd@gmail.com>
In-Reply-To: <d90a451aa1f7af0c4ad8e05f6523f4ed57499c14.camel@cybertec.at>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/03bf1dba-cb83-47cb-81b5-f091168e4772%40gmail.com>
Precedence: bulk

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