STAC Server responding with SQL Create Error when using /collections/<id>/items with dedicated STAC Reader

[ChristineSeeliger]

In our setup with a dedicated AWS Aurora READ replica as Reader, the STAC server responds with this error message when querying the collections/_collection_id/items endpoints.
{
"code": "ReadOnlySQLTransactionError",
"description": "cannot execute CREATE TABLE in a read-only transaction"
}

The issues obviously disappears when setting the reader to the read/write instance however, I assume having a writeable instance as READER wouldn't be the intention.

[bitner]

Right now, using search on pgstac is not a read only operation. It uses two tables searches and search_wheres that allow for the use of a hash for queries (leveraged by titiler-pgstac), cache expensive calls for context counts and maintain statistics about how many times queries have been used.

[bkanuka]

Is there a bug or discussion about making search (optionally) a read-only operation? I would be interested in this.

[bitner]

@ChristineSeeliger @bkanuka I've begun work on adding an option to allow use on read only instances in #215.

You should note that this mode will not allow you to register queries with their hash as used in TiTiler-PGStac.
It also prevents the use of a cache for context results (total number of results) - in particular, if you have the "context" setting turned on, queries (particularly when paging) may be very slow.