Re: Synchronous commit behavior during network outage - Mailing list pgsql-hackers

Hello,

> You can monitor the pg_stat_activity for the SYNC_REP_WAIT_FLUSH wait types to detect this.

I tried to see this this wait_event_type Client or IPC and wait_event Client_Read or SyncRep. In which situation I can see the SYNC_REP_WAIT_FLUSH value?

> You should consider these as in doubt transactions and the client should retry. Again, this can happen in a normal server crash case too. For example, a transaction committed on the server and before sending the acknowledgement crashed.  *The client should know how to handle these cases.*

This can be an option for us in our case. But there also needs to be a process how to detect these "stuck commits" and how to invalidate/remove them, because in reality, if the app/user would not see the change in the database, it/he/she will try to insert/delete it again. If it just stuck without management, it will create a queue which can cause, that in the queue there will be 2 similar inserts/deletes which can again cause issues (like with the primary key I mentioned before).

 This shouldn't be a problem as the previous transaction is still holding the locks and the new transaction is blocked behind this. Outside of the sync replication, this can happen today too with glitches/timeouts/ retries between the client and the server. Am I missing something?

So the process should be in this case:

- DBA receives information, that write operations stuck (DBA in coordination with the infrastructure team disconnects all clients and prevent new ones to create a new connection).

You can monitor the pg_stat_activity for the SYNC_REP_WAIT_FLUSH wait types to detect this.

 

- DBA will recognize, that there is an issue in communication between the primary and the sync replica (caused the issue with the propagation of commits)
- DBA will see that there are some commits that are in the "stuck state"
- DBA removes these stuck commits. Note: Because the client never received a confirmation about the successful commit -> changes in the DB client tried to perform can't be considered as successful.

You should consider these as in doubt transactions and the client should retry. Again, this can happen in a normal server crash case too. For example, a transaction committed on the server and before sending the acknowledgement crashed.  The client should know how to handle these cases.

- DBA and infrastructure team restore the communication between server nodes to be able to propagate commits from the primary node to sync replica.
- DBA and infrastructure team allows new connections to the database

This approach would require external monitoring and alerting, but I would say, that this is an acceptable solution. Would your patch be able to perform that?

My patch handles ignoring the cancel events. I ended up keeping the other logic (blocking super user connections in the client_authentication_hook.

There is a third problem that I didn't talk about in this thread where the async clients (including logical decoding and replication clients) can get ahead of the new primary and there is no easier way to undo those changes. For this problem, we need to implement some protocol in the WAL sender where it sends the log to the consumer only up to the flush LSN of the standby/quorum replicas. This is something I am working on right now.