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
Thomas Kellerer <
shammat@gmx.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
