I have a Python back-end that uses gdal.Warp to clip a GeoTiff using a geometry
from a Postgres 14 (with PostGIS) table. I set up the gdal.warpOptions and pass
them into gdal.warp as follows:
DB_READ_STRING =
"postgresql://{read_user}:{postgres_password}@{postgres_read_host}:{postgres_port}/{postgres_database}"
warp_options = gdal.WarpOptions(format="GTiff", cutlineDSName=DB_READ_STRING,
cutlineSQL=f"SELECT geom FROM my_table WHERE id=1", cropToCutline=True)
res = gdal.Warp(output_path, my_raster, options=warp_options)
This has been working great with gdal 3.4 in a K8s cluster running in a private
cloud using an Ubuntu 20 base image. I’m in the process of updating to an
Ubuntu 24 LTS image which will be using gdal 3.8.4 and running into problems
where the table name (my_table) in the cutlineSQL can’t be found. The problem
seems to be related to the search_path and the changes made in this PR:
https://github.com/OSGeo/gdal/pull/8642
I turned ON the CPL_DEBUG flag and see the following when trying to perform the
warp:
PG: Client encoding: 'UTF8'
PG: PostGIS schema: 'public'
PG: Modifying search_path from "" to '',"",'public'
PG: Command "SET search_path='',"",'public'" failed. Trying without 'public'.
I managed to narrow down the source of the issue and workaround the problem but
I think there is room for improvement in gdal.
I narrowed the problem down to ogrpgdatasource.cpp which executes a query (SHOW
search_path) to retrieve the search_path. In my case the search_path being
returned was an empty string (or maybe it was a string containing a pair of
double quotes like ‘””’ or NULL, I’m not sure). The code in ogrpgdatasource.cpp
would then create a new search_path with three comma separated values: ‘’, “”,
public. Gdal then tries to set this as the search_path and postgres complains
about the empty string with double quotes.
SET search_path=‘’,“”,public
ERROR: zero-length delimited identifier at or near """"
It looks like the issue stemmed from the fact that the user specified in the
connection string does not have an associated schema. The workaround in my case
was to create a matching schema and explicitly set the search path for my user
to “$user”,public. Simply setting the search_path to $user/public for my role
or user was insufficient, I needed the schema and also had to grant usage on
the new schema to the appropriate role. Setting the search path on the database
was also insufficient to resolve the issue. Here’s the debug output now:
PG: Client encoding: 'UTF8'
PG: PostGIS schema: 'public'
PG: Modifying search_path from "$user", public to '',"$user", public
So yes, there was likely a configuration issue in my crunchyDB cluster, but my
question is, would gdal’s creation of an invalid search_path with the inclusion
of an empty, double quoted string be considered a bug from your point-of-view?
The empty, single quoted string that gdal adds to the search_path also seems
odd but doesn’t appear to cause any issues.
I’m happy to create a new issue in GitHub if you feel this is an issue.
Thanks,
Darren Boss
Lead Full Stack Developer, Development & Digital Services
Water, Land & Resource Stewardship | NRIDS
darren.b...@gov.bc.ca<mailto:darren.b...@gov.bc.ca>
_______________________________________________
gdal-dev mailing list
gdal-dev@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/gdal-dev
