-- 测试表
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 : 标志位
https://www.postgresql.org/docs/10/pageinspect.html