Re: [Postgres-xc-developers] [postgres-xc:bugs] #454 Update triggers on replicated tables may corrupt data

[Mason S. <ma...@st...>]

On Thu, Sep 26, 2013 at 9:51 PM, Amit Khandekar <
ami...@en...> wrote:

> http://sourceforge.net/p/postgres-xc/bugs/402/
>
> But this issue has nothing do with triggers. I think there should be some
> way to reproduce without triggers. Although, if any user triggers or unique
> constraint triggers makes our job of reproducing the issue easy, that would
> be good.
>

Under what other circumstances can one reproduce? When the statement cannot
be pushed down directly, like an UPDATE with volatile functions?

It was not hard to reproduce... insert a few hundred rows in a table.
Execute an UPDATE on one that has a trigger defined. I guess it could
depend on when each node's particular auto-vacuum was run, perhaps with
concurrent activity.

Anyway, I think this is a major issue.



>
>
> On 27 September 2013 09:33, Ashutosh Bapat <
> ash...@en...> wrote:
>
>> Hi Hackers,
>> While working on triggers me and Amit had thought of this problem and
>> there should be a mail from Amit in that regards. What stopped us from
>> working on this more, was a reproduction scenario where same row landed on
>> two different nodes with different CTID. We couldn't get that scenario even
>> with complex ingredients like vacuum analyze etc. If you are hitting this
>> problem, can one provide us a reproduction.
>>
>>
>> On Thu, Sep 26, 2013 at 11:30 PM, mason_s <ma...@us...> wrote:
>>
>>> ------------------------------
>>>
>>> * [bugs:#454] <http://sourceforge.net/p/postgres-xc/bugs/454/> Update
>>> triggers on replicated tables may corrupt data*
>>>
>>> *Status:* open
>>> *Created:* Thu Sep 26, 2013 06:00 PM UTC by mason_s
>>> *Last Updated:* Thu Sep 26, 2013 06:00 PM UTC
>>> *Owner:* nobody
>>>
>>> We noticed that when updating a single row on a replicated table that we
>>> were getting duplicate key violations, even though the primary key columns
>>> were not involved.
>>>
>>> We dug deeper and noticed that the mechanism uses ctid to identify what
>>> to update for update triggers that are non-"shippable" and executed on a
>>> coordinator. In the case of a replicated table, the ctid value may be
>>> different on different nodes for each tuple. It appears that Postgres-XC
>>> just uses one ctid value from one of the nodes and then sends down the same
>>> UPDATE statement to all of the individual nodes.
>>>
>>> It should either get each ctid for each node and update, or determine
>>> the corresponding unique key values and use that in the generated WHERE
>>> clause.
>>> ------------------------------
>>>
>>> Sent from sourceforge.net because you indicated interest in
>>> https://sourceforge.net/p/postgres-xc/bugs/454/
>>>
>>> To unsubscribe from further messages, please visit
>>> https://sourceforge.net/auth/subscriptions/
>>>
>>
>>
>>
>> --
>> Best Wishes,
>> Ashutosh Bapat
>> EnterpriseDB Corporation
>> The Postgres Database Company
>>
>>
>> ------------------------------------------------------------------------------
>> October Webinars: Code for Performance
>> Free Intel webinars can help you accelerate application performance.
>> Explore tips for MPI, OpenMP, advanced profiling, and more. Get the most
>> from
>> the latest Intel processors and coprocessors. See abstracts and register >
>>
>> http://pubads.g.doubleclick.net/gampad/clk?id=60133471&iu=/4140/ostg.clktrk
>> _______________________________________________
>> Postgres-xc-developers mailing list
>> Pos...@li...
>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>>
>>
>
>
> ------------------------------------------------------------------------------
> October Webinars: Code for Performance
> Free Intel webinars can help you accelerate application performance.
> Explore tips for MPI, OpenMP, advanced profiling, and more. Get the most
> from
> the latest Intel processors and coprocessors. See abstracts and register >
> http://pubads.g.doubleclick.net/gampad/clk?id=60133471&iu=/4140/ostg.clktrk
> _______________________________________________
> Postgres-xc-developers mailing list
> Pos...@li...
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>
>


-- 
Mason Sharp

StormDB - http://www.stormdb.com
The Database Cloud
Postgres-XC Support and Services