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
