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; that eventually 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 termination should 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.
