Using pageinspect extension

This article gives a short overview on how to use pageinspect extension and queries to find debug information.

pageinspect is an extension module of PostgreSQL core allowing to have a look at the contents of tables and indexes in the database at a low level. This module is particularly useful for debugging when implementing a new functionality that changes visibility of data like what could do an autovacuum or map visibility feature, or simply to understand the internals of Postgres without having to read much code.

Note that pageinspect is available on AWS RDS but only for PostgreSQL 10.5 and newer.

You can find more detailed information on the PostgreSQL documentation.

pageinspect module provides functions that allow you to inspect the contents of database pages at a low level, which is useful for debugging purposes.

All of these functions may be used only by superusers.

page_diag.txt will provide some forensic data about possible causes of block or tuple corruption in cases where the tuple is visible so it can be SELECTed, and in some cases where SELECTing it causes an error.

It won't help with examination of deleted tuples or old versions of updated tuples; for that you'll also need pg_dirtyread or you'll need to use heap_page_item_attrs to write your own scan for the key value's binary representation.

\o page_diag.txt

CREATE EXTENSION IF NOT EXISTS pageinspect;

-- Set these values to the table to inspect, the column to search by, and the column value for the broken row.
-- If your primary/unique key has more than one column, you'll need to edit the query.
\set tablename table1
\set colname id
\set targetvalue 1
\set VERBOSITY verbose

SELECT (ctid::text::point)[0] as blockno,
(ctid::text::point)[1] AS offset FROM :"tablename"
WHERE :"colname" = :'targetvalue' \gset

SELECT :'blockno' AS blockno, :'offset' AS offset;

SELECT get_raw_page(:'tablename', 'main', :'blockno') AS page \gset

\pset format unaligned
\pset pager off
\pset expanded off
\qecho 'page'
SELECT :'page';
\qecho 'header'
SELECT * FROM page_header(BYTEA :'page');
\qecho 'items'
SELECT lp, lp_off, lp_flags, lp_len, t_hoff, t_xmin, t_xmax,t_field3, t_ctid, to_hex(t_infomask) as infomask, to_hex(t_infomask2) as infomask2, t_bits, t_oid
FROM heap_page_items(bytea :'page');
\qecho 'attrs'
SELECT lp, t_attrs FROM heap_page_item_attrs(BYTEA :'page', (:'tablename')::regclass, false);

\pset format aligned
\pset expanded on
\qecho 'expanded'
SELECT * FROM page_header(BYTEA :'page');
SELECT lp, lp_off, lp_flags, lp_len, t_hoff, t_xmin, t_xmax,t_field3, t_ctid, to_hex(t_infomask) as infomask, to_hex(t_infomask2) as infomask2, t_bits, t_oid
FROM heap_page_items(bytea :'page');
SELECT lp, t_attrs FROM heap_page_item_attrs(BYTEA :'page', (:'tablename')::regclass, false);

\echo 'output written to page_diag.txt for ':tablename.:colname=:targetvalue' @ ctid=(':blockno',':offset')'

WITH RECURSIVE t AS (
SELECT MIN(col) AS col FROM tbl
UNION ALL
SELECT (SELECT MIN(col) FROM tbl WHERE col > t.col)
FROM t WHERE t.col IS NOT NULL
)
SELECT col FROM t WHERE col IS NOT NULL
UNION ALL
SELECT NULL WHERE EXISTS(SELECT 1 FROM tbl WHERE col IS NULL);

The recursive part of the query processes one new row in each pass, finding the smallest column value greater than that on the previous pass; the main query adds in a single null result if needed. The above query runs orders of magnitude faster when "col" has only a proportionally small number of distinct values.

To have vacuum process all pages including those marked all-frozen / all-visible, use the option DISABLE_PAGE_SKIPPING (available from PostgreSQL 9.6), which disables page-skipping behavior. This is intended for use only when the contents of the visibility/freeze map are suspect.

VACUUM (VERBOSE, DISABLE_PAGE_SKIPPING) tablename;

On Amazon RDS, EDB Lasso doesn't gather the pg_controldata information. Below queries may be helpful:

select pg_control_checkpoint();
select pg_control_system();
select pg_control_init();
select pg_control_recovery();