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 |
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 |