an_example_of_using_pageinspect

准备表数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
test=# create table users(id int generated always as identity primary key, name text);
CREATE TABLE

test=# select oid, relname, relfilenode from pg_class where relname = 'users';
oid | relname | relfilenode
-------+---------+-------------
40980 | users | 40980

test=# select c.oid, c.relname, i.indisprimary from pg_class c join pg_index i on c.oid = i.indexrelid where i.indrelid = 'users'::regclass and i.indisprimary;
oid | relname | indisprimary
-------+------------+--------------
40986 | users_pkey | t
(1 row)

test=# select lp,lp_len,t_data from heap_page_items(get_raw_page('users', 0));
ERROR: block number 0 is out of range for relation "users"
test=# select * from bt_page_items('users_pkey',1);
ERROR: block number 1 is out of range
test=# ^C

insert values

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
test=# insert into users(name) values('jeffrey');
INSERT 0 1
test=# select lp,lp_len,t_data from heap_page_items(get_raw_page('users', 0));
lp | lp_len | t_data
----+--------+----------------------------
1 | 36 | \x01000000116a656666726579
(1 row)

test=# select * from bt_page_items('users_pkey',1);
itemoffset | ctid | itemlen | nulls | vars | data | dead | htid | tids
------------+-------+---------+-------+------+-------------------------+------+-------+------
1 | (0,1) | 16 | f | f | 01 00 00 00 00 00 00 00 | f | (0,1) |
(1 row)

test=# select * from page_header(get_raw_page('users', 0));
lsn | checksum | flags | lower | upper | special | pagesize | version | prune_xid
------------+----------+-------+-------+-------+---------+----------+---------+-----------
0/03F5E7A0 | 0 | 0 | 28 | 8152 | 8192 | 8192 | 4 | 0
(1 row)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
test=# insert into users(name) values('Ethan');
INSERT 0 1
test=# select * from page_header(get_raw_page('users', 0));
lsn | checksum | flags | lower | upper | special | pagesize | version | prune_xid
------------+----------+-------+-------+-------+---------+----------+---------+-----------
0/03F62810 | 0 | 0 | 32 | 8112 | 8192 | 8192 | 4 | 0
(1 row)

test=# select lp,lp_len,t_data from heap_page_items(get_raw_page('users', 0));
lp | lp_len | t_data
----+--------+----------------------------
1 | 36 | \x01000000116a656666726579
2 | 34 | \x020000000d457468616e
(2 rows)

test=# select * from bt_page_items('users_pkey',1);
itemoffset | ctid | itemlen | nulls | vars | data | dead | htid | tids
------------+-------+---------+-------+------+-------------------------+------+-------+------
1 | (0,1) | 16 | f | f | 01 00 00 00 00 00 00 00 | f | (0,1) |
2 | (0,2) | 16 | f | f | 02 00 00 00 00 00 00 00 | f | (0,2) |
(2 rows)