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

From: Joao Ferreira <jpgferreira(at)yahoo(dot)com>
To: "pgsql-bugs(at)lists(dot)postgresql(dot)org" <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Subject: Querying foreign table with SP-GiST index results in “ERROR: cache lookup failed for type 0”
Date: 2019-06-18 11:28:35
Message-ID: 1407840601.3022762.1560857315693@mail.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Greetings,
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).

OS: Windows Server 2016 StandardRAM: 16GB
Postgres installer: postgresql-11.3-1-windows-x64.exePostgis installer: postgis-bundle-pg11x64-setup-2.5.2-1.exePostgres version (using 'SELECT version()'): PostgreSQL 11.3, compiled by Visual C++ build 1914, 64-bit Postgis version (using 'SELECT postgis_version()'): 2.5 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
DBA StackExchange: https://dba.stackexchange.com/questions/240718/querying-foreign-table-with-sp-gist-index-results-in-error-cache-lookup-failed

Bug reproduction steps:
1) Create remote database: psql -f remote_db.sql postgres postgres
2) Create local database:psql -f local_db.sql postgres postgres

3) Select directly on remote database: psql -c "SELECT * FROM remote.locations" remote postgres
Output: id | name | lonlat----+------+--------(0 rows)
4) Select through foreign table: psql -c "SELECT * FROM localdb.locations" localdb postgres
Output: id | name | lonlat----+------+--------(0 rows)
5) Add SP-GiST index to remote database:psql -c "CREATE INDEX remote_locations_lonlat_idx ON remote.locations USING spgist (lonlat)" remote postgres
Output:CREATE INDEX

6) Select directly on remote database:psql -c "SELECT * FROM remote.locations" remote postgres
Output: id | name | lonlat----+------+--------(0 rows)
7) Select through foreign table (with log error verbosity set to verbose): psql -c "SET log_error_verbosity TO verbose; SELECT * FROM localdb.locations;" localdb postgres
Output: ERROR:  cache lookup failed for type 0CONTEXT:  remote SQL command: SELECT id, name, lonlat FROM remote.locations

From the logs I have:2019-06-18 12:20:03.195 BST [5688] ERROR:  XX000: cache lookup failed for type 02019-06-18 12:20:03.195 BST [5688] CONTEXT:  remote SQL command: SELECT id, name, lonlat FROM remote.locations2019-06-18 12:20:03.195 BST [5688] LOCATION:  pgfdw_report_error, d:\pginstaller.auto\postgres.windows-x64\contrib\postgres_fdw\connection.c:6332019-06-18 12:20:03.195 BST [5688] STATEMENT:  SET log_error_verbosity TO verbose; SELECT * FROM localdb.locations;

Hope I have provided all the needed information, if not, feel free to ask for it. Thank you in advance for all the help.
Best regards,
João Ferreira

Attachment Content-Type Size
local_db.sql text/plain 3.5 KB
remote_db.sql text/plain 3.9 KB

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Jeff Janes 2019-06-18 12:22:14 Re: Querying foreign table with SP-GiST index results in “ERROR: cache lookup failed for type 0”
Previous Message Thomas Munro 2019-06-18 11:17:07 Re: BUG #15857: Parallel Hash Join makes join instead of exists