一、pageinspect 扩展模块
pageinspect模块提供函数让你从低层次观察数据库页面的内容，对于故障排查及数据库底层数据存储的认知很有帮助。
1.7版本的pageinspect 扩展提供以下函数：
test=# \dx pageinspect
List of installed extensions
Name | Version | Schema | Description
-------------+---------+--------+-------------------------------------------------------
pageinspect | 1.7 | public | inspect the contents of database pages at a low level
(1 row)
test=# \dx+ pageinspect
Objects in extension "pageinspect"
Object description
-------------------------------------------------------------------
function brin_metapage_info(bytea)
function brin_page_items(bytea,regclass)
function brin_page_type(bytea)
function brin_revmap_data(bytea)
function bt_metap(text)
function bt_page_items(bytea)
function bt_page_items(text,integer)
function bt_page_stats(text,integer)
function fsm_page_contents(bytea)
function get_raw_page(text,integer)
function get_raw_page(text,text,integer)
function gin_leafpage_items(bytea)
function gin_metapage_info(bytea)
function gin_page_opaque_info(bytea)
function hash_bitmap_info(regclass,bigint)
function hash_metapage_info(bytea)
function hash_page_items(bytea)
function hash_page_stats(bytea)
function hash_page_type(bytea)
function heap_page_item_attrs(bytea,regclass)
function heap_page_item_attrs(bytea,regclass,boolean)
function heap_page_items(bytea)
function page_checksum(bytea,integer)
function page_header(bytea)
function tuple_data_split(oid,bytea,integer,integer,text)
function tuple_data_split(oid,bytea,integer,integer,text,boolean)
(26 rows)一、通用函数
1、get_raw_page 函数
get_raw_page(relname text,fork text,blkno int) 返回bytea -- fork表示分支，如果只读取主分支，可以用第2个函数。
get_raw_page(relname text,blkno int) 返回bytea
get_raw_page读取指定块，返回bytea值。如：读取t1表的第 0 块
test=# select get_raw_page('t1',0);
......
\x040000005062860600000000000000000000000000000000000000000000000000000000000000000000e800500100200520000000000000309f9a01509eb201a09d5801f09c5801409c5801909b5801e09a5801309a580180995801b0989a0100985801
50975801a0965801f09558014095580190945801e09358013093580180925801d09158012091580170905801c08f5801108f5801608e5801b08d5801008d5801508c5801a08b5801f08a5801408a580190895801e08858013088580180875801d0865801208
6580170855801c08458011084580160835801b082580100825801508158010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
PS：每个关系（relation） 可能会有四种分支，0号分支main为关系数据文件本体，1号分支fsm保存了main分支中空闲空间的信息，2号分支vm保存了main分支中可见性的信息，3号分支init是很少见的特殊分支，通常用于不被日志记录（unlogged）的表与索引。fork 值可以是main , fsm , vm , init 。
2、page_header 函数
test=# select * from page_header(get_raw_page('t1',0));
lsn | checksum | flags | lower | upper | special | pagesize | version | prune_xid
------------+-----------+-------+-------+-------+---------+----------+---------+-----------
4/6866250 | 31424 | 0 | 232 | 336 | 8192 | 8192 | 5 | 0
(1 row)test=# select (100*(upper - lower)/pagesize::float8)::integer as free_pct from page_header(get_raw_page('t1',0));
free_pct
----------
1
(1 row)
主要字段含义：
lsn - 标识此页面的最后更改的 xlog 记录。
checksum - 页面校验和。
flags - 标志位。
lower - 到可用空间开始的偏移量，指向最后一个指针末尾。
upper - 到可用空间末尾的偏移量，指向最新元组的起始位置。
special - 特殊空间开始的偏移量。
pagesize_version - 字节大小和页面布局版本号。
prune_xid - 页面上可能可修剪的元组中最旧的 XID。
PS：checksum域时存放在页面中的校验和，如果页面被损坏它可能是不正确的值。查看实例是否启用数据校验和（1是开启，0是关闭），如果没有启用存储这个值没有意义。
3、page_checksum 函数
page_checksum为页面计算校验和。用这个函数计算的校验和可以和函数page_header的结果域checksum进行比较。如果为这个实例启用了数据校验和，则两个值应该相等。
page_checksum(page bytea,blkno int) returns smallint
使用例子：
test=# select a.checksum as head_checksum , page_checksum from page_header(get_raw_page('t1',0)) as a, page_checksum(get_raw_page('t1',0),0) as page_checksum;
head_checksum | page_checksum
------------------+---------------
15008 | 1696
(1 row)
二者值是不一致的，是因为没有开启data_checksums参数，header_checksum 值是无意义的。
4、tuple_data_split 函数
tuple_data_split 将元组数据拆解成属性。
tuple_data_split(rel_oid oid,t_data bytea,t_infomask integer,t_informask2 integer,t_bits text[,do_detoast bool)] returns bytea[]
实际例子：返回每个字段的二进制流
test=# select tuple_data_split('t1'::regclass,t_data,t_infomask,t_infomask2,t_bits) from heap_page_items(get_raw_page('t1',0)) limit 1;
tuple_data_split
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{"\\x3b0a0000","\\x70675f73746174697374696300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000","\\x0b000000","\\xf12e0000","\\x00000000","\\x0a000000","\\x02000000","\\x3b0a0000","\\x00000000","\\x25000000","\\x00c00744","\\x25000000","\\x180b0000","\\x01","\\x00","\\x70","\\x72","\\x1f00","\\x0000","\\x00","\\x00","\\x00","\\x00","\\x00","\\x01","\\x6e","\\x00","\\x00000000","\\x82030000","\\x01000000","\\x4301000000000000000904000001000000010000000a0000000a0000007f000000",NULL,NULL,NULL}
5、fsm_page_contents 函数
fsm_page_contents显示FSM页面的内部节点结构。
test=# select * from fsm_page_contents(get_raw_page('t1','fsm',0));
fsm_page_contents
-------------------
0: 157 +
1: 157 +
3: 157 +
7: 157 +
15: 157 +
31: 157 +
63: 157 +
127: 157 +
255: 157 +
511: 157 +
1023: 157 +
2047: 157 +
4095: 157 +
fp_next_slot: 0 +
三、HEAP 相关函数
6、heap_page_items 函数
heap_page_items(page bytea) returns setof record
heap_page_items显示一个堆页面上所有的行指针。不管元组对拷贝原始页面的MVCC快照是否可见，他们都会被显示。
test=# select * from heap_page_items(get_raw_page('t1',0)) limit 1;
-[ RECORD 1 ]--------------------------------------------------------------------------------------------------------
lp | 1
lp_off | 7984
lp_flags | 1
lp_len | 205
t_xmin | 3250
t_xmax | 0
t_field3 | 38
t_ctid | (0,1)
t_infomask2 | 33
t_infomask | 2307
t_hoff | 32
t_bits | 1111111111111111111111111111111000000000
t_oid |
t_data | \x3b0a000070675f737461746973746963000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000b000000f12e0000000000000a000000020000003b0a0000000000002500000000c0074425000000180b0000010070721f0000000000000000016e000000000082030000010000004301000000000000000904000001000000010000000a0000000a0000007f000000
具体含义如下：
lp_off 到元组的偏移量（从页面开始）
lp_flags 行指针状态: 0 未使用 1 已使用 2 HOT 重定向 3 死元组
lp_len 元组的字节长度
t_xmin inserting xact ID
t_xmax deleting or locking xact ID
t_field3
t_ctid current TID of this or newer tuple (or a speculative insertion token)
t_infomask2 number of attributes + various flags
t_infomask various flag bits 事物提交状态
t_hoff sizeof header incl. bitmap, padding
t_bits bitmap of NULLs
t_oid create table with OID
t_data actual data
PS: t_infomask 需要重点理解
7、heap_page_item_attrs 函数
与heap_page_item 的差异是 t_attrs 是每个字段的值。
test=# select * from heap_page_item_attrs(get_raw_page('t1',0),'t1'::regclass) limit 1;
-[ RECORD 1 ]-----------------------------------------------------------------------------
lp | 1
lp_off | 7984
lp_flags | 1
lp_len | 205
t_xmin | 3250
t_xmax | 0
t_field3 | 38
t_ctid | (0,1)
t_infomask2 | 33
t_infomask | 2307
t_hoff | 32
t_bits | 1111111111111111111111111111111000000000
t_oid |
t_attrs | {"\\x3b0a0000","\\x70675f73746174697374696300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000","\\x0b000000","\\xf12e0000","\\x00000000","\\x0a000000","\\x02000000","\\x3b0a0000","\\x00000000","\\x25000000","\\x00c00744","\\x25000000","\\x180b0000","\\x01","\\x00","\\x70","\\x72","\\x1f00","\\x0000","\\x00","\\x00","\\x00","\\x00","\\x00","\\x01","\\x6e","\\x00","\\x00000000","\\x82030000","\\x01000000","\\x4301000000000000000904000001000000010000000a0000000a0000007f000000",NULL,NULL,NULL}
四、BTree 索引相关函数
8、bt_metap 函数
bt_metap返回关于一个B树索引元页的信息。
bt_metap(relname text) returns record
实际例子：idx_t1_relname 是索引对象
test=# select * from bt_metap('idx_t1_relname');
-[ RECORD 1 ]-----------+-------
magic | 340322
version | 4
root | 126
level | 2 --索引层级，相当于3层
fastroot | 126
fastlevel | 2
oldest_xact | 0
last_cleanup_num_tuples | -1
9、bt_page_stats 函数
bt_page_stats返回有关B-树索引单一页面的总计信息。
bt_page_stats(relname text,blkno int) returns record
实际例子：
test=# select * from bt_page_stats('idx_t1_relname',3);
-[ RECORD 1 ]-+-----
blkno | 3
type | i
live_items | 121
dead_items | 0
avg_item_size | 43
page_size | 8192
free_size | 2424
btpo_prev | 0
btpo_next | 125
btpo | 1
btpo_flags | 0
10、bt_page_items 函数
bt_page_items返回一个B-树索引页面上的所有细节信息
bt_page_items(relname text,blkno int) returns setof record
实际例子：
test=# select * from bt_page_items('idx_t1_relname',3) limit 3;
-[ RECORD 1 ]-----------------------------------------------------------------------------------------------
itemoffset | 1
ctid | (122,4097)
itemlen | 40
nulls | f
vars | t
data | 5f 70 67 5f 75 73 65 72 5f 6d 61 70 70 69 6e 67 73 00 00 00 00 00 00 00 00 00 00 00 34 96 25 00
-[ RECORD 2 ]-----------------------------------------------------------------------------------------------
itemoffset | 2
ctid | (1,0)
itemlen | 8
nulls | f
vars | f
data |
-[ RECORD 3 ]-----------------------------------------------------------------------------------------------
itemoffset | 3
ctid | (2,4097)
itemlen | 40
nulls | f
vars | t
data | 49 6e 68 65 72 69 74 73 5f 72 65 6c 61 74 69 6f 6e 5f 69 6e 64 65 78 00 00 00 00 00 b9 0f 15 00
五、BRIN 索引相关的函数
11、brin_page_type 函数
brin_page_type返回一个给定的BRIN索引页面的页面类型，如果该页面不是一个合法的BRIN页面则抛出错误。
实际例子：
test=# create index brin_t1_relname on t1 using brin (relname);
CREATE INDEX
test=# select brin_page_type(get_raw_page('brin_t1_relname',0));
-[ RECORD 1 ]--+-----
brin_page_type | meta
test=# select brin_page_type(get_raw_page('brin_t1_relname',1));
-[ RECORD 1 ]--+-------
brin_page_type | revmap
12、brin_metapage_info 函数
brin_metapage_info返回有关一个BRIN索引页面的详细信息。
test=# select * from brin_metapage_info(get_raw_page('brin_t1_relname',0));
-[ RECORD 1 ]--+-----------
magic | 0xA8109CFA
version | 1
pagesperrange | 128
lastrevmappage | 1
13、brin_revmap_data 函数
brin_revmap_data返回一个BRIN索引范围映射页面中元组标识符的表。
test=# select * from brin_revmap_data(get_raw_page('brin_t1_relname',1)) limit 2;
pages
-------
(2,1)
(2,2)
(2 rows)
14、brin_page_items 函数
brin_page_items返回存在BRIN数据页面中存储的数据。
brin_page_items(page bytea,index oid) returns setof record
test=# select * from brin_page_items(get_raw_page('brin_t1_relname',2),'brin_t1_relname');
itemoffset | blknum | attnum | allnulls | hasnulls | placeholder | value
------------+--------+--------+----------+----------+-------------+------------------------------------
1 | 0 | 1 | f | f | f | {Inherits_relation_index .. views}
2 | 128 | 1 | f | f | f | {Inherits_relation_index .. views}
3 | 256 | 1 | f | f | f | {Inherits_relation_index .. views}
4 | 384 | 1 | f | f | f | {Inherits_relation_index .. views}
5 | 512 | 1 | f | f | f | {Inherits_relation_index .. views}
6 | 640 | 1 | f | f | f | {Inherits_relation_index .. views}
7 | 768 | 1 | f | f | f | {Inherits_relation_index .. views}
8 | 896 | 1 | f | f | f | {Inherits_relation_index .. views}
9 | 1024 | 1 | f | f | f | {Inherits_relation_index .. views}
10 | 1152 | 1 | f | f | f | {Inherits_relation_index .. views}
11 | 1280 | 1 | f | f | f | {Inherits_relation_index .. views}
12 | 1408 | 1 | f | f | f | {Inherits_relation_index .. views}
13 | 1536 | 1 | f | f | f | {Inherits_relation_index .. views}
14 | 1664 | 1 | f | f | f | {Inherits_relation_index .. views}
15 | 1792 | 1 | f | f | f | {Inherits_relation_index .. views}
16 | 1920 | 1 | f | f | f | {Inherits_relation_index .. views}
六、GIN 相关函数
15、gin_metapage_info 函数
gin_metapage_info返回有关一个GIN索引元页的信息。
test=# create index gin_t1_relname on t1 using gin (name gin_trgm_ops);
CREATE INDEX
test=# insert into t1 select generate_series(1,1000),md5(random());
INSERT 0 1000
test=# \x
Expanded display is on.
test=# select * from gin_metapage_info(get_raw_page('gin_t1_relname',0));
-[ RECORD 1 ]----+-----------
pending_head | 4294967295
pending_tail | 4294967295
tail_free_size | 0
n_pending_pages | 0
n_pending_tuples | 0
n_total_pages | 2
n_entry_pages | 1
n_data_pages | 0
n_entries | 0
version | 2
16、gin_page_opaque_info 函数
gin_page_opaque_info返回有关一个GIN索引不透明区域的信息，如页面类型等。
test=# select * from gin_page_opaque_info(get_raw_page('gin_t1_relname',0));
-[ RECORD 1 ]---------
rightlink | 4294967295
maxoff | 0
flags | {meta}
test=# select * from gin_page_opaque_info(get_raw_page('gin_t1_relname',1));
-[ RECORD 1 ]---------
rightlink | 4294967295
maxoff | 0
flags | {}
test=# select * from gin_page_opaque_info(get_raw_page('gin_t1_relname',2));
-[ RECORD 1 ]--------
rightlink | 3
maxoff | 12
flags | {deleted}
17、gin_leafpage_items 函数
gin_leafpage_items返回有关储存在一个GIN叶子页面中的数据信息。
七、hash 索引相关函数
18、hash_page_type 函数
hash_page_type返回给定的hash索引页面的页面类型
test=# create index hash_t1_relname on t1 using hash (name);
CREATE INDEX
test=# select * from hash_page_type(get_raw_page('hash_t1_relname',0));
-[ RECORD 1 ]--+---------
hash_page_type | metapage
test=# select * from hash_page_type(get_raw_page('hash_t1_relname',1));
-[ RECORD 1 ]--+-------
hash_page_type | bucket
19、hash_page_stats 函数
hash_page_stats返回有关一个HASH索引的桶页或者溢出页的信息。
test=# select * from hash_page_stats(get_raw_page('hash_t1_relname',1));
-[ RECORD 1 ]---+-----------
live_items | 253
dead_items | 0
page_size | 8192
free_size | 3056
hasho_prevblkno | 3583
hasho_nextblkno | 4294967295
hasho_bucket | 0
hasho_flag | 2
hasho_page_id | 65408
20、hash_page_items 函数
hash_page_items返返回有关一个HASH索引的桶页或者溢出页中存储的数据的信息。
test=# select * from hash_page_items(get_raw_page('hash_t1_relname',1)) limit 5;
itemoffset | ctid | data
------------+------------+----------
1 | (2979,7) | 5754880
2 | (6082,118) | 6430720
3 | (7074,48) | 19689472
4 | (2963,77) | 24383488
5 | (6417,68) | 63070208
21、hash_bitmap_info 函数
hash_bitmap_info返回HASH所以一个特定溢出页在位图页中的位的状态
22、hash_metapage_info 函数
hash_metapage_info返回一个HASH索引的元页中存放的信息。
test=# select * from hash_metapage_info(get_raw_page('hash_t1_relname',0));
-[ RECORD 1 ]-----------------------------------------
magic | 105121344
version | 4
ntuples | 1001000
ffactor | 307
bsize | 8120
bmsize | 4096
bmshift | 15
maxbucket | 3583
highmask | 4095
lowmask | 2047
ovflpoint | 20
firstfree | 513
nmaps | 1
procid | 400
spares | {0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,513,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0}
mapp | {3585,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0}
