From: Wim Rouquart <wim.rouquart@kbc.be>
To: "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Subject: Index (primary key) corrupt?
Thread-Topic: Index (primary key) corrupt?
Thread-Index: Adwol1a+rcvHuOuQQceM5FEl8oW9YQ==
Date: Thu, 18 Sep 2025 12:25:38 +0000
Message-ID:  <AS2PR05MB1075477BA334D4DA262AD75BFEF16A@AS2PR05MB10754.eurprd05.prod.outlook.com>
Accept-Language: nl-BE, en-US
Content-Language: en-US
msip_labels:  MSIP_Label_d44a7eb9-e308-4cb8-ad88-b50d70445f3a_Enabled=True;MSIP_Label_d44a7eb9-e308-4cb8-ad88-b50d70445f3a_SiteId=64af2aee-7d6c-49ac-a409-192d3fee73b8;MSIP_Label_d44a7eb9-e308-4cb8-ad88-b50d70445f3a_SetDate=2025-09-18T12:04:13.0000000Z;MSIP_Label_d44a7eb9-e308-4cb8-ad88-b50d70445f3a_Name=d44a7eb9-e308-4cb8-ad88-b50d70445f3a;MSIP_Label_d44a7eb9-e308-4cb8-ad88-b50d70445f3a_ContentBits=3;MSIP_Label_d44a7eb9-e308-4cb8-ad88-b50d70445f3a_Method=Standard
authentication-results: dkim=none (message not signed)
 header.d=none;dmarc=none action=none header.from=kbc.be;
x-ms-publictraffictype: Email
x-ms-traffictypediagnostic: AS2PR05MB10754:EE_|AS5PR05MB11091:EE_
x-ms-office365-filtering-correlation-id: f20ca00c-4bbd-4038-72ff-08ddf6ae79fd
x-ms-exchange-senderadcheck: 1
x-ms-exchange-antispam-relay: 0
x-microsoft-antispam:  BCL:0;ARA:13230040|19092799006|366016|1800799024|376014|38070700021|8096899003;
x-microsoft-antispam-message-info:  =?us-ascii?Q?AMNO9tBQ004XDjvDyDlubRELRi5HY1j0Ma4csVCbeixcMK1y2EpnF+b1xMvb?=
 =?us-ascii?Q?HBfVMB+5bfzCeTvzh0N7ojqZCdCWaOmCcmHLxkHF8vB59g2ltoLdtrXOY3S9?=
 =?us-ascii?Q?7JNCo+FZJe3lNd2c2LLmmZFVNo27XsDGgJtzWB9JCX3+gWry6qr9jPcxOUS2?=
 =?us-ascii?Q?rodeXRZWhQt705TWBGrwwCduyzPyBeYlp0RP84Mixbs1rk28jfoO8Q6s+eSU?=
 =?us-ascii?Q?Xc45ojOW5pAFdlD9JZZXDERaOlNMUz3UAeW1SWw9uOGIfTVtZxHh1DLXS5mt?=
 =?us-ascii?Q?57ne1BUTuWfaa9Uph9O2z0Ts+PqMNuOk5Sh7EA5rLzPY08E3O3f5c5P/eHvz?=
 =?us-ascii?Q?f7jAH0TyJAOF4y2B/jMWBrCDN0mxoXpAbx2hkoxJPcVlN6b6Jb0iH2lunpt8?=
 =?us-ascii?Q?oThce4AW97T4iM0Nwe7mR7TPOiuQ6tozkJnivbAx9TUaaykJ9cW1sGNX/QA/?=
 =?us-ascii?Q?1qqwSjgsJsP3Su/ZhmDd4iMyfbrwLy6xgK+XTuqaBynAz7PDX8NuLIE2jMOp?=
 =?us-ascii?Q?qezoEaLGCcrp192f/2ODJHUYp1qMwWwidhGsRoLTIhT/tGcz/03jJA8LZnqE?=
 =?us-ascii?Q?Ki/WzoaAlU9nfmWquBGs35Iwyf0/vtIhusq7IdKqlH3uL2/pQ4INVc4xktqG?=
 =?us-ascii?Q?Zpt48btRvKw8Khqzp146TvUGcXPEYnd0v8X8w4I2CIdJN4fVPfrEuGa4w1lm?=
 =?us-ascii?Q?vaEt1wFyAdQmU9rK0DmIMH0crAUAXkcKoJm3REcUooEEhuk5W5qukf9+LkFI?=
 =?us-ascii?Q?6O93Rt97xnHQvRJXwVqAE3rwdy0LKrtSHREvrVmBHFr/OYoZNasvNkWiSasS?=
 =?us-ascii?Q?zyr3NB8jochQHq/EIMZTw0fGDR4fHiiNTyPD+SRnM+3o6KHPKY/TwvXjg10z?=
 =?us-ascii?Q?M6dxCJW9RAPjShGg4YkpDYVkzpR4P50hevZgJskPyoT6FacwVds/5lgLTwiJ?=
 =?us-ascii?Q?t4P0QY4NVh8wxNUWqeayCcVr2yBLsOLrFKsSlpFHFLN+wJlWdCZX0MjFLrGj?=
 =?us-ascii?Q?dP/5KW5CVu3kzReDsjvSIfHNUgN8lpTo7DQry5N5sZ0G34be0mqZgRU57KNT?=
 =?us-ascii?Q?MT4QGf8kmgDUwb6RG7g8La/dgE7KqRP/1XVoEB4nTcRtHxHtnb/jnp5DY9E0?=
 =?us-ascii?Q?IGJALVgw8hjatO3IEXPmPM5IR8kQl0ez+nGZouDqRwEFCloZK3p1/SJza7bs?=
 =?us-ascii?Q?qCZOrkCZJ/OQX0ZEk1Kro1TGZac4WRlQOlTV396qCz7jTyOrxMlOA1u8y2j1?=
 =?us-ascii?Q?ZKJ9DbNCvCupmUqsmopWZGDeeP+z8FQ2nz8kpjSvsQEjhjrAsWN7zcnJX1dU?=
 =?us-ascii?Q?/fj7XE2WkwG8RqQvxkMEvzH+R3vKnf0GXnLvcTMzpeadE4C/HdvQFeR15oxm?=
 =?us-ascii?Q?wltEvlrMEccBIachdASlIXcwRoiJzvfmsQ0TVDNOGZ1FKpifV+RY4KRGhXgu?=
 =?us-ascii?Q?D5+XsBJcNyglOFTFUTgm4H10mXVpb9OXaZpmDDWDh9fRbg83jdwHkQ=3D=3D?=
x-forefront-antispam-report:  CIP:255.255.255.255;CTRY:;LANG:en;SCL:1;SRV:;IPV:NLI;SFV:NSPM;H:AS2PR05MB10754.eurprd05.prod.outlook.com;PTR:;CAT:NONE;SFS:(13230040)(19092799006)(366016)(1800799024)(376014)(38070700021)(8096899003);DIR:OUT;SFP:1101;
x-ms-exchange-antispam-messagedata-chunkcount: 1
x-ms-exchange-antispam-messagedata-0:  =?us-ascii?Q?Roqaqp7eWQGl07Z+JwjD5LvqrdVqQ2nMQQLWWIheTaeH8lcm02tdSBUevQ+y?=
 =?us-ascii?Q?9UIdjZ0VtNQJ5Mt3NosIZ0JMy/RJOd3bekkaPMk5W5OgzpvH0NjWjFy3FEKq?=
 =?us-ascii?Q?2CJFnYdsn7xmS1NmxZiXiaBXnEFV9QsCCHLyAAa4XhXVUl3dzExOksWwjP3k?=
 =?us-ascii?Q?z/NrClik/9VMRmD6hGqzy3aBmeslFkPQbV0BoGnc7oWE7lmVZMkrfAw3WT1k?=
 =?us-ascii?Q?aG8/MAWR2sEOU3UcbzZaGt4fLOoea5KaHiVpBWzULQflNwFDjkx+mF0mZHN0?=
 =?us-ascii?Q?ImAWd6IcCvaw7PLCGDhfUzIh/HEo7/R+qPoN4U0A4I1mBs1ylOQv5KofPUEJ?=
 =?us-ascii?Q?6OMKHow1xKut4GWNzPE0r8MEpZN45Y56gYjpzy6BXbyvkOgO2vdK6owenJv3?=
 =?us-ascii?Q?4AT41cp/OAuBvE/QzNU9yt//eT5oxMrZnyqat7D0PmZpC3iQKCU4TporYJVg?=
 =?us-ascii?Q?bpCzJtDKrqj8lnTpiy2oAuan+Q9piPskfXiPtRtJ9S2iZ/b9IviPaDtTXLUl?=
 =?us-ascii?Q?f0hkVyvjcGDDPTiIzvT5jNmau/TtUOl6tuzbJ9k8t4GgGvqFHMccebjVnH0V?=
 =?us-ascii?Q?pcEN+1C2lsyXP34urNMzYxfGLw6oukGps7ibrSA+hxfmCPrfglMyiKDKArPP?=
 =?us-ascii?Q?kNXhfC/6Nnu+PIlqHccJNkbFVT3cBu4PtJrnZb0UaKYTayx7kyByNqCKD9fw?=
 =?us-ascii?Q?6dC1caBuk9HW3FLY99EMCaKCYsSXvczPxm3+msy3D0pWTRFPeyDHGc2BEEQg?=
 =?us-ascii?Q?Vt6UmPkL3BbdVfdwvL5iD05tdGf3R40qjb4zN0Y1nkyfYCzca3ZBNp1w+R7S?=
 =?us-ascii?Q?Q9TX2oKVpWHfsA/twWilpNqWS1WMPEEjE7VIrwoDzcVi9kxwkj6gU9++Rt08?=
 =?us-ascii?Q?Iq+hJGkIAD+81hb9j9ihf27pMpOWx6umti1druGQ/bkH6ZbqMLHPXGhSw20K?=
 =?us-ascii?Q?4ASOjrZWYbtQXkLZ/nlzvO4P6c6NFoqQnr9pvco81/FjGhm3MYJSe68y2uFt?=
 =?us-ascii?Q?VgBTG4Uk1E0+ZGE0H4OINrjT6IbRIrdYXTHWeZoWLBndGMpdwQBCjWtRzK/Q?=
 =?us-ascii?Q?oV8uWK4kTFxjNUlxLWaRky1hWCtzhuz5k2wB5mQ7eMDk65wqpcqgDtejtYkt?=
 =?us-ascii?Q?84hVFW8nNiTIgPxmE4CAR7mDjxQyIGNj3t+/Z6c83y0fRQrFAv3owZ7jDECW?=
 =?us-ascii?Q?STL38DbCSDNex1t5g9Ggk3G9omYhC65VuR+61KHqH+yAVQ+S7U3sYrN3AzcO?=
 =?us-ascii?Q?Gx0sfEu4KUoRLia4q3Wt4/JKODNeeAMqM/Wv5r30o8SgSrvoTOarpKkC4nEB?=
 =?us-ascii?Q?sZr3L6Z5yw0DUHWzp2Jw6BmxQFE9GvzGj4HhC7AisHS1X5SLgwiwzYyzC9uE?=
 =?us-ascii?Q?0hqE2Y178P04obtGujLUnzTmgqdlzvzPMdlU2YUBJ4SGKMczXIWbqZ6eQAGq?=
 =?us-ascii?Q?2qo9T+E5njKfoljGyTvnTTZaNk2S4gRmDUVy9NTOFdB2EcaMMw9B9YxhALjg?=
 =?us-ascii?Q?b9FX9OD/eAXfqhBf5Lfwg0Img1s8/gSCjkOx4cebEJGFmx2YQmsu4MvtoaTl?=
 =?us-ascii?Q?YdpndEdtUxVqEc0ceaDA7diMpuBrqAlzqFh56Xcx?=
Content-Type: multipart/alternative;
	boundary="_000_AS2PR05MB1075477BA334D4DA262AD75BFEF16AAS2PR05MB10754eu_"
MIME-Version: 1.0
X-OriginatorOrg: kbc.be
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-AuthSource: AS2PR05MB10754.eurprd05.prod.outlook.com
X-MS-Exchange-CrossTenant-Network-Message-Id: f20ca00c-4bbd-4038-72ff-08ddf6ae79fd
X-MS-Exchange-CrossTenant-originalarrivaltime: 18 Sep 2025 12:25:38.7433
 (UTC)
X-MS-Exchange-CrossTenant-fromentityheader: Hosted
X-MS-Exchange-CrossTenant-id: 64af2aee-7d6c-49ac-a409-192d3fee73b8
X-MS-Exchange-CrossTenant-mailboxtype: HOSTED
X-MS-Exchange-CrossTenant-userprincipalname: VtjmjPFSnmXYpfAMPrDp2OIEPFylUlc1nE6kYHFVZLJx9Xn2ZV5wEPIHUobi/PqPDP5Bv2gf/zKMR5kIhIAusQ==
X-MS-Exchange-Transport-CrossTenantHeadersStamped: AS5PR05MB11091
List-Id: <pgsql-general.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-general@lists.postgresql.org>
List-Owner: <mailto:pgsql-general-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-general>
Archived-At: <https://www.postgresql.org/message-id/AS2PR05MB1075477BA334D4DA262AD75BFEF16A%40AS2PR05MB10754.eurprd05.prod.outlook.com>
Precedence: bulk

--_000_AS2PR05MB1075477BA334D4DA262AD75BFEF16AAS2PR05MB10754eu_
Content-Type: text/plain; charset="us-ascii"
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable

Internal

Hello,

When doing a pg_dump of one of our databases one of the tables primary keys=
 doesn't get exported. Pg_dump just skips this index, without any warning w=
hatsoever (verbose mode was used to doublecheck).

When doing a REINDEX the issue is fixed.

As this seems to me to be some form of index corruption, I tried using amch=
eck (bt_index_check and bt_index_parent_check) to verify for corruption but=
 both resulted with no issues (the index is a btree).

I would expect the corruption to show up when using amcheck, am I hitting s=
ome kind of bug here?

Are there any other ways to doublecheck for corruption (without enabling ch=
ecksum upfront)?

This concerns a PostgreSQL version 15 btw.

Thanks!

Disclaimer <https://www.kbc.com/KBCmailDisclaimer>

--_000_AS2PR05MB1075477BA334D4DA262AD75BFEF16AAS2PR05MB10754eu_
Content-Type: text/html; charset="us-ascii"
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable

<html xmlns:v=3D"urn:schemas-microsoft-com:vml" xmlns:o=3D"urn:schemas-micr=
osoft-com:office:office" xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" xmlns=3D"http:=
//www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Dus-ascii">
<meta name=3D"Generator" content=3D"Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
	{font-family:"Cambria Math";
	panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
	{font-family:Aptos;
	panose-1:2 11 0 4 2 2 2 2 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0cm;
	font-size:11.0pt;
	font-family:"Aptos",sans-serif;
	mso-ligatures:standardcontextual;
	mso-fareast-language:EN-US;}
span.EmailStyle17
	{mso-style-type:personal-compose;
	font-family:"Aptos",sans-serif;
	color:windowtext;}
.MsoChpDefault
	{mso-style-type:export-only;
	font-size:11.0pt;
	mso-fareast-language:EN-US;}
@page WordSection1
	{size:612.0pt 792.0pt;
	margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.WordSection1
	{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext=3D"edit">
<o:idmap v:ext=3D"edit" data=3D"1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang=3D"NL-BE" link=3D"#467886" vlink=3D"#96607D" style=3D"word-wrap:=
break-word">
<p style=3D"font-family:Calibri;font-size:10pt;color:#000000;margin:15pt;fo=
nt-style:normal;font-weight:normal;text-decoration:none;" align=3D"Center">
Internal<br>
</p>
<br>
<div>
<div class=3D"WordSection1">
<p class=3D"MsoNormal">Hello,<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal"><span lang=3D"EN-US">When doing a pg_dump of one of =
our databases one of the tables primary keys doesn&#8217;t get exported. Pg=
_dump just skips this index, without any warning whatsoever (verbose mode w=
as used to doublecheck).<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span lang=3D"EN-US"><o:p>&nbsp;</o:p></span></p>
<p class=3D"MsoNormal"><span lang=3D"EN-US">When doing a REINDEX the issue =
is fixed.<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span lang=3D"EN-US"><o:p>&nbsp;</o:p></span></p>
<p class=3D"MsoNormal"><span lang=3D"EN-US">As this seems to me to be some =
form of index corruption, I tried using amcheck (bt_index_check and bt_inde=
x_parent_check) to verify for corruption but both resulted with no issues (=
the index is a btree).<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span lang=3D"EN-US"><o:p>&nbsp;</o:p></span></p>
<p class=3D"MsoNormal"><span lang=3D"EN-US">I would expect the corruption t=
o show up when using amcheck, am I hitting some kind of bug here?<o:p></o:p=
></span></p>
<p class=3D"MsoNormal"><span lang=3D"EN-US"><o:p>&nbsp;</o:p></span></p>
<p class=3D"MsoNormal"><span lang=3D"EN-US">Are there any other ways to dou=
blecheck for corruption (without enabling checksum upfront)?<o:p></o:p></sp=
an></p>
<p class=3D"MsoNormal"><span lang=3D"EN-US"><o:p>&nbsp;</o:p></span></p>
<p class=3D"MsoNormal"><span lang=3D"EN-US">This concerns a PostgreSQL vers=
ion 15 btw.<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span lang=3D"EN-US"><o:p>&nbsp;</o:p></span></p>
<p class=3D"MsoNormal"><span lang=3D"EN-US">Thanks!<o:p></o:p></span></p>
</div>
</div>
<br><a href=3D"https://www.kbc.com/KBCmailDisclaimer">Disclaimer</a></body>
</html>

--_000_AS2PR05MB1075477BA334D4DA262AD75BFEF16AAS2PR05MB10754eu_--