1

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
    
  • Do you get the same error message if you execute that SQL command directly on the remote database? – Laurenz Albe Jun 17 at 13:53
  • Replying to @LaurenzAlbe, I don't get the error if I run "SELECT * from remote.locations;" on the 'remote' database. I also don't get the error if the remote table doesn't have the index. – João Ferreira Jun 17 at 15:31
  • Try to create a reproducible test case and report it as a PostgreSQL bug. – Laurenz Albe Jun 17 at 19:05
  • What version of PostGis? How did you install PostgreSQL and PostGis? (compile from source, or use which package manager and which repo?) – jjanes Jun 18 at 0:49
  • @jjanes I'm using a windows server machine. I used the installers: "postgresql-11.3-1-windows-x64.exe" and "postgis-bundle-pg11x64-setup-2.5.2-1.exe" – João Ferreira Jun 18 at 10:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.