From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: Dupe Key Violations in Logical Replication with PKs in Place
Date: Tue, 14 Nov 2023 10:04:28 -0600
Message-ID: <401c360a-ff4c-4904-8608-22b24b2179d6@gmail.com> (raw)
In-Reply-To: <CAHJZqBC_mnKAH_vwep8cmVqyLhnj1CN+tc36kt73VBXpK_Etsg@mail.gmail.com>
References: <CAHJZqBC_mnKAH_vwep8cmVqyLhnj1CN+tc36kt73VBXpK_Etsg@mail.gmail.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: ronljohnsonjr@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Dupe Key Violations in Logical Replication with PKs in Place
In-Reply-To: <401c360a-ff4c-4904-8608-22b24b2179d6@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox