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

1 vote
0 answers
118 views
                          I'm using PostgreSQL 11.3 (compiled by Visual C++ build 1914, 64-bit) 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.  

Does anyone knows how to query the foreign table without throwing the error and still keeping the SP-GiST index on the remote table?
  
  
Here's a sample SQL to reproduce the error:  

1. Create remote database:

        CREATE DATABASE remote
            WITH 
            OWNER = postgres
            ENCODING = 'UTF8'
            LC_COLLATE = 'English_United States.1252'
            LC_CTYPE = 'English_United States.1252'
            TABLESPACE = pg_default
            CONNECTION LIMIT = -1;

   
2. Create local database:

        CREATE DATABASE localdb
            WITH 
            OWNER = postgres
            ENCODING = 'UTF8'
            LC_COLLATE = 'English_United States.1252'
            LC_CTYPE = 'English_United States.1252'
            TABLESPACE = pg_default
            CONNECTION LIMIT = -1;

3. Setup remote database (connect to 'remote' database before running the following SQL):

        CREATE EXTENSION postgis;
        CREATE EXTENSION postgres_fdw;
        
        CREATE SCHEMA remote;
        
        CREATE TABLE remote.locations
        (
            id bigserial NOT NULL UNIQUE,
        	name character varying,
            lonlat geometry NOT NULL,
            PRIMARY KEY (id)
        ) 
        WITH (
            OIDS = FALSE
        );
        CREATE INDEX remote_locations_lonlat_idx
            ON remote.locations USING spgist
            (lonlat);

4. Setup local database (connect to 'localdb' database before running the following SQL):

        CREATE EXTENSION postgis;
        CREATE EXTENSION postgres_fdw;
        
        CREATE SCHEMA localdb;
        
        CREATE SERVER remote FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'localhost', dbname 'remote', port '5432', extensions 'postgis');
        CREATE USER MAPPING FOR postgres SERVER remote OPTIONS (user 'postgres', password 'YOUR_POSTGRES_USER_PASSWORD');
        
        IMPORT FOREIGN SCHEMA remote FROM SERVER remote INTO localdb OPTIONS (import_not_null 'true');

5. Querying the foreign table...

        SELECT * from localdb.locations;

6. ...results in:

        ERROR:  cache lookup failed for type 0
        CONTEXT:  remote SQL command: SELECT id, name, lonlat FROM remote.locations
        SQL state: XX000
                        
Asked by João Ferreira (11 rep)
Jun 17, 2019, 01:08 PM
Querying foreign table with SP-GiST index results in "ERROR: cache lookup failed for type 0"

Related Questions
