Postgresql页面底层数据查询方法pageinspect

-- 测试表
drop table testbl;
create table testbl(i int primary key not null, id int not null, info varchar(200) not null);
insert into testbl select generate_series(1,10000), (random()*100)::integer, repeat('DUfw',(random()*50)::integer);
delete from testbl where i%2 = 0 and i < 50;


postgres=# select * from testbl where i < 10;
 i | id |                                   info                                   
---+----+--------------------------------------------------------------------------
 1 | 26 | DUfwDUfwDUfwDUfwDUfwDUfwDUfwDUfwDUfwDUfwDUfwDUfwDUfwDUfwDUfwDUfwDUfwDUfwDUfwDUfwDUfwDUfwDU
 3 |  4 | 
 5 | 48 | DUfwDUfwDUfwDUfwDUfwDUfwDUfwDUfwDUfwDUfwDUfwDUfwDUfwDUfwDUfwDUfwDUfwDUfw
 7 | 18 | DUfwDUfwDUfwDUfwDUfwDUfwDUfwDUfwDUfwDUfwDUfwDUfwDUfwDUfwDUfw
 9 |  3 | DUfwDUfwDUfwDUfwDUfwDUfwDUfwDUfwDUfwDUfwDUfwDUfwDUfwDUfw

-- 查询数据修改时间（打开track_commit_timestamp参数）
select xmin,xmax,pg_xact_commit_timestamp(xmin) from t1;
  xmin   | xmax |   pg_xact_commit_timestamp    
---------+------+-------------------------------
 1835931 |    0 | 2022-01-10 11:27:04.263795+08
 1835932 |    0 | 2022-01-10 11:27:06.595674+08
 1835933 |    0 | 2022-01-10 11:27:08.356085+08
 1835934 |    0 | 2022-01-10 11:27:10.885906+08
 1835935 |    0 | 2022-01-10 11:27:12.661314+08
 1835936 |    0 | 2022-01-10 11:27:16.601655+08
 
 
SELECT 
	t_xmin,
	t_xmax,
	pg_xact_commit_timestamp(t_xmin),
	case t_xmax when 0 then null else pg_xact_commit_timestamp(t_xmax) end
FROM heap_page_items(get_raw_page('testbl', 0))
limit 10;
 t_xmin  | t_xmax  |   pg_xact_commit_timestamp    |   pg_xact_commit_timestamp   
---------+---------+-------------------------------+------------------------------
 1835997 |       0 | 2022-01-10 14:27:41.786763+08 | 
 1835997 | 1835998 | 2022-01-10 14:27:41.786763+08 | 2022-01-10 14:27:47.34692+08
 1835997 |       0 | 2022-01-10 14:27:41.786763+08 | 
 1835997 | 1835998 | 2022-01-10 14:27:41.786763+08 | 2022-01-10 14:27:47.34692+08
 1835997 |       0 | 2022-01-10 14:27:41.786763+08 | 
 1835997 | 1835998 | 2022-01-10 14:27:41.786763+08 | 2022-01-10 14:27:47.34692+08
 1835997 |       0 | 2022-01-10 14:27:41.786763+08 | 
 1835997 | 1835998 | 2022-01-10 14:27:41.786763+08 | 2022-01-10 14:27:47.34692+08
 1835997 |       0 | 2022-01-10 14:27:41.786763+08 | 
 1835997 | 1835998 | 2022-01-10 14:27:41.786763+08 | 2022-01-10 14:27:47.34692+08
(10 rows)

-- 查询页头信息
SELECT * FROM page_header(get_raw_page('pg_class', 0));
    lsn     | checksum | flags | lower | upper | special | pagesize | version | prune_xid 
------------+----------+-------+-------+-------+---------+----------+---------+-----------
 7/8C4D2A90 |        0 |     1 |   220 |  2728 |    8192 |     8192 |       4 |   1835939
 
-- 查询空闲空间映射表
SELECT fsm_page_contents(get_raw_page('testbl', 'fsm', 0));

-- 查询元组信息
SELECT * FROM heap_page_items(get_raw_page('testbl', 0));

SELECT 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, length(t_data) FROM heap_page_items(get_raw_page('testbl', 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 | length 
----+--------+----------+--------+---------+---------+----------+--------+-------------+------------+--------+--------+-------+--------
  1 |   8056 |        1 |    129 | 1835997 |       0 |        0 | (0,1)  |           3 |       2306 |     24 |        |       |    105
  2 |   7904 |        1 |    149 | 1835997 | 1835998 |        0 | (0,2)  |        8195 |        258 |     24 |        |       |    125
  3 |   7688 |        1 |    212 | 1835997 |       0 |        0 | (0,3)  |           3 |       2306 |     24 |        |       |    188
  4 |   7488 |        1 |    200 | 1835997 | 1835998 |        0 | (0,4)  |        8195 |        258 |     24 |        |       |    176
  5 |   7440 |        1 |     45 | 1835997 |       0 |        0 | (0,5)  |           3 |       2306 |     24 |        |       |     21
  6 |   7248 |        1 |    188 | 1835997 | 1835998 |        0 | (0,6)  |        8195 |        258 |     24 |        |       |    164
  7 |   7096 |        1 |    145 | 1835997 |       0 |        0 | (0,7)  |           3 |       2306 |     24 |        |       |    121
  8 |   7040 |        1 |     53 | 1835997 | 1835998 |        0 | (0,8)  |        8195 |        258 |     24 |        |       |     29
  9 |   6904 |        1 |    133 | 1835997 |       0 |        0 | (0,9)  |           3 |       2306 |     24 |        |       |    109
 10 |   6832 |        1 |     65 | 1835997 | 1835998 |        0 | (0,10) |        8195 |        258 |     24 |        |       |     41
 

SELECT * FROM heap_page_item_attrs(get_raw_page('testbl', 0), 'testbl'::regclass);

SELECT tuple_data_split('testbl'::regclass, t_data, t_infomask, t_infomask2, t_bits) FROM heap_page_items(get_raw_page('testbl', 0)) limit 1;
 {"\\x01000000","\\x47000000","\\xc3445566774455667744556677445566774455667744556677445566774455667744556677445566774455667744556677445566
774455667744556677445566774455667744556677445566774455667744556677445566774455667744556677"}


select * from testbl limit 1;
 i | id |                                               info                                               
---+----+--------------------------------------------------------------------------------------------------
 1 | 71 | DUfwDUfwDUfwDUfwDUfwDUfwDUfwDUfwDUfwDUfwDUfwDUfwDUfwDUfwDUfwDUfwDUfwDUfwDUfwDUfwDUfwDUfwDUfwDUfw
 
-- 字符串按ASCII格式存放
-- 44 : D
-- 55 : U
-- 66 : f
-- 77 : w

-- \\xc3 : 标志位