Team Ai
Datasetpublic

codekingpro/portable-devtools

sourceHugging Faceupdated 5mo agoView on Hugging Face
1likes15kdownloads
amcheck.html377 linesDownload Raw Back to html
1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>F.2. amcheck — tools to verify table and index consistency</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="adminpack.html" title="F.1. adminpack — pgAdmin support toolpack" /><link rel="next" href="auth-delay.html" title="F.3. auth_delay — pause on authentication failure" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">F.2. amcheck — tools to verify table and index consistency</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="adminpack.html" title="F.1. adminpack — pgAdmin support toolpack">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="contrib.html" title="Appendix F. Additional Supplied Modules and Extensions">Up</a></td><th width="60%" align="center">Appendix F. Additional Supplied Modules and Extensions</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="auth-delay.html" title="F.3. auth_delay — pause on authentication failure">Next</a></td></tr></table><hr /></div><div class="sect1" id="AMCHECK"><div class="titlepage"><div><div><h2 class="title" style="clear: both">F.2. amcheck — tools to verify table and index consistency <a href="#AMCHECK" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="amcheck.html#AMCHECK-FUNCTIONS">F.2.1. Functions</a></span></dt><dt><span class="sect2"><a href="amcheck.html#AMCHECK-OPTIONAL-HEAPALLINDEXED-VERIFICATION">F.2.2. Optional <em class="parameter"><code>heapallindexed</code></em> Verification</a></span></dt><dt><span class="sect2"><a href="amcheck.html#AMCHECK-USING-AMCHECK-EFFECTIVELY">F.2.3. Using <code class="filename">amcheck</code> Effectively</a></span></dt><dt><span class="sect2"><a href="amcheck.html#AMCHECK-REPAIRING-CORRUPTION">F.2.4. Repairing Corruption</a></span></dt></dl></div><a id="id-1.11.7.12.2" class="indexterm"></a><p>3  The <code class="filename">amcheck</code> module provides functions that allow you to4  verify the logical consistency of the structure of relations.5 </p><p>6  The B-Tree checking functions verify various <span class="emphasis"><em>invariants</em></span> in the7  structure of the representation of particular relations.  The8  correctness of the access method functions behind index scans and9  other important operations relies on these invariants always10  holding.  For example, certain functions verify, among other things,11  that all B-Tree pages have items in <span class="quote">“<span class="quote">logical</span>”</span> order (e.g.,12  for B-Tree indexes on <code class="type">text</code>, index tuples should be in13  collated lexical order).  If that particular invariant somehow fails14  to hold, we can expect binary searches on the affected page to15  incorrectly guide index scans, resulting in wrong answers to SQL16  queries.  If the structure appears to be valid, no error is raised.17 </p><p>18  Verification is performed using the same procedures as those used by19  index scans themselves, which may be user-defined operator class20  code.  For example, B-Tree index verification relies on comparisons21  made with one or more B-Tree support function 1 routines.  See <a class="xref" href="xindex.html#XINDEX-SUPPORT" title="38.16.3. Index Method Support Routines">Section 38.16.3</a> for details of operator class support22  functions.23 </p><p>24  Unlike the B-Tree checking functions which report corruption by raising25  errors, the heap checking function <code class="function">verify_heapam</code> checks26  a table and attempts to return a set of rows, one row per corruption27  detected.  Despite this, if facilities that28  <code class="function">verify_heapam</code> relies upon are themselves corrupted, the29  function may be unable to continue and may instead raise an error.30 </p><p>31  Permission to execute <code class="filename">amcheck</code> functions may be granted32  to non-superusers, but before granting such permissions careful consideration33  should be given to data security and privacy concerns.  Although the34  corruption reports generated by these functions do not focus on the contents35  of the corrupted data so much as on the structure of that data and the nature36  of the corruptions found, an attacker who gains permission to execute these37  functions, particularly if the attacker can also induce corruption, might be38  able to infer something of the data itself from such messages.39 </p><div class="sect2" id="AMCHECK-FUNCTIONS"><div class="titlepage"><div><div><h3 class="title">F.2.1. Functions <a href="#AMCHECK-FUNCTIONS" class="id_link">#</a></h3></div></div></div><div class="variablelist"><dl class="variablelist"><dt><span class="term">40     <code class="function">bt_index_check(index regclass, heapallindexed boolean) returns void</code>41     <a id="id-1.11.7.12.8.2.1.1.2" class="indexterm"></a>42    </span></dt><dd><p>43      <code class="function">bt_index_check</code> tests that its target, a44      B-Tree index, respects a variety of invariants.  Example usage:45</p><pre class="screen">46test=# SELECT bt_index_check(index =&gt; c.oid, heapallindexed =&gt; i.indisunique),47               c.relname,48               c.relpages49FROM pg_index i50JOIN pg_opclass op ON i.indclass[0] = op.oid51JOIN pg_am am ON op.opcmethod = am.oid52JOIN pg_class c ON i.indexrelid = c.oid53JOIN pg_namespace n ON c.relnamespace = n.oid54WHERE am.amname = 'btree' AND n.nspname = 'pg_catalog'55-- Don't check temp tables, which may be from another session:56AND c.relpersistence != 't'57-- Function may throw an error when this is omitted:58AND c.relkind = 'i' AND i.indisready AND i.indisvalid59ORDER BY c.relpages DESC LIMIT 10;60 bt_index_check |             relname             | relpages61----------------+---------------------------------+----------62                | pg_depend_reference_index       |       4363                | pg_depend_depender_index        |       4064                | pg_proc_proname_args_nsp_index  |       3165                | pg_description_o_c_o_index      |       2166                | pg_attribute_relid_attnam_index |       1467                | pg_proc_oid_index               |       1068                | pg_attribute_relid_attnum_index |        969                | pg_amproc_fam_proc_index        |        570                | pg_amop_opr_fam_index           |        571                | pg_amop_fam_strat_index         |        572(10 rows)73</pre><p>74      This example shows a session that performs verification of the75      10 largest catalog indexes in the database <span class="quote">“<span class="quote">test</span>”</span>.76      Verification of the presence of heap tuples as index tuples is77      requested for the subset that are unique indexes.  Since no78      error is raised, all indexes tested appear to be logically79      consistent.  Naturally, this query could easily be changed to80      call <code class="function">bt_index_check</code> for every index in the81      database where verification is supported.82     </p><p>83      <code class="function">bt_index_check</code> acquires an <code class="literal">AccessShareLock</code>84      on the target index and the heap relation it belongs to. This lock mode85      is the same lock mode acquired on relations by simple86      <code class="literal">SELECT</code> statements.87      <code class="function">bt_index_check</code> does not verify invariants88      that span child/parent relationships, but will verify the89      presence of all heap tuples as index tuples within the index90      when <em class="parameter"><code>heapallindexed</code></em> is91      <code class="literal">true</code>.  When a routine, lightweight test for92      corruption is required in a live production environment, using93      <code class="function">bt_index_check</code> often provides the best94      trade-off between thoroughness of verification and limiting the95      impact on application performance and availability.96     </p></dd><dt><span class="term">97     <code class="function">bt_index_parent_check(index regclass, heapallindexed boolean, rootdescend boolean) returns void</code>98     <a id="id-1.11.7.12.8.2.2.1.2" class="indexterm"></a>99    </span></dt><dd><p>100      <code class="function">bt_index_parent_check</code> tests that its101      target, a B-Tree index, respects a variety of invariants.102      Optionally, when the <em class="parameter"><code>heapallindexed</code></em>103      argument is <code class="literal">true</code>, the function verifies the104      presence of all heap tuples that should be found within the105      index.  When the optional <em class="parameter"><code>rootdescend</code></em>106      argument is <code class="literal">true</code>, verification re-finds107      tuples on the leaf level by performing a new search from the108      root page for each tuple.  The checks that can be performed by109      <code class="function">bt_index_parent_check</code> are a superset of the110      checks that can be performed by <code class="function">bt_index_check</code>.111      <code class="function">bt_index_parent_check</code> can be thought of as112      a more thorough variant of <code class="function">bt_index_check</code>:113      unlike <code class="function">bt_index_check</code>,114      <code class="function">bt_index_parent_check</code> also checks115      invariants that span parent/child relationships, including checking116      that there are no missing downlinks in the index structure.117      <code class="function">bt_index_parent_check</code> follows the general118      convention of raising an error if it finds a logical119      inconsistency or other problem.120     </p><p>121      A <code class="literal">ShareLock</code> is required on the target index by122      <code class="function">bt_index_parent_check</code> (a123      <code class="literal">ShareLock</code> is also acquired on the heap relation).124      These locks prevent concurrent data modification from125      <code class="command">INSERT</code>, <code class="command">UPDATE</code>, and <code class="command">DELETE</code>126      commands.  The locks also prevent the underlying relation from127      being concurrently processed by <code class="command">VACUUM</code>, as well as128      all other utility commands.  Note that the function holds locks129      only while running, not for the entire transaction.130     </p><p>131      <code class="function">bt_index_parent_check</code>'s additional132      verification is more likely to detect various pathological133      cases.  These cases may involve an incorrectly implemented134      B-Tree operator class used by the index that is checked, or,135      hypothetically, undiscovered bugs in the underlying B-Tree index136      access method code.  Note that137      <code class="function">bt_index_parent_check</code> cannot be used when138      hot standby mode is enabled (i.e., on read-only physical139      replicas), unlike <code class="function">bt_index_check</code>.140     </p></dd></dl></div><div class="tip"><h3 class="title">Tip</h3><p>141    <code class="function">bt_index_check</code> and142    <code class="function">bt_index_parent_check</code> both output log143    messages about the verification process at144    <code class="literal">DEBUG1</code> and <code class="literal">DEBUG2</code> severity145    levels.  These messages provide detailed information about the146    verification process that may be of interest to147    <span class="productname">PostgreSQL</span> developers.  Advanced users148    may also find this information helpful, since it provides149    additional context should verification actually detect an150    inconsistency.  Running:151</p><pre class="programlisting">152SET client_min_messages = DEBUG1;153</pre><p>154    in an interactive <span class="application">psql</span> session before155    running a verification query will display messages about the156    progress of verification with a manageable level of detail.157   </p></div><div class="variablelist"><dl class="variablelist"><dt><span class="term">158     <code class="function">159      verify_heapam(relation regclass,160                    on_error_stop boolean,161                    check_toast boolean,162                    skip text,163                    startblock bigint,164                    endblock bigint,165                    blkno OUT bigint,166                    offnum OUT integer,167                    attnum OUT integer,168                    msg OUT text)169      returns setof record170     </code>171    </span></dt><dd><p>172      Checks a table, sequence, or materialized view for structural corruption,173      where pages in the relation contain data that is invalidly formatted, and174      for logical corruption, where pages are structurally valid but175      inconsistent with the rest of the database cluster.176     </p><p>177      The following optional arguments are recognized:178     </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">on_error_stop</code></span></dt><dd><p>179         If true, corruption checking stops at the end of the first block in180         which any corruptions are found.181        </p><p>182         Defaults to false.183        </p></dd><dt><span class="term"><code class="literal">check_toast</code></span></dt><dd><p>184         If true, toasted values are checked against the target relation's185         TOAST table.186        </p><p>187         This option is known to be slow.  Also, if the toast table or its188         index is corrupt, checking it against toast values could conceivably189         crash the server, although in many cases this would just produce an190         error.191        </p><p>192         Defaults to false.193        </p></dd><dt><span class="term"><code class="literal">skip</code></span></dt><dd><p>194         If not <code class="literal">none</code>, corruption checking skips blocks that195         are marked as all-visible or all-frozen, as specified.196         Valid options are <code class="literal">all-visible</code>,197         <code class="literal">all-frozen</code> and <code class="literal">none</code>.198        </p><p>199         Defaults to <code class="literal">none</code>.200        </p></dd><dt><span class="term"><code class="literal">startblock</code></span></dt><dd><p>201         If specified, corruption checking begins at the specified block,202         skipping all previous blocks.  It is an error to specify a203         <em class="parameter"><code>startblock</code></em> outside the range of blocks in the204         target table.205        </p><p>206         By default, checking begins at the first block.207        </p></dd><dt><span class="term"><code class="literal">endblock</code></span></dt><dd><p>208         If specified, corruption checking ends at the specified block,209         skipping all remaining blocks.  It is an error to specify an210         <em class="parameter"><code>endblock</code></em> outside the range of blocks in the target211         table.212        </p><p>213         By default, all blocks are checked.214        </p></dd></dl></div><p>215      For each corruption detected, <code class="function">verify_heapam</code> returns216      a row with the following columns:217     </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">blkno</code></span></dt><dd><p>218         The number of the block containing the corrupt page.219        </p></dd><dt><span class="term"><code class="literal">offnum</code></span></dt><dd><p>220         The OffsetNumber of the corrupt tuple.221        </p></dd><dt><span class="term"><code class="literal">attnum</code></span></dt><dd><p>222         The attribute number of the corrupt column in the tuple, if the223         corruption is specific to a column and not the tuple as a whole.224        </p></dd><dt><span class="term"><code class="literal">msg</code></span></dt><dd><p>225         A message describing the problem detected.226        </p></dd></dl></div></dd></dl></div></div><div class="sect2" id="AMCHECK-OPTIONAL-HEAPALLINDEXED-VERIFICATION"><div class="titlepage"><div><div><h3 class="title">F.2.2. Optional <em class="parameter"><code>heapallindexed</code></em> Verification <a href="#AMCHECK-OPTIONAL-HEAPALLINDEXED-VERIFICATION" class="id_link">#</a></h3></div></div></div><p>227  When the <em class="parameter"><code>heapallindexed</code></em> argument to B-Tree228  verification functions is <code class="literal">true</code>, an additional229  phase of verification is performed against the table associated with230  the target index relation.  This consists of a <span class="quote">“<span class="quote">dummy</span>”</span>231  <code class="command">CREATE INDEX</code> operation, which checks for the232  presence of all hypothetical new index tuples against a temporary,233  in-memory summarizing structure (this is built when needed during234  the basic first phase of verification).  The summarizing structure235  <span class="quote">“<span class="quote">fingerprints</span>”</span> every tuple found within the target236  index.  The high level principle behind237  <em class="parameter"><code>heapallindexed</code></em> verification is that a new238  index that is equivalent to the existing, target index must only239  have entries that can be found in the existing structure.240 </p><p>241  The additional <em class="parameter"><code>heapallindexed</code></em> phase adds242  significant overhead: verification will typically take several times243  longer.  However, there is no change to the relation-level locks244  acquired when <em class="parameter"><code>heapallindexed</code></em> verification is245  performed.246 </p><p>247  The summarizing structure is bound in size by248  <code class="varname">maintenance_work_mem</code>.  In order to ensure that249  there is no more than a 2% probability of failure to detect an250  inconsistency for each heap tuple that should be represented in the251  index, approximately 2 bytes of memory are needed per tuple.  As252  less memory is made available per tuple, the probability of missing253  an inconsistency slowly increases.  This approach limits the254  overhead of verification significantly, while only slightly reducing255  the probability of detecting a problem, especially for installations256  where verification is treated as a routine maintenance task.  Any257  single absent or malformed tuple has a new opportunity to be258  detected with each new verification attempt.259 </p></div><div class="sect2" id="AMCHECK-USING-AMCHECK-EFFECTIVELY"><div class="titlepage"><div><div><h3 class="title">F.2.3. Using <code class="filename">amcheck</code> Effectively <a href="#AMCHECK-USING-AMCHECK-EFFECTIVELY" class="id_link">#</a></h3></div></div></div><p>260  <code class="filename">amcheck</code> can be effective at detecting various types of261  failure modes that <a class="link" href="app-initdb.html#APP-INITDB-DATA-CHECKSUMS"><span class="application">data262  checksums</span></a> will fail to catch.  These include:263 264  </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>265     Structural inconsistencies caused by incorrect operator class266     implementations.267    </p><p>268     This includes issues caused by the comparison rules of operating269     system collations changing. Comparisons of datums of a collatable270     type like <code class="type">text</code> must be immutable (just as all271     comparisons used for B-Tree index scans must be immutable), which272     implies that operating system collation rules must never change.273     Though rare, updates to operating system collation rules can274     cause these issues. More commonly, an inconsistency in the275     collation order between a primary server and a standby server is276     implicated, possibly because the <span class="emphasis"><em>major</em></span> operating277     system version in use is inconsistent.  Such inconsistencies will278     generally only arise on standby servers, and so can generally279     only be detected on standby servers.280    </p><p>281     If a problem like this arises, it may not affect each individual282     index that is ordered using an affected collation, simply because283     <span class="emphasis"><em>indexed</em></span> values might happen to have the same284     absolute ordering regardless of the behavioral inconsistency. See285     <a class="xref" href="locale.html" title="24.1. Locale Support">Section 24.1</a> and <a class="xref" href="collation.html" title="24.2. Collation Support">Section 24.2</a> for286     further details about how <span class="productname">PostgreSQL</span> uses287     operating system locales and collations.288    </p></li><li class="listitem"><p>289     Structural inconsistencies between indexes and the heap relations290     that are indexed (when <em class="parameter"><code>heapallindexed</code></em>291     verification is performed).292    </p><p>293     There is no cross-checking of indexes against their heap relation294     during normal operation.  Symptoms of heap corruption can be subtle.295    </p></li><li class="listitem"><p>296     Corruption caused by hypothetical undiscovered bugs in the297     underlying <span class="productname">PostgreSQL</span> access method298     code, sort code, or transaction management code.299    </p><p>300     Automatic verification of the structural integrity of indexes301     plays a role in the general testing of new or proposed302     <span class="productname">PostgreSQL</span> features that could plausibly allow a303     logical inconsistency to be introduced.  Verification of table304     structure and associated visibility and transaction status305     information plays a similar role.  One obvious testing strategy306     is to call <code class="filename">amcheck</code> functions continuously307     when running the standard regression tests.  See <a class="xref" href="regress-run.html" title="33.1. Running the Tests">Section 33.1</a> for details on running the tests.308    </p></li><li class="listitem"><p>309     File system or storage subsystem faults where checksums happen to310     simply not be enabled.311    </p><p>312     Note that <code class="filename">amcheck</code> examines a page as represented in some313     shared memory buffer at the time of verification if there is only a314     shared buffer hit when accessing the block. Consequently,315     <code class="filename">amcheck</code> does not necessarily examine data read from the316     file system at the time of verification. Note that when checksums are317     enabled, <code class="filename">amcheck</code> may raise an error due to a checksum318     failure when a corrupt block is read into a buffer.319    </p></li><li class="listitem"><p>320     Corruption caused by faulty RAM, or the broader memory subsystem.321    </p><p>322     <span class="productname">PostgreSQL</span> does not protect against correctable323     memory errors and it is assumed you will operate using RAM that324     uses industry standard Error Correcting Codes (ECC) or better325     protection.  However, ECC memory is typically only immune to326     single-bit errors, and should not be assumed to provide327     <span class="emphasis"><em>absolute</em></span> protection against failures that328     result in memory corruption.329    </p><p>330     When <em class="parameter"><code>heapallindexed</code></em> verification is331     performed, there is generally a greatly increased chance of332     detecting single-bit errors, since strict binary equality is333     tested, and the indexed attributes within the heap are tested.334    </p></li></ul></div><p>335 </p><p>336  Structural corruption can happen due to faulty storage hardware, or337  relation files being overwritten or modified by unrelated software.338  This kind of corruption can also be detected with339  <a class="link" href="checksums.html" title="30.2. Data Checksums"><span class="application">data page340  checksums</span></a>.341 </p><p>342  Relation pages which are correctly formatted, internally consistent, and343  correct relative to their own internal checksums may still contain344  logical corruption.  As such, this kind of corruption cannot be detected345  with <span class="application">checksums</span>.  Examples include toasted346  values in the main table which lack a corresponding entry in the toast347  table, and tuples in the main table with a Transaction ID that is older348  than the oldest valid Transaction ID in the database or cluster.349 </p><p>350  Multiple causes of logical corruption have been observed in production351  systems, including bugs in the <span class="productname">PostgreSQL</span>352  server software, faulty and ill-conceived backup and restore tools, and353  user error.354 </p><p>355  Corrupt relations are most concerning in live production environments,356  precisely the same environments where high risk activities are least357  welcome.  For this reason, <code class="function">verify_heapam</code> has been358  designed to diagnose corruption without undue risk.  It cannot guard359  against all causes of backend crashes, as even executing the calling360  query could be unsafe on a badly corrupted system.   Access to <a class="link" href="catalogs-overview.html" title="53.1. Overview">catalog tables</a> is performed and could361  be problematic if the catalogs themselves are corrupted.362 </p><p>363  In general, <code class="filename">amcheck</code> can only prove the presence of364  corruption; it cannot prove its absence.365 </p></div><div class="sect2" id="AMCHECK-REPAIRING-CORRUPTION"><div class="titlepage"><div><div><h3 class="title">F.2.4. Repairing Corruption <a href="#AMCHECK-REPAIRING-CORRUPTION" class="id_link">#</a></h3></div></div></div><p>366  No error concerning corruption raised by <code class="filename">amcheck</code> should367  ever be a false positive.  <code class="filename">amcheck</code> raises368  errors in the event of conditions that, by definition, should never369  happen, and so careful analysis of <code class="filename">amcheck</code>370  errors is often required.371 </p><p>372  There is no general method of repairing problems that373  <code class="filename">amcheck</code> detects.  An explanation for the root cause of374  an invariant violation should be sought.  <a class="xref" href="pageinspect.html" title="F.25. pageinspect — low-level inspection of database pages">pageinspect</a> may play a useful role in diagnosing375  corruption that <code class="filename">amcheck</code> detects.  A <code class="command">REINDEX</code>376  may not be effective in repairing corruption.377 </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="adminpack.html" title="F.1. adminpack — pgAdmin support toolpack">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="contrib.html" title="Appendix F. Additional Supplied Modules and Extensions">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="auth-delay.html" title="F.3. auth_delay — pause on authentication failure">Next</a></td></tr><tr><td width="40%" align="left" valign="top">F.1. adminpack — pgAdmin support toolpack </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> F.3. auth_delay — pause on authentication failure</td></tr></table></div></body></html>
codekingpro/portable-devtools · Team Ai