Thread: Isolation levels on primary and standby

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?

Thanks,
RKN

> 13 янв. 2022 г., в 16:16, RKN Sai Krishna <rknsaiforpostgres@gmail.com> написал(а):
>
> 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?

Hi, RKN!

Transaction isolation level can be set at the beginning of each individual transaction.
You can read more about transaction isolation level at [0].

There are many settings which can be configured different on Standby. E.g. default_transaction_isolation [1]. This is
expectedbehaviour, AFAIK. 

Thanks!

Best regards, Andrey Borodin.


[0] https://www.postgresql.org/docs/current/transaction-iso.html
[1] https://www.postgresql.org/docs/current/runtime-config-client.html

On Thu, Jan 13, 2022 at 4:47 PM RKN Sai Krishna
<rknsaiforpostgres@gmail.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.