Use pageinspect EXTENSION view PostgreSQL Page’s raw infomation

25 minute read

背景

之前介绍过一个pg_filedump的外部命令,是用来查看PostgreSQL数据库存储底层的信息的。

链接:

http://blog.163.com/digoal@126/blog/static/163877040201142610215685/

今天再介绍一个PostgreSQL的EXTENSION,也可以用来查看PostgreSQL数据库存储底层的东西。

digoal=> \c digoal postgres  
You are now connected to database "digoal" as user "postgres".  
digoal=# create extension pageinspect;  
CREATE EXTENSION  
Time: 4.259 ms  
digoal=# \c digoal digoal  
You are now connected to database "digoal" as user "digoal".  
digoal=> insert into tbl_user_info select generate_series(1,10000),'zhou','digoal','sky-mobi';  
INSERT 0 10000  
Time: 19.679 ms  
digoal=> select max(ctid) from tbl_user_info;  
   max     
---------  
 (73,72)  
(1 row)  

74个PAGE

注意只有超级用户可以执行,普通用户执行将报权限不足的错误。

digoal=>             select * from get_raw_page('tbl_user_info',0);  
ERROR:  must be superuser to use raw functions  

获得一个PAGE的bytea内容,可以使用get_raw_page函数。bytea的输出可以是escape或hex模式。

digoal=> \c digoal postgres  
You are now connected to database "digoal" as user "postgres".  
digoal=# set bytea_output='hex';  
SET  
digoal=# select * from get_raw_page('tbl_user_info',0);  

返回结果类似如下

此处省略N字

0000000550700000000000000000000000000000600040002091800060000000b7a686f750f6469676f616c13736b792d6d6f6269000000000000005507000000000  
00000000000000000000500040002091800050000000b7a686f750f6469676f616c13736b792d6d6f626900000000000000550700000000000000000000000000000  
400040002091800040000000b7a686f750f6469676f616c13736b792d6d6f62690000000000000055070000000000000000000000000000030004000209180003000  
0000b7a686f750f6469676f616c13736b792d6d6f626900000000000000550700000000000000000000000000000200040002091800020000000b7a686f750f64696  
76f616c13736b792d6d6f626900000000000000550700000000000000000000000000000100040002091800010000000b7a686f750f6469676f616c13736b792d6d6  
f626900000000000000  
(1 row)  

修改输出格式后

digoal=# set bytea_output='escape';  
SET  

结果类似如下

\000\002\011\030\000\011\000\000\000\013zhou\017digoal\023sky-mobi\000\000\000\000\000\000\000U\007\000\000\000\000\000\000\000\000\  
000\000\000\000\000\000\010\000\004\000\002\011\030\000\010\000\000\000\013zhou\017digoal\023sky-mobi\000\000\000\000\000\000\000U\0  
07\000\000\000\000\000\000\000\000\000\000\000\000\000\000\007\000\004\000\002\011\030\000\007\000\000\000\013zhou\017digoal\023sky-  
mobi\000\000\000\000\000\000\000U\007\000\000\000\000\000\000\000\000\000\000\000\000\000\000\006\000\004\000\002\011\030\000\006\00  
0\000\000\013zhou\017digoal\023sky-mobi\000\000\000\000\000\000\000U\007\000\000\000\000\000\000\000\000\000\000\000\000\000\000\005  
\000\004\000\002\011\030\000\005\000\000\000\013zhou\017digoal\023sky-mobi\000\000\000\000\000\000\000U\007\000\000\000\000\000\000\  
000\000\000\000\000\000\000\000\004\000\004\000\002\011\030\000\004\000\000\000\013zhou\017digoal\023sky-mobi\000\000\000\000\000\00  
0\000U\007\000\000\000\000\000\000\000\000\000\000\000\000\000\000\003\000\004\000\002\011\030\000\003\000\000\000\013zhou\017digoal  
\023sky-mobi\000\000\000\000\000\000\000U\007\000\000\000\000\000\000\000\000\000\000\000\000\000\000\002\000\004\000\002\011\030\00  
0\002\000\000\000\013zhou\017digoal\023sky-mobi\000\000\000\000\000\000\000U\007\000\000\000\000\000\000\000\000\000\000\000\000\000  
\000\001\000\004\000\002\011\030\000\001\000\000\000\013zhou\017digoal\023sky-mobi\000\000\000\000\000\000\000  

这里查询到得是一个MAIN PAGE的RAW内容。

另外还可以查询fsm , vm page的内容。具体的数据结构描述可以参考源代码。

另外还有几个函数如

get_raw_page(relname text, blkno int) returns bytea  
get_raw_page(relname text, fork text, blkno int) returns bytea  

fork取值范围 main , fsm , vm

查看一个PAGE的头部信息:(数据结构参考源代码)

digoal=# select * from page_header(get_raw_page('tbl_user_info',0));  
    lsn    | tli | flags | lower | upper | special | pagesize | version | prune_xid   
-----------+-----+-------+-------+-------+---------+----------+---------+-----------  
 0/E85E330 |   1 |     0 |   568 |   576 |    8192 |     8192 |       4 |         0  
(1 row)  

0号page的lower和upper值之间剩余只有576-568=8字节 。一会我们看一下一条items+tuples的长度,已经超过8字节,因此0号PAGE不适合插入新行了。

tli 表示时间线,这个一般用于RESETXLOG后或触发PROMOTE之后WAL时间线的分辨。用pg_controldata可以看到LIN的信息。

如下:

postgres@db5-> pg_controldata   
pg_control version number:            903  
Catalog version number:               201104251  
Database system identifier:           5607124598447901567  
Database cluster state:               in production  
pg_control last modified:             Fri 27 May 2011 05:23:36 PM CST  
Latest checkpoint location:           0/E933940  
Prior checkpoint location:            0/E83EF68  
Latest checkpoint's REDO location:    0/E933940  
Latest checkpoint's TimeLineID:       1  

其他不再多做解释,还是参照源代码。

查看ITEMS的信息:

digoal=# select * from heap_page_items(get_raw_page('tbl_user_info',0));  

lp表示line point

lp_off是tuple在page中的相对位置

t_xmin,t_xmax都与实务有关

t_ctid就是表示这个LINE POINT指向的位置。

其他参考源代码.

 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   
-----+--------+----------+--------+--------+--------+----------+---------+-------------+------------+--------+--------+-------  
   1 |   8136 |        1 |     49 |   1877 |      0 |        0 | (0,1)   |           4 |       2306 |     24 |        |        
   2 |   8080 |        1 |     49 |   1877 |      0 |        0 | (0,2)   |           4 |       2306 |     24 |        |        
   3 |   8024 |        1 |     49 |   1877 |      0 |        0 | (0,3)   |           4 |       2306 |     24 |        |        
   4 |   7968 |        1 |     49 |   1877 |      0 |        0 | (0,4)   |           4 |       2306 |     24 |        |        
   5 |   7912 |        1 |     49 |   1877 |      0 |        0 | (0,5)   |           4 |       2306 |     24 |        |        
   6 |   7856 |        1 |     49 |   1877 |      0 |        0 | (0,6)   |           4 |       2306 |     24 |        |        
   7 |   7800 |        1 |     49 |   1877 |      0 |        0 | (0,7)   |           4 |       2306 |     24 |        |        
   8 |   7744 |        1 |     49 |   1877 |      0 |        0 | (0,8)   |           4 |       2306 |     24 |        |        
   9 |   7688 |        1 |     49 |   1877 |      0 |        0 | (0,9)   |           4 |       2306 |     24 |        |        
  10 |   7632 |        1 |     49 |   1877 |      0 |        0 | (0,10)  |           4 |       2306 |     24 |        |        
  11 |   7576 |        1 |     49 |   1877 |      0 |        0 | (0,11)  |           4 |       2306 |     24 |        |        
  12 |   7520 |        1 |     49 |   1877 |      0 |        0 | (0,12)  |           4 |       2306 |     24 |        |        
  13 |   7464 |        1 |     49 |   1877 |      0 |        0 | (0,13)  |           4 |       2306 |     24 |        |        
  14 |   7408 |        1 |     49 |   1877 |      0 |        0 | (0,14)  |           4 |       2306 |     24 |        |        
  15 |   7352 |        1 |     49 |   1877 |      0 |        0 | (0,15)  |           4 |       2306 |     24 |        |        
  16 |   7296 |        1 |     49 |   1877 |      0 |        0 | (0,16)  |           4 |       2306 |     24 |        |        
  17 |   7240 |        1 |     49 |   1877 |      0 |        0 | (0,17)  |           4 |       2306 |     24 |        |        
  18 |   7184 |        1 |     49 |   1877 |      0 |        0 | (0,18)  |           4 |       2306 |     24 |        |        
  19 |   7128 |        1 |     49 |   1877 |      0 |        0 | (0,19)  |           4 |       2306 |     24 |        |        
  20 |   7072 |        1 |     49 |   1877 |      0 |        0 | (0,20)  |           4 |       2306 |     24 |        |        
  21 |   7016 |        1 |     49 |   1877 |      0 |        0 | (0,21)  |           4 |       2306 |     24 |        |        
  22 |   6960 |        1 |     49 |   1877 |      0 |        0 | (0,22)  |           4 |       2306 |     24 |        |        
  23 |   6904 |        1 |     49 |   1877 |      0 |        0 | (0,23)  |           4 |       2306 |     24 |        |        
  24 |   6848 |        1 |     49 |   1877 |      0 |        0 | (0,24)  |           4 |       2306 |     24 |        |        
  25 |   6792 |        1 |     49 |   1877 |      0 |        0 | (0,25)  |           4 |       2306 |     24 |        |        
  26 |   6736 |        1 |     49 |   1877 |      0 |        0 | (0,26)  |           4 |       2306 |     24 |        |        
  27 |   6680 |        1 |     49 |   1877 |      0 |        0 | (0,27)  |           4 |       2306 |     24 |        |        
  28 |   6624 |        1 |     49 |   1877 |      0 |        0 | (0,28)  |           4 |       2306 |     24 |        |        
  29 |   6568 |        1 |     49 |   1877 |      0 |        0 | (0,29)  |           4 |       2306 |     24 |        |        
  30 |   6512 |        1 |     49 |   1877 |      0 |        0 | (0,30)  |           4 |       2306 |     24 |        |        
  31 |   6456 |        1 |     49 |   1877 |      0 |        0 | (0,31)  |           4 |       2306 |     24 |        |        
  32 |   6400 |        1 |     49 |   1877 |      0 |        0 | (0,32)  |           4 |       2306 |     24 |        |        
  33 |   6344 |        1 |     49 |   1877 |      0 |        0 | (0,33)  |           4 |       2306 |     24 |        |        
  34 |   6288 |        1 |     49 |   1877 |      0 |        0 | (0,34)  |           4 |       2306 |     24 |        |        
  35 |   6232 |        1 |     49 |   1877 |      0 |        0 | (0,35)  |           4 |       2306 |     24 |        |        
  36 |   6176 |        1 |     49 |   1877 |      0 |        0 | (0,36)  |           4 |       2306 |     24 |        |        
  37 |   6120 |        1 |     49 |   1877 |      0 |        0 | (0,37)  |           4 |       2306 |     24 |        |        
  38 |   6064 |        1 |     49 |   1877 |      0 |        0 | (0,38)  |           4 |       2306 |     24 |        |        
  39 |   6008 |        1 |     49 |   1877 |      0 |        0 | (0,39)  |           4 |       2306 |     24 |        |        
  40 |   5952 |        1 |     49 |   1877 |      0 |        0 | (0,40)  |           4 |       2306 |     24 |        |        
  41 |   5896 |        1 |     49 |   1877 |      0 |        0 | (0,41)  |           4 |       2306 |     24 |        |        
  42 |   5840 |        1 |     49 |   1877 |      0 |        0 | (0,42)  |           4 |       2306 |     24 |        |        
  43 |   5784 |        1 |     49 |   1877 |      0 |        0 | (0,43)  |           4 |       2306 |     24 |        |        
  44 |   5728 |        1 |     49 |   1877 |      0 |        0 | (0,44)  |           4 |       2306 |     24 |        |        
  45 |   5672 |        1 |     49 |   1877 |      0 |        0 | (0,45)  |           4 |       2306 |     24 |        |        
  46 |   5616 |        1 |     49 |   1877 |      0 |        0 | (0,46)  |           4 |       2306 |     24 |        |        
  47 |   5560 |        1 |     49 |   1877 |      0 |        0 | (0,47)  |           4 |       2306 |     24 |        |        
  48 |   5504 |        1 |     49 |   1877 |      0 |        0 | (0,48)  |           4 |       2306 |     24 |        |        
  49 |   5448 |        1 |     49 |   1877 |      0 |        0 | (0,49)  |           4 |       2306 |     24 |        |        
  50 |   5392 |        1 |     49 |   1877 |      0 |        0 | (0,50)  |           4 |       2306 |     24 |        |        
  51 |   5336 |        1 |     49 |   1877 |      0 |        0 | (0,51)  |           4 |       2306 |     24 |        |        
  52 |   5280 |        1 |     49 |   1877 |      0 |        0 | (0,52)  |           4 |       2306 |     24 |        |        
  53 |   5224 |        1 |     49 |   1877 |      0 |        0 | (0,53)  |           4 |       2306 |     24 |        |        
  54 |   5168 |        1 |     49 |   1877 |      0 |        0 | (0,54)  |           4 |       2306 |     24 |        |        
  55 |   5112 |        1 |     49 |   1877 |      0 |        0 | (0,55)  |           4 |       2306 |     24 |        |        
  56 |   5056 |        1 |     49 |   1877 |      0 |        0 | (0,56)  |           4 |       2306 |     24 |        |        
  57 |   5000 |        1 |     49 |   1877 |      0 |        0 | (0,57)  |           4 |       2306 |     24 |        |        
  58 |   4944 |        1 |     49 |   1877 |      0 |        0 | (0,58)  |           4 |       2306 |     24 |        |        
  59 |   4888 |        1 |     49 |   1877 |      0 |        0 | (0,59)  |           4 |       2306 |     24 |        |        
  60 |   4832 |        1 |     49 |   1877 |      0 |        0 | (0,60)  |           4 |       2306 |     24 |        |        
  61 |   4776 |        1 |     49 |   1877 |      0 |        0 | (0,61)  |           4 |       2306 |     24 |        |        
  62 |   4720 |        1 |     49 |   1877 |      0 |        0 | (0,62)  |           4 |       2306 |     24 |        |        
  63 |   4664 |        1 |     49 |   1877 |      0 |        0 | (0,63)  |           4 |       2306 |     24 |        |        
  64 |   4608 |        1 |     49 |   1877 |      0 |        0 | (0,64)  |           4 |       2306 |     24 |        |        
  65 |   4552 |        1 |     49 |   1877 |      0 |        0 | (0,65)  |           4 |       2306 |     24 |        |        
  66 |   4496 |        1 |     49 |   1877 |      0 |        0 | (0,66)  |           4 |       2306 |     24 |        |        
  67 |   4440 |        1 |     49 |   1877 |      0 |        0 | (0,67)  |           4 |       2306 |     24 |        |        
  68 |   4384 |        1 |     49 |   1877 |      0 |        0 | (0,68)  |           4 |       2306 |     24 |        |        
  69 |   4328 |        1 |     49 |   1877 |      0 |        0 | (0,69)  |           4 |       2306 |     24 |        |        
  70 |   4272 |        1 |     49 |   1877 |      0 |        0 | (0,70)  |           4 |       2306 |     24 |        |        
  71 |   4216 |        1 |     49 |   1877 |      0 |        0 | (0,71)  |           4 |       2306 |     24 |        |        
  72 |   4160 |        1 |     49 |   1877 |      0 |        0 | (0,72)  |           4 |       2306 |     24 |        |        
  73 |   4104 |        1 |     49 |   1877 |      0 |        0 | (0,73)  |           4 |       2306 |     24 |        |        
  74 |   4048 |        1 |     49 |   1877 |      0 |        0 | (0,74)  |           4 |       2306 |     24 |        |        
  75 |   3992 |        1 |     49 |   1877 |      0 |        0 | (0,75)  |           4 |       2306 |     24 |        |        
  76 |   3936 |        1 |     49 |   1877 |      0 |        0 | (0,76)  |           4 |       2306 |     24 |        |        
  77 |   3880 |        1 |     49 |   1877 |      0 |        0 | (0,77)  |           4 |       2306 |     24 |        |        
  78 |   3824 |        1 |     49 |   1877 |      0 |        0 | (0,78)  |           4 |       2306 |     24 |        |        
  79 |   3768 |        1 |     49 |   1877 |      0 |        0 | (0,79)  |           4 |       2306 |     24 |        |        
  80 |   3712 |        1 |     49 |   1877 |      0 |        0 | (0,80)  |           4 |       2306 |     24 |        |        
  81 |   3656 |        1 |     49 |   1877 |      0 |        0 | (0,81)  |           4 |       2306 |     24 |        |        
  82 |   3600 |        1 |     49 |   1877 |      0 |        0 | (0,82)  |           4 |       2306 |     24 |        |        
  83 |   3544 |        1 |     49 |   1877 |      0 |        0 | (0,83)  |           4 |       2306 |     24 |        |        
  84 |   3488 |        1 |     49 |   1877 |      0 |        0 | (0,84)  |           4 |       2306 |     24 |        |        
  85 |   3432 |        1 |     49 |   1877 |      0 |        0 | (0,85)  |           4 |       2306 |     24 |        |        
  86 |   3376 |        1 |     49 |   1877 |      0 |        0 | (0,86)  |           4 |       2306 |     24 |        |        
  87 |   3320 |        1 |     49 |   1877 |      0 |        0 | (0,87)  |           4 |       2306 |     24 |        |        
  88 |   3264 |        1 |     49 |   1877 |      0 |        0 | (0,88)  |           4 |       2306 |     24 |        |        
  89 |   3208 |        1 |     49 |   1877 |      0 |        0 | (0,89)  |           4 |       2306 |     24 |        |        
  90 |   3152 |        1 |     49 |   1877 |      0 |        0 | (0,90)  |           4 |       2306 |     24 |        |        
  91 |   3096 |        1 |     49 |   1877 |      0 |        0 | (0,91)  |           4 |       2306 |     24 |        |        
  92 |   3040 |        1 |     49 |   1877 |      0 |        0 | (0,92)  |           4 |       2306 |     24 |        |        
  93 |   2984 |        1 |     49 |   1877 |      0 |        0 | (0,93)  |           4 |       2306 |     24 |        |        
  94 |   2928 |        1 |     49 |   1877 |      0 |        0 | (0,94)  |           4 |       2306 |     24 |        |        
  95 |   2872 |        1 |     49 |   1877 |      0 |        0 | (0,95)  |           4 |       2306 |     24 |        |        
  96 |   2816 |        1 |     49 |   1877 |      0 |        0 | (0,96)  |           4 |       2306 |     24 |        |        
  97 |   2760 |        1 |     49 |   1877 |      0 |        0 | (0,97)  |           4 |       2306 |     24 |        |        
  98 |   2704 |        1 |     49 |   1877 |      0 |        0 | (0,98)  |           4 |       2306 |     24 |        |        
  99 |   2648 |        1 |     49 |   1877 |      0 |        0 | (0,99)  |           4 |       2306 |     24 |        |        
 100 |   2592 |        1 |     49 |   1877 |      0 |        0 | (0,100) |           4 |       2306 |     24 |        |        
 101 |   2536 |        1 |     49 |   1877 |      0 |        0 | (0,101) |           4 |       2306 |     24 |        |        
 102 |   2480 |        1 |     49 |   1877 |      0 |        0 | (0,102) |           4 |       2306 |     24 |        |        
 103 |   2424 |        1 |     49 |   1877 |      0 |        0 | (0,103) |           4 |       2306 |     24 |        |        
 104 |   2368 |        1 |     49 |   1877 |      0 |        0 | (0,104) |           4 |       2306 |     24 |        |        
 105 |   2312 |        1 |     49 |   1877 |      0 |        0 | (0,105) |           4 |       2306 |     24 |        |        
 106 |   2256 |        1 |     49 |   1877 |      0 |        0 | (0,106) |           4 |       2306 |     24 |        |        
 107 |   2200 |        1 |     49 |   1877 |      0 |        0 | (0,107) |           4 |       2306 |     24 |        |        
 108 |   2144 |        1 |     49 |   1877 |      0 |        0 | (0,108) |           4 |       2306 |     24 |        |        
 109 |   2088 |        1 |     49 |   1877 |      0 |        0 | (0,109) |           4 |       2306 |     24 |        |        
 110 |   2032 |        1 |     49 |   1877 |      0 |        0 | (0,110) |           4 |       2306 |     24 |        |        
 111 |   1976 |        1 |     49 |   1877 |      0 |        0 | (0,111) |           4 |       2306 |     24 |        |        
 112 |   1920 |        1 |     49 |   1877 |      0 |        0 | (0,112) |           4 |       2306 |     24 |        |        
 113 |   1864 |        1 |     49 |   1877 |      0 |        0 | (0,113) |           4 |       2306 |     24 |        |        
 114 |   1808 |        1 |     49 |   1877 |      0 |        0 | (0,114) |           4 |       2306 |     24 |        |        
 115 |   1752 |        1 |     49 |   1877 |      0 |        0 | (0,115) |           4 |       2306 |     24 |        |        
 116 |   1696 |        1 |     49 |   1877 |      0 |        0 | (0,116) |           4 |       2306 |     24 |        |        
 117 |   1640 |        1 |     49 |   1877 |      0 |        0 | (0,117) |           4 |       2306 |     24 |        |        
 118 |   1584 |        1 |     49 |   1877 |      0 |        0 | (0,118) |           4 |       2306 |     24 |        |        
 119 |   1528 |        1 |     49 |   1877 |      0 |        0 | (0,119) |           4 |       2306 |     24 |        |        
 120 |   1472 |        1 |     49 |   1877 |      0 |        0 | (0,120) |           4 |       2306 |     24 |        |        
 121 |   1416 |        1 |     49 |   1877 |      0 |        0 | (0,121) |           4 |       2306 |     24 |        |        
 122 |   1360 |        1 |     49 |   1877 |      0 |        0 | (0,122) |           4 |       2306 |     24 |        |        
 123 |   1304 |        1 |     49 |   1877 |      0 |        0 | (0,123) |           4 |       2306 |     24 |        |        
 124 |   1248 |        1 |     49 |   1877 |      0 |        0 | (0,124) |           4 |       2306 |     24 |        |        
 125 |   1192 |        1 |     49 |   1877 |      0 |        0 | (0,125) |           4 |       2306 |     24 |        |        
 126 |   1136 |        1 |     49 |   1877 |      0 |        0 | (0,126) |           4 |       2306 |     24 |        |        
 127 |   1080 |        1 |     49 |   1877 |      0 |        0 | (0,127) |           4 |       2306 |     24 |        |        
 128 |   1024 |        1 |     49 |   1877 |      0 |        0 | (0,128) |           4 |       2306 |     24 |        |        
 129 |    968 |        1 |     49 |   1877 |      0 |        0 | (0,129) |           4 |       2306 |     24 |        |        
 130 |    912 |        1 |     49 |   1877 |      0 |        0 | (0,130) |           4 |       2306 |     24 |        |        
 131 |    856 |        1 |     49 |   1877 |      0 |        0 | (0,131) |           4 |       2306 |     24 |        |        
 132 |    800 |        1 |     49 |   1877 |      0 |        0 | (0,132) |           4 |       2306 |     24 |        |        
 133 |    744 |        1 |     49 |   1877 |      0 |        0 | (0,133) |           4 |       2306 |     24 |        |        
 134 |    688 |        1 |     49 |   1877 |      0 |        0 | (0,134) |           4 |       2306 |     24 |        |        
 135 |    632 |        1 |     49 |   1877 |      0 |        0 | (0,135) |           4 |       2306 |     24 |        |        
 136 |    576 |        1 |     49 |   1877 |      0 |        0 | (0,136) |           4 |       2306 |     24 |        |        
(136 rows)  

删除一条记录后查看,说明pageinspect不管当前PAGE里的ITEM是否可见,都DUMP出来。

digoal=#   delete from tbl_user_info where id=2;  
DELETE 1  
digoal=# select * from heap_page_items(get_raw_page('tbl_user_info',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   
-----+--------+----------+--------+--------+--------+----------+---------+-------------+------------+--------+--------+-------  
   1 |   8136 |        1 |     49 |   1877 |      0 |        0 | (0,1)   |           4 |       2306 |     24 |        |        
   2 |   8080 |        1 |     49 |   1877 |   1879 |        0 | (0,2)   |           4 |        258 |     24 |        |        

索引PAGE也是可以被pg_inspect翻译的,如

digoal=# create index id_user_info on tbl_user_info(id);  
CREATE INDEX  
digoal=# select * from bt_metap('id_user_info');  
 magic  | version | root | level | fastroot | fastlevel   
--------+---------+------+-------+----------+-----------  
 340322 |       2 |    3 |     1 |        3 |         1  
(1 row)  
  
digoal=# select * from bt_page_stats('id_user_info',0);  
ERROR:  block 0 is a meta page  
digoal=# select * from bt_page_stats('id_user_info',1);  
 blkno | type | live_items | dead_items | avg_item_size | page_size | free_size | btpo_prev | btpo_next | btpo | btpo_flags   
-------+------+------------+------------+---------------+-----------+-----------+-----------+-----------+------+------------  
     1 | l    |        367 |          0 |            16 |      8192 |       808 |         0 |         2 |    0 |          1  
(1 row)  

另外一个函数可以读取到底层的数据,如

        191 | (1,55)  |      16 | f     | f    | bf 00 00 00 00 00 00 00  
        192 | (1,56)  |      16 | f     | f    | c0 00 00 00 00 00 00 00  
        193 | (1,57)  |      16 | f     | f    | c1 00 00 00 00 00 00 00  
        194 | (1,58)  |      16 | f     | f    | c2 00 00 00 00 00 00 00  
        195 | (1,59)  |      16 | f     | f    | c3 00 00 00 00 00 00 00  
        196 | (1,60)  |      16 | f     | f    | c4 00 00 00 00 00 00 00  
        197 | (1,61)  |      16 | f     | f    | c5 00 00 00 00 00 00 00  
        198 | (1,62)  |      16 | f     | f    | c6 00 00 00 00 00 00 00  
        199 | (1,63)  |      16 | f     | f    | c7 00 00 00 00 00 00 00  
        200 | (1,64)  |      16 | f     | f    | c8 00 00 00 00 00 00 00  
        201 | (1,65)  |      16 | f     | f    | c9 00 00 00 00 00 00 00  
        202 | (1,66)  |      16 | f     | f    | ca 00 00 00 00 00 00 00  
        203 | (1,67)  |      16 | f     | f    | cb 00 00 00 00 00 00 00  
        204 | (1,68)  |      16 | f     | f    | cc 00 00 00 00 00 00 00  
        205 | (1,69)  |      16 | f     | f    | cd 00 00 00 00 00 00 00  
        206 | (1,70)  |      16 | f     | f    | ce 00 00 00 00 00 00 00  
        207 | (1,71)  |      16 | f     | f    | cf 00 00 00 00 00 00 00  
        208 | (1,72)  |      16 | f     | f    | d0 00 00 00 00 00 00 00  
        209 | (1,73)  |      16 | f     | f    | d1 00 00 00 00 00 00 00  
        210 | (1,74)  |      16 | f     | f    | d2 00 00 00 00 00 00 00  
        211 | (1,75)  |      16 | f     | f    | d3 00 00 00 00 00 00 00  
        212 | (1,76)  |      16 | f     | f    | d4 00 00 00 00 00 00 00  
        213 | (1,77)  |      16 | f     | f    | d5 00 00 00 00 00 00 00  
        214 | (1,78)  |      16 | f     | f    | d6 00 00 00 00 00 00 00  
        215 | (1,79)  |      16 | f     | f    | d7 00 00 00 00 00 00 00  
        216 | (1,80)  |      16 | f     | f    | d8 00 00 00 00 00 00 00  
        217 | (1,81)  |      16 | f     | f    | d9 00 00 00 00 00 00 00  
        218 | (1,82)  |      16 | f     | f    | da 00 00 00 00 00 00 00  
        219 | (1,83)  |      16 | f     | f    | db 00 00 00 00 00 00 00  
        220 | (1,84)  |      16 | f     | f    | dc 00 00 00 00 00 00 00  
        221 | (1,85)  |      16 | f     | f    | dd 00 00 00 00 00 00 00  
        222 | (1,86)  |      16 | f     | f    | de 00 00 00 00 00 00 00  
        223 | (1,87)  |      16 | f     | f    | df 00 00 00 00 00 00 00  
        224 | (1,88)  |      16 | f     | f    | e0 00 00 00 00 00 00 00  
        225 | (1,89)  |      16 | f     | f    | e1 00 00 00 00 00 00 00  
        226 | (1,90)  |      16 | f     | f    | e2 00 00 00 00 00 00 00  
        227 | (1,91)  |      16 | f     | f    | e3 00 00 00 00 00 00 00  
        228 | (1,92)  |      16 | f     | f    | e4 00 00 00 00 00 00 00  
        229 | (1,93)  |      16 | f     | f    | e5 00 00 00 00 00 00 00  
        230 | (1,94)  |      16 | f     | f    | e6 00 00 00 00 00 00 00  
        231 | (1,95)  |      16 | f     | f    | e7 00 00 00 00 00 00 00  
        232 | (1,96)  |      16 | f     | f    | e8 00 00 00 00 00 00 00  
        233 | (1,97)  |      16 | f     | f    | e9 00 00 00 00 00 00 00  
        234 | (1,98)  |      16 | f     | f    | ea 00 00 00 00 00 00 00  
        235 | (1,99)  |      16 | f     | f    | eb 00 00 00 00 00 00 00  
        236 | (1,100) |      16 | f     | f    | ec 00 00 00 00 00 00 00  
        237 | (1,101) |      16 | f     | f    | ed 00 00 00 00 00 00 00  
        238 | (1,102) |      16 | f     | f    | ee 00 00 00 00 00 00 00  
        239 | (1,103) |      16 | f     | f    | ef 00 00 00 00 00 00 00  
        240 | (1,104) |      16 | f     | f    | f0 00 00 00 00 00 00 00  
        241 | (1,105) |      16 | f     | f    | f1 00 00 00 00 00 00 00  
        242 | (1,106) |      16 | f     | f    | f2 00 00 00 00 00 00 00  
        243 | (1,107) |      16 | f     | f    | f3 00 00 00 00 00 00 00  
        244 | (1,108) |      16 | f     | f    | f4 00 00 00 00 00 00 00  
        245 | (1,109) |      16 | f     | f    | f5 00 00 00 00 00 00 00  
        246 | (1,110) |      16 | f     | f    | f6 00 00 00 00 00 00 00  
        247 | (1,111) |      16 | f     | f    | f7 00 00 00 00 00 00 00  
        248 | (1,112) |      16 | f     | f    | f8 00 00 00 00 00 00 00  
        249 | (1,113) |      16 | f     | f    | f9 00 00 00 00 00 00 00  
        250 | (1,114) |      16 | f     | f    | fa 00 00 00 00 00 00 00  
        251 | (1,115) |      16 | f     | f    | fb 00 00 00 00 00 00 00  
        252 | (1,116) |      16 | f     | f    | fc 00 00 00 00 00 00 00  
        253 | (1,117) |      16 | f     | f    | fd 00 00 00 00 00 00 00  
        254 | (1,118) |      16 | f     | f    | fe 00 00 00 00 00 00 00  
        255 | (1,119) |      16 | f     | f    | ff 00 00 00 00 00 00 00  
        256 | (1,120) |      16 | f     | f    | 00 01 00 00 00 00 00 00  
        257 | (1,121) |      16 | f     | f    | 01 01 00 00 00 00 00 00  
        258 | (1,122) |      16 | f     | f    | 02 01 00 00 00 00 00 00  
        259 | (1,123) |      16 | f     | f    | 03 01 00 00 00 00 00 00  
        260 | (1,124) |      16 | f     | f    | 04 01 00 00 00 00 00 00  
        261 | (1,125) |      16 | f     | f    | 05 01 00 00 00 00 00 00  
        262 | (1,126) |      16 | f     | f    | 06 01 00 00 00 00 00 00  
        263 | (1,127) |      16 | f     | f    | 07 01 00 00 00 00 00 00  
        264 | (1,128) |      16 | f     | f    | 08 01 00 00 00 00 00 00  
        265 | (1,129) |      16 | f     | f    | 09 01 00 00 00 00 00 00  
        266 | (1,130) |      16 | f     | f    | 0a 01 00 00 00 00 00 00  
        267 | (1,131) |      16 | f     | f    | 0b 01 00 00 00 00 00 00  
        268 | (1,132) |      16 | f     | f    | 0c 01 00 00 00 00 00 00  
        269 | (1,133) |      16 | f     | f    | 0d 01 00 00 00 00 00 00  
        270 | (1,134) |      16 | f     | f    | 0e 01 00 00 00 00 00 00  
        271 | (1,135) |      16 | f     | f    | 0f 01 00 00 00 00 00 00  
        272 | (1,136) |      16 | f     | f    | 10 01 00 00 00 00 00 00  

另外还有一个函数fsm_page_contents(page bytea) 用于查看free space map的信息。

Flag Counter

digoal’s 大量PostgreSQL文章入口