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

[Koichi S. <koi...@gm...>]

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