STAC search sometimes returning HTTP 500s with PostgresSyntaxError syntax error at or near "LIMIT"

[davidcalhoun]

EDIT 2: this was fixed by updating from v0.8.5 to v0.9.1, so I'm closing this out. Thank you.

EDIT: we're on an older version, and noticed that some search fixes went into v0.9.0, we're gonna see if we can update on our side. Will update this ticket.

We've been getting inconsistent responses from STAC search - on first request we get HTTP 500 and on subsequent requests we get HTTP 200 for a few hours or so. Then seemingly after a period of inactivity we make the same request and randomly get HTTP 500 again for one request, followed by HTTP 200s again. All for the same STAC POST search payload.

Note that we're using a slightly older version - pgstac v0.8.5

First try - HTTP POST to search endpoint

POST payload:

{
  "collections": [
    "<REDACTED_COLLECTION>"
  ],
  "limit": 100,
  "fields": {
    "include": [
      "properties",
      "collection",
      "links",
      "geometry",
      "assets"
    ],
    "exclude": [
      "properties.proj:epsg",
      "properties.proj:wkt2",
      "properties.proj:transform",
      "properties.proj:bbox",
      "properties.proj:shape"
    ]
  },
  "sortby": [
    {
      "field": "properties.end_datetime",
      "direction": "desc"
    }
  ],
  "datetime": "2024-07-01T00:00:00.000Z/2024-09-30T23:59:59.999Z"
}

HTTP response: HTTP 500 {"code":"PostgresSyntaxError","description":"syntax error at or near \"LIMIT\""}

After checking the logs, it looks like _orderby is being set to a blank string and _where isn't being set in this code.

Second try - HTTP POST to search endpoint

POST payload: exactly the same as above

HTTP response: HTTP 200 with the expected response (note that this particular collection is empty at the moment - is that part of the issue?):

{"type":"FeatureCollection","context":{"limit":100,"returned":0},"features":[],"links":[{"rel":"root","type":"application/json","href":"<REDACTED>"},{"rel":"self","type":"application/json","href":"<REDACTED>"}]}

Extra context

REDACTED_COLLECTION

{
  "id": "<REDACTED>",
  "type": "Collection",
  "links": [
    {
      "rel": "items",
      "type": "application/geo+json",
      "href": "<REDACTED>"
    },
    {
      "rel": "parent",
      "type": "application/json",
      "href": "<REDACTED>"
    },
    {
      "rel": "root",
      "type": "application/json",
      "href": "<REDACTED>"
    },
    {
      "rel": "self",
      "type": "application/json",
      "href": "<REDACTED>"
    },
    {
      "rel": "items",
      "href": "<REDACTED>",
      "type": "application/geo+json"
    }
  ],
  "title": "<REDACTED>",
  "extent": {
    "spatial": {
      "bbox": [
        [
          -150,
          30,
          -70,
          50
        ]
      ]
    },
    "temporal": {
      "interval": [
        [
          null,
          null
        ]
      ]
    }
  },
  "license": "proprietary",
  "description": "<REDACTED>",
  "stac_version": "1.0.0"
}

Note that the interval was previously set to an outdated time interval - I recently changed this to [null, null] on a hunch it was messing with the SQL statement somehow. But to no effect.

Thanks in advance!

[drnextgis]

The issue has been already discussed here.

[davidcalhoun]

This was fixed by updating from v0.8.5 to v0.9.1, so I'm closing this out. Thank you.