When using read-only database option,

Replica lag or not working can cause data inconsistency,

(We had serious issues with it at a production site)

e.g: When creating a course, the record is written to the primary,

And immediately  we try to fetch it from the replica, If its not there,

The course is partially created.

Even after the replica catches up, that course is not usable.

I Have this project that can help demonstrate the issue:

https://github.com/SysBind/moodle-ro-replica

I started  a little patch to fix this,

If get_record_sql with MUST_EXIST couldn't fetch record from the replica,

It will retry from the primary:

https://github.com/SysBind/moodle/tree/master_ro_replica

Reproduction Steps:

• Run 2 postgres instances - primary & replica
• Install clean moodle on it - configured to use the replica for  read-only queries
• Configure 'latency' to 0
• Stop the replication: SELECT pg_wal_replay_pause();
• Create a course - after submitting we get dml_missing_record_exception
• Resume the replication: SELECT pg_wal_replay_resume();
• The course is still defective (No sections, not viewable under category)