Debian Codegraph @

doc/src/sgml/amcheck.sgml

raw
1 <!-- doc/src/sgml/amcheck.sgml -->
2
3 <sect1 id="amcheck" xreflabel="amcheck">
4 <title>amcheck &mdash; tools to verify table and index consistency</title>
5
6 <indexterm zone="amcheck">
7 <primary>amcheck</primary>
8 </indexterm>
9
10 <para>
11 The <filename>amcheck</filename> module provides functions that allow you to
12 verify the logical consistency of the structure of relations.
13 </para>
14
15 <para>
16 The B-Tree checking functions verify various <emphasis>invariants</emphasis> in the
17 structure of the representation of particular relations. The
18 correctness of the access method functions behind index scans and
19 other important operations relies on these invariants always
20 holding. For example, certain functions verify, among other things,
21 that all B-Tree pages have items in <quote>logical</quote> order (e.g.,
22 for B-Tree indexes on <type>text</type>, index tuples should be in
23 collated lexical order). If that particular invariant somehow fails
24 to hold, we can expect binary searches on the affected page to
25 incorrectly guide index scans, resulting in wrong answers to SQL
26 queries. If the structure appears to be valid, no error is raised.
27 While these checking functions are run, the <xref
28 linkend="guc-search-path"/> is temporarily changed to <literal>pg_catalog,
29 pg_temp</literal>.
30 </para>
31 <para>
32 Verification is performed using the same procedures as those used by
33 index scans themselves, which may be user-defined operator class
34 code. For example, B-Tree index verification relies on comparisons
35 made with one or more B-Tree support function 1 routines. See <xref
36 linkend="xindex-support"/> for details of operator class support
37 functions.
38 </para>
39 <para>
40 Unlike the B-Tree checking functions which report corruption by raising
41 errors, the heap checking function <function>verify_heapam</function> checks
42 a table and attempts to return a set of rows, one row per corruption
43 detected. Despite this, if facilities that
44 <function>verify_heapam</function> relies upon are themselves corrupted, the
45 function may be unable to continue and may instead raise an error.
46 </para>
47 <para>
48 Permission to execute <filename>amcheck</filename> functions may be granted
49 to non-superusers, but before granting such permissions careful consideration
50 should be given to data security and privacy concerns. Although the
51 corruption reports generated by these functions do not focus on the contents
52 of the corrupted data so much as on the structure of that data and the nature
53 of the corruptions found, an attacker who gains permission to execute these
54 functions, particularly if the attacker can also induce corruption, might be
55 able to infer something of the data itself from such messages.
56 </para>
57
58 <sect2 id="amcheck-functions">
59 <title>Functions</title>
60
61 <variablelist>
62 <varlistentry>
63 <term>
64 <function>bt_index_check(index regclass, heapallindexed boolean, checkunique boolean) returns void</function>
65 <indexterm>
66 <primary>bt_index_check</primary>
67 </indexterm>
68 </term>
69
70 <listitem>
71 <para>
72 <function>bt_index_check</function> tests that its target, a
73 B-Tree index, respects a variety of invariants. Example usage:
74 <screen>
75 test=# SELECT bt_index_check(index =&gt; c.oid, heapallindexed =&gt; i.indisunique),
76 c.relname,
77 c.relpages
78 FROM pg_index i
79 JOIN pg_opclass op ON i.indclass[0] = op.oid
80 JOIN pg_am am ON op.opcmethod = am.oid
81 JOIN pg_class c ON i.indexrelid = c.oid
82 JOIN pg_namespace n ON c.relnamespace = n.oid
83 WHERE am.amname = 'btree' AND n.nspname = 'pg_catalog'
84 -- Don't check temp tables, which may be from another session:
85 AND c.relpersistence != 't'
86 -- Function may throw an error when this is omitted:
87 AND c.relkind = 'i' AND i.indisready AND i.indisvalid
88 ORDER BY c.relpages DESC LIMIT 10;
89 bt_index_check | relname | relpages
90 ----------------+---------------------------------+----------
91 | pg_depend_reference_index | 43
92 | pg_depend_depender_index | 40
93 | pg_proc_proname_args_nsp_index | 31
94 | pg_description_o_c_o_index | 21
95 | pg_attribute_relid_attnam_index | 14
96 | pg_proc_oid_index | 10
97 | pg_attribute_relid_attnum_index | 9
98 | pg_amproc_fam_proc_index | 5
99 | pg_amop_opr_fam_index | 5
100 | pg_amop_fam_strat_index | 5
101 (10 rows)
102 </screen>
103 This example shows a session that performs verification of the
104 10 largest catalog indexes in the database <quote>test</quote>.
105 Verification of the presence of heap tuples as index tuples is
106 requested for the subset that are unique indexes. Since no
107 error is raised, all indexes tested appear to be logically
108 consistent. Naturally, this query could easily be changed to
109 call <function>bt_index_check</function> for every index in the
110 database where verification is supported.
111 </para>
112 <para>
113 <function>bt_index_check</function> acquires an <literal>AccessShareLock</literal>
114 on the target index and the heap relation it belongs to. This lock mode
115 is the same lock mode acquired on relations by simple
116 <literal>SELECT</literal> statements.
117 <function>bt_index_check</function> does not verify invariants
118 that span child/parent relationships, but will verify the
119 presence of all heap tuples as index tuples within the index
120 when <parameter>heapallindexed</parameter> is
121 <literal>true</literal>. When <parameter>checkunique</parameter>
122 is <literal>true</literal> <function>bt_index_check</function> will
123 check that no more than one among duplicate entries in unique
124 index is visible. When a routine, lightweight test for
125 corruption is required in a live production environment, using
126 <function>bt_index_check</function> often provides the best
127 trade-off between thoroughness of verification and limiting the
128 impact on application performance and availability.
129 </para>
130 </listitem>
131 </varlistentry>
132
133 <varlistentry>
134 <term>
135 <function>bt_index_parent_check(index regclass, heapallindexed boolean, rootdescend boolean, checkunique boolean) returns void</function>
136 <indexterm>
137 <primary>bt_index_parent_check</primary>
138 </indexterm>
139 </term>
140
141 <listitem>
142 <para>
143 <function>bt_index_parent_check</function> tests that its
144 target, a B-Tree index, respects a variety of invariants.
145 Optionally, when the <parameter>heapallindexed</parameter>
146 argument is <literal>true</literal>, the function verifies the
147 presence of all heap tuples that should be found within the
148 index. When <parameter>checkunique</parameter>
149 is <literal>true</literal> <function>bt_index_parent_check</function> will
150 check that no more than one among duplicate entries in unique
151 index is visible. When the optional <parameter>rootdescend</parameter>
152 argument is <literal>true</literal>, verification re-finds
153 tuples on the leaf level by performing a new search from the
154 root page for each tuple. The checks that can be performed by
155 <function>bt_index_parent_check</function> are a superset of the
156 checks that can be performed by <function>bt_index_check</function>.
157 <function>bt_index_parent_check</function> can be thought of as
158 a more thorough variant of <function>bt_index_check</function>:
159 unlike <function>bt_index_check</function>,
160 <function>bt_index_parent_check</function> also checks
161 invariants that span parent/child relationships, including checking
162 that there are no missing downlinks in the index structure.
163 <function>bt_index_parent_check</function> follows the general
164 convention of raising an error if it finds a logical
165 inconsistency or other problem.
166 </para>
167 <para>
168 A <literal>ShareLock</literal> is required on the target index by
169 <function>bt_index_parent_check</function> (a
170 <literal>ShareLock</literal> is also acquired on the heap relation).
171 These locks prevent concurrent data modification from
172 <command>INSERT</command>, <command>UPDATE</command>, and <command>DELETE</command>
173 commands. The locks also prevent the underlying relation from
174 being concurrently processed by <command>VACUUM</command>, as well as
175 all other utility commands. Note that the function holds locks
176 only while running, not for the entire transaction.
177 </para>
178 <para>
179 <function>bt_index_parent_check</function>'s additional
180 verification is more likely to detect various pathological
181 cases. These cases may involve an incorrectly implemented
182 B-Tree operator class used by the index that is checked, or,
183 hypothetically, undiscovered bugs in the underlying B-Tree index
184 access method code. Note that
185 <function>bt_index_parent_check</function> cannot be used when
186 hot standby mode is enabled (i.e., on read-only physical
187 replicas), unlike <function>bt_index_check</function>.
188 </para>
189 </listitem>
190 </varlistentry>
191
192 <varlistentry>
193 <term>
194 <function>gin_index_check(index regclass) returns void</function>
195 <indexterm>
196 <primary>gin_index_check</primary>
197 </indexterm>
198 </term>
199
200 <listitem>
201 <para>
202 <function>gin_index_check</function> tests that its target GIN index
203 has consistent parent-child tuples relations (no parent tuples
204 require tuple adjustment) and page graph respects balanced-tree
205 invariants (internal pages reference only leaf page or only internal
206 pages).
207 </para>
208 </listitem>
209 </varlistentry>
210
211 </variablelist>
212 <tip>
213 <para>
214 <function>bt_index_check</function> and
215 <function>bt_index_parent_check</function> both output log
216 messages about the verification process at
217 <literal>DEBUG1</literal> and <literal>DEBUG2</literal> severity
218 levels. These messages provide detailed information about the
219 verification process that may be of interest to
220 <productname>PostgreSQL</productname> developers. Advanced users
221 may also find this information helpful, since it provides
222 additional context should verification actually detect an
223 inconsistency. Running:
224 <programlisting>
225 SET client_min_messages = DEBUG1;
226 </programlisting>
227 in an interactive <application>psql</application> session before
228 running a verification query will display messages about the
229 progress of verification with a manageable level of detail.
230 </para>
231 </tip>
232
233 <variablelist>
234 <varlistentry>
235 <term>
236 <function>
237 verify_heapam(relation regclass,
238 on_error_stop boolean,
239 check_toast boolean,
240 skip text,
241 startblock bigint,
242 endblock bigint,
243 blkno OUT bigint,
244 offnum OUT integer,
245 attnum OUT integer,
246 msg OUT text)
247 returns setof record
248 </function>
249 </term>
250 <listitem>
251 <para>
252 Checks a table, sequence, or materialized view for structural corruption,
253 where pages in the relation contain data that is invalidly formatted, and
254 for logical corruption, where pages are structurally valid but
255 inconsistent with the rest of the database cluster.
256 </para>
257 <para>
258 The following optional arguments are recognized:
259 </para>
260 <variablelist>
261 <varlistentry>
262 <term><literal>on_error_stop</literal></term>
263 <listitem>
264 <para>
265 If true, corruption checking stops at the end of the first block in
266 which any corruptions are found.
267 </para>
268 <para>
269 Defaults to false.
270 </para>
271 </listitem>
272 </varlistentry>
273 <varlistentry>
274 <term><literal>check_toast</literal></term>
275 <listitem>
276 <para>
277 If true, toasted values are checked against the target relation's
278 TOAST table.
279 </para>
280 <para>
281 This option is known to be slow. Also, if the toast table or its
282 index is corrupt, checking it against toast values could conceivably
283 crash the server, although in many cases this would just produce an
284 error.
285 </para>
286 <para>
287 Defaults to false.
288 </para>
289 </listitem>
290 </varlistentry>
291 <varlistentry>
292 <term><literal>skip</literal></term>
293 <listitem>
294 <para>
295 If not <literal>none</literal>, corruption checking skips blocks that
296 are marked as all-visible or all-frozen, as specified.
297 Valid options are <literal>all-visible</literal>,
298 <literal>all-frozen</literal> and <literal>none</literal>.
299 </para>
300 <para>
301 Defaults to <literal>none</literal>.
302 </para>
303 </listitem>
304 </varlistentry>
305 <varlistentry>
306 <term><literal>startblock</literal></term>
307 <listitem>
308 <para>
309 If specified, corruption checking begins at the specified block,
310 skipping all previous blocks. It is an error to specify a
311 <parameter>startblock</parameter> outside the range of blocks in the
312 target table.
313 </para>
314 <para>
315 By default, checking begins at the first block.
316 </para>
317 </listitem>
318 </varlistentry>
319 <varlistentry>
320 <term><literal>endblock</literal></term>
321 <listitem>
322 <para>
323 If specified, corruption checking ends at the specified block,
324 skipping all remaining blocks. It is an error to specify an
325 <parameter>endblock</parameter> outside the range of blocks in the target
326 table.
327 </para>
328 <para>
329 By default, all blocks are checked.
330 </para>
331 </listitem>
332 </varlistentry>
333 </variablelist>
334 <para>
335 For each corruption detected, <function>verify_heapam</function> returns
336 a row with the following columns:
337 </para>
338 <variablelist>
339 <varlistentry>
340 <term><literal>blkno</literal></term>
341 <listitem>
342 <para>
343 The number of the block containing the corrupt page.
344 </para>
345 </listitem>
346 </varlistentry>
347 <varlistentry>
348 <term><literal>offnum</literal></term>
349 <listitem>
350 <para>
351 The OffsetNumber of the corrupt tuple.
352 </para>
353 </listitem>
354 </varlistentry>
355 <varlistentry>
356 <term><literal>attnum</literal></term>
357 <listitem>
358 <para>
359 The attribute number of the corrupt column in the tuple, if the
360 corruption is specific to a column and not the tuple as a whole.
361 </para>
362 </listitem>
363 </varlistentry>
364 <varlistentry>
365 <term><literal>msg</literal></term>
366 <listitem>
367 <para>
368 A message describing the problem detected.
369 </para>
370 </listitem>
371 </varlistentry>
372 </variablelist>
373 </listitem>
374 </varlistentry>
375 </variablelist>
376 </sect2>
377
378 <sect2 id="amcheck-optional-heapallindexed-verification">
379 <title>Optional <parameter>heapallindexed</parameter> Verification</title>
380 <para>
381 When the <parameter>heapallindexed</parameter> argument to B-Tree
382 verification functions is <literal>true</literal>, an additional
383 phase of verification is performed against the table associated with
384 the target index relation. This consists of a <quote>dummy</quote>
385 <command>CREATE INDEX CONCURRENTLY</command> operation, which checks for the
386 presence of all hypothetical new index tuples against a temporary,
387 in-memory summarizing structure (this is built when needed during
388 the basic first phase of verification). The summarizing structure
389 <quote>fingerprints</quote> every tuple found within the target
390 index. The high level principle behind
391 <parameter>heapallindexed</parameter> verification is that a new
392 index that is equivalent to the existing, target index must only
393 have entries that can be found in the existing structure.
394 </para>
395 <para>
396 The additional <parameter>heapallindexed</parameter> phase adds
397 significant overhead: verification will typically take several times
398 longer. However, there is no change to the relation-level locks
399 acquired when <parameter>heapallindexed</parameter> verification is
400 performed.
401 </para>
402 <para>
403 The summarizing structure is bound in size by
404 <varname>maintenance_work_mem</varname>. In order to ensure that
405 there is no more than a 2% probability of failure to detect an
406 inconsistency for each heap tuple that should be represented in the
407 index, approximately 2 bytes of memory are needed per tuple. As
408 less memory is made available per tuple, the probability of missing
409 an inconsistency slowly increases. This approach limits the
410 overhead of verification significantly, while only slightly reducing
411 the probability of detecting a problem, especially for installations
412 where verification is treated as a routine maintenance task. Any
413 single absent or malformed tuple has a new opportunity to be
414 detected with each new verification attempt.
415 </para>
416
417 </sect2>
418
419 <sect2 id="amcheck-using-amcheck-effectively">
420 <title>Using <filename>amcheck</filename> Effectively</title>
421
422 <para>
423 <filename>amcheck</filename> can be effective at detecting various types of
424 failure modes that <link linkend="checksums">data checksums</link> will fail
425 to catch. These include:
426
427 <itemizedlist>
428 <listitem>
429 <para>
430 Structural inconsistencies caused by incorrect operator class
431 implementations.
432 </para>
433 <para>
434 This includes issues caused by the comparison rules of operating
435 system collations changing. Comparisons of datums of a collatable
436 type like <type>text</type> must be immutable (just as all
437 comparisons used for B-Tree index scans must be immutable), which
438 implies that operating system collation rules must never change.
439 Though rare, updates to operating system collation rules can
440 cause these issues. More commonly, an inconsistency in the
441 collation order between a primary server and a standby server is
442 implicated, possibly because the <emphasis>major</emphasis> operating
443 system version in use is inconsistent. Such inconsistencies will
444 generally only arise on standby servers, and so can generally
445 only be detected on standby servers.
446 </para>
447 <para>
448 If a problem like this arises, it may not affect each individual
449 index that is ordered using an affected collation, simply because
450 <emphasis>indexed</emphasis> values might happen to have the same
451 absolute ordering regardless of the behavioral inconsistency. See
452 <xref linkend="locale"/> and <xref linkend="collation"/> for
453 further details about how <productname>PostgreSQL</productname> uses
454 operating system locales and collations.
455 </para>
456 </listitem>
457 <listitem>
458 <para>
459 Structural inconsistencies between indexes and the heap relations
460 that are indexed (when <parameter>heapallindexed</parameter>
461 verification is performed).
462 </para>
463 <para>
464 There is no cross-checking of indexes against their heap relation
465 during normal operation. Symptoms of heap corruption can be subtle.
466 </para>
467 </listitem>
468 <listitem>
469 <para>
470 Corruption caused by hypothetical undiscovered bugs in the
471 underlying <productname>PostgreSQL</productname> access method
472 code, sort code, or transaction management code.
473 </para>
474 <para>
475 Automatic verification of the structural integrity of indexes
476 plays a role in the general testing of new or proposed
477 <productname>PostgreSQL</productname> features that could plausibly allow a
478 logical inconsistency to be introduced. Verification of table
479 structure and associated visibility and transaction status
480 information plays a similar role. One obvious testing strategy
481 is to call <filename>amcheck</filename> functions continuously
482 when running the standard regression tests. See <xref
483 linkend="regress-run"/> for details on running the tests.
484 </para>
485 </listitem>
486 <listitem>
487 <para>
488 File system or storage subsystem faults when data checksums are
489 disabled.
490 </para>
491 <para>
492 Note that <filename>amcheck</filename> examines a page as represented in some
493 shared memory buffer at the time of verification if there is only a
494 shared buffer hit when accessing the block. Consequently,
495 <filename>amcheck</filename> does not necessarily examine data read from the
496 file system at the time of verification. Note that when checksums are
497 enabled, <filename>amcheck</filename> may raise an error due to a checksum
498 failure when a corrupt block is read into a buffer.
499 </para>
500 </listitem>
501 <listitem>
502 <para>
503 Corruption caused by faulty RAM, or the broader memory subsystem.
504 </para>
505 <para>
506 <productname>PostgreSQL</productname> does not protect against correctable
507 memory errors and it is assumed you will operate using RAM that
508 uses industry standard Error Correcting Codes (ECC) or better
509 protection. However, ECC memory is typically only immune to
510 single-bit errors, and should not be assumed to provide
511 <emphasis>absolute</emphasis> protection against failures that
512 result in memory corruption.
513 </para>
514 <para>
515 When <parameter>heapallindexed</parameter> verification is
516 performed, there is generally a greatly increased chance of
517 detecting single-bit errors, since strict binary equality is
518 tested, and the indexed attributes within the heap are tested.
519 </para>
520 </listitem>
521 </itemizedlist>
522 </para>
523
524 <para>
525 Structural corruption can happen due to faulty storage hardware, or
526 relation files being overwritten or modified by unrelated software.
527 This kind of corruption can also be detected with
528 <link linkend="checksums"><application>data page
529 checksums</application></link>.
530 </para>
531
532 <para>
533 Relation pages which are correctly formatted, internally consistent, and
534 correct relative to their own internal checksums may still contain
535 logical corruption. As such, this kind of corruption cannot be detected
536 with <application>checksums</application>. Examples include toasted
537 values in the main table which lack a corresponding entry in the toast
538 table, and tuples in the main table with a Transaction ID that is older
539 than the oldest valid Transaction ID in the database or cluster.
540 </para>
541
542 <para>
543 Multiple causes of logical corruption have been observed in production
544 systems, including bugs in the <productname>PostgreSQL</productname>
545 server software, faulty and ill-conceived backup and restore tools, and
546 user error.
547 </para>
548
549 <para>
550 Corrupt relations are most concerning in live production environments,
551 precisely the same environments where high risk activities are least
552 welcome. For this reason, <function>verify_heapam</function> has been
553 designed to diagnose corruption without undue risk. It cannot guard
554 against all causes of backend crashes, as even executing the calling
555 query could be unsafe on a badly corrupted system. Access to <link
556 linkend="catalogs-overview">catalog tables</link> is performed and could
557 be problematic if the catalogs themselves are corrupted.
558 </para>
559
560 <para>
561 In general, <filename>amcheck</filename> can only prove the presence of
562 corruption; it cannot prove its absence.
563 </para>
564
565 </sect2>
566 <sect2 id="amcheck-repairing-corruption">
567 <title>Repairing Corruption</title>
568 <para>
569 No error concerning corruption raised by <filename>amcheck</filename> should
570 ever be a false positive. <filename>amcheck</filename> raises
571 errors in the event of conditions that, by definition, should never
572 happen, and so careful analysis of <filename>amcheck</filename>
573 errors is often required.
574 </para>
575 <para>
576 There is no general method of repairing problems that
577 <filename>amcheck</filename> detects. An explanation for the root cause of
578 an invariant violation should be sought. <xref
579 linkend="pageinspect"/> may play a useful role in diagnosing
580 corruption that <filename>amcheck</filename> detects. A <command>REINDEX</command>
581 may not be effective in repairing corruption.
582 </para>
583
584 </sect2>
585
586 </sect1>