Use amcheck

Eugenia Kuzina

Contents

Overview
Installation
Functions

Overview

The amcheck module provides functions that allow you to verify the logical consistency of a relation structure. It can be useful for identifying various types of issues that might go undetected when enabling data checksums. These include:

Structural inconsistencies caused by incorrect operator class implementations.
Structural inconsistencies between indexes and the heap relations that are indexed.
A corruption caused by hypothetical undiscovered bugs in the underlying PostgreSQL access method code, sort code, or transaction management code.
A file system or storage subsystem faults where checksums happen to simply not be enabled.
A corruption caused by faulty RAM or the memory subsystem in general.

Relation pages, which are correctly formatted, internally consistent, and correct relative to their own internal checksums, may still contain logical corruption. This type of corruption cannot be detected with checksums. Examples include toasted values in the main table, which lack a corresponding entry in the TOAST table, and tuples in the main table with a transaction ID that is older than the oldest valid transaction ID in the database or cluster.

NOTE

The amcheck extension can only prove the presence of corruption; it cannot prove its absence.
Since ADP clusters are initialized with the --data-checksum option, many errors related to corrupted index pages will be detected before amcheck starts checking invariants.

The B-Tree checking functions of the amcheck extension verify various invariants in the structure of a relation representation. The correctness of the access method functions behind index scans and other operations depends on invariants always being observed. Functions verify that all B-Tree pages have items in "logical" order (for example, for a B-Tree index on a text column, index tuples should be in collated lexical order). If this invariant somehow fails to hold, we can expect binary searches on the affected page to incorrectly guide index scans, resulting in wrong results of SQL queries.

Unlike B-tree verification functions, which report corruption by throwing errors, the heap verification function verify_heapam checks the table and attempts to return a set of rows: one row for each corruption detected. However, if the assets that the verify_heapam function uses become corrupted, the function may be unable to continue and may raise an error.

When amcheck detects data corruption, false positives are virtually eliminated. The amcheck extension treats situations that should never occur as errors, so amcheck errors typically require careful analysis.

There is no general method of resolving problems that amcheck detects. You can start by finding the root cause of the invariant violation. The pageinspect extension can be useful to diagnose the corruptions detected by amcheck.

Installation

The package required for the amcheck installation is shipped with ADP. To use amcheck, just run the CREATE EXTENSION command:

CREATE EXTENSION amcheck;

NOTE

If the amcheck extension is created in the template1 database used as the default template, all subsequently created databases will have this extension installed.

ADP uses the 1.3 amcheck version. To check it, execute the following query:

SELECT extversion FROM pg_extension
    WHERE extname = 'amcheck';

 extversion
------------
1.3

Once you create the amcheck extension, its functionality will be available in the current database.

Functions

As mentioned above, the amcheck extension provides functions for checking the logical integrity of relation structures, in particular B-tree indexes.

It is possible to grant amcheck permissions to a non-superuser, but security and data privacy considerations should be carefully investigated before doing this. Although these functions primarily provide information about the data structure and the nature of the corruption found, rather than displaying the corrupted data itself, if an attacker can execute these functions, and especially if they manage to corrupt the data, they may be able to obtain confidential information from the data that functions display.

bt_index_check

The bt_index_check function checks whether a target B-tree index satisfies a number of invariants.

bt_index_check(<index> regclass, <heapallindexed> boolean) returns void

where:

<index> — a target B-tree index.
<heapallindexed> (optional) — if true, an additional phase of verification is performed against the table associated with the target index relation. This consists of a "dummy" CREATE INDEX operation, which checks for the presence of all hypothetical new index tuples against a temporary, in-memory summarizing structure (this is built when needed during the first phase of verification). The summarizing structure "fingerprints" every tuple found within the target index. The main idea of this verification is that a new index that is equivalent to the existing target index must only have entries that can be found in the existing structure. The default value is false.

The bt_index_check function acquires a lock of the AccessShareLock type on the target index and the heap relation it belongs to. This lock mode is the same lock mode that SELECT statements use; that is why bt_index_check can be used in a production environment.

The bt_index_check function does not verify invariants that span child/parent relationships but checks the presence of all heap tuples as index tuples within the index when heapallindexed is true.

When a routine, lightweight test for corruption is required in a live production environment, bt_index_check is an appropriate compromise between the comprehensiveness of the check and minimizing the impact on application performance and availability.

The bt_index_check function outputs log messages about the verification process at the debug1 and debug2 severity levels. These messages provide detailed information about the verification process. To change the severity level for the current session, use the following command in an interactive psql session:

SET client_min_messages = DEBUG1;

For more information on log configuration, see Logging.

To test bt_index_check function, create a table and populate it with data:

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    date DATE NOT NULL,
    amount DECIMAL(10,2),
    status VARCHAR(20)
);

INSERT INTO orders (customer_id, date, amount, status)
SELECT
    (random() * 500)::integer,
    CURRENT_DATE - (random() * 365)::integer,
    (random() * 500)::decimal,
    CASE WHEN random() < 0.4 THEN 'pending'
         WHEN random() < 0.7 THEN 'shipped'
         ELSE 'delivered' END
FROM generate_series(1, 2000);

Create indexes:

CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_orders_composite ON orders(customer_id, date);

Run the bt_index_check function:

SELECT bt_index_check('idx_orders_customer');

Result:

DEBUG:  verifying consistency of tree structure for index "idx_orders_customer"
DEBUG:  verifying level 1 (true root level)
DEBUG:  verifying level 0 (leaf level)
 bt_index_check
----------------

(1 row)

Run the bt_index_check function with the heapallindexed parameter set to true:

SELECT bt_index_check('idx_orders_composite', true);

Result:

DEBUG:  verifying consistency of tree structure for index "idx_orders_composite"
DEBUG:  verifying level 1 (true root level)
DEBUG:  verifying level 0 (leaf level)
DEBUG:  verifying that tuples from index "idx_orders_composite" are present in "orders"
DEBUG:  finished verifying presence of 2000 tuples from table "orders" with bitset 0.24% set
 bt_index_check
----------------

(1 row)

bt_index_parent_check

The bt_index_parent_check function also checks whether a target B-tree index satisfies a number of invariants. In addition to invariants inspected by bt_index_check, bt_index_parent_check checks invariants that span parent/child relationships, including missing downlinks in the index structure.

bt_index_parent_check(<index> regclass, <heapallindexed> boolean, <rootdescend> boolean) returns void

where:

<index> — a target B-tree index.
<heapallindexed> (optional) — if true, an additional phase of verification is performed against the table associated with the target index relation. This consists of a "dummy" CREATE INDEX operation, which checks for the presence of all hypothetical new index tuples against a temporary, in-memory summarizing structure (this is built when needed during the first phase of verification). The summarizing structure "fingerprints" every tuple found within the target index. The main idea of this verification is that a new index (which is equivalent to the existing target index) must only have entries that can be found in the existing structure. The default value is false.
<rootdescend> (optional) — if true, verification re-finds tuples on the leaf level by performing a new search from the root page for each tuple.

The bt_index_parent_check function requires a lock of the ShareLock type on the target index and the heap relation it belongs to. This lock prevents a concurrent data modification from the INSERT, UPDATE, and DELETE commands. It also prohibits the underlying relation from being concurrently processed by vacuum, as well as all other utility commands. Note that the function holds a lock only while running, not for the entire transaction.

The bt_index_parent_check function, like bt_index_check, outputs log messages about the verification process at the debug1 and debug2 severity levels.

As an example, call the bt_index_parent_check function for the idx_orders_composite index created above:

SELECT bt_index_parent_check('idx_orders_composite', true, true);

Result:

DEBUG:  verifying consistency of tree structure for index "idx_orders_composite" with cross-level checks
DEBUG:  verifying level 1 (true root level)
DEBUG:  verifying level 0 (leaf level)
DEBUG:  verifying that tuples from index "idx_orders_composite" are present in "orders"
DEBUG:  finished verifying presence of 2000 tuples from table "orders" with bitset 0.24% set
 bt_index_parent_check
-----------------------

(1 row)

verify_heapam

The verify_heapam function checks a table, sequence, or materialized view for structural corruption, where pages in the relation contain data that is invalidly formatted, and for logical corruption, where pages are structurally valid but inconsistent with the rest of the database cluster.

verify_heapam(<relation> regclass,
              <on_error_stop> boolean,
              <check_toast> boolean,
              <skip> text,
              <startblock> bigint,
              <endblock> bigint,
              <blkno> OUT bigint,
              <offnum> OUT integer,
              <attnum> OUT integer,
              <msg> OUT text) returns setof record

The parameters for the verify_heapam function are listed in the table below. All parameters except <relation> are optional.

Name Description Default value

relation

Target relation

—

on_error_stop

If true, corruption checking stops at the end of the first block in which any corruptions are found

false

check_toast

If true, toasted values are checked against the target relation’s TOAST table. This option is slow. Additionally, if the TOAST table or its index is corrupted, checking against TOAST values may crash the server or simply cause an error

false

skip

Valid options are all-visible, all-frozen, and none. If the value is not none, corruption checking skips blocks that are marked as all-visible or all-frozen according to the specified value

none

startblock

If specified, corruption checking begins at the specified block, skipping all previous blocks. Specifying a start block outside the range of blocks in the target table will result in an error

Checking begins at the first block

endblock

If specified, corruption checking ends at the specified block, skipping all remaining blocks. Specifying an end block outside the range of blocks in the target table will result in an error

All blocks are checked

For each corruption detected, verify_heapam returns a row with the columns listed in the table below.

Name

Description

blkno

Number of the block containing the corrupt page

offnum

Offset number of the corrupt tuple

attnum

Attribute number of the corrupt column in the tuple if the corruption is specific to a column and not the entire tuple

msg

Message describing the problem

Example:

SELECT * FROM verify_heapam(
    relation => 'orders'::regclass,
    on_error_stop => false,
    check_toast => true
);

Result:

 blkno | offnum | attnum | msg
-------+--------+--------+-----
(0 rows)

Found a mistake? Seleсt text and press Ctrl+Enter to report it