amcheck

The amcheck module provides functions that allow you to verify the logical consistency of relation structures. If the structure is valid, no errors will be raised.

These functions verify the structure of specific relations, expressing various invariant conditions. The correctness of index scans and critical operations in the underlying access method relies on these invariant conditions holding true. For example, some of these functions verify that all items in B-tree pages are arranged in "logical" order (for instance, for a B-tree index on text, index tuples should be sorted in lexicographic order). If a particular invariant condition fails to hold for some reason, binary searches on the affected pages will fail to correctly guide index scans, ultimately causing SQL queries to return wrong answers.

The verification process uses the same procedures that index scans themselves use, which may involve user-defined operator class code. For example, B-tree index verification relies on comparisons performed by one or more B-tree support function 1 routines.

amcheck functions can only be used by superusers.

1. Functions

bt_index_check(index regclass, heapallindexed boolean) returns void

bt_index_check tests a B-tree index for various invariant conditions. Usage example:

test=## create extension amcheck;

est=## SELECT bt_index_check(index => c.oid, heapallindexed => i.indisunique),

test-#relname,

test-#relpages

test-## FROM pg_index i

test-## JOIN pg_opclass op ON i.indclass[0] = op.oid

test-## JOIN pg_am am ON op.opcmethod = am.oid

test-## JOIN pg_class c ON i.indexrelid = c.oid

test-## JOIN pg_namespace n ON c.relnamespace = n.oid

test-## WHERE am.amname = 'btree' AND n.nspname = 'pg_catalog'

-- Don't check temp tables, which may be from another session:

test-## AND c.relpersistence != 't'

-- Function may throw an error when this is omitted:

test-## AND c.relkind = 'i' AND i.indisready AND i.indisvalid

test-## ORDER BY c.relpages DESC LIMIT 10;

bt_index_check | relname | relpages

----------------+---------------------------------+----------

| pg_depend_depender_index | 53

| pg_depend_reference_index | 53

| pg_proc_proname_args_nsp_index | 36

| pg_description_o_c_o_index | 22

| pg_attribute_relid_attnam_index | 18

| pg_proc_oid_index | 13

| pg_attribute_relid_attnum_index | 12

| pg_collation_name_enc_nsp_index | 10

| pg_collation_oid_index | 7

| pg_operator_oprname_l_r_n_index | 7

(10 rows)

This example session performs verification on the 10 largest catalog indexes in the database "test". For unique indexes, verification is requested that heap tuples have corresponding index tuples. Since no errors were reported, all tested indexes are in a logically consistent state. Naturally, this query can easily be modified to call bt_index_check on every index in the database that supports verification.

bt_index_check requires an AccessShareLock on the target index and its associated heap relation. This lock mode is the same as that required by a simple SELECT statement on a relation. bt_index_check does not verify invariant conditions that span parent-child relationships, but when heapallindexed is true, it verifies that all heap tuples exist as index tuples. When using a routine based on bt_index_check for lightweight corruption testing in a production environment, there is often a trade-off between verification thoroughness and minimizing the impact on application performance.

bt_index_parent_check(index regclass, heapallindexed boolean, rootdescend boolean)

returns void

bt_index_parent_check tests a B-tree index for various invariant conditions.

When the heapallindexed parameter is true, the function verifies the existence of all heap tuples (found in the index).

When the optional parameter rootdescend is true, for each tuple, the verifier re-finds the leaf-level tuple by performing a new search from the root page.

The checks that bt_index_parent_check can perform are a superset of those that bt_index_check can perform. bt_index_parent_check can be thought of as a more comprehensive variant of bt_index_check: unlike bt_index_check, bt_index_parent_check also checks invariant conditions spanning parent/child relationships, including checking for missing downlinks in the index structure. If a logical inconsistency or other problem is found, bt_index_parent_check follows the usual error reporting convention.

bt_index_parent_check requires a ShareLock on the target index (and also a ShareLock on the relation). These locks prevent concurrent data modification from INSERT, UPDATE, and DELETE commands. These locks prevent the underlying relation from being processed by concurrent VACUUM and other utility commands. Note that the function only holds locks during its execution, not for the entire transaction.

The additional verification performed by bt_index_parent_check is more likely to detect various pathological conditions. These conditions may involve an incorrectly implemented B-tree operator class used by the indexed relation, or possibly an undiscovered bug in the underlying B-tree index access method code. Note that unlike bt_index_check, bt_index_parent_check cannot be used when hot standby mode is enabled (i.e., on a read-only physical replica).

Tip: Both bt_index_check and bt_index_parent_check output log messages about the verification process at DEBUG1 and DEBUG2 severity levels. These messages provide detailed information about the verification process and may be useful to Halo developers. Advanced users may find this information helpful as it provides additional context for the inconsistencies that verification actually detects. Run: SET client_min_messages = DEBUG1; in an interactive psql session before running the verification query to display messages about verification progress with a manageable level of detail.

2. Optional heapallindexed Verification

When the heapallindexed parameter of the verification function is true, an additional verification pass is performed against the table associated with the target index relation. This verification consists of a "dummy" CREATE INDEX operation that checks for the presence of all hypothetical new index tuples against a temporary, in-memory summary structure (built as needed during the underlying first-phase verification pass). This summary structure "fingerprints" every tuple in the target index. The high-level principle behind heapallindexed verification is: a new index equivalent to the existing target index must only contain items that can be found in the existing structure.

The additional heapallindexed phase adds significant overhead: verification time typically increases several-fold. However, the relation-level locks required do not change when performing heapallindexed verification.

The size of the summary structure is bounded by maintenance_work_mem. To ensure no more than a 2% probability of failing to detect an inconsistency for each heap tuple that exists in the index, approximately 2 bytes of memory per tuple is required.

As less memory is available per tuple, the probability of missing an inconsistency gradually increases. This approach significantly limits verification overhead while only slightly reducing the probability of detecting problems, especially for installations that run verification as a routine maintenance task. For each new verification attempt, any single missing or malformed tuple has a chance of being detected.

Using amcheck Effectively

amcheck is very effective at detecting various failure modes that data page checksums cannot catch. These include:

• Structural inconsistencies caused by incorrect operator class implementations.

  This includes issues arising from changes in operating system collation comparison rules. Comparisons of collatable data types such as text must be immutable (as all comparisons used for B-tree index scans must be immutable), meaning the operating system collation must remain unchanged. Rarely, operating system collation updates can cause these issues.

  More commonly, inconsistencies in sort order between the primary server and standby servers can be interrelated, possibly due to using different major operating system versions. Such inconsistencies typically only appear on standby servers, and therefore can only be detected on standby servers.

  If such issues arise, they may not affect every index using the affected collation, because the indexed values may happen to have the same absolute ordering regardless of the inconsistent behavior.

• Structural inconsistencies between the index and the indexed relation (when performing heapallindexed verification).

  During normal operation, indexes are not cross-checked against their relations. Symptoms of heap corruption can be subtle.

• Corruption due to (hypothetical) undiscovered bugs in the underlying access method code, sorting code, or transaction management code.

  Automatic verification of index structural integrity plays an important role when testing new features or proposed features that might introduce logical inconsistencies. Verification of table structure, associated visibility and transaction state information plays a similar role. An obvious testing strategy is to continuously invoke amcheck functions while running standard regression tests.

• File system or storage subsystem failures that happen to have checksums disabled.

  Note that amcheck checks pages represented in a shared memory buffer when verifying, if there is at least one shared buffer hit when accessing a block. Therefore, amcheck does not necessarily check data read from the file system during verification. Note: When checksums are enabled, if a corrupted block is read into a buffer, amcheck may raise an error due to a checksum failure.

• Corruption caused by defective RAM or memory subsystems.

  Halo cannot provide protection against correctable memory errors, and it assumes users are using RAM with industry-standard Error Correcting Code (ECC) or better protection technology. However, ECC memory typically only provides immunity to single-bit errors, and it should not be assumed to provide absolute protection against memory corruption failures.

  There is typically a significantly increased chance of detecting single-bit errors when performing heapallindexed verification, because strict binary equality is tested and indexed attributes are tested in the heap.

  Generally, amcheck can only prove the existence of corruption, but it cannot prove that corruption does not exist.

Repairing Corruption

amcheck errors related to corruption should never be treated as false positives. amcheck throws errors in situations that (by definition) should never occur, so amcheck errors often require careful analysis.

There is no general-purpose fix for problems detected by amcheck. You should look for the root cause of the invariant condition violation. pageinspect may play a very useful role in diagnosing corruption detected by amcheck. REINDEX may not be effective in repairing corruption.