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
- local_db.sql (text/plain)
- remote_db.sql (text/plain)
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