Custom conflict handling examples v7

Suggest edits

Setting columns from the source or target

The following example shows the effect of custom conflict handling using the custom conflict-handling function named custom_conflict_dept shown in Custom conflict handling function. This function sets the target node as the winner of update/update conflicts on the dept table.

The update is made on the primary definition node, edb:

edb=# UPDATE dept SET loc = 'PORTLAND' WHERE deptno = 50;
UPDATE 1
edb=# SELECT * FROM dept;
Output
 deptno |    dname    |   loc
--------+-------------+----------
     10 | ACCOUNTING  | NEW YORK
     20 | RESEARCH    | DALLAS
     30 | SALES       | CHICAGO
     40 | OPERATIONS  | BOSTON
     50 | ADVERTISING | PORTLAND
(5 rows)

The following update is made on a second primary node, MMRnode:

MMRnode=# UPDATE dept SET loc = 'LOS ANGELES' WHERE deptno = 50;
UPDATE 1
MMRnode=# SELECT * FROM dept;
Output
 deptno |    dname    |     loc
--------+-------------+-------------
     10 | ACCOUNTING  | NEW YORK
     20 | RESEARCH    | DALLAS
     30 | SALES       | CHICAGO
     40 | OPERATIONS  | BOSTON
     50 | ADVERTISING | LOS ANGELES
(5 rows)

After a synchronization replication, the update/update conflict is detected and resolved as shown in the Conflict History tab.

In the source primary node, the loc column of department 50 loses the value set in its UPDATE statement. The column is reset to the value from the target primary node.

edb=# SELECT * FROM dept;
Output
 deptno |    dname    |     loc
--------+-------------+-------------
     10 | ACCOUNTING  | NEW YORK
     20 | RESEARCH    | DALLAS
     30 | SALES       | CHICAGO
     40 | OPERATIONS  | BOSTON
     50 | ADVERTISING | LOS ANGELES
(5 rows)

In the target primary node, the loc column of department 50 retains the value set from its UPDATE statement.

MMRnode=# SELECT * FROM dept;
Output
 deptno |    dname    |     loc
--------+-------------+-------------
     10 | ACCOUNTING  | NEW YORK
     20 | RESEARCH    | DALLAS
     30 | SALES       | CHICAGO
     40 | OPERATIONS  | BOSTON
     50 | ADVERTISING | LOS ANGELES
(5 rows)

The target node wins the conflict as determined by setting the resolution_code parameter to 2 in the custom conflict-handling function.

Setting columns from the function logic

The following example shows the effect of custom conflict handling using the custom conflict-handling function custom_conflict_emp shown in Custom conflict-handling function. This function sets values coded in the function as the winner of update/update conflicts on the emp table.

The following is the row from the emp table prior to the update:

edb=# edb=# SELECT * FROM emp WHERE empno = 9001;
Output
 empno | ename |   job    | mgr  |      hiredate      |   sal   |  comm   | deptno
-------+-------+----------+------+--------------------+---------+---------+--------
  9001 | SMITH | SALESMAN | 7698 | 31-OCT-13 00:00:00 | 8000.00 | 4000.00 |     30
(1 row)

The following update is made in the primary definition node, edb:

edb=# UPDATE emp SET ename = 'JONES', mgr = 7900, sal = 8500, comm = 5000 WHERE empno = 9001;
UPDATE 1
edb=# SELECT * FROM emp WHERE empno = 9001;
Output
 empno | ename |   job    | mgr  |      hiredate      |   sal   |  comm   | deptno
-------+-------+----------+------+--------------------+---------+---------+--------
  9001 | JONES | SALESMAN | 7900 | 31-OCT-13 00:00:00 | 8500.00 | 5000.00 |     30
(1 row)

The following update is made in a second primary node, MMRnode:

MMRnode=# UPDATE emp SET ename = 'ROGERS', mgr = 7788, sal = 9500, comm = 5000 WHERE empno = 9001;
UPDATE 1
MMRnode=# SELECT * FROM emp WHERE empno = 9001;
Output
 empno | ename  |   job    | mgr  |      hiredate      |   sal   |  comm   | deptno
-------+--------+----------+------+--------------------+---------+---------+--------
  9001 | ROGERS | SALESMAN | 7788 | 31-OCT-13 00:00:00 | 9500.00 | 5000.00 |     30
(1 row)

After the synchronization replication of the primary node, edb contains the following values for the conflicting row:

edb=# SELECT * FROM emp WHERE empno = 9001;
Output
empno |  ename  |   job    | mgr  |      hiredate      |   sal   |  comm   | deptno
-------+---------+----------+------+--------------------+---------+---------+--------
  9001 | Unknown | SALESMAN | 7900 | 31-OCT-31 00:00:00 | 8500.00 | 5000.00 |     30
(1 row)

After the synchronization replication of the primary node, MMRnode, contains the following values for the conflicting row:

MMRnode=# SELECT * FROM emp WHERE empno = 9001;
Output
empno |  ename  |   job    | mgr  |      hiredate      |   sal   |  comm   | deptno
-------+---------+----------+------+--------------------+---------+---------+--------
  9001 | Unknown | SALESMAN | 7900 | 31-OCT-31 00:00:00 | 8500.00 | 5000.00 |     30
(1 row)

The value of the first conflicting column is determined by the custom conflict-handling function for both primary nodes.

Setting columns using the source and target shadow tables

The following example shows how to use the values from the source and target shadow tables to set the final values in the conflicting column.

Note

This custom conflict-handling function uses a column (rrep_old_quantity in this example) that's a column of the shadow table and not of the actual publication table. So you can't use this solution for a publication that uses the log-based method of synchronization replication.

This example use the following table, which contains product inventory.

CREATE TABLE inventory (
    item_id         NUMERIC PRIMARY KEY,
    name            VARCHAR(20),
    quantity        INTEGER
);
INSERT INTO inventory VALUES (1, 'LaserJet Printer 610', 50);
INSERT INTO inventory VALUES (2, 'Scanner 510', 10);
INSERT INTO inventory VALUES (3, 'LCD', 20);

When products are purchased at different locations, resulting in an inventory reduction on several primary nodes, the remaining inventory must be properly updated on all primary nodes to reflect the reduction in all locations. The custom conflict-handling function is coded to properly record the remaining inventory if changes to the same item are made in several locations.

The following example uses the primary definition node, edb and a second primary node, MMRnode. Initially, the inventory table has the same contents on both primary nodes.

edb=# SELECT * FROM inventory;
Output
 item_id |         name         | quantity
---------+----------------------+----------
       1 | LaserJet Printer 610 |       50
       2 | Scanner 510          |       10
       3 | LCD                  |       20
(3 rows)

After creating the primary nodes, the following shows the resulting shadow table structures in the primary definition node:

edb=# \d _edb_replicator_pub.rrst_edb_inventory;
                  Table "_edb_replicator_pub.rrst_edb_inventory"
Output
Column          |            Type             |         Modifiers
-------------------------+-----------------------------+---------------------------
 rrep_sync_id            | numeric                     | not null
 rrep_common_id          | numeric                     |
 rrep_operation_type     | character(1)                |
 rrep_tx_timestamp       | timestamp without time zone | default current_timestamp
 item_id                 | numeric                     |
 name                    | character varying(20)       |
 quantity                | integer                     |
 rrep_old_item_id        | numeric                     |
 rrep_old_name           | character varying(20)       |
 rrep_old_quantity       | integer                     |
 rrep_tx_conflict_status | character(1)                |
Indexes:
    "rrst_edb_inventory_pkey" PRIMARY KEY, btree (rrep_sync_id)

Similarly, in the second primary node the same shadow table is created.

MMRnode=# \d _edb_replicator_pub.rrst_edb_inventory
                  Table "_edb_replicator_pub.rrst_edb_inventory"
Output
Column          |            Type             |         Modifiers
-------------------------+-----------------------------+---------------------------
 rrep_sync_id            | numeric                     | not null
 rrep_common_id          | numeric                     |
 rrep_operation_type     | character(1)                |
 rrep_tx_timestamp       | timestamp without time zone | default current_timestamp
 item_id                 | numeric                     |
 name                    | character varying(20)       |
 quantity                | integer                     |
 rrep_old_item_id        | numeric                     |
 rrep_old_name           | character varying(20)       |
 rrep_old_quantity       | integer                     |
 rrep_tx_conflict_status | character(1)                |
Indexes:
    "rrst_edb_inventory_pkey" PRIMARY KEY, btree (rrep_sync_id)

For an update transaction, the shadow table contains the column values both:

  • Before the update was made on the publication table (columns with names rrep_old_column_name)
  • After the update was applied (columns named identically to the publication table column names)

The custom conflict-handling function uses both the current and old values of the quantity columns from the source and target shadow tables as shown by the following.

CREATE OR REPLACE FUNCTION edb.custom_conflict_inventory (
    INOUT   source              _edb_replicator_pub.rrst_edb_inventory,
    IN      target              _edb_replicator_pub.rrst_edb_inventory,
    IN      conflict_column     VARCHAR(255),
    OUT     resolution_message  VARCHAR(255),
    OUT     resolution_code     INTEGER
)
AS
$$
DECLARE
BEGIN
    source.quantity := source.rrep_old_quantity
      - ((source.rrep_old_quantity - source.quantity)
       + (target.rrep_old_quantity - target.quantity));
    resolution_code := 3;
    resolution_message := 'Custom conflict handling: Quantity adjusted';
END;
$$
LANGUAGE plpgsql;

Assume two items with item_id of 1 are purchased on the primary definition node:

edb=# UPDATE inventory SET quantity = quantity - 2 WHERE item_id = 1;
UPDATE 1
edb=# SELECT * FROM inventory WHERE item_id = 1;
Output
 item_id |         name         | quantity
---------+----------------------+----------
       1 | LaserJet Printer 610 |       48
(1 row)

Also assume one item with item_id of 1 is purchased from the second primary node:

MMRnode=# UPDATE inventory SET quantity = quantity - 1 WHERE item_id = 1;
UPDATE 1
MMRnode=# SELECT * FROM inventory WHERE item_id = 1;
Output
 item_id |         name         | quantity
---------+----------------------+----------
       1 | LaserJet Printer 610 |       49
(1 row)

After the synchronization replication and invocation of the custom conflict handling function, the quantity column for item_id 1 is correctly set to 47 in both primary nodes:

edb=# SELECT * FROM inventory WHERE item_id = 1;
Output
 item_id |         name         | quantity
---------+----------------------+----------
       1 | LaserJet Printer 610 |       47
(1 row)
edb=# \c MMRnode MMRuser
Password for user MMRuser:
You are now connected to database "MMRnode" as user "MMRuser".
MMRnode=# SET search_path TO edb;
SET
MMRnode=# SELECT * FROM inventory WHERE item_id = 1;
Output
 item_id |         name         | quantity
---------+----------------------+----------
       1 | LaserJet Printer 610 |       47
(1 row)
← Prev

Adding a custom conflict-handling function

Next →

Manual conflict resolution for the trigger-based method