codekingpro/portable-devtools
115k
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 => c.oid, heapallindexed => 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>