PostgresSyntaxError","description":"syntax error at or near \") when fetching items

[jometho]

When I do a stac search against the Planetary Computer stac api (https://planetarycomputer.microsoft.com/api/stac/v1) and get the ItemSearch, then trying to get the individual items from the paginated ItemSearch, oine of the pages throws an exception with the following error "code":"PostgresSyntaxError","description":"syntax error at or near \")\""

Wraping the loop like so does not catch the exception that seems to come from stac_utils. try: for item in items: results.append(compose_results(item, geojson_aoi)) except:

Consequently, we loose all the items in this Item Search, is an improvement that could be done to gracefully handle these errors. I suspect the problem is while getting the paginated items one of the queries parses a bad character to Postgres on Planetary server side and hence the error. But I'm out of options on how to handle it and continue with the remaining good results.

Sample code to reproduce

from pystac_client import Client
def test_reproduce_stac_api_error():
    client = Client.open("https://planetarycomputer.microsoft.com/api/stac/v1")

    item_search = client.search(
        datetime= "2022-06-01T00:00:00Z/2022-11-30T00:00:00Z",
        collections= ["sentinel-2-l2a"],
        intersects= {"coordinates": [
            [[-123.02807090795983, 49.270111606171525], [-123.02279737380513, 49.2989211208955],
             [-123.11640256572116, 49.307087919613224], [-123.17572980003439, 49.30107040967934],
             [-123.22846511640321, 49.282583461923394], [-123.23769379729633, 49.24989190143958],
             [-123.24692247818946, 49.21330330987789], [-123.2699941798202, 49.1706541972527],
             [-123.23250051250207, 49.11939296946704], [-123.07587575971581, 49.087100572108426],
             [-122.93076784010388, 49.09482384190619], [-122.8384810311727, 49.1461629742241],
             [-122.81606852043195, 49.231897087071246], [-122.99940095844943, 49.29780237148185],
             [-123.00071934143389, 49.26856207396219], [-122.83789904281912, 49.22682163950586],
             [-122.87415457489953, 49.14970281858811], [-122.9756700647242, 49.122100433404995],
             [-123.12728410796859, 49.1328844445041], [-123.22616283182361, 49.16220508925912],
             [-123.20375032108322, 49.26899220395606], [-123.07916312902576, 49.28275438380882],
             [-123.02807090795983, 49.270111606171525]]], "type": "Polygon"},
        query= {"eo:cloud_cover": {"lt": 100.0}},
        sortby= [{"field": "properties.created", "direction": "desc"},
                   {"field": "properties.eo:cloud_cover", "direction": "asc"}]
    )
    for item in item_search.items():
        print(f"Item ID {item.id}")

Will throw an error os this sort

POST https://planetarycomputer.microsoft.com/api/stac/v1/search Headers: {'User-Agent': 'python-requests/2.28.1', 'Accept-Encoding': 'gzip, deflate', 'Accept': '*/*', 'Connection': 'keep-alive', 'Content-Length': '1368', 'Content-Type': 'application/json'} Payload: {"limit": 100, "datetime": "2022-06-01T00:00:00Z/2022-11-30T00:00:00Z", "collections": ["sentinel-2-l2a"], "intersects": {"coordinates": [[[-123.02807090795983, 49.270111606171525], [-123.02279737380513, 49.2989211208955], [-123.11640256572116, 49.307087919613224], [-123.17572980003439, 49.30107040967934], [-123.22846511640321, 49.282583461923394], [-123.23769379729633, 49.24989190143958], [-123.24692247818946, 49.21330330987789], [-123.2699941798202, 49.1706541972527], [-123.23250051250207, 49.11939296946704], [-123.07587575971581, 49.087100572108426], [-122.93076784010388, 49.09482384190619], [-122.8384810311727, 49.1461629742241], [-122.81606852043195, 49.231897087071246], [-122.99940095844943, 49.29780237148185], [-123.00071934143389, 49.26856207396219], [-122.83789904281912, 49.22682163950586], [-122.87415457489953, 49.14970281858811], [-122.9756700647242, 49.122100433404995], [-123.12728410796859, 49.1328844445041], [-123.22616283182361, 49.16220508925912], [-123.20375032108322, 49.26899220395606], [-123.07916312902576, 49.28275438380882], [-123.02807090795983, 49.270111606171525]]], "type": "Polygon"}, "query": {"eo:cloud_cover": {"lt": 100.0}}, "sortby": [{"field": "properties.created", "direction": "desc"}, {"field": "properties.eo:cloud_cover", "direction": "asc"}], "token": "next:S2A_MSIL2A_20221027T191511_R056_T10UEV_20221028T085251"}
<Response [500]>

[mmcfarland]

Ref: https://github.com/stac-utils/pgstac/issues/152

[jometho]

@mmcfarland thanks for your efforts. I'll use one sortBy parameter meanwhile to proceed and roll out our product. Your support is highly appreciated.

[mmcfarland]

Hi @jometho -- we've included the referenced fix in our latest release and your test case is now returning items. Thanks for the reproducible example!