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

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

On Fri, Feb 14, 2014 at 1:36 PM, Koichi Suzuki <koi...@gm...>wrote:

> I understood the situation.   So there's three areas we need to fix.
>
> 1) Trigger,
>

I studied the following emails and associated bug reports

(a) Mason's email subject [postgres-xc:bugs] #454 Update triggers on
replicated tables may corrupt data
(b) Amit's email subject [Postgres-xc-core] UPDATE queries on replicated
tables
(c) Amit's bug report : #402 Issue with updates on replicated tables

and concluded that although this bug was observed when Amit was working on
triggers and Mason reported that the data corruption was observed with
update triggers defined on replicated tables, it actually has nothing to do
with triggers. Its a general problem in updates/deletes to replicated
tables.

What exact problem with triggers do you have in mind that comes under this
bug?



> 2) FQS-able updates/deletes.
>

Nothing needs to be done to handle this case. This case is already working
fine and would not be impacted by whatever we do to fix the non-FQS case.


>
> I'm afraid we've not concluded how to solve these two yet.
>
> 3) Non-FQS cursor may work.   It will be nice if 1) and 2) solution
> can be shred here too.
>
> Abbas, could you summarize these three cases?
>
> Regards;
> ---
> Koichi Suzuki
>
>
> 2014-02-14 16:02 GMT+09:00 Ashutosh Bapat <ash...@en...
> >:
> >
> >
> >
> > On Fri, Feb 14, 2014 at 12:22 PM, Abbas Butt <
> abb...@en...>
> > wrote:
> >>
> >> 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.
> >>
> >
> > Very well explained. Thank you. Hope that clears the doubts.
> >
> >>
> >>
> >> 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.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.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>