From: | Etsuro Fujita <etsuro(dot)fujita(at)gmail(dot)com> |
---|---|
To: | Jeff Janes <jeff(dot)janes(at)gmail(dot)com> |
Cc: | Joao Ferreira <jpgferreira(at)yahoo(dot)com>, "pgsql-bugs(at)lists(dot)postgresql(dot)org" <pgsql-bugs(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Querying foreign table with SP-GiST index results in “ERROR: cache lookup failed for type 0” |
Date: | 2019-06-24 11:06:05 |
Message-ID: | CAPmGK16s=M3P28tP_XRiWT49nYysa72MOx57VWWA_VJekrkCNQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
On Thu, Jun 20, 2019 at 6:16 PM Etsuro Fujita <etsuro(dot)fujita(at)gmail(dot)com> wrote:
> On Tue, Jun 18, 2019 at 9:22 PM Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
> > On Tue, Jun 18, 2019 at 8:02 AM Joao Ferreira <jpgferreira(at)yahoo(dot)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 original table has an SP-GiST index on a geometry column, the query on the foreign table fails with "ERROR: cache lookup failed for 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'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.
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
From | Date | Subject | |
---|---|---|---|
Next Message | Juan José Santamaría Flecha | 2019-06-24 11:44:18 | Re: BUG #15789: libpq compilation with OpenSSL 1.1.1b fails on Windows with Visual Studio 2017 |
Previous Message | David Rowley | 2019-06-24 02:51:19 | Re: BUG #15869: Custom aggregation returns null when parallelized |