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

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 422-6466

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.

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

-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company