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

Etsuro Fujita <etsuro(dot)fujita(at)gmail(dot)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