Error: Cannot execute INSERT in a read-only transaction

[jose-lpa]

Hello. First please apologise if this has been asked before. I am having a blocker issue with PgSTAC when trying to run it in AWS cloud using a Postgres RDS setup.

The RDS setup consists of a PostgreSQL cluster with a master instance for writing data and a replica for reading it.

In this standard setup, the replica instance is automatically set up as read-only. This effectively means that no INSERT transactions are accepted by this PostgreSQL instance. Unfortunately, this doesn't play well at all with the logic behind the STAC search functionality. As I can see in PgSTAC implememntation, there are quite a lot of INSERT operations during a regular STAC search. This makes impossible to have the cloud database setup I was thinking on: master-replica(s), where the master is only used for writing and all the replicas for reading, e.g. "searching" or "consulting" items.

This master-replica implementation is not uncommon or something customised by me. Instead, it kind of comes as default setup in cloud providers such as AWS and GCP when you aim to start scaling the system (I've checked in both, and both have the same problem).

My question here is: where are you guys running PgSTAC? How do you configure a high-availability setup with replicas, if I may ask? Where can I find documentation about deploying PgSTAC in a cloud environment?

Many thanks for your time.

[drnextgis]

@jose-lpa please take a look at this #215

[drnextgis]

I experienced a similar issue, though with a slight difference:

postgis=> SELECT get_version();
 get_version 
-------------
 0.9.1
(1 row)

postgis=> SELECT pgstac.readonly();
 readonly 
----------
 t
(1 row)

postgis=> SHOW transaction_read_only;
 transaction_read_only 
-----------------------
 on
(1 row)

postgis=> SELECT search('{}');
NOTICE:  SEARCH: {}
NOTICE:  FILTER: <NULL>
ERROR:  cannot execute SELECT FOR UPDATE in a read-only transaction
CONTEXT:  SQL statement "SELECT *         FROM search_wheres WHERE md5(_where)=inwhere_hash FOR UPDATE"
PL/pgSQL function where_stats(text,boolean,jsonb) line 57 at SQL statement
PL/pgSQL function search(jsonb) line 65 at assignment

It’s strange because this scenario is covered by the test.

@bitner, do you have any idea what might be causing this?

[jose-lpa]

@jose-lpa please take a look at this #215

Many thanks @drnextgis and sorry for my late response. I finally found in the PgSTAC documentation about the [read-only mode](UPDATE pgstac_settings SET value = true WHERE name = 'readonly';).

In full detail, in case anyone else need to know, we made our standard master-replica setup in AWS RDS work with no more issues by just running this statement:

UPDATE pgstac_settings SET value = true WHERE name = 'readonly';

Personally, what I found a bit confusing is the difference @bitner makes between STAC and PostgreSQL database in his comment.

[...] When I think read vs write in the context of "read host" and "write host", I am thinking about the actual records themselves, so the "write host" is used with the transactions api and the "read host" is used for everything else. I've never really counted the statistics, cache, or registering of a search that get written to the database as "writes" from a stac perspective. [...]

IMHO, being PostgreSQL the absolute backend of this application, it would make sense to consider as "writing" anything that actually writes rows to the database, whether it is related to STAC specs or not.

Anyway, I guess this issue can be closed, as the issue was on my side for not properly configuring it, and documentation on how to do it already exists 🙂

Thank you again.