search

stac-utils / stac-fastapi-pgstac

PostgreSQL backend for stac-fastapi using pgstac (https://github.com/stac-utils/pgstac)
MIT License
45 stars 20 forks source link

Problems using seperate postgres reader/writer hosts with pgstac #4

Open duckontheweb opened 2 years ago

duckontheweb commented 2 years ago

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 commented 1 year ago

confirmed here that search is not a read-only operation: https://github.com/stac-utils/pgstac/issues/191#issuecomment-1613537719

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 commented 6 months ago

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

mindflayer commented 3 weeks ago

As far as I understand, stac-fastapi-pgstac still knows nothing about the readonly mode, right?

jbeezley commented 3 weeks ago

I was able to get it to work in RDS. You have to use one of the pre-release 3.0 versions of stac-fastapi-pgstac, then update the pgstac setting readonly to true.

mindflayer commented 2 weeks ago

I was able to get it to work in RDS. You have to use one of the pre-release 3.0 versions of stac-fastapi-pgstac, then update the pgstac setting readonly to true.

What version of pgSTAC did you have to use, @jbeezley ? I don't quite understand what is the right combination for make it happen. Thanks in advance!

jbeezley commented 2 weeks ago

Looks like 3.0 of stac-fastapi-pgstac was just released. It will use pgstac 0.8.x. I'm using the docker images posted at https://github.com/stac-utils/stac-fastapi-pgstac/pkgs/container/stac-fastapi-pgstac and setting environment variables for the reader/writer endpoints from RDS.