Menu
▾
▴
Re: [Postgres-xc-developers] Replicated update and delete
|
From: Abbas B. <abb...@en...> - 2014-02-14 05:55:35
|
On Fri, Feb 14, 2014 at 10:48 AM, Ashutosh Bapat < ash...@en...> wrote: > > > > On Fri, Feb 14, 2014 at 7:25 AM, Abbas Butt <abb...@en...>wrote: > >> >> >> >> On Thu, Feb 13, 2014 at 11:24 AM, 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. >>> >> >> Thanks for the suggestion but we currently do not support WCO and we were >> thinking of fixing this issue before we declare 1.2 beta is generally >> available. >> >> > > Abbas, WCO doesn't work from the coordinator, but there is no reason why > it shouldn't work at the datanode. So internally between coordinator and > the datanode, we can always use WCO. > True, Thanks for the clarification. > > >> >>> >>> 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 >>> >>> >> >> >> -- >> -- >> *Abbas* >> Architect >> >> Ph: 92.334.5100153 >> Skype ID: gabbasb >> www.enterprisedb.co <http://www.enterprisedb.com/>m<http://www.enterprisedb.com/> >> >> *Follow us on Twitter* >> @EnterpriseDB >> >> Visit EnterpriseDB for tutorials, webinars, whitepapers<http://www.enterprisedb.com/resources-community>and more<http://www.enterprisedb.com/resources-community> >> > > > > -- > Best Wishes, > Ashutosh Bapat > EnterpriseDB Corporation > The Postgres Database Company > -- -- *Abbas* Architect Ph: 92.334.5100153 Skype ID: gabbasb www.enterprisedb.co <http://www.enterprisedb.com/>m<http://www.enterprisedb.com/> *Follow us on Twitter* @EnterpriseDB Visit EnterpriseDB for tutorials, webinars, whitepapers<http://www.enterprisedb.com/resources-community>and more<http://www.enterprisedb.com/resources-community> |
