Обсуждение: Querying foreign table with SP-GiST index results in “ERROR: cache lookup failed for type 0”

On Tue, Jun 18, 2019 at 9:22 PM Jeff Janes <jeff.janes@gmail.com> wrote:
> On Tue, Jun 18, 2019 at 8:02 AM Joao Ferreira <jpgferreira@yahoo.com> wrote:
>> I'm using PostgreSQL 11.3 and I want to access a table in another Postgres database using postgres_fdw but since the
originaltable has an SP-GiST index on a geometry column, the query on the foreign table fails with "ERROR: cache lookup
failedfor type 0", even if the table is empty (Step 7). This doesn't happen if the query is done directly to the remote
table(Steps 3 and 6) or if the remote table doesn't have the index (Steps 3 and 4). 

I started working on this.

> I've verified this on ubuntu 18.04 with 11.3 installed from PGDG apt repo, and attached a single-file reproduction.

I've also verified this on my environment with PG 11.4.

> If I change from sp-gist to just gist, there is no problem.  And if I change to indexing a built-in sp-gist operator
class(point rather than geom), there is also no problem. 

Verified.

Will continue investigations.

Best regards,
Etsuro Fujita

On Thu, Jun 20, 2019 at 6:16 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
> On Tue, Jun 18, 2019 at 9:22 PM Jeff Janes <jeff.janes@gmail.com> wrote:
> > On Tue, Jun 18, 2019 at 8:02 AM Joao Ferreira <jpgferreira@yahoo.com> wrote:
> >> I'm using PostgreSQL 11.3 and I want to access a table in another Postgres database using postgres_fdw but since
theoriginal table has an SP-GiST index on a geometry column, the query on the foreign table fails with "ERROR: cache
lookupfailed for type 0", even if the table is empty (Step 7). This doesn't happen if the query is done directly to the
remotetable (Steps 3 and 6) or if the remote table doesn't have the index (Steps 3 and 4). 

> > I've verified this on ubuntu 18.04 with 11.3 installed from PGDG apt repo, and attached a single-file reproduction.
>
> I've also verified this on my environment with PG 11.4.
>
> > If I change from sp-gist to just gist, there is no problem.  And if I change to indexing a built-in sp-gist
operatorclass (point rather than geom), there is also no problem. 
>
> Verified.
>
> Will continue investigations.

I found that this error is thrown by index_can_return()
(spgcanreturn()) called from get_relation_info() for the SP-GiST index
on the locations table.  IIUC, the cause of that is: the SP-GiST
config function defined in PostGIS shown below called from
spgGetCache() in spgcanreturn() doesn't work well for the postgres_fdw
case, in which case cfg->prefixType and cfg->leafType are set to
InvalidOid, causing the "ERROR: cache lookup failed for type 0" error
in fillTypeDesc() callled later in spgGetCache() for the
cfg->prefixType, because TypenameGetTypid() returns InvalidOid in that
case since that in postgres_fdw we run the connection with search_path
restricted to pg_catalog while box2df is created in the public schema
in an environment built using a PostGIS default configuration.

PGDLLEXPORT Datum gserialized_spgist_config_2d(PG_FUNCTION_ARGS)
{
    spgConfigOut *cfg = (spgConfigOut *)PG_GETARG_POINTER(1);

--> Oid boxoid = TypenameGetTypid("box2df");
    cfg->prefixType = boxoid;
    cfg->labelType = VOIDOID; /* We don't need node labels. */
    cfg->leafType = boxoid;
    cfg->canReturnData = false;
    cfg->longValuesOK = false;

    PG_RETURN_VOID();
}

I'm a newbie to PostGIS, so maybe I'm missing something, but isn't it
a bit fragile to use TypenameGetTypid() here?  I just thought it would
be better to set boxoid in a more hard-coded way in this function, not
using TypenameGetTypid().

Best regards,
Etsuro Fujita

Etsuro Fujita <etsuro.fujita@gmail.com> writes:
> I found that this error is thrown by index_can_return()
> (spgcanreturn()) called from get_relation_info() for the SP-GiST index
> on the locations table.

> PGDLLEXPORT Datum gserialized_spgist_config_2d(PG_FUNCTION_ARGS)
> {
>     spgConfigOut *cfg = (spgConfigOut *)PG_GETARG_POINTER(1);

> --> Oid boxoid = TypenameGetTypid("box2df");
>     cfg->prefixType = boxoid;
>     cfg->labelType = VOIDOID; /* We don't need node labels. */
>     cfg->leafType = boxoid;
>     cfg->canReturnData = false;
>     cfg->longValuesOK = false;

>     PG_RETURN_VOID();
> }

> I'm a newbie to PostGIS, so maybe I'm missing something, but isn't it
> a bit fragile to use TypenameGetTypid() here?

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.

This is a Postgis bug.  It's probably not quite trivial to solve,
since this code can't know what schema the type is installed in
at runtime.  This seems related to the discussions we had awhile
ago about how planner support functions can find out the OIDs of
extension objects reliably.  I'm not sure where Paul is on that...

            regards, tom lane

Paul Ramsey <pramsey@cleverelephant.ca> writes:
>> On Jun 26, 2019, at 2:06 PM, Tom Lane <tgl@sss.pgh.pa.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:
callingover FDW the search_path was so limited the internal lookup functions couldn’t find the spatial_ref_sys table. I
justlooked up the work-around, and it looks like we infer that the table we’re looking for (part of PostGIS) resides in
thesame 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.

            regards, tom lane

On Wed, Jun 26, 2019 at 4:39 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Paul Ramsey <pramsey@cleverelephant.ca> writes:
> >> On Jun 26, 2019, at 2:06 PM, Tom Lane <tgl@sss.pgh.pa.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)
residesin 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