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

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

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.


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