an_example_of_using_pageinspect

Posted on In

准备表数据

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)