| From: | Kirill Reshke <reshkekirill(at)gmail(dot)com> |
|---|---|
| To: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
| Subject: | Enhance btree's pageinspect |
| Date: | 2026-01-18 19:58:34 |
| Message-ID: | CALdSSPg45LCbcTN7sVZBsv+QOLLXXuRVLe6cNx1dyohVO022Jg@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
Hi!
I am using page inspect to inspect btree's page frequently, during
various data corruption investigations.
Please find two patches that would be very helpful to my own use
cases, and I believe they are universally useful. v1-0001 provides
pretty-print functionality for bt_page_items().
This makes much easier to get which exactly data page contains
example output:
```
xxxx/yyy M # select * from bt_page_items('name', zzz, true) where
itemoffset=34 or itemoffset=35 or itemoffset=36;
itemoffset | ctid | itemlen | nulls | vars | data
| dead | htid | tids
------------+------------+---------+-------+------+-----------------------+------+------------+------------------------------------------
34 | (24,8194) | 40 | f | t | (p5844)=(ab) | f
| (209642,4) | {"(209642,4)","(969129,4)"}
35 | (440908,5) | 24 | f | t | (p5844)=(a-b) | f
| (440908,5) | [null]
36 | (24,8195) | 48 | f | t | (p5844)=(ab) | f
| (447717,5) | {"(447717,5)","(597129,5)","(612355,2)"}
(3 rows)
```
^^
Yes, there was corruption... I replaced all private data, but you can
get a general idea of how it works.
example with include columns:
```
reshke=# select * from bt_page_items('y'::text, 5::int8, true);
itemoffset | ctid | itemlen | nulls | vars |
data | dead | htid | tids
------------+---------+---------+-------+------+------------------------------------------------+------+---------+------
1 | (5,1) | 16 | f | f | (i, j) INCLUDE
(k)=(1045, 0) INCLUDE (4) | | |
2 | (4,44) | 24 | f | f | (i, j) INCLUDE
(k)=(784, 785) INCLUDE (786) | f | (4,44) |
3 | (4,45) | 24 | f | f | (i, j) INCLUDE
(k)=(785, 786) INCLUDE (787) | f | (4,45) |
4 | (4,46) | 24 | f | f | (i, j) INCLUDE
(k)=(786, 787) INCLUDE (788) | f | (4,46) |
5 | (4,47) | 24 | f | f | (i, j) INCLUDE
(k)=(787, 788) INCLUDE (789) | f | (4,47) |
```
v1-0002 adds the bt_page_opaque function, which is also a very useful
tool to get page LSN and flags.
```
reshke=# select i, (bt_page_opaque(get_raw_page('y', i))).* from
generate_series(1,29) i;
i | lsn | btpo_prev | btpo_next | btpo_level | flags | btpo_cycleid
----+------------+-----------+-----------+------------+--------+--------------
1 | 0/01BD7060 | 0 | 2 | 0 | {leaf} | 0
2 | 0/01BE2AE8 | 1 | 4 | 0 | {leaf} | 0
3 | 0/01D061D0 | 0 | 0 | 1 | {root} | 0
4 | 0/01BEE570 | 2 | 5 | 0 | {leaf} | 0
5 | 0/01BF9FE0 | 4 | 6 | 0 | {leaf} | 0
6 | 0/01C05A68 | 5 | 7 | 0 | {leaf} | 0
7 | 0/01C114F0 | 6 | 8 | 0 | {leaf} | 0
8 | 0/01C1CF78 | 7 | 9 | 0 | {leaf} | 0
9 | 0/01C28A00 | 8 | 10 | 0 | {leaf} | 0
10 | 0/01C34488 | 9 | 11 | 0 | {leaf} | 0
11 | 0/01C3FEF8 | 10 | 12 | 0 | {leaf} | 0
12 | 0/01C4B980 | 11 | 13 | 0 | {leaf} | 0
13 | 0/01C57408 | 12 | 14 | 0 | {leaf} | 0
14 | 0/01C62E90 | 13 | 15 | 0 | {leaf} | 0
15 | 0/01C6E918 | 14 | 16 | 0 | {leaf} | 0
16 | 0/01C7A3A0 | 15 | 17 | 0 | {leaf} | 0
17 | 0/01C85E10 | 16 | 18 | 0 | {leaf} | 0
18 | 0/01C91898 | 17 | 19 | 0 | {leaf} | 0
19 | 0/01C9D320 | 18 | 20 | 0 | {leaf} | 0
20 | 0/01CA8DA8 | 19 | 21 | 0 | {leaf} | 0
21 | 0/01CB4830 | 20 | 22 | 0 | {leaf} | 0
22 | 0/01CC02B8 | 21 | 23 | 0 | {leaf} | 0
23 | 0/01CCBD28 | 22 | 24 | 0 | {leaf} | 0
24 | 0/01CD77B0 | 23 | 25 | 0 | {leaf} | 0
25 | 0/01CE3238 | 24 | 26 | 0 | {leaf} | 0
26 | 0/01CEECC0 | 25 | 27 | 0 | {leaf} | 0
27 | 0/01CFA748 | 26 | 28 | 0 | {leaf} | 0
28 | 0/01D061D0 | 27 | 29 | 0 | {leaf} | 0
29 | 0/01D0BA00 | 28 | 0 | 0 | {leaf} | 0
(29 rows)
```
--
Best regards,
Kirill Reshke
| Attachment | Content-Type | Size |
|---|---|---|
| v1-0001-bt_page_items-pretty-print.patch | application/octet-stream | 8.0 KB |
| v1-0002-Add-bt_page_opaque-function.patch | application/octet-stream | 4.5 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Mihail Nikalayeu | 2026-01-18 21:52:00 | Re: Adding REPACK [concurrently] |
| Previous Message | Kirill Reshke | 2026-01-18 18:56:24 | Re: let ALTER TABLE DROP COLUMN drop whole-row referenced object |