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

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

I'm thinking to correct trigger implementation to use primary key or
"unique and not null" columns.   As you suggested, this could be a serious
problem.    I found that CTID can be different very easily in replicated
tables and it is very dangerous to use current CTID to identify tuples. ,

CTID of replicated tables can be different very easily as follows:

1. Start at the status where a block B1 of the table S can accommodate only
one more tuple both in the datanode D1 and D2.   D1 is the primary node so
every transaction updating S comes to D1 first.

2. Transaction T1 and T2 are trying to insert new tuple R1 and R2
respectively.   Suppose they are not conflicting.

3. T1 comes first to D1 and inserts R1, which goes to B1.

4. Then T2 comes to D1 and inserts R1, which goes to a new block B2.

5. Coordinator running T1 is very busy so T2 comes first to D2, insert R2,
which goes to B1.

6. Then T1 comes to D2, insert R1, which goes to a new block B2.

So CTID of R1 points to B1 at D1, B2 at D2,  CTID of R2 points to B2 at D1,
B1 at D2.

This can happen very easily but not easy to reproduce systematically.   It
is simple to do similar hypothetical experiment inserting rows even in a
same block.

Any more thoughts?

Regards;

---
Koichi Suzuki


2013/11/6 Mason Sharp <ms...@tr...>

>
>
>
> On Mon, Nov 4, 2013 at 8:17 PM, Koichi Suzuki <koi...@gm...>wrote:
>
>> Mason;
>>
>> Thanks for this big patch.    I understand the intention and would like
>> other members to look at this patch too.
>>
>
> Sure.
>
>
>>
>> I'd like to know your idea if this can be a part of the TRIGGER
>> improvement?
>>
>
> What did you have in mind? Yes, this impacts triggers as well.
>
> Regards,
>
> Mason
>
>
>>
>>
>> 2013/11/2 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.
>>>
>>>
>>>
> --
> Mason Sharp
>
> TransLattice - http://www.translattice.com
> Distributed and Clustered Database Solutions
>
>
>