pageinspect

A contrib module enabling low-level inspection of database pages

pageinspect is a contrib module providing functions enabling direct inspection of database pages, including information about different kinds of index.

pageinspect was added in PostgreSQL 8.3. Some functions were previously part of pgstattuple.

Change history

Work-in-progress

Examples

General usage

postgres=# CREATE TABLE sometable(
  id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  val TEXT
);
CREATE TABLE

postgres=# INSERT INTO sometable VALUES(default,'foo');
INSERT 0 1

postgres=# SELECT * FROM page_header(get_raw_page('sometable',0));
    lsn    | checksum | flags | lower | upper | special | pagesize | version | prune_xid
-----------+----------+-------+-------+-------+---------+----------+---------+-----------
 0/30833B8 |        0 |     0 |    28 |  8160 |    8192 |     8192 |       4 |         0
(1 row)

postgres=# VACUUM sometable ;
VACUUM
Time: 11.628 ms
postgres=# SELECT * FROM page_header(get_raw_page('sometable',0));
    lsn    | checksum | flags | lower | upper | special | pagesize | version | prune_xid
-----------+----------+-------+-------+-------+---------+----------+---------+-----------
 0/3085510 |        0 |     4 |    28 |  8160 |    8192 |     8192 |       4 |         0
(1 row)


postgres=# UPDATE sometable SET val='bar';
UPDATE 1

postgres=# SELECT * FROM page_header(get_raw_page('sometable',0));
    lsn    | checksum | flags | lower | upper | special | pagesize | version | prune_xid
-----------+----------+-------+-------+-------+---------+----------+---------+-----------
 0/308BAA8 |        0 |     0 |    32 |  8128 |    8192 |     8192 |       4 |       627
(1 row)

Time: 0.530 ms
postgres=# SELECT * FROM heap_page_items(get_raw_page('sometable',0));
 lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid |       t_data
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+--------------------
  1 |   8160 |        1 |     32 |    626 |    627 |        0 | (0,2)  |       16386 |        258 |     24 |        |       | \x0500000009666f6f
  2 |   8128 |        1 |     32 |    627 |      0 |        0 | (0,2)  |       32770 |      10242 |     24 |        |       | \x0500000009626172
(2 rows)

postgres=# SELECT ctid, * FROM sometable;
 ctid  | id | val
-------+----+-----
 (0,2) |  5 | bar
(1 row)

postgres=# SELECT tuple_data_split('sometable'::regclass, t_data, t_infomask, t_infomask2, t_bits) FROM heap_page_items(get_raw_page('sometable', 0));
       tuple_data_split
-------------------------------
 {"\\x05000000","\\x09666f6f"}
 {"\\x05000000","\\x09626172"}
(2 rows)

postgres=# VACUUM sometable ;
VACUUM


postgres=# SELECT * FROM heap_page_items(get_raw_page('sometable',0));
 lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid |       t_data
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+--------------------
  1 |      2 |        2 |      0 |        |        |          |        |             |            |        |        |       |
  2 |   8160 |        1 |     32 |    627 |      0 |        0 | (0,2)  |       32770 |      10498 |     24 |        |       | \x0500000009626172
(2 rows)

postgres=# SELECT * FROM page_header(get_raw_page('sometable',0));
    lsn    | checksum | flags | lower | upper | special | pagesize | version | prune_xid
-----------+----------+-------+-------+-------+---------+----------+---------+-----------
 0/3091F50 |        0 |     4 |    32 |  8160 |    8192 |     8192 |       4 |         0
(1 row)

Dump a page from a relation (using psql):

\copy (SELECT get_raw_page(oid::regclass::text, 12345) FROM pg_class WHERE relfilenode = '678910') to '/tmp/raw.data' (FORMAT binary);

Categories

Contrib module, Data consistency and forensics, PostgreSQL internals, Storage

See also

pg_freespacemap, pgstattuple, pg_visibility