Problem with ST_NRings

[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;