Re: [Postgres-xc-bugs] Fix a bug about UPDATE ... RETURNNING...

[鈴木 幸市 <ko...@in...>]

Thanks a lot for finding the bug and the patch.

Addition of “FOR UPDATE” is controlled by separatem_rowmarks() in planner.c, which is specific to XC.

Could you please review this function as well as subquery_planner() calling this function and see if your patch does not conflict with them?

Best Regards;
---
Koichi Suzuki

2014/07/03 15:04、ZhangJulian <jul...@ou...<mailto:jul...@ou...>> のメール：

Hi all,

I found a bug and made a fix, but need your review since I am not familar with big picture of the Optimizer.

The reproduced steps:
create table t1 (c1 int, c2 int) distribute by hash(c1);
insert into t1 values(1,1);

Then you run a UPDATE statement 10 times, the c2 is expected to be added to 11.
for i in `seq 1 10`; do psql postgres -c "update t1 set c2 = c2 + 1 where c1 = 1 returning c2" & done
But unfortunately the c2 is just added to 2.

The error can be examined from the below explained plan:
postgres=# explain verbose update t1 set c2 = c2 + 1 where c1 = 1 returning c2;
                                                         QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
 Update on public.t1  (cost=0.00..2.50 rows=1000 width=18)
   Output: t1.c2
   Node/s: datanode1, datanode2
   Node expr: t1.c1
   Remote query: UPDATE ONLY public.t1 SET c1 = $1, c2 = $2 WHERE ((t1.ctid = $3) AND (t1.xc_node_id = $4)) RETURNING t1.c2
   ->  Data Node Scan on t1 "_REMOTE_TABLE_QUERY_"  (cost=0.00..2.50 rows=1000 width=18)
         Output: t1.c1, (t1.c2 + 1), t1.c1, t1.ctid, t1.xc_node_id
         Node/s: datanode1
         Remote query: SELECT c1, c2, ctid, xc_node_id FROM ONLY public.t1 WHERE (c1 = 1)
(9 rows)

The remote query has no FOR UPDATE clause, so all parallel UPDATE statements got the same initial c2 value(that is 1), and do UPDATE it to 2.

I made the fix as the append patch file, then the explained plan is changed as below.
postgres=# explain verbose update t1 set c2 = c2 + 1 where c1 = 1 returning c2;
                                                         QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
 Update on public.t1  (cost=0.00..2.50 rows=1000 width=18)
   Output: t1.c2
   Node/s: datanode1, datanode2
   Node expr: t1.c1
   Remote query: UPDATE ONLY public.t1 SET c1 = $1, c2 = $2 WHERE ((t1.ctid = $3) AND (t1.xc_node_id = $4)) RETURNING t1.c2
   ->  Data Node Scan on t1 "_REMOTE_TABLE_QUERY_"  (cost=0.00..2.50 rows=1000 width=18)
         Output: t1.c1, (t1.c2 + 1), t1.c1, t1.ctid, t1.xc_node_id
         Node/s: datanode1
         Remote query: SELECT c1, c2, ctid, xc_node_id FROM ONLY public.t1 WHERE (c1 = 1) FOR UPDATE OF t1
(9 rows)

When I run the command below again,
for i in `seq 1 10`; do psql postgres -c "update t1 set c2 = c2 + 1 where c1 = 1 returning c2" & done
Only one statement return succussfully, and other statements got WARNING and ERROR displayed as the append file error.txt. Do you think it is the expected results?

Thanks
Julian
<20140703_update_with_returnning.patch><error.txt>------------------------------------------------------------------------------
Open source business process management suite built on Java and Eclipse
Turn processes into business applications with Bonita BPM Community Edition
Quickly connect people, data, and systems into organized workflows
Winner of BOSSIE, CODIE, OW2 and Gartner awards
http://p.sf.net/sfu/Bonitasoft_______________________________________________
Postgres-xc-bugs mailing list
Pos...@li...
https://lists.sourceforge.net/lists/listinfo/postgres-xc-bugs