Re: In logical replication concurrent update of partition key creates a duplicate record on standby.

On Thu, Feb 8, 2018 at 5:55 PM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> On Wed, Feb 7, 2018 at 6:00 PM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>> On Wed, Feb 7, 2018 at 3:42 PM, amul sul <sulamul(at)gmail(dot)com> wrote:
>>> On Wed, Feb 7, 2018 at 3:03 PM, Amit Khandekar <amitdkhan(dot)pg(at)gmail(dot)com> wrote:
>>>> On 7 February 2018 at 13:53, amul sul <sulamul(at)gmail(dot)com> wrote:
>>>>> Hi,
>>>>>
>>>>> If an update of partition key involves tuple movement from one partition to
>>>>> another partition then there will be a separate delete on one partition and
>>>>> insert on the other partition made.
>>>>>
>>>>> In the logical replication if an update performed on the master and standby at
>>>>> the same moment, then replication worker tries to replicate delete + insert
>>>>> operation on standby. While replying master changes on standby for the delete
>>>>> operation worker will log "concurrent update, retrying" message (because the
>>>>> update on standby has already deleted) and move forward to reply the next
>>>>> insert operation. Standby update also did the same delete+insert is as part of
>>>>> the update of partition key in a result there will be two records inserted on
>>>>> standby.
>>>>
>>>> A quick thinking on how to resolve this makes me wonder if we can
>>>> manage to pass some information through logical decoding that the
>>>> delete is part of a partition key update. This is analogous to how we
>>>> set some information locally in the tuple by setting
>>>> tp.t_data->t_ctid.ip_blkid to InvalidBlockNumber.
>>>>
>>>
>>> +1,
>>>
>>
>> I also mentioned the same thing in the other thread [1], but I think
>> that alone won't solve the dual record problem as you are seeing. I
>> think we need to do something for next insert as you are suggesting.
>>
>
> Can you please once check what was the behavior before Update Tuple
> routing patch (Commit-id: 2f178441) went in?
>

Before this commit such update will be failed with following error:

postgres=# update foo set a=2, b='node1_update' where a=1;
ERROR: new row for relation "foo1" violates partition constraint
DETAIL: Failing row contains (2, node1_update).

Regards,
Amul