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

[Ahsan H. <ahs...@en...>]

On Fri, Feb 14, 2014 at 6:46 AM, Abbas Butt <abb...@en...>wrote:

>
>
>
> 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?
>

hmm...it should. Could they be affected by locale setting?


>> 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.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>
>
>
> ------------------------------------------------------------------------------
> 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
>
>


-- 
Ahsan Hadi
Snr Director Product Development
EnterpriseDB Corporation
The Enterprise Postgres Company

Phone: +92-51-8358874
Mobile: +92-333-5162114

Website: www.enterprisedb.com
EnterpriseDB Blog: http://blogs.enterprisedb.com/
Follow us on Twitter: http://www.twitter.com/enterprisedb

This e-mail message (and any attachment) is intended for the use of the
individual or entity to whom it is addressed. This message contains
information from EnterpriseDB Corporation that may be privileged,
confidential, or exempt from disclosure under applicable law. If you are
not the intended recipient or authorized to receive this for the intended
recipient, any use, dissemination, distribution, retention, archiving, or
copying of this communication is strictly prohibited. If you have received
this e-mail in error, please notify the sender immediately by reply e-mail
and delete this message.