Re: Logical replication without a Primary Key

On 2017-12-07 11:38:51 -0800, Joshua D. Drake wrote:
> On 12/07/2017 10:49 AM, Robert Haas wrote:
> > On Thu, Dec 7, 2017 at 9:43 AM, Petr Jelinek
> > <petr(dot)jelinek(at)2ndquadrant(dot)com> wrote:
> > > No it won't, it will update only one row, it does not try to find
> > > multiple matching rows.
> > Good, because that's exactly what it should do. I mean, if you have
> > on the master two tuples that are identical, and you update one of
> > them, then the replica had better update exactly one of them as well.
> > Since they are identical, it doesn't matter *which* one gets updated
> > on the replica, but if you update *both* of them on the replica, then
> > things are out of sync.
>
> Well I think that is a problem actually. If I have:
>
> A    B   C
> foo,bar,baz
> foo,bar,baz
>
> And then I say:
>
> UPDATE test set A = 1 where C = baz
>
> I have updated two rows because there is no primary key to identify the
> differences. Both of those rows should be updated and thus replicated
> otherwise, logical replication (of this specific table) provides inaccurate
> data on the subscriber.

Not a problem. If you updated both rows, then there's two cases:
a) the update actually changed the column values. In which case the first per-row
change that's replicated updates the first row, but the second one won't
again find it as matching in all columns.
b) the update didn't actually change anything. In which case the same
row gets updated twice, but because the column values didn't change,
that doesn't matter.

Greetings,

Andres Freund