Querying information_schema.tables.is_insertable_into with vsizip datasource errors

[robe2]

CREATE EXTENSION ogr_fdw;
CREATE SCHEMA staging;
DROP SERVER IF EXISTS fds_ne CASCADE;
CREATE SERVER fds_ne 
    FOREIGN DATA WRAPPER ogr_fdw
    OPTIONS(datasource '/vsizip//vsicurl/http://postgis.us/downloads/ne_10m_admin_1_states_provinces.zip', format 'ESRI Shapefile');

IMPORT FOREIGN SCHEMA ogr_all FROM SERVER fds_ne INTO staging; 

I also tried with: datasources which are equivalent: /fdw_data/argentina-latest-free.shp.zip and /vsizip//fdw_data/argentina-latest-free.shp.zip. Both work as far as linking and querying but the one with /vsizip chokes in the information_schema.tables query

CREATE SERVER fds_ne 
    FOREIGN DATA WRAPPER ogr_fdw
    OPTIONS(datasource '/vsizip//http://postgis.us/downloads/ne_10m_admin_1_states_provinces.zip', format 'ESRI Shapefile');

Works fine outputs:

NOTICE:  Number of tables to be created 1
IMPORT FOREIGN SCHEMA

This also works:

SELECT f_table_name, f_geometry_column, coord_dimension, srid, type 
FROM geometry_columns 
WHERE f_table_schema = 'staging';

This gives error, but leaving out the is_insertable_into column, it works okay

SELECT table_name, table_type, is_insertable_into 
FROM information_schema.tables 
WHERE table_schema = 'staging';

Error:

ERROR:  GDAL OpenFailed [4] Unable to open /vsizip//vsicurl/http://postgis.us/downloads/ne_10m_admin_1_states_provinces.zip/ne_10m_admin_1_states_provinces.shp or /vsizip//vsicurl/http://postgis.us/downloads/ne_10m_admin_1_states_provinces.zip/ne_10m_admin_1_states_provinces.SHP.
SQL state: XX000

Version info -- I'm puzzled why my ogr_fdw_version is giving me different GDAL version from PostGIS.

SELECT ogr_fdw_version();

outputs:

OGR_FDW="1.1" GDAL="3.2.0"

SELECT postgis_full_version();

outputs:

POSTGIS="3.2.0dev 3.2.0alpha1-19-gf48c4a86a" [EXTENSION] PGSQL="140" GEOS="3.10.0dev-CAPI-1.15.0" SFCGAL="1.3.10" PROJ="7.1.1" GDAL="GDAL 3.2.3, released 2021/04/27" LIBXML="2.9.9" LIBJSON="0.12" LIBPROTOBUF="1.2.1" WAGYU="0.5.0 (Internal)" TOPOLOGY RASTER

[robe2]

Ignore the comment about the mismatched GDAL version. I just checked on my production system and it reads: OGR_FDW="1.1" GDAL="3.2.3"

So I must have compiled my dev with GDAL 3.2.3 (but I'm running it with GDAL 3.2.0) and it's registering the running version and PostGIS is registering the compiled version.

[robe2]

Okay this may be something screwy with my dev setup or something about PG14 RC1 or GDAL 3.2.0 (fixed in 3.2.3). I just tried on my prod version runningPostgreSQL 13.4, compiled by Visual C++ build 1914, 64-bit

SELECT table_name, table_type ,is_insertable_into 
FROM information_schema.tables 
WHERE table_schema = 'staging';

and it gives expected error:

ERROR:  GDAL AppDefined [1] Only read-only mode is supported for /vsicurl
SQL state: XX000

It does look like I did compile ogr_fdw with GDAL 3.2.0 so guess it is reading the compiled version. My runtime is 3.2.3. Oh well going to close this out as screwed up Regina system.

[robe2]

Reopening I just realized this is an error too and not a notice. The issue is many drivers e.g npgsql use information_schema.tables and it would be obscure for a query to fail because of an external link -- so it should somehow be caught on the ogr_fdw side and throw a false or something. I think we had this same issue with some update flag on information_schema.columns

[pramsey]

Can you close this and write up a more terse one with just the reproduction steps and the actual/expected results? I'm not 100% sure what the error you are reporting is.

[robe2]

grrh you and your obsession with terseness.