Problem with ST_NRings

[Marco Boeringa]

Op 3-12-2025 om 04:38 schreef Regina Obe:
>> 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
>>
>> --
>> Erik Wienhold
> It would be better if you used the fully qualified name.
> Using search_path in a function crushes query performance.
>
>   CREATE FUNCTION my_func(public.geometry)
>       RETURNS int
>       AS $$ SELECT public.ST_NRings($1); $$ LANGUAGE sql;

Hi Regina,

Thanks for that important warning about the performance aspect. However, 
do you possibly have any real world figures of how much of a difference 
this makes? Just doing an internet search on 'search_path' and 
'performance' doesn't turn up much of relevant results for something 
that might be critical. I can see how a custom search path loaded with 
objects might turn a 'search' into a heavy operation, but I would expect 
PostgreSQL to at least cache some reference to the main functions and 
objects of system 'public', 'pg_catalog' and 'pg_temp' schema's, but 
maybe I am just naive here?

Although I could change the code, having to add 'public' before each 
PostGIS function or class, would make my Python code assembling queries 
a lot less readable. Or is the performance issue you express concerns 
about mainly related to function definitions like in CREATE FUNCTION, 
which would make adjustment much more straightforward, as it would only 
require modifying the function definitions themselves, not all uses in SQL?

Marco