Re: Isolation levels on primary and standby

On Thu, Jan 13, 2022 at 4:47 PM RKN Sai Krishna
<rknsaiforpostgres(at)gmail(dot)com> wrote:
>
> Hello All,
>
> It looks like we could have different isolation levels on primary and
> standby servers in the context of replication. If the primary crashes
> and a standby server is made as primary, there could be change in
> query results because of isolation levels. Is that expected?

I think it is possible because the standbys are free to use their own
isolation levels for different purposes. During the failover onto the
standby, the code/tool that's triggering the failover will have to
take care of resetting the isolation level back to the crashed
primary. Presently, the standby requires the max_connections,
max_worker_processes, max_wal_senders, max_prepared_transactions and
max_locks_per_transaction (see the code in
CheckRequiredParameterValues) parameters to be the same as with the
primary, otherwise the standby doesn't start. The postgres doesn't
enforce the standby's isolation level with the primary, though.

IIUC, the WAL that gets generated on the primary doesn't depend on its
isolation level, in other words, the WAL records have no information
of the isolation level. It is the MVCC snapshot, that is taken at the
start of the txn, doing the trick for different isolation levels.

Regards,
Bharath Rupireddy.