Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tqa0o-0079Y9-S6 for pgsql-general@arkaria.postgresql.org; Fri, 07 Mar 2025 15:53:23 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tqa0n-009Kkg-DM for pgsql-general@arkaria.postgresql.org; Fri, 07 Mar 2025 15:53:21 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tqa0m-009KY1-T7 for pgsql-general@lists.postgresql.org; Fri, 07 Mar 2025 15:53:21 +0000 Received: from mail-il1-x12c.google.com ([2607:f8b0:4864:20::12c]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tqa0k-001W5e-1w for pgsql-general@postgresql.org; Fri, 07 Mar 2025 15:53:19 +0000 Received: by mail-il1-x12c.google.com with SMTP id e9e14a558f8ab-3cf8e017abcso7146385ab.1 for ; Fri, 07 Mar 2025 07:53:18 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1741362798; x=1741967598; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=19pOUoH8MJ4ikXgMej5hAbyiZfowwbXxq/UsRFHy3cg=; b=HxuTNjXpM+ZgMjfl8AjuPgdGsJ3dLsTpf3PVMiu3btWmsJJgPkP5pbGeLTzRMcQawd nN+OsOQ38zO3ZPl0WEAV2JnwecwORMYqGYpgqDFxIymB4Yq9hfZpGnYAtoa96yQgPZNV NPwDeq0yINRQR3oVQFU3QsU7bEq6jB+OqQVGwoL2R5r/+Tu8cFBARGq97bzFxWF1QuVs CyLgDeN/am4+md4CZNqu0vu0kGn9CSpNnDr7s7jMRHaolHIvkcjFbGccxj0z16P1V/DV JAqLwegUqNa2vhz448RUwV4f6wxj9SVhBp9waz2m7fSrRb9wbQsBj4FmEgRbQnXQbyoR WpAw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1741362798; x=1741967598; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=19pOUoH8MJ4ikXgMej5hAbyiZfowwbXxq/UsRFHy3cg=; b=EoTmtFFtgQKdgch8YdHrrDrJ3/UDwqhD/1gafyoKwWs317IA80Ogb/OCi+QSvEYMqL bbHQwF2djIRTuWufWqbRIQtiT26kp2gjnp0n5dcrq1S4a50fuycvvL8x7JxHMmGjVTh4 tTJXMuESKG43zwU9qYGHP1sGfhEJQvGLfPtRqZs8lQjxXHv8jzeISLgiXq0TeF5XkcYO 1KZxXMEThELiR9S2ekSj/J3e6IIwtkDdR9fc1K2fJDmdWrCe25FV5/b6g3Fu6Sdia9Bj GqpTI9hAA7lcDyLX6HcA0lSA0kQwaw1WVfW0hTZsyaJKy1uuthq1SmzV46G5gX+wmqS8 k2eg== X-Forwarded-Encrypted: i=1; AJvYcCUDcR3vkRN0YWhg6NIDye+6MvCKXNb/tkeYF7XJXfGEg7O2v9pbz3fvVyNlt2N5vnCymK1GnLDiwPLmUOcj@postgresql.org X-Gm-Message-State: AOJu0YyMmnbY3KtxwTM8LdOtrTO0dCcEAqOmozFqAoTh/aADjLKEiaeL jd326GHoP/ZagI7P4E41dUvSz3l1ME/+FING9sEdunOAvIA4au+kKpqln3Uy1JSHE/+rkg2GILD 1CrVLPyME4RBl7JY0tjRdGS+Inp8= X-Gm-Gg: ASbGncs9z3QqhUm22uUhHkYL3XuHFJSPt2PDCMBySZvI6VPGNZMSJ3YZ3PkN0gAEv98 SsLeg7w5ZPJjlyL5exuur4uvwh+TktoV40RI4GrZmJTPn0tCpgCYIZrVeUB+K8ChitllXtrS+Pu 1NQwcnzT6We67f/CP9jaMW9+Ivx+Rs6xUar/DFwegDLYXNAsvtkh/cvgMcvQDm X-Google-Smtp-Source: AGHT+IGZbqnyCVJOcguJEJ95xQZEfpiwxXc94utFR2W4HgI+G6rhtTu8CS2xwNsMn5Im7YR18mY+Wsg4G0XqVM/Eqqg= X-Received: by 2002:a05:6e02:1c03:b0:3d3:ced4:db9b with SMTP id e9e14a558f8ab-3d4419473b8mr45116955ab.5.1741362797918; Fri, 07 Mar 2025 07:53:17 -0800 (PST) MIME-Version: 1.0 References: <36356aa1-1a5c-44af-a1c0-f190249812ca@aklaver.com> <6aee94f9-d421-4622-bccc-bdac37969be0@aklaver.com> <75b33741-ee99-4524-b63a-edad21c1266d@aklaver.com> In-Reply-To: From: Greg Sabino Mullane Date: Fri, 7 Mar 2025 10:52:40 -0500 X-Gm-Features: AQ5f1Jq8iIBPeiu-GAJY2pzLAgc3OofYEOBy1pTLs3Ah2Y0osfC5JX2aliwLloU Message-ID: Subject: Re: Duplicate Key Values To: mark bradley Cc: Adrian Klaver , pgsql-general Content-Type: multipart/alternative; boundary="000000000000fef8dd062fc29e3d" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000fef8dd062fc29e3d Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Fri, Mar 7, 2025 at 9:35=E2=80=AFAM mark bradley wrote: > This is what MS Copilot has to say about this apparent bug where Postgres > inserts extra rows violating a primary keys uniqueness constraint: > > Yes, this issue has been encountered by others. There are a few potential > reasons why this might happen: > > 1. *Sequence Out of Sync*: Sometimes, the sequence that generates > unique values for the primary key can become out of sync, especially a= fter > a bulk import or a database restore. You can check if the sequence is = out > of sync and reset it if necessary. > 2. *Index Corruption*: Index corruption can occur due to various > reasons, such as hardware failures or bugs in earlier versions of > PostgreSQL. This can lead to duplicate primary keys being inserted. > 3. *Table Inheritance*: If you are using table inheritance, primary > keys are not enforced among inherited tables. This can lead to duplica= tes > if not handled correctly. > 4. *Application Logic*: Sometimes, the application logic might > inadvertently insert duplicate records. Reviewing the application code= and > insert statements can help identify and resolve such issues. > > This is AI gobbledygook, and can be ignored. The only real option is number 2 (index corruption). > 1. There is no index on the primary key *node_id*, and I understand > there should be one. > > There is an index, as your table definition showed. What to do? I hesitate to just delete my tables and start over because > this error will reoccur. > The error should not reoccur. At least, a normal Postgres system will prevent this from happening in the first place. To clean it up, carefully run the below. If an error appears, or something does not look right, rollback and stop. -- Encourage not using indexes: set enable_indexscan =3D 0; set enable_bitmapscan =3D 0; set enable_indexonlyscan =3D 0; -- Sanity check. This should return a number greater than 1. If not, stop. set search_path =3D public; select count(*) from dataset where node_id =3D 26; -- Make a backup: create table dataset_backup as select * from dataset; -- Test out the process on a subset of the data: create table test_dataset as select * from dataset where node_id < 30; create table test_dataset_duperows_20250307 (like dataset); begin; set local session_replication_role =3D 'replica'; with goodctids as (select min(ctid) from TEST_dataset group by node_id) , mydelete as (delete from TEST_dataset where not exists (select 1 from goodctids where min=3Dctid) returning *) insert into test_dataset_duperows_20250307 select * from mydelete; reset session_replication_role; commit; -- STOP HERE and examine the test_dataset and test_dataset_duperows_20250307 tables -- -- If ZERO rows were deleted, then you should no go further, -- as some of the underlying assumptions must be wrong. -- Do the real table: create table dataset_duperows_20250307 (like dataset); begin; set local session_replication_role =3D 'replica'; with goodctids as (select min(ctid) from dataset group by node_id) , mydelete as (delete from dataset where not exists (select 1 from goodctids where min=3Dctid) returning *) insert into dataset_duperows_20250307 select * from mydelete; reset session_replication_role; commit; -- Rebuild the index reindex index concurrently dataset_pkey; -- Put things back from good measure: reset enable_indexscan; reset enable_bitmapscan; reset enable_indexonlyscan; drop table test_dataset; drop table test_dataset_duperows; Given the issues, I would keep the dataset_backup table around for a while. And make sure your backups are running and up to date. You might also want to reindex all the tables in your database, to see if any other issues are lurking. Check your Postgres logs closely to see if anything else unusual has appeared. Look over your OS logs to see if there are clues as to how the corruption happened. Maybe you recently upgraded your OS? Cheers, Greg -- Crunchy Data - https://www.crunchydata.com Enterprise Postgres Software Products & Tech Support --000000000000fef8dd062fc29e3d Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Fri, Mar 7, 2025 at 9:35=E2=80=AFAM ma= rk bradley <markbradyju@outlo= ok.com> wrote:
This is what MS Copilot has to say about this apparent bug where Postgres i= nserts extra rows violating a primary keys uniqueness constraint:

Yes, this issue has been encountered by others. There are a few potential r= easons why this might happen:
  1. Sequence Out of Sync= : Sometimes, the sequence that generates unique values for the primary key = can become out of sync, especially after a bulk import or a database restor= e. You can check if the sequence is out of sync and reset it if necessary.
  2. Index Corruption: In= dex corruption can occur due to various reasons, such as hardware failures = or bugs in earlier versions of PostgreSQL. This can lead to duplicate prima= ry keys being inserted.
  3. Table Inheritance: I= f you are using table inheritance, primary keys are not enforced among inhe= rited tables. This can lead to duplicates if not handled correctly.
  4. Application Logic: S= ometimes, the application logic might inadvertently insert duplicate record= s. Reviewing the application code and insert statements can help identify a= nd resolve such issues.
This i= s AI gobbledygook, and can be ignored. The only real option is=C2=A0number = 2 (index corruption).

  • = There is no index on the primary key = node_id, and= I understand there should be one.=C2=A0
    • =

    There is an index, as your table definitio= n showed.

    What to do?=C2=A0 I hesitate to just delete my tables and start over becaus= e this error will reoccur.=C2=A0=C2=A0
    <= br>
    The error should not reoccur. At least, a normal Postgres sys= tem will prevent this from happening in the first place. To clean it up, ca= refully run the below. If an error appears, or something does not look righ= t, rollback and stop.

    -- Encourage not using index= es:
    set enable_indexscan =3D 0;
    set enable_bitmapscan =3D 0;
    set enable_indexonlyscan =3D 0;

    -- Sanity check.= This should return a number greater than 1. If not, stop.
    set se= arch_path =3D public;
    select count(*) from dataset where node_id = =3D 26;

    -- Make a backup:
    create table dataset_= backup as select * from dataset;

    -- Test out the process on a subset= of the data:
    create table test_dataset as select * from dataset where n= ode_id < 30;
    create table test_dataset_duperows_20250307 (like datase= t);
    begin;
    set local session_replication_role =3D 'replica';<= br>with goodctids as (select min(ctid) from TEST_dataset group by node_id)<= br>=C2=A0, mydelete as (delete from TEST_dataset where not exists (select 1= from goodctids where min=3Dctid)
    =C2=A0 =C2=A0returning *)
    insert in= to test_dataset_duperows_20250307 select * from mydelete;
    reset session_= replication_role;
    commit;

    -- STOP HERE and exam= ine the test_dataset and test_dataset_duperows_20250307 tables
    --
    -- = If ZERO rows were deleted, then you should no go further,
    -- as s= ome of the underlying assumptions must be wrong.

    -- Do the real tabl= e:
    create table dataset_duperows_20250307 (like dataset);
    begin;
    s= et local session_replication_role =3D 'replica';
    with goodctids = as (select min(ctid) from dataset group by node_id)
    =C2=A0 , mydelete as= (delete from dataset where not exists (select 1 from goodctids where min= =3Dctid)
    =C2=A0 =C2=A0 returning *)
    insert into dataset_duperows_2025= 0307 select * from mydelete;
    reset session_replication_role;
    commit;<= /div>

    -- Rebuild the index
    reindex index concurrently= dataset_pkey;

    -- Put things back from good measure:
    reset enable= _indexscan;
    reset enable_bitmapscan;
    reset enable_indexonlysca= n;

    drop table test_dataset;
    drop table test_dataset_duperows;

    Given the issues, I would keep the dataset_backup tab= le around for a while. And make sure your backups are running and up to dat= e.

    You might also want to reindex all the tables in your data= base, to see if any other issues are lurking.

    = Check your Postgres logs closely to see if anything else unusual has appear= ed.

    Look over your OS logs to see if there are clu= es as to how the corruption happened. Maybe you recently upgraded your OS?<= /div>

    Cheers,
    Greg

    --
    Crunchy Data - = https://www.crunchydata.com
    Enterprise Postgres Software Prod= ucts & Tech Support

    --000000000000fef8dd062fc29e3d--