Exploring conflict handling with PGD v5
In a multi-master architecture like PGD, conflicts happen. PGD is built to handle them.
A conflict can occur when one database node has an update from an application to a row and another node has a different update to the same row. This type of conflict is called a row-level conflict. Conflicts aren't errors. Resolving them effectively is core to how Postgres Distributed maintains consistency.
The best way to handle conflicts is not to have them in the first place! Use PGD's Always-On architecture with proxies to ensure that your applications write to the same server in the cluster.
When conflicts occur, though, it's useful to know how PGD resolves them, how you can control that resolution, and how you can find out that they're happening. Row insertion and row updates are two actions that can cause conflicts.
To see how it works, you need to open a command line view of all the servers.
At the end of each quick start, you'll have a cluster with four nodes and these roles:
|Host name||Host role|
|kaboom||PGD data node and pgd-proxy co-host|
|kaftan||PGD data node and pgd-proxy co-host|
|kaolin||PGD data node and pgd-proxy co-host|
|kapok||Barman backup node|
You'll use these hostnames throughout this exercise.
xpanes, a utility that allows you to quickly create multiple terminal sessions that you can easily switch between. It isn't installed by default, so you'll have to install it. Start by connecting to the kaboom node with ssh:
If you're running the quick start on Docker, you'll be using Rocky Linux, a Red Hat derivative. To perform the xpanes install, run:
If you're running the quick start on AWS, you'll be using Debian Linux. To perform the xpanes install, run:
You need to be logged in as the enterprisedb user to allow authentication to work:
Then, run the following command to connect to three database servers and a proxy server:
xpanes takes the command after
-c and uses the values in the arguments that follow to create a command to run. That means that, after you run it, there will be four panes. Three panes will be connected to the database nodes kaboom, kaftan, and kaolin on port 5444. One will be connected to the pgd-proxy running on kaboom on port 6432. Each one will be logged into the database as enterprisedb.
Press Control-b followed by q to briefly display the numeric values for each pane.
To switch the focus between the panes, you can use Control-b and the cursor keys to navigate between them. Or you can use Control-b followed by q and the number of the pane you want to focus on. We'll show both ways.
Move to the bottom-left pane using Control-b ↓ Control-b → or Control-b q 3**.
To make a conflict, you first need a simple table. In the pane that currently has focus, enter:
In the pane that currently has focus, enter:
select command displays the conflict history for the cluster. The
\watch 1 command is a psql command that reruns the preceding command every second.
You are now ready to generate a conflict.
The most basic form of conflict is when an insert happens to a table on two different nodes and both have the same primary key. You can now create that scenario and observe it.
Move to the top-left pane using Control-b ↑ Control-b ← or Control-b q 0. This pane is the kaboom node. Start a transaction there, and insert a row:
Next, move to the top-right pane using Control-b → or Control-b q 1. This pane is the kaftan node. Here, you'll also start a transaction and insert into the same row with different data.
You now have two transactions open on different servers, with an insert operation already performed successfully. You need to commit both transactions at this point:
- Use Control-b ← or Control-b q 0, and then enter
- Use Control-b → or Control-b q 1, and then enter
You'll see that both commits are working. However, in the bottom-right pane, you can see the conflict being detected.
A row in the conflict history now notes a conflict in the table where the
insert_exists. It also notes that the resolution for this conflict is that the newer record, based on the timing of the commit, is retained. This conflict is called an INSERT/INSERT conflict. You can read more about this type of conflict in INSERT/INSERT conflicts.
When different updates to the same records take place on different nodes, a conflict occurs. You can create that scenario with the current configuration, too. Leave
\watch 1 running in the bottom-right pane.
Move to the top-left pane using Control-b ← or Control-b q 0. This pane is the kaboom node. Here, start a transaction and update a row:
Next, move to the top-right pane using Control-b → or Control-b q 1. This pane is the kaftan node. Here, also start a transaction, and update the same row with different data:
You now have two transactions open on different servers, with an update operation already performed successfully. You need to commit both transactions at this point:
- Use Control-b cursor-left or Control-b q 0, and then enter
- Use Control-b cursor-right or Control-b q 1, and then enter
Again you'll see both commits working. And, again, in the bottom-right pane, you can see the update conflict being detected.
An additional row in the conflict history shows an
update_origin_change conflict occurred and that the resolution was
apply_remote. This resolution means that the remote change was applied, updating the record. This conflict is called an UPDATE/UPDATE conflict and is explained in more detail in UPDATE/UPDATE conflicts.
You can quickly exit Tmux and all the associated sessions. First terminate any running processes, as they will otherwise continue running after the session is killed. Press Control-b and then enter
:kill-session. This approach is simpler than quitting each pane's session one at a time using Control-D or
You are now equipped to explore all the possible conflict scenarios and resolutions that can occur. For full details of how conflicts are managed, see Conflicts in the documentation. While ideally you should avoid conflicts, it's important to know that, when they do happen, they're recorded and managed by Postgres Distributed's integrated and configurable conflict resolver.