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

On Tue, Feb 13, 2018 at 5:25 AM, Peter Eisentraut
<peter(dot)eisentraut(at)2ndquadrant(dot)com> wrote:
> On 2/8/18 10:54, amul sul wrote:
>> Not really, like ExecUpdate for an update of partition key if delete is failed
>> then the further insert will be skipped, but you are correct, it might be more
>> tricky than I can think -- there is no guarantee that the next insert operation
>> which replication worker trying to replicate is part of the update of partition
>> key mechanism. How can one identify that an insert operation on one relation is
>> related to previously deleting operation on some other relation?
>
> I think you somehow need to stitch this back together in logical
> decoding and publish it as an update operation.
>

I am not sure what you have in mind, but it seems difficult to
piggyback it on exiting delete operation because both delete and
insert are separate operations. What might work out is that we have a
separate special update WAL record which constitutes both delete and
insert. Now, it is easier said than done, because current update
tuple routing mechanism uses existing infrastructure for delete and
insert, and now if we want to use a different WAL, then we might need
to change the existing infrastructure as well to avoid writing three
WAL records (one for delete, one for insert, one for special update)
for this operation.

> Otherwise, wrong things
> happen. For example, what happens to a publication that is configured
> to only publish inserts? What happens to update triggers on the
> receiving table? What if the subscriber side is partitioned differently?
>

All of these seems to be valid points and will get addressed if we
decide to tinker WAL format and logical decoding for this operation.
OTOH, we might want to document this behavior for v11 as we are
discussing for some other related issues [1][2].

[1] - https://www.postgresql.org/message-id/CA%2BTgmoY_h%2B3J46zShEZD0_KLRHa1NsJkGrC4Ou%3DBqt%3DKRboHtg%40mail.gmail.com
[2] - https://www.postgresql.org/message-id/CA%2BTgmoacGfUSWSMVRci-duVFSGOoevgq43mSY9Sztd1RRhiHjg%40mail.gmail.com

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com