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
