Re: Synchronous commit behavior during network outage

Hello Satyanarayana,

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

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

Thank you
Ondrej

On 20/04/2021 22:19, SATYANARAYANA NARLAPURAM wrote:
> One idea here is to make the backend ignore query cancellation/backend
> termination while waiting for the synchronous commit ACK. This way
> client never reads the data that was never flushed remotely. The
> problem with this approach is that your backends get stuck until your
> commit log record is flushed on the remote side. Also, the client can
> see the data not flushed remotely if the server crashes and comes back
> online. You can prevent the latter case by making a SyncRepWaitForLSN
> before opening up the connections to the non-superusers. I have a
> working prototype of this logic, if there is enough interest I can
> post the patch.
>
>
>
>
>
> On Tue, Apr 20, 2021 at 11:25 AM Ondřej Žižka <ondrej(dot)zizka(at)stratox(dot)cz
> <mailto:ondrej(dot)zizka(at)stratox(dot)cz>> wrote:
>
> I am sorry, I forgot mentioned, that in the second situation I
> added a
> primary key to the table.
>
> Ondrej
>
>
> On 20/04/2021 18:49, Ondřej Žižka wrote:
> > Hello Aleksander,
> >
> > Thank you for the reaction. This was tested on version 13.2.
> >
> > There are also other possible situations with the same setup and
> > similar issue:
> >
> > -----------------
> > When the background process on server fails....
> >
> > On postgresql1:
> > tecmint=# select * from a; --> LAN on sync replica is OK
> >  id
> > ----
> >   1
> > (1 row)
> >
> > tecmint=# insert into a values (2); ---> LAN on sync replica is
> DOWN
> > and insert is waiting. During this time kill the background
> process on
> > the PostgreSQL server for this session
> > WARNING:  canceling the wait for synchronous replication and
> > terminating connection due to administrator command
> > DETAIL:  The transaction has already committed locally, but
> might not
> > have been replicated to the standby.
> > server closed the connection unexpectedly
> >     This probably means the server terminated abnormally
> >     before or while processing the request.
> > The connection to the server was lost. Attempting reset: Succeeded.
> > tecmint=# select * from a;
> >  id
> > ----
> >   1
> >   2
> > (2 rows)
> >
> > tecmint=# ---> LAN on sync replica is still DOWN
> >
> > The potgres session will restore after the background process
> failed.
> > When you run select on master, it still looks OK. But data is still
> > not replicated on the sync replica. If we lost the master now, we
> > would lost this data as well.
> >
> > **************
> > Another case
> > **************
> >
> > Kill the client process.
> >
> > tecmint=# select * from a;
> >  id
> > ----
> >   1
> >   2
> >   3
> > (3 rows)
> > tecmint=#                --> Disconnect the sync replica now.
> LAN on
> > replica is DOWN
> > tecmint=# insert into a values (4); --> Kill the client process
> > Terminated
> > xzizka(at)service-vm:~$ psql -U postgres -h 192.168.122.6 -p 5432
> -d tecmint
> > Password for user postgres:
> > psql (13.2 (Debian 13.2-1.pgdg100+1))
> > Type "help" for help.
> >
> > tecmint=# select * from a;
> >  id
> > ----
> >   1
> >   2
> >   3
> > (3 rows)
> >
> > tecmint=# --> Number 4 is not there. Now switch the LAN on sync
> > replica ON.
> >
> > ----------
> >
> > Result from sync replica after the LAN is again UP:
> > tecmint=# select * from a;
> >  id
> > ----
> >   1
> >   2
> >   3
> >   4
> > (4 rows)
> >
> >
> > In this situation, try to insert the number 4 again to the table.
> >
> > tecmint=# select * from a;
> >  id
> > ----
> >   1
> >   2
> >   3
> > (3 rows)
> >
> > tecmint=# insert into a values (4);
> > ERROR:  duplicate key value violates unique constraint "a_pkey"
> > DETAIL:  Key (id)=(4) already exists.
> > tecmint=#
> >
> > This is really strange... Application can be confused, It is not
> > possible to insert record, which is not there, but some systems
> which
> > use the sync node as a read replica maybe already read that record
> > from the sync replica database and done some steps which can cause
> > issues and can be hard to track.
> >
> > If I say, that it would be hard to send the CTRL+C to the database
> > from the client, I need to say, that the 2 situations I
> described here
> > can happen in real.
> >
> > What do you think?
> >
> > Thank you and regards
> > Ondrej
> >
> > On 20/04/2021 17:23, Aleksander Alekseev wrote:
> >> Hi Ondřej,
> >>
> >> Thanks for the report. It seems to be a clear violation of what is
> >> promised in the docs. Although it's unlikely that someone
> implemented
> >> an application which deals with important data and "pressed
> Ctr+C" as
> >> it's done in psql. So this might be not such a critical issue after
> >> all. BTW what version of PostgreSQL are you using?
> >>
> >>
> >> On Mon, Apr 19, 2021 at 10:13 PM Ondřej Žižka
> >> <ondrej(dot)zizka(at)stratox(dot)cz <mailto:ondrej(dot)zizka(at)stratox(dot)cz>> wrote:
> >>> Hello all,
> >>> I would like to know your opinion on the following behaviour I
> see
> >>> for PostgreSQL setup with synchronous replication.
> >>>
> >>> This behaviour happens in a special use case. In this use case,
> >>> there are 2 synchronous replicas with the following config
> (truncated):
> >>>
> >>> - 2 nodes
> >>> - synchronous_standby_names='*'
> >>> - synchronous_commit=remote_apply
> >>>
> >>>
> >>> With this setup run the following steps (LAN down - LAN between
> >>> master and replica):
> >>> -----------------
> >>> postgres=# truncate table a;
> >>> TRUNCATE TABLE
> >>> postgres=# insert into a values (1); -- LAN up, insert has been
> >>> applied to replica.
> >>> INSERT 0 1
> >>> Vypnu LAN na serveru se standby:
> >>> postgres=# insert into a values (2); --LAN down, waiting for a
> >>> confirmation from sync replica. In this situation cancel it
> (press
> >>> CTRL+C)
> >>> ^CCancel request sent
> >>> WARNING:  canceling wait for synchronous replication due to user
> >>> request
> >>> DETAIL:  The transaction has already committed locally, but might
> >>> not have been replicated to the standby.
> >>> INSERT 0 1
> >>> There will be warning that commit was performed only locally:
> >>> 2021-04-12 19:55:53.063 CEST [26104] WARNING: canceling wait for
> >>> synchronous replication due to user request
> >>> 2021-04-12 19:55:53.063 CEST [26104] DETAIL:  The transaction has
> >>> already committed locally, but might not have been replicated
> to the
> >>> standby.
> >>>
> >>> postgres=# insert into a values (2); --LAN down, waiting for a
> >>> confirmation from sync replica. In this situation cancel it
> (press
> >>> CTRL+C)
> >>> ^CCancel request sent
> >>> WARNING:  canceling wait for synchronous replication due to user
> >>> request
> >>> DETAIL:  The transaction has already committed locally, but might
> >>> not have been replicated to the standby.
> >>> INSERT 0 1
> >>> postgres=# insert into a values (2); --LAN down, waiting for sync
> >>> replica, second attempt, cancel it as well (CTRL+C)
> >>> ^CCancel request sent
> >>> WARNING:  canceling wait for synchronous replication due to user
> >>> request
> >>> DETAIL:  The transaction has already committed locally, but might
> >>> not have been replicated to the standby.
> >>> INSERT 0 1
> >>> postgres=# update a set n=3 where n=2; --LAN down, waiting for
> sync
> >>> replica, cancel it (CTRL+C)
> >>> ^CCancel request sent
> >>> WARNING:  canceling wait for synchronous replication due to user
> >>> request
> >>> DETAIL:  The transaction has already committed locally, but might
> >>> not have been replicated to the standby.
> >>> UPDATE 2
> >>> postgres=# update a set n=3 where n=2; -- run the same
> >>> update,because data from the previous attempt was commited on
> >>> master, it is sucessfull, but no changes
> >>> UPDATE 0
> >>> postgres=# select * from a;
> >>>   n
> >>> ---
> >>>   1
> >>>   3
> >>>   3
> >>> (3 rows)
> >>> postgres=#
> >>> ------------------------
> >>>
> >>> Now, there is only value 1 in the sync replica table (no other
> >>> values), data is not in sync. This is expected, after the LAN
> >>> restore, data will come sync again, but if the main/primary node
> >>> will fail and we failover to replica before the LAN is back up or
> >>> the storage for this node would be destroyed and data would
> not sync
> >>> to replica before it, we will lose data even if the client
> received
> >>> successful commit (with a warning).
> >>>  From the synchronous_commit=remote_write level and "higher", I
> >>> would expect, that when the remote application (doesn't matter if
> >>> flush, write or apply) would not be applied I would not receive a
> >>> confirmation about the commit (even with a warning). Something
> like,
> >>> if there is no commit from sync replica, there is no commit on
> >>> primary and if someone performs the steps above, the whole
> >>> transaction will not send a confirmation.
> >>>
> >>> This can cause issues if the application receives a confirmation
> >>> about the success and performs some follow-up steps e.g. create a
> >>> user account and sends a request to the mail system to create an
> >>> account or create a VPN account. If the scenario above happens,
> >>> there can exist a VPN account that does not have any presence
> in the
> >>> central database and can be a security issue.
> >>>
> >>> I hope I explained it sufficiently. :-)
> >>>
> >>> Do you think, that would be possible to implement a process that
> >>> would solve this use case?
> >>>
> >>> Thank you
> >>> Ondrej
> >>
> >>
>
>