<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<p>Darren,</p>
<p>thanks for the report. I could indeed reproducing setting first
an empty search path (single quote single quote)<br>
</p>
<p>alter role {my_user_name} set search_path='';</p>
<p>Fix in <a class="moz-txt-link-freetext" href="https://github.com/OSGeo/gdal/pull/10980">https://github.com/OSGeo/gdal/pull/10980</a></p>
<p>Even<br>
</p>
<div class="moz-cite-prefix">Le 09/10/2024 à 21:18, Boss, Darren
WLRS:EX via gdal-dev a écrit :<br>
</div>
<blockquote type="cite"
cite="mid:YT3P288MB0160528083A4F703C6E661A6AA7E2@YT3P288MB0160.CANP288.PROD.OUTLOOK.COM">
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<meta name="Generator"
content="Microsoft Word 15 (filtered medium)">
<style>@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;}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;}div.WordSection1
{page:WordSection1;}</style>
<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"
moz-do-not-send="true" class="moz-txt-link-freetext">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"
moz-do-not-send="true"><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>
<br>
<fieldset class="moz-mime-attachment-header"></fieldset>
<pre class="moz-quote-pre" wrap="">_______________________________________________
gdal-dev mailing list
<a class="moz-txt-link-abbreviated" href="mailto:gdal-dev@lists.osgeo.org">gdal-dev@lists.osgeo.org</a>
<a class="moz-txt-link-freetext" href="https://lists.osgeo.org/mailman/listinfo/gdal-dev">https://lists.osgeo.org/mailman/listinfo/gdal-dev</a>
</pre>
</blockquote>
<pre class="moz-signature" cols="72">--
<a class="moz-txt-link-freetext" href="http://www.spatialys.com">http://www.spatialys.com</a>
My software is free, but my time generally not.</pre>
</body>
</html>