From: Masataka S. <pg...@gm...> - 2014-03-18 10:31:11
|
Hi, This patch still have unconcerned path that causes regression. Here's a script to fail. ---------------------------------------------------------------------------------------------------------------- CREATE TABLE a ( id INTEGER, val INTEGER, PRIMARY KEY (id, val) ) DISTRIBUTE BY REPLICATION; INSERT INTO a VALUES(1, 6); PREPARE ps AS DELETE FROM a WHERE id in (SELECT id FROM a GROUP BY id HAVING SUM(val) > 0); EXECUTE ps; ---------------------------------------------------------------------------------------------------------------- Here's an explained plan. ---------------------------------------------------------------------------------------------------------------- QUERY PLAN ------------------------------------------------------------------------------------------------------------ Delete on public.a (cost=0.12..14.38 rows=5 width=42) Primary node/s: datanode1 Node/s: datanode2 Remote query: DELETE FROM ONLY public.a WHERE ((a.id = $1) AND (a.val = $2)) -> Hash Join (cost=0.12..14.38 rows=5 width=42) Output: a.id, a.val, a.ctid, "ANY_subquery".* Hash Cond: ("ANY_subquery".id = a.id) -> Subquery Scan on "ANY_subquery" (cost=0.00..10.00 rows=1000 width=32) Output: "ANY_subquery".*, "ANY_subquery".id -> Data Node Scan on "__REMOTE_GROUP_QUERY__" (cost=0.00..0.00 rows=1000 width=8) Output: id Node/s: datanode1 Remote query: SELECT id FROM ONLY public.a WHERE true GROUP BY 1 HAVING (sum(val) > 0) -> Hash (cost=0.00..0.00 rows=1000 width=14) Output: a.id, a.val, a.ctid -> Data Node Scan on a "_REMOTE_TABLE_QUERY_" (cost=0.00..0.00 rows=1000 width=14) Output: a.id, a.val, a.ctid Node/s: datanode1 Remote query: SELECT id, val, ctid FROM ONLY public.a WHERE true ---------------------------------------------------------------------------------------------------------------- The query sends a subquery "DELETE FROM ONLY public.a WHERE ((a.id = $1) AND (a.val = $2))" to datanodes. Then the subquery parameters should be 2 integers but a coordinator tells datanodes that the subquery has 1 tid parameter bound to ctid of the row to be deleted. I think this is a remnant from the days of using ctid. Does anyone have an idea to fix this issue? Regards. On 25 February 2014 13:33, Abbas Butt <abb...@en...> wrote: > Hi, > Attached please find revised and updated patch that performs updates/deletes > to replicated tables based on either primary key or unique index under the > following conditions > 1. The replicated table has either a primary key or a unique index defined. > 2. The query is not changing the primary key itself. > Otherwise ctid is used, like we were using previously. > > Getting a clean regression with the patch was not easy. Lot of issues had to > be fixed especially with deletes. > Regression and DBT-1 now work cleanly after that patch. > Test cases are added in the regression to make sure the added functionality > is working fine. > > The attached patch does not include the cursors approach as of now. > The patch to use cursors in case when primary key cannot be used is not > complete yet. The changes in the planner are done, but the changes in > executor are still to do. The current executor does not work with a fetch > and update plan, and needs some re factoring. > > Best Regards > > > > > On Mon, Feb 24, 2014 at 10:12 PM, Ahsan Hadi <ahs...@en...> > wrote: >> >> Abbas, >> Please give us an update on progress? >> >> >> On Thu, Feb 20, 2014 at 3:38 PM, Abbas Butt <abb...@en...> >> wrote: >>> >>> >>> >>> >>> On Thu, Feb 20, 2014 at 3:25 PM, Ahsan Hadi <ahs...@en...> >>> wrote: >>>> >>>> >>>> >>>> >>>> On Wed, Feb 19, 2014 at 3:56 PM, Abbas Butt >>>> <abb...@en...> wrote: >>>>> >>>>> Lets do this >>>>> 1. If the replicated table has a unique key defined , then use Mason's >>>>> patch after testing and refinement if required. >>>>> 2. If the replicated table has no key, then we can use cursors idea >>>>> proposed by Ashutosh. >>>>> >>>>> Does every body agree? >>>> >>>> >>>> Yes. >>>> >>>> Can you provide a patch for the above before monday? >>> >>> >>> I will try my best to. >>> >>>> >>>> >>>>> >>>>> >>>>> >>>>> >>>>> On Wed, Feb 19, 2014 at 12:15 PM, Koichi Suzuki <koi...@gm...> >>>>> wrote: >>>>>> >>>>>> 2014-02-19 16:00 GMT+09:00 Ahsan Hadi <ahs...@en...>: >>>>>> > >>>>>> > >>>>>> > >>>>>> > On Tue, Feb 18, 2014 at 11:26 PM, Amit Khandekar >>>>>> > <ami...@en...> wrote: >>>>>> >> >>>>>> >> >>>>>> >> >>>>>> >> >>>>>> >> On 13 February 2014 11:54, Ashutosh Bapat >>>>>> >> <ash...@en...> wrote: >>>>>> >>> >>>>>> >>> One more solution would be to use cursors for replicated tables. >>>>>> >>> The idea >>>>>> >>> is to open cursors on all the copies of the table and append the >>>>>> >>> query with >>>>>> >>> an ORDER BY clause on all the columns. Thus we are sure that the >>>>>> >>> current of >>>>>> >>> each of these cursors point to same row on all the copies. While >>>>>> >>> fetching a >>>>>> >>> row from a replicated table, we fetch from all the cursors and >>>>>> >>> choose only >>>>>> >>> one row for the data processing. While updating or deleting we >>>>>> >>> send UPDATE >>>>>> >>> or DELETE with WHERE CURRENT OF. The down side of this approach is >>>>>> >>> that, if >>>>>> >>> there are coordinator quals, we will end up locking more rows than >>>>>> >>> necessary, increasing the probability of the deadlock but at least >>>>>> >>> there >>>>>> >>> won't be a necessary restriction of having primary or unique key >>>>>> >>> and we >>>>>> >>> won't break backward compatibility. >>>>>> >>> >>>>>> >>> If there two identical rows, we might mix the update from >>>>>> >>> different >>>>>> >>> nodes, but then who knew which of them were corresponded across >>>>>> >>> the nodes to >>>>>> >>> start with. >>>>>> >>> >>>>>> >> >>>>>> >> Locking all rows doesn't look good especially because we are >>>>>> >> looking for a >>>>>> >> permanent long term solution. If we can come up with some other >>>>>> >> solution >>>>>> >> that avoids this, we better avoid this compromise. For a replicated >>>>>> >> table >>>>>> >> with 10000 rows, all concurrent updates will be serialized even if >>>>>> >> they are >>>>>> >> updating a different row. >>>>>> >> >>>>>> >> Other thing is datanode performance impact for ORDER BY all >>>>>> >> columns, >>>>>> >> especially with many large size columns. I had also mentioned about >>>>>> >> ORDER BY >>>>>> >> in approach A. I am not sure whether there is some kind of >>>>>> >> optimization in >>>>>> >> the sort, such as: if we find unique rows with the first n columns, >>>>>> >> it does >>>>>> >> not compare the rest of the columns. >>>>>> >> >>>>>> >> I think declaring cursors is a cool idea in general for DMLs but it >>>>>> >> requires refactoring of DML planning, and also it requires ORDER >>>>>> >> BY. There >>>>>> >> is a concurrent update issue #398 for which we do require a >>>>>> >> refactor of DML >>>>>> >> handling. While doing that it will be clearer whether declaring >>>>>> >> cursor is >>>>>> >> really beneficial or if it's not feasible. For ORDER BY, again, for >>>>>> >> long >>>>>> >> term, we should have a primary key or an internal unique key so >>>>>> >> that rows >>>>>> >> can be ordered on that single column as against all columns. So >>>>>> >> again, we >>>>>> >> still are better of with a new system column. >>>>>> >> >>>>>> >> >>>>>> >> As regards to approach C, if we find a way to uniquely generate a >>>>>> >> new row >>>>>> >> id independently, then the task of generating rowid will be pretty >>>>>> >> lightweight. We won't require any other table to store it or >>>>>> >> generate it. >>>>>> >> The coordinator will generate it at each insert (both fqs and >>>>>> >> non-fqs), or >>>>>> >> may be datanodes themselves find a way to generate a new rowid >>>>>> >> which is >>>>>> >> always the same regardless of the datanode. A combination of gxid, >>>>>> >> timestamp >>>>>> >> and cmd id can be used to construct a unique rowid at the >>>>>> >> coordinator. >>>>>> >> >>>>>> >> I think one action plan can be : >>>>>> >> 1. Use Mason's patch and tweak it so that it needs very little >>>>>> >> modification later on if and when we add the system rowid column. >>>>>> >> 2. Check in the patch but let it not error out if the primary key >>>>>> >> is not >>>>>> >> there. This way we would at least make the replicated tables with >>>>>> >> primary >>>>>> >> keys work without data issues, but continue to work as it is now >>>>>> >> for tables >>>>>> >> without primary key. >>>>>> >> 3. Lastly support the new system row id implementation, and do an >>>>>> >> incremental change in Mason's checked in changes to use this id >>>>>> >> instead of >>>>>> >> primary key. >>>>>> >> >>>>>> > >>>>>> > Sounds good but as i understand 3 is not targeted for 1.2? >>>>>> >>>>>> I agree on this too. System column will benefit not only this, but >>>>>> also WCO, more variety of the cursor, and Triggers. I agree this is >>>>>> targeted to 1.3 or later. >>>>>> >>>>>> Regards; >>>>>> --- >>>>>> Koichi Suzuki >>>>>> >>>>>> > >>>>>> >> >>>>>> >> >>>>>> >>> >>>>>> >>> On Thu, Feb 13, 2014 at 9:45 AM, Koichi Suzuki >>>>>> >>> <koi...@gm...> >>>>>> >>> wrote: >>>>>> >>>> >>>>>> >>>> Hi, >>>>>> >>>> >>>>>> >>>> I tested the patch and found that primary key is mandatory. We >>>>>> >>>> need >>>>>> >>>> to modify regression test considerably to give each replicated >>>>>> >>>> table >>>>>> >>>> primary keys. >>>>>> >>>> >>>>>> >>>> I think this patch helps but I'm not afraid this is good, >>>>>> >>>> especially >>>>>> >>>> when we try to take XC features back to PG. >>>>>> >>>> >>>>>> >>>> Did you post another patch to use all column values if primary >>>>>> >>>> key is >>>>>> >>>> not available? >>>>>> >>>> >>>>>> >>>> I think better way is as follows: >>>>>> >>>> >>>>>> >>>> 1) If primary key is defined, use it, >>>>>> >>>> 2) If not, create a primary key as system column, the size should >>>>>> >>>> be >>>>>> >>>> 64bit. >>>>>> >>>> 3) If primary key is added to a replicated table, remove system >>>>>> >>>> primary >>>>>> >>>> key. >>>>>> >>>> >>>>>> >>>> The value of primary key can be obtained as follows: >>>>>> >>>> >>>>>> >>>> 1) add new column to pgxc_class catalog to represent maximum >>>>>> >>>> value of >>>>>> >>>> the system primary key, >>>>>> >>>> 2) when first "insert" is done to the primary node, system >>>>>> >>>> primary key >>>>>> >>>> value is taken from 1) and 1) is updated. The value is returned >>>>>> >>>> to >>>>>> >>>> the coordinator to be propagated to other nodes. >>>>>> >>>> 3) when subsequent "insert" is being done, system primary key >>>>>> >>>> value is >>>>>> >>>> added to the column value. In this case, each datanode updates >>>>>> >>>> 1) >>>>>> >>>> column value if it is larger than the current maximum value. >>>>>> >>>> >>>>>> >>>> 3) is important to change primary node to another. This is >>>>>> >>>> needed to >>>>>> >>>> carry over the primary node to another. >>>>>> >>>> >>>>>> >>>> ALTER TABLE should take care of them. >>>>>> >>>> >>>>>> >>>> Other issues are: >>>>>> >>>> >>>>>> >>>> 4) pg_dump/pg_dumpall should not include this system column >>>>>> >>>> value, >>>>>> >>>> 5) cluster may need to handle this too to repack system primary >>>>>> >>>> key >>>>>> >>>> value (not now but at least in 1.3 or later). >>>>>> >>>> >>>>>> >>>> Regards; >>>>>> >>>> --- >>>>>> >>>> Koichi Suzuki >>>>>> >>>> >>>>>> >>>> >>>>>> >>>> 2013-11-02 9:26 GMT+09:00 Mason Sharp <ms...@tr...>: >>>>>> >>>> > Please see attached patch that tries to address the issue of XC >>>>>> >>>> > using >>>>>> >>>> > CTID >>>>>> >>>> > for replicated updates and deletes when it is evaluated at a >>>>>> >>>> > coordinator >>>>>> >>>> > instead of being pushed down. >>>>>> >>>> > >>>>>> >>>> > The problem here is that CTID could be referring to a different >>>>>> >>>> > tuple >>>>>> >>>> > altogether on a different data node, which is what happened for >>>>>> >>>> > one of >>>>>> >>>> > our >>>>>> >>>> > Postgres-XC support customers, leading to data issues. >>>>>> >>>> > >>>>>> >>>> > Instead, the patch looks for a primary key or unique index >>>>>> >>>> > (with the >>>>>> >>>> > primary >>>>>> >>>> > key preferred) and uses those values instead of CTID. >>>>>> >>>> > >>>>>> >>>> > The patch could be improved further. Extra parameters are set >>>>>> >>>> > even if >>>>>> >>>> > not >>>>>> >>>> > used in the execution of the prepared statement sent down to >>>>>> >>>> > the data >>>>>> >>>> > nodes. >>>>>> >>>> > >>>>>> >>>> > Regards, >>>>>> >>>> > >>>>>> >>>> > >>>>>> >>>> > -- >>>>>> >>>> > Mason Sharp >>>>>> >>>> > >>>>>> >>>> > TransLattice - http://www.translattice.com >>>>>> >>>> > Distributed and Clustered Database Solutions >>>>>> >>>> > >>>>>> >>>> > >>>>>> >>>> > >>>>>> >>>> > ------------------------------------------------------------------------------ >>>>>> >>>> > November Webinars for C, C++, Fortran Developers >>>>>> >>>> > Accelerate application performance with scalable programming >>>>>> >>>> > models. >>>>>> >>>> > Explore >>>>>> >>>> > techniques for threading, error checking, porting, and tuning. >>>>>> >>>> > Get the >>>>>> >>>> > most >>>>>> >>>> > from the latest Intel processors and coprocessors. See >>>>>> >>>> > abstracts and >>>>>> >>>> > register >>>>>> >>>> > >>>>>> >>>> > >>>>>> >>>> > http://pubads.g.doubleclick.net/gampad/clk?id=60136231&iu=/4140/ostg.clktrk >>>>>> >>>> > _______________________________________________ >>>>>> >>>> > Postgres-xc-developers mailing list >>>>>> >>>> > Pos...@li... >>>>>> >>>> > >>>>>> >>>> > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers >>>>>> >>>> > >>>>>> >>>> >>>>>> >>>> >>>>>> >>>> >>>>>> >>>> ------------------------------------------------------------------------------ >>>>>> >>>> Android apps run on BlackBerry 10 >>>>>> >>>> Introducing the new BlackBerry 10.2.1 Runtime for Android apps. >>>>>> >>>> Now with support for Jelly Bean, Bluetooth, Mapview and more. >>>>>> >>>> Get your Android app in front of a whole new audience. Start >>>>>> >>>> now. >>>>>> >>>> >>>>>> >>>> >>>>>> >>>> http://pubads.g.doubleclick.net/gampad/clk?id=124407151&iu=/4140/ostg.clktrk >>>>>> >>>> _______________________________________________ >>>>>> >>>> Postgres-xc-developers mailing list >>>>>> >>>> Pos...@li... >>>>>> >>>> >>>>>> >>>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers >>>>>> >>> >>>>>> >>> >>>>>> >>> >>>>>> >>> >>>>>> >>> -- >>>>>> >>> Best Wishes, >>>>>> >>> Ashutosh Bapat >>>>>> >>> EnterpriseDB Corporation >>>>>> >>> The Postgres Database Company >>>>>> >>> >>>>>> >>> >>>>>> >>> >>>>>> >>> ------------------------------------------------------------------------------ >>>>>> >>> Android apps run on BlackBerry 10 >>>>>> >>> Introducing the new BlackBerry 10.2.1 Runtime for Android apps. >>>>>> >>> Now with support for Jelly Bean, Bluetooth, Mapview and more. >>>>>> >>> Get your Android app in front of a whole new audience. Start now. >>>>>> >>> >>>>>> >>> >>>>>> >>> http://pubads.g.doubleclick.net/gampad/clk?id=124407151&iu=/4140/ostg.clktrk >>>>>> >>> _______________________________________________ >>>>>> >>> Postgres-xc-developers mailing list >>>>>> >>> Pos...@li... >>>>>> >>> >>>>>> >>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers >>>>>> >>> >>>>>> >> >>>>>> >> >>>>>> >> >>>>>> >> >>>>>> >> ------------------------------------------------------------------------------ >>>>>> >> Managing the Performance of Cloud-Based Applications >>>>>> >> Take advantage of what the Cloud has to offer - Avoid Common >>>>>> >> Pitfalls. >>>>>> >> Read the Whitepaper. >>>>>> >> >>>>>> >> >>>>>> >> http://pubads.g.doubleclick.net/gampad/clk?id=121054471&iu=/4140/ostg.clktrk >>>>>> >> >>>>>> >> _______________________________________________ >>>>>> >> Postgres-xc-developers mailing list >>>>>> >> Pos...@li... >>>>>> >> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers >>>>>> >> >>>>>> > >>>>>> > >>>>>> > >>>>>> > -- >>>>>> > Ahsan Hadi >>>>>> > Snr Director Product Development >>>>>> > EnterpriseDB Corporation >>>>>> > The Enterprise Postgres Company >>>>>> > >>>>>> > Phone: +92-51-8358874 >>>>>> > Mobile: +92-333-5162114 >>>>>> > >>>>>> > Website: www.enterprisedb.com >>>>>> > EnterpriseDB Blog: http://blogs.enterprisedb.com/ >>>>>> > Follow us on Twitter: http://www.twitter.com/enterprisedb >>>>>> > >>>>>> > This e-mail message (and any attachment) is intended for the use of >>>>>> > the >>>>>> > individual or entity to whom it is addressed. This message contains >>>>>> > information from EnterpriseDB Corporation that may be privileged, >>>>>> > confidential, or exempt from disclosure under applicable law. If you >>>>>> > are not >>>>>> > the intended recipient or authorized to receive this for the >>>>>> > intended >>>>>> > recipient, any use, dissemination, distribution, retention, >>>>>> > archiving, or >>>>>> > copying of this communication is strictly prohibited. If you have >>>>>> > received >>>>>> > this e-mail in error, please notify the sender immediately by reply >>>>>> > e-mail >>>>>> > and delete this message. >>>>>> > >>>>>> > >>>>>> > ------------------------------------------------------------------------------ >>>>>> > Managing the Performance of Cloud-Based Applications >>>>>> > Take advantage of what the Cloud has to offer - Avoid Common >>>>>> > Pitfalls. >>>>>> > Read the Whitepaper. >>>>>> > >>>>>> > http://pubads.g.doubleclick.net/gampad/clk?id=121054471&iu=/4140/ostg.clktrk >>>>>> > _______________________________________________ >>>>>> > Postgres-xc-developers mailing list >>>>>> > Pos...@li... >>>>>> > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers >>>>>> > >>>>>> >>>>>> >>>>>> ------------------------------------------------------------------------------ >>>>>> Managing the Performance of Cloud-Based Applications >>>>>> Take advantage of what the Cloud has to offer - Avoid Common Pitfalls. >>>>>> Read the Whitepaper. >>>>>> >>>>>> http://pubads.g.doubleclick.net/gampad/clk?id=121054471&iu=/4140/ostg.clktrk >>>>>> _______________________________________________ >>>>>> Postgres-xc-developers mailing list >>>>>> Pos...@li... >>>>>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers >>>>> >>>>> >>>>> >>>>> >>>>> -- >>>>> -- >>>>> Abbas >>>>> Architect >>>>> >>>>> Ph: 92.334.5100153 >>>>> Skype ID: gabbasb >>>>> www.enterprisedb.com >>>>> >>>>> Follow us on Twitter >>>>> @EnterpriseDB >>>>> >>>>> Visit EnterpriseDB for tutorials, webinars, whitepapers and more >>>> >>>> >>>> >>>> >>>> -- >>>> Ahsan Hadi >>>> Snr Director Product Development >>>> EnterpriseDB Corporation >>>> The Enterprise Postgres Company >>>> >>>> Phone: +92-51-8358874 >>>> Mobile: +92-333-5162114 >>>> >>>> Website: www.enterprisedb.com >>>> EnterpriseDB Blog: http://blogs.enterprisedb.com/ >>>> Follow us on Twitter: http://www.twitter.com/enterprisedb >>>> >>>> This e-mail message (and any attachment) is intended for the use of the >>>> individual or entity to whom it is addressed. This message contains >>>> information from EnterpriseDB Corporation that may be privileged, >>>> confidential, or exempt from disclosure under applicable law. If you are not >>>> the intended recipient or authorized to receive this for the intended >>>> recipient, any use, dissemination, distribution, retention, archiving, or >>>> copying of this communication is strictly prohibited. If you have received >>>> this e-mail in error, please notify the sender immediately by reply e-mail >>>> and delete this message. >>> >>> >>> >>> >>> -- >>> -- >>> Abbas >>> Architect >>> >>> Ph: 92.334.5100153 >>> Skype ID: gabbasb >>> www.enterprisedb.com >>> >>> Follow us on Twitter >>> @EnterpriseDB >>> >>> Visit EnterpriseDB for tutorials, webinars, whitepapers and more >> >> >> >> >> -- >> Ahsan Hadi >> Snr Director Product Development >> EnterpriseDB Corporation >> The Enterprise Postgres Company >> >> Phone: +92-51-8358874 >> Mobile: +92-333-5162114 >> >> Website: www.enterprisedb.com >> EnterpriseDB Blog: http://blogs.enterprisedb.com/ >> Follow us on Twitter: http://www.twitter.com/enterprisedb >> >> This e-mail message (and any attachment) is intended for the use of the >> individual or entity to whom it is addressed. This message contains >> information from EnterpriseDB Corporation that may be privileged, >> confidential, or exempt from disclosure under applicable law. If you are not >> the intended recipient or authorized to receive this for the intended >> recipient, any use, dissemination, distribution, retention, archiving, or >> copying of this communication is strictly prohibited. If you have received >> this e-mail in error, please notify the sender immediately by reply e-mail >> and delete this message. > > > > > -- > -- > Abbas > Architect > > Ph: 92.334.5100153 > Skype ID: gabbasb > www.enterprisedb.com > > Follow us on Twitter > @EnterpriseDB > > Visit EnterpriseDB for tutorials, webinars, whitepapers and more > > ------------------------------------------------------------------------------ > Flow-based real-time traffic analytics software. Cisco certified tool. > Monitor traffic, SLAs, QoS, Medianet, WAAS etc. with NetFlow Analyzer > Customize your own dashboards, set traffic alerts and generate reports. > Network behavioral analysis & security monitoring. All-in-one tool. > http://pubads.g.doubleclick.net/gampad/clk?id=126839071&iu=/4140/ostg.clktrk > _______________________________________________ > Postgres-xc-developers mailing list > Pos...@li... > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers > |