Message-ID: <401c360a-ff4c-4904-8608-22b24b2179d6@gmail.com>
Date: Tue, 14 Nov 2023 10:04:28 -0600
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Dupe Key Violations in Logical Replication with PKs in Place
Content-Language: en-US
To: pgsql-admin@lists.postgresql.org
References: 
 <CAHJZqBC_mnKAH_vwep8cmVqyLhnj1CN+tc36kt73VBXpK_Etsg@mail.gmail.com>
From: Ron <ronljohnsonjr@gmail.com>
In-Reply-To: 
 <CAHJZqBC_mnKAH_vwep8cmVqyLhnj1CN+tc36kt73VBXpK_Etsg@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/401c360a-ff4c-4904-8608-22b24b2179d6%40gmail.com>
Precedence: bulk

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.