pageinspect
The pageinspect module provides functions that allow you to examine the contents of database pages at a low level, which is useful for debugging purposes. All of these functions can only be used by superusers.
#22.1. General Functions
get_raw_page(relname text, fork text, blkno int) returns bytea
get_raw_page reads the specified block of the named relation and returns a copy as a bytea value. This allows obtaining a single time-consistent copy of the block. For the main data fork, fork should be 'main', for the free space map 'fsm', for the visibility map 'vm', and for the initialization fork 'init'.
get_raw_page(relname text, blkno int) returns bytea
A shorthand version of get_raw_page for reading the main fork. Equivalent to get_raw_page(relname, 'main', blkno)
page_header(page bytea) returns record
page_header displays the common fields of all Halo heap and index pages.
A page image obtained with get_raw_page should be passed as the argument. For example:
test=## SELECT * FROM page_header(get_raw_page('pg_class', 0));
lsn | checksum | flags | lower | upper | special | pagesize | version | prune_xid
------------+----------+-------+-------+-------+---------+----------+---------+-----------
0/71936378 | 0 | 1 | 212 | 2736 | 8192 | 8192 | 4 | 1279
(1 row)
The returned columns correspond to the fields in the PageHeaderData structure.
The checksum field is the checksum stored in the page, which may be incorrect if the page is corrupted. If data checksums are not enabled for this instance, the stored value is meaningless.
page_checksum(page bytea, blkno int4) returns smallint
page_checksum computes a checksum for the page as if it were placed at the given block number.
A page image obtained with get_raw_page should be passed as the argument. For example:
test=## SELECT page_checksum(get_raw_page('pg_class', 0), 0);
page_checksum
---------------
17728
(1 row)
Note: The checksum depends on the block number, so the matching block number should be passed (unless debugging).
The checksum computed by this function can be compared with the checksum result field from the page_header function. If data checksums are enabled for this instance, the two values should be equal.
fsm_page_contents(page bytea) returns text
fsm_page_contents displays the internal node structure of an FSM page. For example:
=## SELECT fsm_page_contents(get_raw_page('pg_class', 'fsm', 0));
The output is a multi-line string, with one line per node in the binary tree within the page. Only non-zero nodes are printed. The "next" pointer, which points to the next slot to be returned within the page, is also printed.
2. Heap Functions
heap_page_items(page bytea) returns setof record
heap_page_items displays all line pointers on a heap page. For line pointers that are in use, the tuple header and raw tuple data are also displayed. Tuples are displayed regardless of whether they are visible to the MVCC snapshot at the time the raw page was copied.
A heap page image obtained with get_raw_page should be passed as the argument. For example:
test=## SELECT * FROM heap_page_items(get_raw_page('pg_class', 0));
The heap_tuple_infomask_flags function can be used to unpack the t_infomask and t_infomask2 flag bits for heap tuples.
tuple_data_split(rel_oid oid, t_data bytea, t_infomask integer, t_infomask2 integer, t_bits text [, do_detoast bool]) returns bytea[]
tuple_data_split splits tuple data into attributes in the same way as the backend does internally.
test=## SELECT tuple_data_split('pg_class'::regclass, t_data, t_infomask,
t_infomask2, t_bits) FROM heap_page_items(get_raw_page('pg_class', 0));
This function should be called with the same parameters as the return attributes of heap_page_items.
If do_detoast is true, attributes will be de-TOASTed as needed. The default is false.
heap_page_item_attrs(page bytea, rel_oid regclass [, do_detoast bool]) returns
setof record
heap_page_item_attrs is equivalent to heap_page_items, except that it returns the raw tuple data as an array of attributes, which will be de-TOASTed if do_detoast is true (default is false).
A heap page image obtained with get_raw_page should be passed as the argument. For example:
test=## SELECT * FROM heap_page_item_attrs(get_raw_page('pg_class', 0),
'pg_class'::regclass);
heap_tuple_infomask_flags(t_infomask integer, t_infomask2 integer) returns record
heap_tuple_infomask_flags decodes the t_infomask and t_infomask2 returned by heap_page_items into a set of human-readable arrays of flag names, one column for all flags and one column for combined flags. For example:
test=## SELECT t_ctid, raw_flags, combined_flags FROM heap_page_items(get_raw_page('pg_class', 0)),LATERAL heap_tuple_infomask_flags(t_infomask, t_infomask2) WHERE t_infomask IS NOT NULL OR t_infomask2 IS NOT NULL;
This function should be called with the same parameters as the return attributes of heap_page_items.
Combined flags are displayed for source-level macros that account for multiple raw bit values, such as HEAP_XMIN_FROZEN.
3. B-Tree Functions
bt_metap(relname text) returns record
bt_metap returns information about a B-tree index metapage. For example:
test=## SELECT * FROM bt_metap('pg_cast_oid_index');
magic | version | root | level | fastroot | fastlevel | last_cleanup_num_delpages | last_cleanup_num_tuples | allequ
alimage
--------+---------+------+-------+----------+-----------+---------------------------+-------------------------+---------------
340322 | 4 | 1 | 0 | 1 | 0 | 0 | -1 | f
(1 row)
bt_page_stats(relname text, blkno int) returns record
bt_page_stats returns summary information about a single page of a B-tree index. For example:
test=## SELECT * FROM bt_page_stats('pg_cast_oid_index', 1);
blkno | type | live_items | dead_items | avg_item_size | page_size | free_size | btpo_prev | btpo_next | btpo_level | btpo_flags
-------+------+------------+------------+---------------+-----------+-----------+-----------+-----------+------------+------------
1 | l | 272 | 0 | 16 | 8192 | 2708 | 0 | 0 | 0 | 3
(1 row)
bt_page_items(relname text, blkno int) returns setof record
bt_page_items returns detailed information about all items on a B-tree index page. For example:
test=## SELECT itemoffset, ctid, itemlen, nulls, vars, data, dead, htid,
tids[0:2] AS some_tids
FROM bt_page_items('tenk2_hundred', 5);
itemoffset | ctid | itemlen | nulls | vars | data |
dead | htid | some_tids
------------+-----------+---------+-------+------+-------------------------+------+--------+---------------------
1 | (16,1) | 16 | f | f | 30 00 00 00 00 00 00 00 |
| |
2 | (16,8292) | 616 | f | f | 24 00 00 00 00 00 00 00 |
f | (1,6) | {"(1,6)","(10,22)"}
3 | (16,8292) | 616 | f | f | 25 00 00 00 00 00 00 00 |
f | (1,18) | {"(1,18)","(4,22)"}
4 | (16,8292) | 616 | f | f | 26 00 00 00 00 00 00 00 |
f | (4,18) | {"(4,18)","(6,17)"}
5 | (16,8292) | 616 | f | f | 27 00 00 00 00 00 00 00 |
f | (1,2) | {"(1,2)","(1,19)"}
6 | (16,8292) | 616 | f | f | 28 00 00 00 00 00 00 00 |
f | (2,24) | {"(2,24)","(4,11)"}
7 | (16,8292) | 616 | f | f | 29 00 00 00 00 00 00 00 |
f | (2,17) | {"(2,17)","(11,2)"}
8 | (16,8292) | 616 | f | f | 2a 00 00 00 00 00 00 00 |
f | (0,25) | {"(0,25)","(3,20)"}
9 | (16,8292) | 616 | f | f | 2b 00 00 00 00 00 00 00 |
f | (0,10) | {"(0,10)","(0,14)"}
10 | (16,8292) | 616 | f | f | 2c 00 00 00 00 00 00 00 |
f | (1,3) | {"(1,3)","(3,9)"}
11 | (16,8292) | 616 | f | f | 2d 00 00 00 00 00 00 00 |
f | (6,28) | {"(6,28)","(11,1)"}
12 | (16,8292) | 616 | f | f | 2e 00 00 00 00 00 00 00 |
f | (0,27) | {"(0,27)","(1,13)"}
13 | (16,8292) | 616 | f | f | 2f 00 00 00 00 00 00 00 |
f | (4,17) | {"(4,17)","(4,21)"}
(13 rows)
This is a B-tree leaf page. All tuples pointing to the table are posting list tuples (all of these tuples together store 100 6-byte TIDs). There is also a "high key" tuple at itemoffset number 1. The ctid is used to store encoded information for each tuple in this example, although leaf page tuples typically store the heap TID directly in the ctid field, rather than having tids as a list of TIDs stored as a posting list.
In internal pages (not shown), the block number portion of ctid is a "downlink", which is the block number of another page within the index itself. The offset portion of ctid (the second number) stores encoded information about the tuple, for example, the number of columns present (suffix truncation may have removed unnecessary suffix columns). Truncated columns are treated as having the value "minus infinity".
htid shows the heap TID of the tuple, regardless of the underlying tuple representation. This value may match ctid, or can be decoded from the alternative representations used by posting list tuples and tuples from internal pages. Tuples in internal pages typically truncate implementation-level heap TID columns, which is represented as a NULL htid value.
Note: The first item on any non-rightmost page (pages with a non-zero value in the btpo_next field) is the page's "high key", meaning its data serves as an upper bound for all items on that page, and its ctid field does not point to another block. Furthermore, on internal pages, the first real data item (the first item that is not a high key) reliably has every column truncated, leaving no actual values in its data field. However, such an item does have a downlink in its ctid field.
For more details about B-tree index structure.
bt_page_items(page bytea) returns setof record
You can also pass a page as a bytea value to bt_page_items. A page image obtained with get_raw_page should be passed as the argument. Thus, the previous example can be rewritten as:
test=## SELECT itemoffset, ctid, itemlen, nulls, vars, data, dead, htid,
tids[0:2] AS some_tids
FROM bt_page_items(get_raw_page('tenk2_hundred', 5));
itemoffset | ctid | itemlen | nulls | vars | data |
dead | htid | some_tids
------------+-----------+---------+-------+------+-------------------------+------+--------+---------------------
1 | (16,1) | 16 | f | f | 30 00 00 00 00 00 00 00 |
| |
2 | (16,8292) | 616 | f | f | 24 00 00 00 00 00 00 00 |
f | (1,6) | {"(1,6)","(10,22)"}
3 | (16,8292) | 616 | f | f | 25 00 00 00 00 00 00 00 |
f | (1,18) | {"(1,18)","(4,22)"}
4 | (16,8292) | 616 | f | f | 26 00 00 00 00 00 00 00 |
f | (4,18) | {"(4,18)","(6,17)"}
5 | (16,8292) | 616 | f | f | 27 00 00 00 00 00 00 00 |
f | (1,2) | {"(1,2)","(1,19)"}
6 | (16,8292) | 616 | f | f | 28 00 00 00 00 00 00 00 |
f | (2,24) | {"(2,24)","(4,11)"}
7 | (16,8292) | 616 | f | f | 29 00 00 00 00 00 00 00 |
f | (2,17) | {"(2,17)","(11,2)"}
8 | (16,8292) | 616 | f | f | 2a 00 00 00 00 00 00 00 |
f | (0,25) | {"(0,25)","(3,20)"}
9 | (16,8292) | 616 | f | f | 2b 00 00 00 00 00 00 00 |
f | (0,10) | {"(0,10)","(0,14)"}
10 | (16,8292) | 616 | f | f | 2c 00 00 00 00 00 00 00 |
f | (1,3) | {"(1,3)","(3,9)"}
11 | (16,8292) | 616 | f | f | 2d 00 00 00 00 00 00 00 |
f | (6,28) | {"(6,28)","(11,1)"}
12 | (16,8292) | 616 | f | f | 2e 00 00 00 00 00 00 00 |
f | (0,27) | {"(0,27)","(1,13)"}
13 | (16,8292) | 616 | f | f | 2f 00 00 00 00 00 00 00 |
f | (4,17) | {"(4,17)","(4,21)"}
(13 rows)
All other details are the same as explained in the previous item.
C.22.4. BRIN Functions
brin_page_type(page bytea) returns text
brin_page_type returns the page type of a given BRIN index page, or throws an error if the page is not a valid BRIN page. For example:
test=## SELECT brin_page_type(get_raw_page('brinidx', 0));
brin_page_type
----------------
meta
brin_metapage_info(page bytea) returns record
brin_metapage_info returns various information about a BRIN index metapage. For example:
test=## SELECT * FROM brin_metapage_info(get_raw_page('brinidx', 0));
magic | version | pagesperrange | lastrevmappage
------------+---------+---------------+----------------
0xA8109CFA | 1 | 4 | 2
brin_revmap_data(page bytea) returns setof tid
brin_revmap_data returns a list of tuple identifiers from a BRIN index range map page. For example:
test=## SELECT * FROM brin_revmap_data(get_raw_page('brinidx', 2)) LIMIT 5;
pages
---------
(6,137)
(6,138)
(6,139)
(6,140)
(6,141)
brin_page_items(page bytea, index oid) returns setof record
brin_page_items returns the data stored in BRIN data pages. For example:
test=## SELECT * FROM brin_page_items(get_raw_page('brinidx', 5),
'brinidx')
ORDER BY blknum, attnum LIMIT 6;
itemoffset | blknum | attnum | allnulls | hasnulls | placeholder | value
------------+--------+--------+----------+----------+-------------+----
137 | 0 | 1 | t | f | f |
137 | 0 | 2 | f | f | f | {1 .. 88}
138 | 4 | 1 | t | f | f |
138 | 4 | 2 | f | f | f | {89 ..176}
139 | 8 | 1 | t | f | f |
139 | 8 | 2 | f | f | f | {177 ..264}
#The returned columns correspond to the fields in the BrinMemTuple and BrinValues structures.
5. GIN Functions
gin_metapage_info(page bytea) returns record
gin_metapage_info returns information about a GIN index metapage. For example:
t
est=## SELECT * FROM gin_metapage_info(get_raw_page('gin_index', 0));
-[ RECORD 1 ]----+-----------
pending_head | 4294967295
pending_tail | 4294967295
tail_free_size | 0
n_pending_pages | 0
n_pending_tuples | 0
n_total_pages | 7
n_entry_pages | 6
n_data_pages | 0
n_entries | 693
version | 2
gin_page_opaque_info(page bytea) returns record
gin_page_opaque_info returns information about the opaque area of a GIN index, such as the page type. For example:
test=## SELECT * FROM gin_page_opaque_info(get_raw_page('gin_index', 2));
rightlink | maxoff | flags
-----------+--------+------------------------
5 | 0 | {data,leaf,compressed}
(1 row)
gin_leafpage_items(page bytea) returns setof record
gin_leafpage_items returns information about the data stored in a GIN leaf page. For example:
test=## SELECT first_tid, nbytes, tids[0:5] AS some_tids
FROM gin_leafpage_items(get_raw_page('gin_test_idx', 2));
first_tid | nbytes | some_tids
-----------+--------+-----------------------------------------------------
(8,41) | 244 | {"(8,41)","(8,43)","(8,44)","(8,45)","(8,46)"}
(10,45) | 248 | {"(10,45)","(10,46)","(10,47)","(10,48)","(10,49)"}
(12,52) | 248 | {"(12,52)","(12,53)","(12,54)","(12,55)","(12,56)"}
(14,59) | 320 | {"(14,59)","(14,60)","(14,61)","(14,62)","(14,63)"}
(167,16) | 376 |
{"(167,16)","(167,17)","(167,18)","(167,19)","(167,20)"}
(170,30) | 376 |
{"(170,30)","(170,31)","(170,32)","(170,33)","(170,34)"}
(173,44) | 197 |
{"(173,44)","(173,45)","(173,46)","(173,47)","(173,48)"}
(7 rows)
6. Hash Functions
hash_page_type(page bytea) returns text
hash_page_type returns the page type of a given HASH index page. For example:
test=## SELECT hash_page_type(get_raw_page('con_hash_index', 0));
hash_page_type
----------------
metapage
hash_page_stats(page bytea) returns setof record
hash_page_stats returns information about a HASH index page. For example:
test=## SELECT * FROM hash_page_stats(get_raw_page('con_hash_index', 1));
-[ RECORD 1 ]---+-----------
live_items | 407
dead_items | 0
page_size | 8192
free_size | 8
hasho_prevblkno | 4096
hasho_nextblkno | 8474
hasho_bucket | 0
hasho_flag | 66
hasho_page_id | 65408
hash_page_items(page bytea) returns setof record
hash_page_items returns information about the data stored in a bucket page or overflow page of a HASH index. For example:
test=## SELECT * FROM hash_page_items(get_raw_page('con_hash_index', 1)) LIMIT
5;
itemoffset | ctid | data
------------+-----------+------------
1 | (899,77) | 1053474816
2 | (897,29) | 1053474816
3 | (894,207) | 1053474816
4 | (892,159) | 1053474816
5 | (890,111) | 1053474816
hash_bitmap_info(index oid, blkno int) returns record
hash_bitmap_info returns the status of a particular page in a HASH index bitmap. For example:
=## SELECT * FROM hash_bitmap_info('con_hash_index', 2052);
bitmapblkno | bitmapbit | bitstatus
-------------+-----------+-----------
65 | 3 |
thash_metapage_info(page bytea) returns record
hash_metapage_info returns information stored in the metapage of a HASH index. For example:
=## SELECT magic, version, ntuples, ffactor, bsize, bmsize, bmshift,
-## maxbucket, highmask, lowmask, ovflpoint, firstfree, nmaps, procid,
-## regexp_replace(spares::text, '(,0)*}', '}') as spares,
-## regexp_replace(mapp::text, '(,0)*}', '}') as mapp
-## FROM hash_metapage_info(get_raw_page('con_hash_index', 0));
-[ RECORD 1 ]-------------------------------------------------------------------------------
magic | 105121344
version | 4
ntuples | 500500
ffactor | 40
bsize | 8152
bmsize | 4096
bmshift | 15
maxbucket | 12512
highmask | 16383
lowmask | 8191
ovflpoint | 28
firstfree | 1204
nmaps | 1
procid | 450
spares | {0,0,0,0,0,0,1,1,1,1,1,1,1,1,3,4,4,4,45,55,58,59,
508,567,628,704,1193,1202,1204}
mapp | {65}