ogr utilities do not respect the PostgreSQL search_path

[mattskone]

Expected behavior and actual behavior.

I expect ogr utilities (ogrinfo, ogr2ogr) to use the schema search_path set in the target database if a schema or search path is not otherwise specified.

The use case is controlling the schema search path at the database level rather than at the clients. This allows the database schemas to change (eg, in a blue/green deployment pattern) without having to change client code.

This worked fine until GDAL 3.1.0.

Steps to reproduce the problem.

These examples connect to a local instance of PostgreSQL 16 where search_path is set to "$user", vectors, sys, users, extensions.

Using GDAL verison <= 3.0.4, the correct search_path is returned (and queries to tables in those schemas are successful):

# ogrinfo PG:"host=localhost dbname=foo user=bar password=password" -sql "show search_path"
...
OGRFeature(sql_statement):1
  search_path (String) = "$user", vectors, sys, users, extensions

However, when using GDAL >= 3.1.0, the search_path returned is always public, extensions:

# ogrinfo PG:"host=localhost dbname=foo user=bar password=password" -sql "show search_path"
...
OGRFeature(sql_statement):1
  search_path (String) = public, extensions

Operating system

Ubuntu 20.04, 22.04

GDAL version and provenance

Docker images

• osgeo/gdal:ubuntu-small-3.0.4 (works as expected)
• osgeo/gdal:ubuntu-small-3.1.0 (fails as noted)
• osgeo/gdal:ubuntu-small-3.6.3 (fails as noted)

[jratike80]

The latest supported GDAL version is 3.7 so please test with it first. You could have also mentioned these gis.stackexcange questions because their include some details and also an usage example of -oo prelude_statements.

https://gis.stackexchange.com/questions/469340/ogr2ogr-postgresql-driver-doesnt-seem-to-work-with-list-of-schemas?
https://gis.stackexchange.com/questions/469339/why-doesnt-ogr2ogr-support-the-postgresql-schema-search-path/469356?

Also, we prefer to use gdal-dev mailing list for asking questions. If the reported behavior appears to be a bug then it is the time to make a bug report.

[rouault]

should be fixed per #8642