2

I get the following error when trying to send queries to a Postgres server in hot standby:

    BEGIN;
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY;
    COMMIT;

And the error:

SQL Error [0A000]: ERROR: cannot use serializable mode in a hot standby
  Hint: You can use REPEATABLE READ instead.

Error position:

My question is why is this limitation in place? Given that no query on the standby itself could cause an anomaly, it seems like REPEATABLE READ is equivalent to SERIALIZABLE. My best guess is that replication from the primary to the standby may cause partially executed transactions to be visible on the standby. However, after reading about Predicate Locks, it would seem that the standby could use these locks to prevent any anomalies from being visible to the clients.

Even after reading the docs, I don't understand how purely read only transactions could experience an anomaly.

Improve this question

1 Answer 1

Reset to default
4

Serialization doesn't work on standby servers because the predicate locks need to be applied on the primary to work correctly. A client connected to a standby can't know what rows are currently being touched on the primary. Since that's necessary information to avoid the anomalies, you are stuck with them on a standby.

When I was working on the hot_standby_feedback mechanism we thought about letting standby servers participate in serialization via that communications channel. The code would have been tricky at best, and seemed more likely a cause of new failure modes than a reliable solution. Coordinating transaction order among multiple database nodes is complicated.

Improve this answer
1
  • What kind of anomaly could exist? Why does applying the predicate at the standy not prevent the anomaly from occurring? It seems like anomalies between clients transacting on the the primary AND the standby might get them, but not between 2 TX's on the same standby. Commented Oct 17 at 21:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.