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

[Ashutosh B. <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.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