From: ZhangJulian <jul...@ou...> - 2014-07-04 09:42:12
|
Hi Koichi, Because in this case, it is an UPDATE statement, so root->rowMarks is null, and separatem_rowmarks() is not applicable here. My patch is in pgxc_build_shippable_query_baserel(), which will add "FOR UDPATE" to a remote SELECT query only when the original statemetn is a UPDATE. I feel the patch is ok, but "make check" test got many "deadlock" errors. Currently, I can do nothing except rewriting the query to work around the bug. Rewrite: UPDATE ... RETURNING ... To: SELECT ... INTO ... FOR UPDATE; UPDATE.... I plan to look the deadlock issue next step, but it seems a bit difficult to me. :) Thanks Julian From: ko...@in... To: jul...@ou... CC: pos...@li... Subject: Re: [Postgres-xc-bugs] Fix a bug about UPDATE ... RETURNNING... Date: Fri, 4 Jul 2014 02:51:48 +0000 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...> のメル: 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 |