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 1v040w-00ACMI-FH for pgsql-general@arkaria.postgresql.org; Sat, 20 Sep 2025 20:16:58 +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 1v040t-00DmDn-PB for pgsql-general@arkaria.postgresql.org; Sat, 20 Sep 2025 20:16:55 +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 1v040t-00DmDf-AN for pgsql-general@lists.postgresql.org; Sat, 20 Sep 2025 20:16:55 +0000 Received: from mail-pg1-x531.google.com ([2607:f8b0:4864:20::531]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1v040q-001URf-2b for pgsql-general@lists.postgresql.org; Sat, 20 Sep 2025 20:16:54 +0000 Received: by mail-pg1-x531.google.com with SMTP id 41be03b00d2f7-b5241e51764so2502248a12.1 for ; Sat, 20 Sep 2025 13:16:53 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1758399411; x=1759004211; darn=lists.postgresql.org; h=to:references:message-id:date:cc:in-reply-to:from:subject :mime-version:content-transfer-encoding:from:to:cc:subject:date :message-id:reply-to; bh=h7lM5JuSGLKNXNP61zrH8Lwmoh6Qd++URWqAJYWj+jw=; b=Bo/ZlUj31aWheYxQztFMmZO8Y3OrZHunlCNdhgSvjJUQsW/E5pSr1PVP6b7N1otqZK yqFSg3aeIsO5pIT9TLEgRAvUy9wlbHPqU+S53KuCddyGQd/kiCFcGkOJ4018FZe5QlrC 1Zefxf6olWKdHZSvEtC4mdmMoAcAxegBr3mzwcTRlqTvwpVEOyrBWz63eLqbAEyDzN5S 32RoYuYiUUWoyx9hlnjPmg8nNe2Gj2a0R+g7g46uS/DB2KA77N9jg7aqG0hZkw/SnopD 0Fd6aDNrjr26qOQqFbcpP4UJd7rT5wUHSPIyefAOCXUxEr4hfGofJRkzsyc7X6b5pZuh Ja5A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1758399411; x=1759004211; h=to:references:message-id:date:cc:in-reply-to:from:subject :mime-version:content-transfer-encoding:x-gm-message-state:from:to :cc:subject:date:message-id:reply-to; bh=h7lM5JuSGLKNXNP61zrH8Lwmoh6Qd++URWqAJYWj+jw=; b=rtTLyT4VlLQHv2wZYwnHmrSeiDRGvSK8+1k/n3d/m9kVxglZooKw/NKYo8D5K4lM9I Wzv+i2y7UBpDufP2vdGyTfdXv3tIS0zwzXdXQEeZxTKfrkIBWPCzorboUvEYLi+NcrVY ujDABejHBzzfmo1dlJbDBgggo7sE70a20G4qwe/QbEanyK4XTHBHPe4lFjufy4qO68PI 95iIxquLuAGwrFvTt5K7SI4BA9JH95D4wpU+SmYywwpJHksweeQHFyuypkHLnVAuH+RA Fr8rf1jeiSMohAw02kv+PpzhxSZeJNx/8mjGw7uaoYcLc4cS2eEKOQo4v6MF0TgERU9G wB8g== X-Forwarded-Encrypted: i=1; AJvYcCUnkTWJLT4moJG3LqQvI9aMinR2B2mIMoaJEKB41Sav5NKUjSOqH9JG/EI313jBiVvRot6u6eKmystKThjP@lists.postgresql.org X-Gm-Message-State: AOJu0YzI+D+bPyhDIm5hDH9TZw5Fys7T7kwj10iAebUa0K6wdcu2Dqvg 1FM3huNTAiBJ1IFae9TSJG9r+5Yb62cxbmvVezs5byk8nYepR91vU9Ha X-Gm-Gg: ASbGncuz0oRPW1ekcDmImhcrUaleoiy2tY3Z76YV+UKjlf9VAamSU4njm01VEst+/Tu VAdod/P1C0/RnqRQMXrJQaeZp6L8vW5lxbcTyDjh+wZpQ8ruc4TvADdAqlus3gy0txNq+dJz1z8 ezf8iQVdK2bNybQp0tEBB90YRF1nEQYIX5jWtV8c0JLvqqwOrW+RKUMMn9NQleOhRNEfsO9z6/a xYopfE3QARoW5RBfsvIfsluOpGf7Ou9DzRYsB/PAhwjjZ+FM4v8WGQlPbDXelVET7qfp/mhj1O0 hycagP6O+hPTLw24Z+Uk+ffT1WOvtxtHUPE1lQVIi6dhI4ZaV/B45f5S28+BKoJuY3dBXWx30sy 3FnDoQCbWicEQ1+xDfMfDdUh+mMu2CDhTuIp8SYLttQ== X-Google-Smtp-Source: AGHT+IFqFGw5wnGMVMemrlxv0ONbKf+rYR/aeBmiRFgM7Di+wBB/lDZgodQ/wTSPMBi31h5OgflGRw== X-Received: by 2002:a17:902:b903:b0:267:44e6:11d3 with SMTP id d9443c01a7336-269ba45a564mr68053735ad.21.1758399411432; Sat, 20 Sep 2025 13:16:51 -0700 (PDT) Received: from smtpclient.apple ([108.63.171.235]) by smtp.gmail.com with ESMTPSA id 98e67ed59e1d1-330607b22bbsm8750740a91.15.2025.09.20.13.16.50 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Sat, 20 Sep 2025 13:16:51 -0700 (PDT) Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Mime-Version: 1.0 (1.0) Subject: Re: Index (primary key) corrupt? From: Rob Sargent In-Reply-To: <92999289-f47b-46ac-8867-9fa08041e2ac@aklaver.com> Cc: "David G. Johnston" , Wim Rouquart , Ron Johnson , pgsql-general@lists.postgresql.org Date: Sat, 20 Sep 2025 13:16:40 -0700 Message-Id: <795E8AB6-CE90-4BEE-B410-BD2B15E9DC92@gmail.com> References: <92999289-f47b-46ac-8867-9fa08041e2ac@aklaver.com> To: Adrian Klaver X-Mailer: iPhone Mail (22G100) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk > On Sep 20, 2025, at 9:58=E2=80=AFAM, Adrian Klaver wrote: > =EF=BB=BFOn 9/20/25 09:26, Rob Sargent wrote: >>> So the problem goes away once you=E2=80=99ve reindexed yet you claim it=E2= =80=99s consistent? What are you doing to get the problem to recur after you= =E2=80=99ve done reindex to make it work? >>> David >> I was assuming the OP has a dump of the affected condition and is restor= ing (and perhaps re-fixing). No? >=20 > =46rom this post: >=20 > https://www.postgresql.org/message-id/AS2PR05MB107548567EEDAAB3AF74A6C59EF1= 1A%40AS2PR05MB10754.eurprd05.prod.outlook.com >=20 > " > > Is the PK definition in the pg_dump file? For plain text format can you g= rep/find it? >=20 >=20 > It is in neither, that=E2=80=99s why I'm sure it doesn't get exported. Aft= er a REINDEX statement it is. >=20 >=20 > >How is the dump file being restored? >=20 >=20 > As the code to generate the index is not in the dumpfile this seems irrele= vant to me. > " >=20 > Make of that what you will. >=20 >=20 > -- > Adrian Klaver > adrian.klaver@aklaver.com I don=E2=80=99t see the complete REINDEX command used but from the output of= the query on pg_index it looks like reindex using index name would succeed,= no? Again assuming this was done against the dump which may or may not have= matched a grep attempt. Lord knows I=E2=80=99ve had my share of false negat= ives with grep.=20