Re: Synchronous commit behavior during network outage

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.*

I have just a light knowledge of the in-doubt transaction. Need to study
more about it, but in real world the client is mostly 'stupid' and does
expect only COMMIT or ROLLBACK. Nothing between.

> 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.

We setup and architecture where are 4 nodes and Patroni as a cluster
manager. Two nodes are sync an each sync node has 1 async. In case
something like this happen (e.g. network to sync replica fails and user
press the CTRL+C), the async replica receives the transaction and apply
it. If the outage is longer than some time (30s by default), management
software checks the LSN and create a new sync replica from the ASYNC
replica.

Ondrej

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
On 21/04/2021 09:20, SATYANARAYANA NARLAPURAM wrote:
>
> 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.
>