<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=Windows-1252">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Aptos;
panose-1:2 11 0 4 2 2 2 2 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
font-size:11.0pt;
font-family:"Aptos",sans-serif;
mso-ligatures:standardcontextual;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#467886;
text-decoration:underline;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:11.0pt;
mso-ligatures:none;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.WordSection1
{page:WordSection1;}
--></style>
</head>
<body lang="EN-CA" link="#467886" vlink="#96607D" style="word-wrap:break-word">
<div class="WordSection1">
<p class="MsoNormal">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:
</p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">DB_READ_STRING = "postgresql://{read_user}:{postgres_password}@{postgres_read_host}:{postgres_port}/{postgres_database}"</p>
<p class="MsoNormal">warp_options = gdal.WarpOptions(format="GTiff", cutlineDSName=DB_READ_STRING, cutlineSQL=f"SELECT geom FROM my_table WHERE id=1", cropToCutline=True)</p>
<p class="MsoNormal">res = gdal.Warp(output_path, my_raster, options=warp_options)</p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">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:
<a href="https://github.com/OSGeo/gdal/pull/8642">https://github.com/OSGeo/gdal/pull/8642</a></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">I turned ON the CPL_DEBUG flag and see the following when trying to perform the warp:</p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">PG: Client encoding: 'UTF8'</p>
<p class="MsoNormal">PG: PostGIS schema: 'public'</p>
<p class="MsoNormal">PG: Modifying search_path from "" to '',"",'public'</p>
<p class="MsoNormal">PG: Command "SET search_path='',"",'public'" failed. Trying without 'public'.</p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">I managed to narrow down the source of the issue and workaround the problem but I think there is room for improvement in gdal.</p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">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.</p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">SET search_path=‘’,“”,public</p>
<p class="MsoNormal">ERROR: zero-length delimited identifier at or near """"</p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">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:</p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">PG: Client encoding: 'UTF8'<o:p></o:p></p>
<p class="MsoNormal">PG: PostGIS schema: 'public'<o:p></o:p></p>
<p class="MsoNormal">PG: Modifying search_path from "$user", public to '',"$user", public</p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">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.</p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">I’m happy to create a new issue in GitHub if you feel this is an issue.</p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Thanks,</p>
<p class="MsoNormal"><o:p> </o:p></p>
<div>
<div>
<p class="MsoNormal"><span style="font-family:"Calibri",sans-serif;color:black;mso-ligatures:none">Darren Boss<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Calibri",sans-serif;color:black;mso-ligatures:none">Lead Full Stack Developer, Development & Digital Services<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Calibri",sans-serif;color:black;mso-ligatures:none">Water, Land & Resource Stewardship | NRIDS<o:p></o:p></span></p>
<p class="MsoNormal"><a href="mailto:Darren.Boss@gov.bc.ca"><span style="font-family:"Calibri",sans-serif;color:#0563C1;mso-ligatures:none">Darren.Boss@gov.bc.ca</span></a><span style="font-family:"Calibri",sans-serif;color:black;mso-ligatures:none"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Calibri",sans-serif;mso-ligatures:none"><o:p> </o:p></span></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
</body>
</html>