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.1. 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="contrib.html" title="Appendix F. Additional Supplied Modules and Extensions" /><link rel="next" href="auth-delay.html" title="F.2. 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.1. amcheck — tools to verify table and index consistency</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="contrib.html" title="Appendix F. Additional Supplied Modules and Extensions">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 18.0 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="auth-delay.html" title="F.2. 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.1. 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.1.1. Functions</a></span></dt><dt><span class="sect2"><a href="amcheck.html#AMCHECK-OPTIONAL-HEAPALLINDEXED-VERIFICATION">F.1.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.1.3. Using <code class="filename">amcheck</code> Effectively</a></span></dt><dt><span class="sect2"><a href="amcheck.html#AMCHECK-REPAIRING-CORRUPTION">F.1.4. Repairing Corruption</a></span></dt></dl></div><a id="id-1.11.7.11.2" class="indexterm"></a><p>
3 The <code class="filename">amcheck</code> module provides functions that allow you to
4 verify the logical consistency of the structure of relations.
6 The B-Tree checking functions verify various <span class="emphasis"><em>invariants</em></span> in the
7 structure of the representation of particular relations. The
8 correctness of the access method functions behind index scans and
9 other important operations relies on these invariants always
10 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 in
13 collated lexical order). If that particular invariant somehow fails
14 to hold, we can expect binary searches on the affected page to
15 incorrectly guide index scans, resulting in wrong answers to SQL
16 queries. If the structure appears to be valid, no error is raised.
17 While these checking functions are run, the <a class="xref" href="runtime-config-client.html#GUC-SEARCH-PATH">search_path</a> is temporarily changed to <code class="literal">pg_catalog,
20 Verification is performed using the same procedures as those used by
21 index scans themselves, which may be user-defined operator class
22 code. For example, B-Tree index verification relies on comparisons
23 made with one or more B-Tree support function 1 routines. See <a class="xref" href="xindex.html#XINDEX-SUPPORT" title="36.16.3. Index Method Support Routines">Section 36.16.3</a> for details of operator class support
26 Unlike the B-Tree checking functions which report corruption by raising
27 errors, the heap checking function <code class="function">verify_heapam</code> checks
28 a table and attempts to return a set of rows, one row per corruption
29 detected. Despite this, if facilities that
30 <code class="function">verify_heapam</code> relies upon are themselves corrupted, the
31 function may be unable to continue and may instead raise an error.
33 Permission to execute <code class="filename">amcheck</code> functions may be granted
34 to non-superusers, but before granting such permissions careful consideration
35 should be given to data security and privacy concerns. Although the
36 corruption reports generated by these functions do not focus on the contents
37 of the corrupted data so much as on the structure of that data and the nature
38 of the corruptions found, an attacker who gains permission to execute these
39 functions, particularly if the attacker can also induce corruption, might be
40 able to infer something of the data itself from such messages.
41 </p><div class="sect2" id="AMCHECK-FUNCTIONS"><div class="titlepage"><div><div><h3 class="title">F.1.1. Functions <a href="#AMCHECK-FUNCTIONS" class="id_link">#</a></h3></div></div></div><div class="variablelist"><dl class="variablelist"><dt><span class="term">
42 <code class="function">bt_index_check(index regclass, heapallindexed boolean, checkunique boolean) returns void</code>
43 <a id="id-1.11.7.11.8.2.1.1.2" class="indexterm"></a>
45 <code class="function">bt_index_check</code> tests that its target, a
46 B-Tree index, respects a variety of invariants. Example usage:
47 </p><pre class="screen">
48 test=# SELECT bt_index_check(index => c.oid, heapallindexed => i.indisunique),
52 JOIN pg_opclass op ON i.indclass[0] = op.oid
53 JOIN pg_am am ON op.opcmethod = am.oid
54 JOIN pg_class c ON i.indexrelid = c.oid
55 JOIN pg_namespace n ON c.relnamespace = n.oid
56 WHERE am.amname = 'btree' AND n.nspname = 'pg_catalog'
57 -- Don't check temp tables, which may be from another session:
58 AND c.relpersistence != 't'
59 -- Function may throw an error when this is omitted:
60 AND c.relkind = 'i' AND i.indisready AND i.indisvalid
61 ORDER BY c.relpages DESC LIMIT 10;
62 bt_index_check | relname | relpages
63 ----------------+---------------------------------+----------
64 | pg_depend_reference_index | 43
65 | pg_depend_depender_index | 40
66 | pg_proc_proname_args_nsp_index | 31
67 | pg_description_o_c_o_index | 21
68 | pg_attribute_relid_attnam_index | 14
69 | pg_proc_oid_index | 10
70 | pg_attribute_relid_attnum_index | 9
71 | pg_amproc_fam_proc_index | 5
72 | pg_amop_opr_fam_index | 5
73 | pg_amop_fam_strat_index | 5
76 This example shows a session that performs verification of the
77 10 largest catalog indexes in the database <span class="quote">“<span class="quote">test</span>”</span>.
78 Verification of the presence of heap tuples as index tuples is
79 requested for the subset that are unique indexes. Since no
80 error is raised, all indexes tested appear to be logically
81 consistent. Naturally, this query could easily be changed to
82 call <code class="function">bt_index_check</code> for every index in the
83 database where verification is supported.
85 <code class="function">bt_index_check</code> acquires an <code class="literal">AccessShareLock</code>
86 on the target index and the heap relation it belongs to. This lock mode
87 is the same lock mode acquired on relations by simple
88 <code class="literal">SELECT</code> statements.
89 <code class="function">bt_index_check</code> does not verify invariants
90 that span child/parent relationships, but will verify the
91 presence of all heap tuples as index tuples within the index
92 when <em class="parameter"><code>heapallindexed</code></em> is
93 <code class="literal">true</code>. When <em class="parameter"><code>checkunique</code></em>
94 is <code class="literal">true</code> <code class="function">bt_index_check</code> will
95 check that no more than one among duplicate entries in unique
96 index is visible. When a routine, lightweight test for
97 corruption is required in a live production environment, using
98 <code class="function">bt_index_check</code> often provides the best
99 trade-off between thoroughness of verification and limiting the
100 impact on application performance and availability.
101 </p></dd><dt><span class="term">
102 <code class="function">bt_index_parent_check(index regclass, heapallindexed boolean, rootdescend boolean, checkunique boolean) returns void</code>
103 <a id="id-1.11.7.11.8.2.2.1.2" class="indexterm"></a>
105 <code class="function">bt_index_parent_check</code> tests that its
106 target, a B-Tree index, respects a variety of invariants.
107 Optionally, when the <em class="parameter"><code>heapallindexed</code></em>
108 argument is <code class="literal">true</code>, the function verifies the
109 presence of all heap tuples that should be found within the
110 index. When <em class="parameter"><code>checkunique</code></em>
111 is <code class="literal">true</code> <code class="function">bt_index_parent_check</code> will
112 check that no more than one among duplicate entries in unique
113 index is visible. When the optional <em class="parameter"><code>rootdescend</code></em>
114 argument is <code class="literal">true</code>, verification re-finds
115 tuples on the leaf level by performing a new search from the
116 root page for each tuple. The checks that can be performed by
117 <code class="function">bt_index_parent_check</code> are a superset of the
118 checks that can be performed by <code class="function">bt_index_check</code>.
119 <code class="function">bt_index_parent_check</code> can be thought of as
120 a more thorough variant of <code class="function">bt_index_check</code>:
121 unlike <code class="function">bt_index_check</code>,
122 <code class="function">bt_index_parent_check</code> also checks
123 invariants that span parent/child relationships, including checking
124 that there are no missing downlinks in the index structure.
125 <code class="function">bt_index_parent_check</code> follows the general
126 convention of raising an error if it finds a logical
127 inconsistency or other problem.
129 A <code class="literal">ShareLock</code> is required on the target index by
130 <code class="function">bt_index_parent_check</code> (a
131 <code class="literal">ShareLock</code> is also acquired on the heap relation).
132 These locks prevent concurrent data modification from
133 <code class="command">INSERT</code>, <code class="command">UPDATE</code>, and <code class="command">DELETE</code>
134 commands. The locks also prevent the underlying relation from
135 being concurrently processed by <code class="command">VACUUM</code>, as well as
136 all other utility commands. Note that the function holds locks
137 only while running, not for the entire transaction.
139 <code class="function">bt_index_parent_check</code>'s additional
140 verification is more likely to detect various pathological
141 cases. These cases may involve an incorrectly implemented
142 B-Tree operator class used by the index that is checked, or,
143 hypothetically, undiscovered bugs in the underlying B-Tree index
144 access method code. Note that
145 <code class="function">bt_index_parent_check</code> cannot be used when
146 hot standby mode is enabled (i.e., on read-only physical
147 replicas), unlike <code class="function">bt_index_check</code>.
148 </p></dd><dt><span class="term">
149 <code class="function">gin_index_check(index regclass) returns void</code>
150 <a id="id-1.11.7.11.8.2.3.1.2" class="indexterm"></a>
152 <code class="function">gin_index_check</code> tests that its target GIN index
153 has consistent parent-child tuples relations (no parent tuples
154 require tuple adjustment) and page graph respects balanced-tree
155 invariants (internal pages reference only leaf page or only internal
157 </p></dd></dl></div><div class="tip"><h3 class="title">Tip</h3><p>
158 <code class="function">bt_index_check</code> and
159 <code class="function">bt_index_parent_check</code> both output log
160 messages about the verification process at
161 <code class="literal">DEBUG1</code> and <code class="literal">DEBUG2</code> severity
162 levels. These messages provide detailed information about the
163 verification process that may be of interest to
164 <span class="productname">PostgreSQL</span> developers. Advanced users
165 may also find this information helpful, since it provides
166 additional context should verification actually detect an
167 inconsistency. Running:
168 </p><pre class="programlisting">
169 SET client_min_messages = DEBUG1;
171 in an interactive <span class="application">psql</span> session before
172 running a verification query will display messages about the
173 progress of verification with a manageable level of detail.
174 </p></div><div class="variablelist"><dl class="variablelist"><dt><span class="term">
175 <code class="function">
176 verify_heapam(relation regclass,
177 on_error_stop boolean,
189 Checks a table, sequence, or materialized view for structural corruption,
190 where pages in the relation contain data that is invalidly formatted, and
191 for logical corruption, where pages are structurally valid but
192 inconsistent with the rest of the database cluster.
194 The following optional arguments are recognized:
195 </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">on_error_stop</code></span></dt><dd><p>
196 If true, corruption checking stops at the end of the first block in
197 which any corruptions are found.
200 </p></dd><dt><span class="term"><code class="literal">check_toast</code></span></dt><dd><p>
201 If true, toasted values are checked against the target relation's
204 This option is known to be slow. Also, if the toast table or its
205 index is corrupt, checking it against toast values could conceivably
206 crash the server, although in many cases this would just produce an
210 </p></dd><dt><span class="term"><code class="literal">skip</code></span></dt><dd><p>
211 If not <code class="literal">none</code>, corruption checking skips blocks that
212 are marked as all-visible or all-frozen, as specified.
213 Valid options are <code class="literal">all-visible</code>,
214 <code class="literal">all-frozen</code> and <code class="literal">none</code>.
216 Defaults to <code class="literal">none</code>.
217 </p></dd><dt><span class="term"><code class="literal">startblock</code></span></dt><dd><p>
218 If specified, corruption checking begins at the specified block,
219 skipping all previous blocks. It is an error to specify a
220 <em class="parameter"><code>startblock</code></em> outside the range of blocks in the
223 By default, checking begins at the first block.
224 </p></dd><dt><span class="term"><code class="literal">endblock</code></span></dt><dd><p>
225 If specified, corruption checking ends at the specified block,
226 skipping all remaining blocks. It is an error to specify an
227 <em class="parameter"><code>endblock</code></em> outside the range of blocks in the target
230 By default, all blocks are checked.
231 </p></dd></dl></div><p>
232 For each corruption detected, <code class="function">verify_heapam</code> returns
233 a row with the following columns:
234 </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">blkno</code></span></dt><dd><p>
235 The number of the block containing the corrupt page.
236 </p></dd><dt><span class="term"><code class="literal">offnum</code></span></dt><dd><p>
237 The OffsetNumber of the corrupt tuple.
238 </p></dd><dt><span class="term"><code class="literal">attnum</code></span></dt><dd><p>
239 The attribute number of the corrupt column in the tuple, if the
240 corruption is specific to a column and not the tuple as a whole.
241 </p></dd><dt><span class="term"><code class="literal">msg</code></span></dt><dd><p>
242 A message describing the problem detected.
243 </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.1.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>
244 When the <em class="parameter"><code>heapallindexed</code></em> argument to B-Tree
245 verification functions is <code class="literal">true</code>, an additional
246 phase of verification is performed against the table associated with
247 the target index relation. This consists of a <span class="quote">“<span class="quote">dummy</span>”</span>
248 <code class="command">CREATE INDEX</code> operation, which checks for the
249 presence of all hypothetical new index tuples against a temporary,
250 in-memory summarizing structure (this is built when needed during
251 the basic first phase of verification). The summarizing structure
252 <span class="quote">“<span class="quote">fingerprints</span>”</span> every tuple found within the target
253 index. The high level principle behind
254 <em class="parameter"><code>heapallindexed</code></em> verification is that a new
255 index that is equivalent to the existing, target index must only
256 have entries that can be found in the existing structure.
258 The additional <em class="parameter"><code>heapallindexed</code></em> phase adds
259 significant overhead: verification will typically take several times
260 longer. However, there is no change to the relation-level locks
261 acquired when <em class="parameter"><code>heapallindexed</code></em> verification is
264 The summarizing structure is bound in size by
265 <code class="varname">maintenance_work_mem</code>. In order to ensure that
266 there is no more than a 2% probability of failure to detect an
267 inconsistency for each heap tuple that should be represented in the
268 index, approximately 2 bytes of memory are needed per tuple. As
269 less memory is made available per tuple, the probability of missing
270 an inconsistency slowly increases. This approach limits the
271 overhead of verification significantly, while only slightly reducing
272 the probability of detecting a problem, especially for installations
273 where verification is treated as a routine maintenance task. Any
274 single absent or malformed tuple has a new opportunity to be
275 detected with each new verification attempt.
276 </p></div><div class="sect2" id="AMCHECK-USING-AMCHECK-EFFECTIVELY"><div class="titlepage"><div><div><h3 class="title">F.1.3. Using <code class="filename">amcheck</code> Effectively <a href="#AMCHECK-USING-AMCHECK-EFFECTIVELY" class="id_link">#</a></h3></div></div></div><p>
277 <code class="filename">amcheck</code> can be effective at detecting various types of
278 failure modes that <a class="link" href="app-initdb.html#APP-INITDB-DATA-CHECKSUMS"><span class="application">data
279 checksums</span></a> will fail to catch. These include:
281 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
282 Structural inconsistencies caused by incorrect operator class
285 This includes issues caused by the comparison rules of operating
286 system collations changing. Comparisons of datums of a collatable
287 type like <code class="type">text</code> must be immutable (just as all
288 comparisons used for B-Tree index scans must be immutable), which
289 implies that operating system collation rules must never change.
290 Though rare, updates to operating system collation rules can
291 cause these issues. More commonly, an inconsistency in the
292 collation order between a primary server and a standby server is
293 implicated, possibly because the <span class="emphasis"><em>major</em></span> operating
294 system version in use is inconsistent. Such inconsistencies will
295 generally only arise on standby servers, and so can generally
296 only be detected on standby servers.
298 If a problem like this arises, it may not affect each individual
299 index that is ordered using an affected collation, simply because
300 <span class="emphasis"><em>indexed</em></span> values might happen to have the same
301 absolute ordering regardless of the behavioral inconsistency. See
302 <a class="xref" href="locale.html" title="23.1. Locale Support">Section 23.1</a> and <a class="xref" href="collation.html" title="23.2. Collation Support">Section 23.2</a> for
303 further details about how <span class="productname">PostgreSQL</span> uses
304 operating system locales and collations.
305 </p></li><li class="listitem"><p>
306 Structural inconsistencies between indexes and the heap relations
307 that are indexed (when <em class="parameter"><code>heapallindexed</code></em>
308 verification is performed).
310 There is no cross-checking of indexes against their heap relation
311 during normal operation. Symptoms of heap corruption can be subtle.
312 </p></li><li class="listitem"><p>
313 Corruption caused by hypothetical undiscovered bugs in the
314 underlying <span class="productname">PostgreSQL</span> access method
315 code, sort code, or transaction management code.
317 Automatic verification of the structural integrity of indexes
318 plays a role in the general testing of new or proposed
319 <span class="productname">PostgreSQL</span> features that could plausibly allow a
320 logical inconsistency to be introduced. Verification of table
321 structure and associated visibility and transaction status
322 information plays a similar role. One obvious testing strategy
323 is to call <code class="filename">amcheck</code> functions continuously
324 when running the standard regression tests. See <a class="xref" href="regress-run.html" title="31.1. Running the Tests">Section 31.1</a> for details on running the tests.
325 </p></li><li class="listitem"><p>
326 File system or storage subsystem faults when data checksums are
329 Note that <code class="filename">amcheck</code> examines a page as represented in some
330 shared memory buffer at the time of verification if there is only a
331 shared buffer hit when accessing the block. Consequently,
332 <code class="filename">amcheck</code> does not necessarily examine data read from the
333 file system at the time of verification. Note that when checksums are
334 enabled, <code class="filename">amcheck</code> may raise an error due to a checksum
335 failure when a corrupt block is read into a buffer.
336 </p></li><li class="listitem"><p>
337 Corruption caused by faulty RAM, or the broader memory subsystem.
339 <span class="productname">PostgreSQL</span> does not protect against correctable
340 memory errors and it is assumed you will operate using RAM that
341 uses industry standard Error Correcting Codes (ECC) or better
342 protection. However, ECC memory is typically only immune to
343 single-bit errors, and should not be assumed to provide
344 <span class="emphasis"><em>absolute</em></span> protection against failures that
345 result in memory corruption.
347 When <em class="parameter"><code>heapallindexed</code></em> verification is
348 performed, there is generally a greatly increased chance of
349 detecting single-bit errors, since strict binary equality is
350 tested, and the indexed attributes within the heap are tested.
351 </p></li></ul></div><p>
353 Structural corruption can happen due to faulty storage hardware, or
354 relation files being overwritten or modified by unrelated software.
355 This kind of corruption can also be detected with
356 <a class="link" href="checksums.html" title="28.2. Data Checksums"><span class="application">data page
357 checksums</span></a>.
359 Relation pages which are correctly formatted, internally consistent, and
360 correct relative to their own internal checksums may still contain
361 logical corruption. As such, this kind of corruption cannot be detected
362 with <span class="application">checksums</span>. Examples include toasted
363 values in the main table which lack a corresponding entry in the toast
364 table, and tuples in the main table with a Transaction ID that is older
365 than the oldest valid Transaction ID in the database or cluster.
367 Multiple causes of logical corruption have been observed in production
368 systems, including bugs in the <span class="productname">PostgreSQL</span>
369 server software, faulty and ill-conceived backup and restore tools, and
372 Corrupt relations are most concerning in live production environments,
373 precisely the same environments where high risk activities are least
374 welcome. For this reason, <code class="function">verify_heapam</code> has been
375 designed to diagnose corruption without undue risk. It cannot guard
376 against all causes of backend crashes, as even executing the calling
377 query could be unsafe on a badly corrupted system. Access to <a class="link" href="catalogs-overview.html" title="52.1. Overview">catalog tables</a> is performed and could
378 be problematic if the catalogs themselves are corrupted.
380 In general, <code class="filename">amcheck</code> can only prove the presence of
381 corruption; it cannot prove its absence.
382 </p></div><div class="sect2" id="AMCHECK-REPAIRING-CORRUPTION"><div class="titlepage"><div><div><h3 class="title">F.1.4. Repairing Corruption <a href="#AMCHECK-REPAIRING-CORRUPTION" class="id_link">#</a></h3></div></div></div><p>
383 No error concerning corruption raised by <code class="filename">amcheck</code> should
384 ever be a false positive. <code class="filename">amcheck</code> raises
385 errors in the event of conditions that, by definition, should never
386 happen, and so careful analysis of <code class="filename">amcheck</code>
387 errors is often required.
389 There is no general method of repairing problems that
390 <code class="filename">amcheck</code> detects. An explanation for the root cause of
391 an invariant violation should be sought. <a class="xref" href="pageinspect.html" title="F.23. pageinspect — low-level inspection of database pages">pageinspect</a> may play a useful role in diagnosing
392 corruption that <code class="filename">amcheck</code> detects. A <code class="command">REINDEX</code>
393 may not be effective in repairing corruption.
394 </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="contrib.html" title="Appendix F. Additional Supplied Modules and Extensions">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.2. auth_delay — pause on authentication failure">Next</a></td></tr><tr><td width="40%" align="left" valign="top">Appendix F. Additional Supplied Modules and Extensions </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="40%" align="right" valign="top"> F.2. auth_delay — pause on authentication failure</td></tr></table></div></body></html>
begriffs.com / projects / ai-pg / blob