Handle exception on catalog from OperationalError to improve availability

[marcos-nieto-usds]

New Relic has been sending alerts lately with a title such as this one: catalog (prod) query result is > 0.0 on 'catalog.data.gov unavailable' triggered by FACET AWS_US_EAST_1_catalog (prod). The actual region is irrelevant. What's important is that this is mainly caused by this type of error which leads to this effect:
sqlalchemy.exc.OperationalError: (psycopg.errors.SerializationFailure) canceling statement due to conflict with recovery
[2026-09-01 18:26:41,092] ERROR in app: Exception on / [GET]

Example of logs from NewRelic are located here:
https://one.newrelic.com/logger?account=1601367&begin=1788286818434&end=1788286938434&state=d1e356a7-a399-92e1-6f43-d1bbac1d8845

How to reproduce

1. The Exception on / [GET] is basically an exception occurring at the homepage where we display the datasets. At the homepage there is a call to count_all_datasets_in_search function which fails when trying to GET the total number of datasets from our index.
2. The failure is from a SerializationFailure error which causes type 500 errors. These errors are intermittent, the new relic alert is triggered once there's at least one failure within a 60 second window.
3. Every minute, there's another check for this, if the site continues to return a 500, then the alert will remain until the site comes back up, resolving the alert.
4. The actual issue is a bit specific with the behavior of our database. First, there's a query that is running for reading the contents for the Select on the root page. This would be on the read replica.
5. At the same time, on the primary, a request occurs, from some source, such as "vacuum cleanup" which is removing old row versions that the replica still needs.
6. If the read query takes a long time, while the request is occurring, then the replica must decide between the request or the read.
7. Postgres has a setting max_standby_streaming_delay which tells the DB how long to wait, before opting for "OperationalError: (psycopg.errors.SerializationFailure) canceling statement due to conflict with recovery
8. The log " DETAIL: User query might have needed to see row versions that must be removed." from https://onenr.io/0MR2Ly91YQY suggests the example error detailed in step 5.

Expected behavior

SerializationFailure error should be caught or handled so that catalog stays available.

Actual behavior

The issue is that there's a SerializationFailure error that is not caught or handled, which returns as a 500 error by flask.

Sketch

Instead of messing with DB settings that affect this behavior, I'd say its more straightforward to just handle the exception thrown. Catch the exception and add a retry with a small delay and let it fail after a certain amount of attempts.

[marcos-nieto-usds]

The logs provide OperationalError documentation straight from SQLAlchemy:

• https://docs.sqlalchemy.org/en/20/errors.html#error-e3q8

[marcos-nieto-usds]

The actual queries that have been identified for this behavior, mentioned by the alert, are interface.get_dataset_by_dcat_identifier and interface.get_organization_by_id. The retry approach will solve this issue, when it comes up. The cause of why these queries are long-running, at times, is the fundamental question that is currently being investigated.