Re: Dupe Key Violations in Logical Replication with PKs in Place

On 11/14/23 09:44, Don Seiler wrote:
> Good morning,
>
> I'm in the midst of a logical replication migration, using PG native 
> logical replication from PG 12 on Ubuntu 18.04 to PG 15 on Ubuntu 22.04. 
> All PG packages are from the PGDG apt repo.
>
> Things had been going smoothly for the most part over the past week, 
> however in the past 24 hours I've had the subscribers error out (I have 
> disable-on-error set) on 3 separate tables for duplicate key violations on 
> INSERT statements. In all 3 cases, the table in question has a valid PK on 
> both the publication and subscription sides.
>
> The record in question exists on both sides and is identical. In all 3 
> cases, I delete the row on the subscriber and re-enable the subscriptions. 
> The INSERT proceeds and inserts an identical row to the one that I just 
> deleted and everything proceeds happily.
>
> I'm very confused, however, as to how this scenario is possible if I have 
> a PK enforced on both sides, although I believe that the publication side 
> PK alone should have prevented this.

What data type(s) in those columns, and what locale on each server? Maybe 
the change from 18.04/12 to 22.04/15 has caused some weirdness with 
"foreign" characters.


-- 
Born in Arizona, moved to Babylonia.