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

Joao Ferreira <jpgferreira@yahoo.com>

From: Joao Ferreira <jpgferreira@yahoo.com>
To: "pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>
Date: 2019-06-18T11:28:35Z
Lists: pgsql-bugs

Attachments

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