Pivotal Knowledge Base

Follow

ERROR: Multiple updates to a row by the same query is not allowed

Environment

Product Version
 Pivotal Greenplum (GPDB)  4.3.5+
 OS  RHEL 6.x
 Others  

Symptom

When attempting to run a non-deterministic update on a table with the Pivotal Optimizer (SET optimizer=on), an error occurs.

Error Message:

Note: These entries will come directly from the client tool in use (eg. psql):

ERROR: multiple updates to a row by the same query is not allowed

Cause 

The Pivotal Query Optimizer, unlike the legacy optimizer, does not allow non-deterministic updates. This ensures that only a specific unique row is updated and not an undetermined one when there are multiple matches.

Consider the following:

gpadmin=# create table table01 ( a int, b int) distributed by (a);
CREATE TABLE
gpadmin=# create table table02 ( x int, y int) distributed by (x);
CREATE TABLE
gpadmin=# INSERT INTO table01 values (1, 1),(2,1),(3,1);
INSERT 0 3
gpadmin=# INSERT INTO table02 values (1, 10),(1,11),(2,20),(3,30),(1,12);
INSERT 0 5
gpadmin=# select * from table01 order by 1;
a | b 
---+---
 1 | 1
 2 | 1
 3 | 1
(3 rows)

gpadmin=# select * from table02 order by 1;
 x | y  
---+----
 1 | 11
 1 | 10
 1 | 12
 2 | 20
 3 | 30
(5 rows)

gpadmin=# set optimizer=on;
SET
gpadmin=# UPDATE table01 t1 SET b=t2.y from table02 t2 where t1.a = t2.x;
ERROR: multiple updates to a row by the same query is not allowed  (seg0 gpdb:40000 pid=79372)

Since table03.x has multiple "1" values in it, when the update occurs which row would be updated? Since this is a non-deterministic update the error is produced.

However, the legacy optimizer will proceed to update a value but in this scenario, it could update table01 with any value from table02 where x=1:


gpadmin=# set optimizer=off;
SET
gpadmin=# UPDATE table01 t1 SET b=t2.y from table02 t2 where t1.a = t2.x;
UPDATE 3
test4352=# select * from table01 order by 1;
 a | b  
---+----
 1 | 10
 2 | 20
 3 | 30
(3 rows)

 Resolution

 This error and activity are documented behavior for the Pivotal Optimizer.

 See http://gpdb.docs.pivotal.io/4350/admin_guide/query/topics/query-piv-opt-changed.html for more  information.

  • Ensure uniqueness in your tables to make sure that there is not a non-deterministic update attempted. Building on the example above, one could check for uniqueness as below and can see that this kind of update has 3 potential rows that could be updated:
    • gpadmin=# SELECT x,count(*) FROM table02 a WHERE EXISTS (SELECT * FROM table01 b WHERE a.x=b.a) GROUP BY x HAVING count(*)>1;
       x | count
      ---+-------
       1 |     3
      (1 row)
  • Use a different predicate/WHERE clause to ensure only individual rows are updated.
  • Attempt using the legacy optimizer as a workaround.

 

Comments

Powered by Zendesk