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.