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

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 422-6466

The case that we are trying to solve here is when the user statement is not
ship-able and has to be evaluated at the coordinator. If it was ship-able,
there is no problem in that case, it would get shipped like you are
suggesting. However if it is not ship-able, then we will have a step in the
query plan to first select the row to be updated and then a step to update
that row (update being a two step process). What Ashutosh is suggesting is
to have a cursor with an order by for all rows and the quals that the user
query had, and then update the row using WCO.
The reason of using cursors is to base the update on where the cursor
currently points to, rather than the ctid of the row, which could be
different on the datanodes.

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

> If we can use the same ORDER BY clause, I don't understand why we need
> cursor.   We can just ship statements.
> ---
> Koichi Suzuki
>
>
> 2014-02-14 15:20 GMT+09:00 Abbas Butt <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.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>