The PostgreSQL streaming physical replication notably does not synchonize sequence data[^1].

For sequences that are only used to generate unique IDs (for primary keys), this is not a concern because those IDs *will be* replicated correctly.

However it's a different story for sequences that are used directly. For us it means that on read-only cursors targeting database replicas, the registry and cache generation numbers (`base_registry_signaling` and `base_cache_signaling_*`) will remain fixed and out of sync with the values on the read-write master database.

As a consequence, workers serving alternating read-write and read-only requests will keep reloading the registry or discarding caches, because they will jump between the progressing master sequences, and the fixed sequences on the replica.

For HTTP workers this causes a peformance hit and suboptimal usage of resources, as the registry will be needlessly reloaded after a "sequence jump", or caches discarded.

For Gevent workers it may be much worse, because the registry reloading will *yield* and may be started by hundreds of different greenlets, blocking as many database connections, until the connection pool is exhausted. Repeat and rinse...

Note that the problem could manifest even for a worker that only ever processes read-write requests (like Gevent), because the registry and signaling checks were explicitly done in read-only mode.

As a mitigation, this patch forces the cursor mode to "read-write" during the registry loading and signaling checks, regardless of the actual request mode. This causes some minimal extra load on the master database, but ensures that the signaling checks will always use the correct sequences. The actual request handling, which is the costly part, will still be executed in the dynamically selected cursor mode.

PS: this patch does not change the possibility of having a pure read-only server, with a replica connection used for both the read-write and read-only database connections in the config. One should however be aware that the signaling sequences will never advance, so the replica will not receive the signals.

-----
Note: in master we could overcome this limitation by defining an explicit append-only `base_signal` table, with an `id` PK column driven by a sequence, like all tables. Then, whenever we want to trigger a signal, we would simply insert a row in the table with the corresponding signal:

```sql

CREATE TABLE base_signal (
    id SERIAL PRIMARY KEY,
    signal VARCHAR
);

-- increment signal `registry`
INSERT INTO base_signal (signal) VALUES ('registry')

-- check current signal `registry`:
SELECT max(id) FROM base_signal WHERE signal = 'registry';

```

The `base_signal` table would be properly synchronized and visible on replicas, and could be trivially garbage collected by the autovacuum system. As a bonus, it would also make signaling transactional, in the sense that a transaction would be unable to trigger a signal and then rollback due to a later error. The sequence ID might advance, but the row won't be inserted.

Cfr task-4399456

[^1]: https://www.postgresql.org/docs/16/logical-replication-restrictions.html