Re: Re: Querying foreign table with SP-GiST index results in “ERROR: cache lookup failed for type 0”

On Wed, Jun 26, 2019 at 4:39 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Paul Ramsey <pramsey(at)cleverelephant(dot)ca> writes:
> >> On Jun 26, 2019, at 2:06 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >> More than a bit :-(. That's guaranteed to fail if type box2df isn't
> >> in the search path, which it likely won't be during a remote query,
> >> since postgres_fdw locks down the search path to just pg_catalog.
>
> > We had a similar problem, if I recall correctly, doing lookups of spatial reference information, for similar reasons: calling over FDW the search_path was so limited the internal lookup functions couldn’t find the spatial_ref_sys table. I just looked up the work-around, and it looks like we infer that the table we’re looking for (part of PostGIS) resides in the same schema as the calling function (part of PostGIS), and then cache that schema name for future use.
>
> Yeah. The variant I was thinking about was to look up the namespace
> of the geometry type (which in this example you could get from attType
> of the spgConfigIn parameter struct) and assume that box2df is in that
> schema. But working from the function's own schema might be better
> because you could do the same thing in all these places, rather than
> needing to find an associated object in some call-site-specific way.

I've patched trunk, 2.5 and 2.4, so a fix should be in the next patch
release or in 3.0 if you wait that long.

https://trac.osgeo.org/postgis/ticket/4440

Thanks for the good reproduction case.

ATB,
P