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

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

In this case, we can use these conditions as WHERE clause in usual
UPDATE/DELETE statements, which we cannot somehow now.
---
Koichi Suzuki


2014-02-14 10:46 GMT+09:00 Abbas Butt <abb...@en...>:
>
>
>
> On Fri, Feb 14, 2014 at 6:09 AM, Koichi Suzuki <koi...@gm...>
> wrote:
>>
>> I don't think it works because there're no guarantee each cursor
>> against different datanode returns rows in the same order.
>
>
> Why would a query with an ORDER BY clause on all the columns not return the
> rows in the same order on each datanode?
>
>>
>>
>> Regards;
>> ---
>> Koichi Suzuki
>>
>>
>> 2014-02-13 15:50 GMT+09:00 Ashutosh Bapat
>> <ash...@en...>:
>> > WCO on datanodes should be working fine, so that shouldn't be a problem.
>> >
>> >
>> > On Thu, Feb 13, 2014 at 12:13 PM, 鈴木 幸市 <ko...@in...>
>> > wrote:
>> >>
>> >> Are you sure that this does not come up with any bad side effects to
>> >> support WCO in 1.3?
>> >> ---
>> >> Koichi Suzuki
>> >>
>> >> 2014/02/13 15:24、Ashutosh Bapat <ash...@en...> のメール：
>> >>
>> >> 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.
>> >>
>> >>
>> >> 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
>> >>
>> >>
>> >
>> >
>> >
>> > --
>> > 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