From: Koichi S. <koi...@gm...> - 2014-02-14 05:58:42
|
2014-02-14 14:55 GMT+09:00 Abbas Butt <abb...@en...>: > > > > 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. Again, there are no guarantee that all cursors for a replicated table returns rows in the same order. It is as dangerous as ctid. > >> >> >>>> >>>> >>>> >>>> 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.com >>> >>> Follow us on Twitter >>> @EnterpriseDB >>> >>> Visit EnterpriseDB for tutorials, webinars, whitepapers and more >> >> >> >> >> -- >> Best Wishes, >> Ashutosh Bapat >> EnterpriseDB Corporation >> The Postgres Database Company > > > > > -- > -- > 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 |