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
