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 1v3XBF-0008RW-Qv for pgsql-hackers@arkaria.postgresql.org; Tue, 30 Sep 2025 10:01:57 +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 1v3XBD-008Rne-Lj for pgsql-hackers@arkaria.postgresql.org; Tue, 30 Sep 2025 10:01:56 +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 1v3XBD-008RnW-81 for pgsql-hackers@lists.postgresql.org; Tue, 30 Sep 2025 10:01:56 +0000 Received: from mail-ej1-x62e.google.com ([2a00:1450:4864:20::62e]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1v3XBB-000g3q-0l for pgsql-hackers@lists.postgresql.org; Tue, 30 Sep 2025 10:01:54 +0000 Received: by mail-ej1-x62e.google.com with SMTP id a640c23a62f3a-b463f986f80so7800266b.2 for ; Tue, 30 Sep 2025 03:01:53 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1759226512; x=1759831312; darn=lists.postgresql.org; h=to:date:message-id:subject:mime-version:from:from:to:cc:subject :date:message-id:reply-to; bh=Me+Sd6+wsJsYCJ0fnu0fWup2SY2KNLssSxWcZ7E7XHQ=; b=eyzLxgFL7dtvaD+ykDdXmsSAxauqR1pzvPWY1bPR1oYXH+8PhHMxKFy6F0QM/gVTlK DanScVoOOG9l/3lslU+ZJYoeX3r6gDWfoRrwq+zaEEKeDeIiSxvdW7HOoRWFoaI/zuV7 pSp3H/gjwPRKknfZ76rPt7vh7MwwCyEl0Un2ZBKrzAFPNjahoKhM/d/hIv6jlYtQM6y0 HMGFGUWFItNZ39jiQ90nFIqg3OmiHAC3blnhQJXOcf1he3vxuJRk4X5JVV70tTNsU5XM NLIOkIaqZjfMg5XsGlOQkFmwqtmlcKlh+KFoVZo/pWtCRVMAgf7Tj+YTYFo6/cJG9tMw t2wA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1759226512; x=1759831312; h=to:date:message-id:subject:mime-version:from:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=Me+Sd6+wsJsYCJ0fnu0fWup2SY2KNLssSxWcZ7E7XHQ=; b=PIn+nvxe3ISl2g34Wo5wTRdg8uez9Ur80+D51HheWg0j7cKTJ+oBdEKuo3yj9cx3NE mVmJfT6vwExVaiq+DJqScGOA9ziivO2JThvxe/LY35crFLkLtRRUqqFJSwaUjxnAOIvE tzop+TJFlvgNPVeDHbCwolj4s7qWkAa0fKWkYmmx92Mr/gDz1kiZk2KHWojHTtRo59CZ IkpD0swD/nilLf1vU9PzrIr3Y2gCAPQpUE1jZV8S7rl3Srb2PqFRe/UCm3OInHMMuPhJ MSYDZ9Lv+j5GimU9YqC4WtibPyZvxp+I0hBbDS6m9vYNd72wZ7r1odAeo+v6rRfHADWH YtXA== X-Gm-Message-State: AOJu0YxnN13SUmCFAzj3NgNhqlMSe0Z9cTEx7/A2FK6raBXHsJVsbdGi ojDFWodMenHt6W5TET1hJMS8yKNGmYR4iRP/jMV/EQKniWuYVtJXdqzXVykpCA== X-Gm-Gg: ASbGncuT4VphxQJOUcueQA2f3zRB5YAybtNNH5N3W9hCki/LB8j0qd8GOEQaByx0GZc mu5FBIrddWohzAqYX+kh4P7ongv6rPwhdrMI53Lde+Oggh+pQvJtDXMo+GXB6Zlw6x07j8CQ2EU RUxdlPJakG2ZMUIkeMDQ1xTv4tlKnxCywsN0LGd/Te6RZV6lBHG3GcdgOXT3JkfdKszASNkwLM3 zW82uFC8zgcAFSOOT8269uSe4GRmda7LZe3gDIvO060smqKEIdyDp7Helt82RgTG9dLXw+hpMXr TcgrFvZdBItpITQ15KFTeXSl5NRd7kGN+hidlXcFZPYXxFkPKFLl05n9esN2u5q3ZYlriuxz0eN mB7AUaYWuNbkhfv0O/+grRiD/aSpxFPbabUa/l0dw/9ZjigpwcA6Wj1oiOfCqEo4H5ZexZtuidF RemvMp7YSSP2NvtiGeufi5xY5tJHvPCFG5tzsAp+zudcLcHUTJvFnsiGyweiN1gO29UaUy3mKiq 16ecU0dO8Wb4KU= X-Google-Smtp-Source: AGHT+IG5DeqOx1fZJDAsixbq/yvWgkLw5UM5QBEjJUqZsuAsbqC5OrpAWIcKXZaMtvJGBVpmjP4WFw== X-Received: by 2002:a17:907:3c8f:b0:b04:36bb:545 with SMTP id a640c23a62f3a-b34bb12fc3dmr2315971166b.48.1759226511867; Tue, 30 Sep 2025 03:01:51 -0700 (PDT) Received: from smtpclient.apple (2001-1c04-0681-7700-f0c6-582f-3a73-786e.cable.dynamic.v6.ziggo.nl. [2001:1c04:681:7700:f0c6:582f:3a73:786e]) by smtp.gmail.com with ESMTPSA id a640c23a62f3a-b353efa46b2sm1111803666b.24.2025.09.30.03.01.51 for (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Tue, 30 Sep 2025 03:01:51 -0700 (PDT) From: Frits Hoogland Content-Type: multipart/alternative; boundary="Apple-Mail=_9C7190F0-2588-47E7-A03E-E7406698B325" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3864.100.1.1.5\)) Subject: The ability of postgres to determine loss of files of the main fork Message-Id: <013D63E2-5D75-492E-85FF-1D5CC0148C82@gmail.com> Date: Tue, 30 Sep 2025 12:01:40 +0200 To: pgsql-hackers@lists.postgresql.org X-Mailer: Apple Mail (2.3864.100.1.1.5) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_9C7190F0-2588-47E7-A03E-E7406698B325 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=us-ascii Hi hackers, Postgres determines the availability of the main fork, the actual data = file, upon reading it. This is also what amcheck will detect. However, if a relation exceeds 1GB and has more than a single data file segment, there is the option of silent data loss. For example, if a = table consists of five segments and the third one goes missing, a sequential = scan will happily conclude that the table consists only of two segments and = won't report an error. Only an index scan that tries to return a row in the missing segment will report an error. Currently, this kind of data loss cannot be detected if you check the integrity of the table using verify_heapam(). Only if you manually use = the primary key index to obtain data from a missing segment, you will get an = error. Therefore, I would like to request an enhancement: add an option to verify_heapam() that causes the primary key index to be scanned and = makes sure that all line pointers in the index point to existing tuples. An alternative might be to track the number of segments of a relation in pg_class, but that may be difficult to make crash-safe. Frits Hoogland --Apple-Mail=_9C7190F0-2588-47E7-A03E-E7406698B325 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=us-ascii  Hi hackers,

Postgres determines the = availability of the main fork, the actual data = file,
upon reading it. This is also what = amcheck will detect.

However, if a = relation exceeds 1GB and has more than a single data = file
segment, there is the option of silent data loss. =  For example, if a table
consists of five segments and the third one goes = missing, a sequential scan
will happily conclude that the table consists only of two = segments and won't
report an error.  Only an index scan that tries to return a = row in the
missing segment will report an error.

Currently, this kind of data loss cannot be = detected if you check the
integrity of the table using verify_heapam().  Only = if you manually use the
primary key index to obtain data from a missing segment, = you will get an error.

Therefore, I would like to request an enhancement: = add an option to
verify_heapam() that causes the primary key index to be scanned = and makes
sure that all line pointers in the index point to existing = tuples.

An alternative = might be to track the number of segments of a relation in
pg_class, but = that may be difficult to make crash-safe.

Frits = Hoogland




= --Apple-Mail=_9C7190F0-2588-47E7-A03E-E7406698B325--