Re: Logical replication without a Primary Key

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.

Thanks,

JD

--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc

PostgreSQL Centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://pgconf.org
***** Unless otherwise stated, opinions are my own. *****