Error "unable to get a stable set of rows in the source table"

Symptom

When MERGE INTO is executed to update the target table based on the matching conditions, error "unable to get a stable set of rows in the source table" is reported.

When the target table products is updated based on the matching condition product_id=1502 in the source table newproducts, an error is reported.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

CREATE TABLE products (product_id INTEGER,product_name VARCHAR2(60),category VARCHAR2(60));

INSERT INTO products VALUES (1501, 'vivitar 35mm', 'electrncs'),(1502, 'olympus is50', 'electrncs'),(1600, 'play gym', 'toys');

CREATE TABLE newproducts (product_id INTEGER,product_name VARCHAR2(60),category VARCHAR2(60));

INSERT INTO newproducts VALUES (1502, 'olympus camera', 'electrncs'),(1600, 'lamaze', 'toys'),(1502, 'skateboard', 'toy');

MERGE INTO products p
  USING newproducts np
  ON (p.product_id = np.product_id)
  WHEN MATCHED THEN
  UPDATE SET p.product_name = np.product_name, p.category = np.category WHERE np.product_id = 1502;
ERROR:  dn_6003_6004: unable to get a stable set of rows in the source tables

Possible Causes

In the source table newproducts, there are two records whose product_id is 1502, and the default value of behavior_compat_options is used. Therefore, an error is reported when two records are matched during MERGE INTO.

MERGE INTO is used to update a destination table, or to insert a row of the source table to the destination table based on certain matching conditions. If multiple rows meet the conditions, GaussDB(DWS) may perform either the following operations:

1. Report the error "unable to get a stable set of rows in the source table".
2. A random row is inserted, which may not be the row you want.

Check the value of behavior_compat_options. If behavior_compat_options is set to the default value, an error will be reported when multiple rows are matched. If behavior_compat_options is set to merge_update_multi, no error will be reported, and one of the matched rows will be randomly inserted.

Therefore, if the MERGE INTO result is not as expected, check whether the parameter is set and whether multiple rows of data are matched. If yes, modify the service logic based on the site requirements.

Solutions

• Solution 1: Set behavior_compat_options to merge_update_multi.
  When multiple rows are matched in the target table, no error is reported. Instead, data in one of the matched rows are randomly used. This may cause incomplete result.

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

  SET behavior_compat_options=merge_update_multi; MERGE INTO products p USING newproducts np ON (p.product_id = np.product_id) WHEN MATCHED THEN UPDATE SET p.product_name = np.product_name, p.category = np.category WHERE np.product_id = 1502; MERGE 1 SELECT * FROM products ; product_id | product_name | category ------------+----------------+----------- 1501 | vivitar 35mm | electrncs 1502 | olympus camera | electrncs 1600 | play gym | toys (3 rows)

• Solution 2: Modify the MERGE INTO matching condition.

  You are advised to select an expression with a unique result as the matching condition.

  1 2 3 4 5 6 7 8 9 10 11 12 13 14

  MERGE INTO products p USING newproducts np ON (p.product_id = np.product_id) WHEN MATCHED THEN UPDATE SET p.product_name = np.product_name, p.category = np.category WHERE np.product_id != 1502; MERGE 1 SELECT * FROM products; product_id | product_name | category ------------+----------------+----------- 1501 | vivitar 35mm | electrncs 1502 | olympus camera | electrncs 1600 | lamaze | toys (3 rows)