Problems using seperate postgres reader/writer hosts with pgstac

[nkleinbaer]

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

stac-fastapi/stac_fastapi/pgstac/stac_fastapi/pgstac/core.py

Lines 119 to 120 in 5dc8e0e

	request: Request = kwargs["request"]
	pool = request.app.state.readpool

This is not a problem when a single host is used for both read and write, as in the docker compose yaml.

stac-fastapi/docker-compose.yml

Lines 47 to 48 in 5dc8e0e

	- POSTGRES_HOST_READER=database
	- POSTGRES_HOST_WRITER=database

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

[duckontheweb]

This issue is now being tracked by stac-utils/stac-fastapi-pgstac#4