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

John Ashmead schrieb am 20.07.2020 um 20:11:
> 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 <http://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.
>

Interesting idea, I'll keep this in mind.

For now everything works fine, so there is no pressing need.

Thomas