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 Standard
RAM: 16GB
Postgres installer: postgresql-11.3-1-windows-x64.exe
Postgis installer: postgis-bundle-pg11x64-setup-2.5.2-1.exe
Postgres 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
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 0
CONTEXT: 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 0
2019-06-18 12:20:03.195 BST [5688] CONTEXT: remote SQL command: SELECT id, name, lonlat FROM remote.locations
2019-06-18 12:20:03.195 BST [5688] LOCATION: pgfdw_report_error, d:\pginstaller.auto\postgres.windows-x64\contrib\postgres_fdw\connection.c:633
2019-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