Re: Commit to primary with unavailable sync standby

On 19/12/2019 11:04, Andrey Borodin wrote:
> Hi!
> 
> I cannot figure out proper way to implement safe HA upsert. I will be very grateful if someone would help me.
> 
> Imagine we have primary server after failover. It is network-partitioned. We are doing INSERT ON CONFLICT DO NOTHING;
thateventually timed out.
 
> 
> az1-grx88oegoy6mrv2i/db1 M > WITH new_doc AS (
>      INSERT INTO t(
>          pk,
>          v,
>          dt
>      )
>      VALUES
>      (
>          5,
>          'text',
>          now()
>      )
>      ON CONFLICT (pk) DO NOTHING
>      RETURNING pk,
>                v,
>                dt)
>     SELECT new_doc.pk from new_doc;
> ^CCancel request sent
> WARNING:  01000: 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.
> LOCATION:  SyncRepWaitForLSN, syncrep.c:264
> Time: 2173.770 ms (00:02.174)
> 
> Here our driver decided that something goes wrong and we retry query.
> 
> az1-grx88oegoy6mrv2i/db1 M > WITH new_doc AS (
>      INSERT INTO t(
>          pk,
>          v,
>          dt
>      )
>      VALUES
>      (
>          5,
>          'text',
>          now()
>      )
>      ON CONFLICT (pk) DO NOTHING
>      RETURNING pk,
>                v,
>                dt)
>     SELECT new_doc.pk from new_doc;
>   pk
> ----
> (0 rows)
> 
> Time: 4.785 ms
> 
> Now we have split-brain, because we acknowledged that row to client.
> How can I fix this?
> 
> There must be some obvious trick, but I cannot see it... Or maybe cancel of sync replication should be disallowed and
terminationshould be treated as system failure?
 
> 
> Best regards, Andrey Borodin.
> 

You're hitting the CAP theorem ( https://en.wikipedia.org/wiki/CAP_theorem )


You cannot do it with fewer than 3 nodes, as the moment you set your 
standby to synchronous to achieve consistency, both your nodes become 
single points of failure.


With 3 or more nodes you can perform what is called a quorum write 
against ( floor(<total_nodes> / 2) + 1 ) nodes .


With 3+ nodes, the "easy" strategy is to set a <quorum - 1> number of 
standby nodes in synchronous_standby_names ( 
https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-SYNCHRONOUS-STANDBY-NAMES 
)


This however makes it tricky to pick the correct standby for promotions 
during auto-failovers, as you need to freeze all the standbys listed in 
the above setting in order to correctly determine which one has the 
highest WAL location without running into race conditions (as the 
operation is non-atomic, stateful and sticky).


I personally prefer to designate a fixed synchronous set at setup time 
and automatically set a static synchronous_standby_names on the master 
whenever a failover occurs. That allows for a simpler failover mechanism 
as you know they got the latest WAL location.



If you want an off-the shelf solution, nowadays Patroni seems to be all 
the rage.




-- 
Regards

Fabio Ugo Venchiarutti
OSPCFC Network Engineering Dpt.
Ocado Technology

-- 


Notice: 
This email is confidential and may contain copyright material of 
members of the Ocado Group. Opinions and views expressed in this message 
may not necessarily reflect the opinions and views of the members of the 
Ocado Group.

If you are not the intended recipient, please notify us 
immediately and delete all copies of this message. Please note that it is 
your responsibility to scan this message for viruses.

References to the 
"Ocado Group" are to Ocado Group plc (registered in England and Wales with 
number 7098618) and its subsidiary undertakings (as that expression is 
defined in the Companies Act 2006) from time to time. The registered office 
of Ocado Group plc is Buildings One & Two, Trident Place, Mosquito Way, 
Hatfield, Hertfordshire, AL10 9UL.