Custom conflict handling examples v7
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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"
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"
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;
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;
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;
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;
item_id | name | quantity ---------+----------------------+---------- 1 | LaserJet Printer 610 | 47 (1 row)