st_dwithin : no spatial operator found

[grantfin]

I did look at prior issues, but they seemed much older, and possibly using SQL versions.

Product versions

select postgis_full_version();
                                                                        postgis_full_version
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
 POSTGIS="3.0.1 ec2a9aa" [EXTENSION] PGSQL="120" GEOS="3.8.1-CAPI-1.13.3" PROJ="6.2.1" LIBXML="2.9.10" LIBJSON="0.13.1" LIBPROTOBUF="1.3.2" WAGYU="0.4.3 (Internal)"

select version();
                                                   version
-------------------------------------------------------------------------------------------------------------
 PostgreSQL 12.3 on x86_64-apple-darwin16.7.0, compiled by Apple LLVM version 8.1.0 (clang-802.0.42), 64-bit

Replication steps

create database geo_test;
\c geo_test
create extension postgis; 
create schema test; 
set search_path to test;
create table a (lat float8, lng float8, idx_col public.geography generated always as (public.st_makepoint(lng,lat)::public.geography)stored);
create index idx_a_geog on a using gist(idx_col);
with g as (select public.st_makepoint(28,28)::public.geography a) select * from a, g where public.st_dwithin(g.a, a.idx_col, 5000, true);
ERROR:  no spatial operator found for 'st_dwithin': opfamily 603008 type 602965

Now, include the public schema...

set search_path to test, public;
SET
with g as (select public.st_makepoint(28,28)::public.geography a) select * from a, g where public.st_dwithin(g.a, a.idx_col, 5000, true);
 lat | lng | idx_col | a
-----+-----+---------+---
(0 rows)

[Algunenano]

This is present in the current master branch too. I've had a quick look and I don't see how the PG system cache might depend on the query search_path.

[ansel]

A customer of us stumbled about the same issue in the context of materialized views. While all objects are fully schema qualified by the user, postgis itself nevertheless performs a non-schema-qualified lookup of st_dwithin in this context.

The problem is serious because recent versions of pg_restore force the search_path to empty for securty reasons. So while one can work around this issue by setting search_path with normal sessions it's not possible to do that during a restore from backup, leading to pg_restore failures if postgis functions are part of index expressions or materialized views despite them being fully schema-qualified.

Here's a testcase that demonstrates the problem in materialized views. Putting the postgis schema into search_path will make the example work but yield a database that cannot be successfully dumped and restored:

create database regression;
\c regression
create extension postgis schema postgis;
create schema postgis;
create extension postgis schema postgis;
create table t gist (c postgis.geometry);
create index on t using gist (c);
select 1 from t where postgis.st_dwithin(postgis.st_geomfromtext('POINT(3373062 5626876)'::text, 31467), c, 1000.);

The unqualified lookup appears to happen via postgis_index_supportfn in this context:

(gdb) bt
#0  postgis_oid (typ=typ@entry=GEOMETRYOID) at lwgeom_pg.c:96
#1  0x00007fb139a031b3 in get_strategy_by_type (index=1, first_type=16498) at gserialized_supportfn.c:382
#2  postgis_index_supportfn (fcinfo=<optimized out>) at gserialized_supportfn.c:385
#3  0x00005631d50ccd2d in FunctionCall1Coll (flinfo=0x7ffe158edd00, collation=<optimized out>, arg1=<optimized out>) at ./build/../src/backend/utils/fmgr/fmgr.c:1140
#4  0x00005631d50cd535 in OidFunctionCall1Coll (functionId=<optimized out>, collation=collation@entry=0, arg1=arg1@entry=140729260105088) at ./build/../src/backend/utils/fmgr/fmgr.c:1418

An ad-hoc fix that made the dump restorable was injecting the following statement between --section=data and --section=post-data during the restore.

alter function postgis.postgis_index_supportfn set search_path = postgis;

Not sure if adding this to the extension control file is the best fix though: Maybe making postgis_index_supportfn look in which schema it resides and using the same to find to qualify the postgis function lookup is a better way?

[Algunenano]

Hi ansel, thanks a lot for the detailed explanation.

Using that, it became pretty clear what the issue was: the index support function (postgis_index_supportfn) needs to know the oid of the internal types (they aren't constant) and to do that it uses postgis_oid. postgis_oid either knows the types already because we have initialized the internal cache or it uses TypenameGetTypid, which only look up the types inside the current search_path.

The solution is to make sure we initialize the internal cache at the start of postgis_index_supportfn.

The initial patch that fixes it is here: ​https://github.com/postgis/postgis/pull/581 But I'm going to review what other functions might end up using the cache and ensure it's always initialized.

[Algunenano]

I've found and fixed some other functions that are affected by the same issue, as they relied on being able to do the dynamic lookup (which only works when the types are available in the search_path).

This is a similar issue as #4661.

[Raúl Marín <git@…>]

In 678f526/git:

Ensure all functions using postgis_oid initialize the internal cache

References #4739
Closes ​https://github.com/postgis/postgis/pull/581

[Raúl Marín <git@…>]

In cb66c8b/git:

Ensure all functions using postgis_oid initialize the internal cache

Closes #4739