Re: Logical replication from 11.x to 12.x and "unique key violations"

I have had this problem with logical replication on PG 10 repeatedly. In a clean build no problem.

But if I am restarting replication because of some problem I’ve seen problems with rows already present.

My own fix, which has worked in my shop, is to add replica triggers to check for the row being already present. If it is, they drop the row on the floor. This lets stuff come in in whatever order it happens to come in.

Sample code:

if TG_OP = ‘INSERT’ then
select id into id1 from table1 where id = new.id;
if id1 is not null then
— log error for analysis
return null;
end if;
end if;

In an ideal world, this would probably not be necessary. But it can be tricky to restart replication in an absolutely clean way across all tables and in this case it can be better to allow for a bit of overlap in the rows being replicated.

FWIW,

John

> On Jul 20, 2020, at 1:47 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Thomas Kellerer <shammat(at)gmx(dot)net> writes:
>>> I have a strange error when using logical replication between a 11.2
>>> source database and a 12.3 target.
>>>
>>> If I create the publication with all needed tables (about 50) at
>>> once, I get "duplicate key value violates unique constraint xxx_pkey"
>>> errors during the initial replication (when creating the
>>> subscription).
>>>
>>> When create the publication only with a few tables, the initial data
>>> sync works without problems. To replicate all tables, I add the
>>> tables incrementally to the publication, and refresh the
>>> subscription.
>>>
>>> If I do it like that (step-by-step) everything works fine. Tables
>>> that generated the "duplicate key value" error previously will
>>> replicate just fine. The tables are quite small, some of them less
>>> then 100 rows.
>
> I have not looked at the code, but it wouldn't surprise me if the initial
> replication just copies all the specified tables in some random order.
> If there are FK references involved, the replication would have to be
> done with referenced tables first, and I bet there's no logic for that.
> (Even if there was, it could not cope with circular references or
> self-references.)
>
> Best bet might be to not install the subscriber's foreign key
> constraints till after the initial sync is done.
>
> regards, tom lane
>
>
>

John Ashmead
139 Montrose Avenue
Rosemont, PA, 19010-1508
(610) 527 9560
mobile (610) 247 2323
john(dot)ashmead(at)ashmeadsoftware(dot)com