Обсуждение: Dupe Key Violations in Logical Replication with PKs in Place
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.
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.
> On Nov 14, 2023, at 9:04 AM, Ron <ronljohnsonjr@gmail.com> wrote: > > 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 causedsome weirdness with "foreign" characters. Problems with the locale updates are not limited to non-ASCII characters, sorting rules involving "-" changed, for instance.When you make that OS upgrade, you really, really, need to reindex all text types.
On Tue, Nov 14, 2023 at 10:04 AM Ron <ronljohnsonjr@gmail.com> wrote:
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.
The data is UUID strings (typical alphanumeric characters and hyphens) stored in varchar(36) fields, a few booleans and an int. Nothing exotic/weird/foreign to a US-based observer. The DBs are both using UTF8, the LANG env is set to "en_US.utf8" in both sides as well. The primary key fields are those varchar fields storing UUID strings, not using a sequence or any type of auto-incrementing value.
Don Seiler
www.seiler.us
www.seiler.us
> On Nov 14, 2023, at 9:10 AM, Don Seiler <don@seiler.us> wrote: > > The data is UUID strings (typical alphanumeric characters and hyphens) stored in varchar(36) fields, Well, there you go: digits and dashes, the sort order changed.
On Tue, Nov 14, 2023 at 10:10 AM Scott Ribe <scott_ribe@elevated-dev.com> wrote:
> On Nov 14, 2023, at 9:04 AM, Ron <ronljohnsonjr@gmail.com> wrote:
>
> 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.
Problems with the locale updates are not limited to non-ASCII characters, sorting rules involving "-" changed, for instance. When you make that OS upgrade, you really, really, need to reindex all text types.
That's precisely why I'm using logical replication for this migration. The indexes are new on the subscriber server. Re-indexing should only be necessary if I were to do physical replication/restore onto a 22.04 server, no?
Don.
Don Seiler
www.seiler.us
www.seiler.us
On Tue, Nov 14, 2023 at 10:12 AM Scott Ribe <scott_ribe@elevated-dev.com> wrote:
> On Nov 14, 2023, at 9:10 AM, Don Seiler <don@seiler.us> wrote:
>
> The data is UUID strings (typical alphanumeric characters and hyphens) stored in varchar(36) fields,
Well, there you go: digits and dashes, the sort order changed.
But this is a logical replica. That problem and its re-indexing solution should only apply to physical replicas.
Don Seiler
www.seiler.us
www.seiler.us
> On Nov 14, 2023, at 9:12 AM, Don Seiler <don@seiler.us> wrote: > > That's precisely why I'm using logical replication for this migration. The indexes are new on the subscriber server. Re-indexingshould only be necessary if I were to do physical replication/restore onto a 22.04 server, no? As far as I know, you are right about that--I missed the part where you said logical replication onto new clusters.
On Tue, Nov 14, 2023 at 9:44 AM Don Seiler <don@seiler.us> 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.
Well this looks to be human error/cause after all. I made the mistake of announcing the upcoming migration and one eager developer connected to the new/subscription DB and ran some inserts (also running them on the old/publication DB). The inserts were all in one transaction, and look to be responsible for all 3 of duplicate key incidents.
Lesson learned would be that I should have disabled HBA to our apps/developers until the maintenance window and migration are over if I don't want them to connect.
Lesson learned would be that I should have disabled HBA to our apps/developers until the maintenance window and migration are over if I don't want them to connect.
Don Seiler
www.seiler.us
www.seiler.us
> On Nov 14, 2023, at 10:54 AM, Don Seiler <don@seiler.us> wrote: > > Well this looks to be human error/cause after all. I made the mistake of announcing the upcoming migration and one eagerdeveloper connected to the new/subscription DB and ran some inserts (also running them on the old/publication DB). Theinserts were all in one transaction, and look to be responsible for all 3 of duplicate key incidents. So this would be about the 2 billionth or so episode in the DBA series titled "shit happens" ;-) Glad you figured it out.
On Tue, Nov 14, 2023 at 12:02 PM Scott Ribe <scott_ribe@elevated-dev.com> wrote:
> On Nov 14, 2023, at 10:54 AM, Don Seiler <don@seiler.us> wrote:
>
> Well this looks to be human error/cause after all. I made the mistake of announcing the upcoming migration and one eager developer connected to the new/subscription DB and ran some inserts (also running them on the old/publication DB). The inserts were all in one transaction, and look to be responsible for all 3 of duplicate key incidents.
So this would be about the 2 billionth or so episode in the DBA series titled "shit happens" ;-) Glad you figured it out.
I'm not sure why his connection didn't show up in my PG logs. I log connects and disconnects but I don't see anything coming from our apps or VPN network CIDRs, that would have been an important lead early on.
Don Seiler
www.seiler.us
www.seiler.us