Why doesn't ogr2ogr support the PostgreSQL schema search path?

Question

I have a PostGIS database with a table vector_data in schema vectors, and the vectors schema is listed in the database search_path. In GDAL <= 3.0.4, this command works fine (that is, the vector_data table is reachable because it's in the search_path):

ogr2ogr -f GeoJSON "test.geojson" PG:"host=localhost port=5432 user=username password=password" -sql "SELECT ST_AsGeoJSON(geom) FROM vector_data LIMIT 1;"

However, in GDAL >= 3.1.0, that same command fails as if the search_path is being ignored:

ERROR 1: ERROR: relation "vector_data" does not exist
LINE 1: ...LCursor CURSOR for SELECT ST_AsGeoJSON(geom) FROM vector_dat...
 ^

Questions: Why did this ogr2ogr behavior change, and precisely where (which GitHub issue/PR)?

• Mike T: try adding -lco SCHEMA=your_schema_name to your command
• jbalk: In your sql, you need to specify the schema if it's not in public. So change vector_data to vectors.vector_data
• Matt Skone: @MikeT @jbalk I get the same error using -lco SCHEMA. I'm aware that I can specify a schema in the connection string, though, or specify it using dot notation in the query, and those do work. But my question is WHY did the behavior change? Was this intentional within GDAL? Or did GDAL pick up an unintended change in the newer libpq driver (version 10 vs 12)? I'm surprised to find a breaking change in a GDAL minor release (3.0.4 to 3.1.0).
• Mike T: Probably introduced with this change for 3.1.0. Report the bug to GDAL.

Answer 1

If the change was due to that commit https://github.com/OSGeo/gdal/pull/2405 then obviously it did not break anything that is tested. The PostGIS Python tests are in https://github.com/OSGeo/gdal/blob/master/autotest/ogr/ogr_pg.py. I guess that the code that handles the schema options is in https://github.com/OSGeo/gdal/blob/master/ogr/ogrsf_frmts/pg/ogrpgdatasource.cpp. Unfortunately I cannot read the code well enough to understand what it does for the search_path.

You can check what search_path GDAL gets by with ogrinfo. For me it shows:

ogrinfo PG:"host=localhost port=5432 user=username password=password" -sql "show search_path"
...
OGRFeature(sql_statement):1
 search_path (String) = "$user", public

It is possible to set the search_path with the -prelude_statements open option. For example

ogrinfo PG:"host=localhost port=5432 user=username password=password" -sql "show search_path" -oo prelude_statements="SET search_path TO public,lidar"
...
OGRFeature(sql_statement):1
 search_path (String) = public, lidar

This way I query the data from a table that exists in the lidar schema, but not in "public" with

ogrinfo PG:"host=localhost port=5432 user=username -sql "select count(*) from test_dem2" -oo prelude_statements="SET search_path TO public,lidar"
...
OGRFeature(sql_statement):0
 count (Integer64) = 2

• Matt Skone: Thanks - ogrinfo is much easier to repro/demo this issue! But the problem behavior still exists. If I use -prelude_statements to set the search_path in GDAL <=3.0.4, that search_path is returned. But in GDAL >=3.1.0, I get "search_path (String) = public, extensions" no matter what search_path I attempt to set.
• Matt Skone: What GDAL version are you using in your examples above? @user30184
• user30184: Version 3.8dev.
• Matt Skone: Isn't 3.7.2 the most recent? github.com/OSGeo/gdal/releases
• user30184: Yes, 3.7.2 is the current version and 3.7.3 will be released perhaps tomorrow. 3.8dev is the development version. The fix for the search_path issue should be included in version 3.8 github.com/OSGeo/gdal/pull/8642. But the open option workaround for setting the search path seems to work for me also with some older versions. Did you notice that you must use it with every command, it does not have a permanent effect?