Problem with ST_NRings

[Marco Boeringa]

Thanks Erik,

Adding the search paths to the function definitions as you suggested, 
indeed fixed the issue. Still slightly puzzled that I didn't run into 
this before in the past year since upgrading to PG17/18, but I guess 
that I missed the particular configuration hitting this part of my code.

Marco

Op 2-12-2025 om 20:43 schreef Erik Wienhold:
> On 2025-12-02 20:19 +0100, Marco Boeringa wrote:
>> If I change the CREATE MATERIALIZED VIEW to CREATE TABLE, exactly the same
>> SQL statement succeeds... Both modes used to work. My current setup is
>> PostgreSQL 18.1 / PostGIS 3.6.1.
>>
>> Op 2-12-2025 om 19:57 schreef Marco Boeringa:
>>> As part of a larger workflow, I am using a custom PostgreSQL function
>>> that depends on ST_NRings, and is used to create a MATERIALIZED VIEW.
>>>
>>> This has worked flawlessly until recently. I now ran into an issue with
>>> the error message below. Going over my own code calling the custom
>>> function that includes the ST_NRings call, I just cannot see any recent
>>> code change made by myself explaining this sudden error. This part of
>>> the code has been stable for quite a while.
>>>
>>> As input to the custom function, WGS1984 OpenStreetMap data is used,
>>> stored as  'geometry' type. There are actually explicit type casts in
>>> the code as well, so even that part of the error message I cannot
>>> explain for now.
>>>
>>> My question: has there been any change to ST_NRings in a recent update
>>> of PostGIS (3.6.0 or 3.6.1?), that might explain this error? E.g. should
>>> the 'public' be in this error message at all, I would have expected
>>> "ERROR:  function st_nrings(geometry) does not exist"?
>>>
>>> ERROR:  function st_nrings(public.geometry) does not exist
>>> LINE 8:    WHEN ST_NRings($1) = 1 THEN $1
>>>                  ^
>>> HINT:  No function matches the given name and argument types. You might
>>> need to add explicit type casts.
> Postgres runs REFRESH MATERIALIZED VIEW (and other maintenance commands)
> with a safe search_path since version 17 [1].  That also explains why
> you see type public.geometry instead of just geometry in the error
> message.
>
> You need to use the qualified function name public.ST_NRings in your
> custom function.  Or change the search_path config of that function so
> that it no longer depends on the session search_path, e.g.:
>
> CREATE FUNCTION my_func(public.geometry)
>      RETURNS int
>      SET search_path = public, pg_catalog, pg_temp
>      AS $$ SELECT ST_NRings($1); $$ LANGUAGE sql;
>
> [1] https://www.postgresql.org/docs/17/release-17.html#RELEASE-17-MIGRATION
>