Repository files navigation

An extension that provides basic consistency checking functionality fortables and b-tree indexes. Currently this performs basic checks at thepage and item level, for example:

• page header features (lower <= upper <= special etc.)
• items not overlapping
• attributes not overflowing the tuple end
• invalid varlena sizes (negative or over 1GB, ...)

Moreover it's possible to cross-check the table and indexes, i.e. tocheck if there are any missing / superfluous items in the index(compared to the heap).

This extensiondoes not implement correcting any of the issues,nor it fully checks the index structure (except for the generic pagechecks mentioned above). The extension does not support index typesother than b-tree (yet).

This is a regular extension (9.1) or a contrib module (9.0), so it may beinstalled rather easily - do either this

$ make install$ psql dbname -c "CREATE EXTENSION pg_check"

or this (on 9.0)

$ make install$ psql dbname < `pg_config --sharedir`/contrib/pg_check--0.1.0.sql

and the extension should be installed.

Currently there are four functions available

• pg_check_table(name, blk_from, blk_to) - checks range of blocks ofthe heap table
• pg_check_table(name, checkIndexes, crossCheck) - checks the tablewith the options to check all indexes on it, and even cross-checkingthe indexes with the table
• pg_check_index(name, blk_from, blk_to) - checks range of blocks forthe index
• pg_check_index(name) - checks a single index

So if you want to check table "my_table" and all the indexes on it, do this:

db=# SELECT pg_check_table('my_table', true, true);

and it will print out info about the checks (and return number of issues).

Be very careful about running thepg_check_table withcrossCheck=truebecause that means a more restrictive lock mode (SHARE ROW EXCLUSIVE) isneeded instead of the ACCESS SHARE lock used withcrossCheck=false.

So use cross-checking wisely, as it prevents any modification of the data(table or indexes). This may even cause deadlocks, if another processacquires the locks in different order (index before table). The lockon the table is held the whole time, the locks on the indexes are acquiredonly when checking the indexes (so there's always at most one index locked).

The extension (once loaded) uses these two options:

• pg_check.debug = {true | false}
• pg_check.bitmap_format = {binary, base64, hex, none}

The first one allows you to enable debug output when cross-checking thetable and indexes - by default it's set tofalse and by setting it totrue the extension will print details about the bitmaps.

The bimap may be printed in several formats - "binary" (as a sequence of0s and 1s), "hex" (hexa-decimal) or "base64" (the usual base64 encoding).Or it may be disabled by "none" (thus only basic info about the bitmapwill be printed). By default the format is "binary".

This is intended for debugging purposes only, the amount of informationprinted may be significant (even megabytes).

The functions may print various info about the blocks/tuples, depending onthe client_min_messages level.

• WARNING - only info about actual issues
• DEBUG1 - info about pages
• DEBUG2 - info about tuples on a page
• DEBUG3 - info about attributes of a tuple

This software is provided under the BSD license. See LICENSE for details.