Re: BUG #18813: Materialized view creation regression when inlining recursive SQL function

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