Problems using seperate postgres reader/writer hosts with pgstac

[duckontheweb]

Ported from stac-utils/stac-fastapi#370.

Original Issue:

I'm running into problems when using pgstac with separate reader and writer hosts (in my case an Aurora cluster). Search endpoints return an error similar to the following:

 cannot execute SELECT FOR UPDATE in a read-only transaction

The issue appears to be that the SQL functions defined by pgstac for searching perform operations that require write access (select for update, create temp table, etc). But, the search endpoints connect to the DB through the readpool
https://github.com/stac-utils/stac-fastapi/blob/5dc8e0eb63d9cc07663968ed5ed9505e753bf298/stac_fastapi/pgstac/stac_fastapi/pgstac/core.py#L119-L120

This is not a problem when a single host is used for both read and write, as in the docker compose yaml. https://github.com/stac-utils/stac-fastapi/blob/5dc8e0eb63d9cc07663968ed5ed9505e753bf298/docker-compose.yml#L47-L48

When POSTGRES_HOST_READER points to a host that is truly read-only, however, this becomes a problem.

[bkanuka]

confirmed here that search is not a read-only operation:
stac-utils/pgstac#191 (comment)

The problem may be here: https://github.com/stac-utils/stac-fastapi-pgstac/blob/main/stac_fastapi/pgstac/core.py#L163

Because search is a write operation, it should be using ...get_connection(request, "w")

[jbeezley]

Now that stac-utils/pgstac#215 is merged. Does that fix this issue? Is there anything that needs to be done to make use of it?