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

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

Hmm.. Yes, it could be serious.   I suppose that CTID changes only when
vacuum full occurs.   Vacuum freeze will updates xmim to the smallest value
and usual vacuum recycles "dead" row area.   We may have a chance to assign
different CTIDs to the same rows in replicated table if vacuum full runs
locally in datanodes.  (My assumption may be wrong.   Does vacuum freeze
move tuples and change their CTID?)

I think we have two different fix for this.

1. Do not assume "the same CTID to the same row".   In this case, to
provide "row identification" to replicated tables, we may need different
system column,

2. Ask primary key for replicated tables when trigger is used, or

3. Run vacuum full (and freeze, if vacuum freeze moves tuples among blocks)
in more strict way, that is, lock all the replica in the datanode in
advance so that CTID can be maintained the same.

I understand the approach 1 and 2 is safer.   Maybe we can begin with 2 and
add 1.   If vacuum freeze moves tuples, approach 3 may not be acceptable.

Anyway, it can take a bit and need more discussion on the spec.

Regards;

---
Koichi Suzuki


2013/9/28 Mason Sharp <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
>
>
> ------------------------------------------------------------------------------
> 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
>
>