Re: [Postgres-xc-developers] Replicated update and delete

[Abbas B. <abb...@en...>]

On Fri, Feb 14, 2014 at 10:58 AM, Koichi Suzuki <koi...@gm...>wrote:

> 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.
>

Could you please explain a little further, how would a query that has all
table columns in the ORDER BY clause return rows in different order when
run on the datanodes?



>
> >
> >>
> >>
> >>>>
> >>>>
> >>>>
> >>>> 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
>



-- 
-- 
*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>