In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-01-16 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)05/31 Report--
This article introduces the knowledge of "header and row data pointer analysis in PostgreSQL Page". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!
1. Test data
Create a table and insert several rows of data
Drop table if exists t_page
Create table t_page (id int,c1 char (8), c2 varchar (16))
Insert into t_page values (1 ~ (1) ~ (1) ~ (1) ~ (1))
Insert into t_page values (2, 2, 2, 2, 5, 2, 5, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 4, 2, 2, 2, 2, 2, 2, 2, 4, 4, 2, 2, 2, 2, 4, 4, 4, 2, 2, 4, 4, 4, 2, 4, 4, 4, 4, 4, 4, 2, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4,
Insert into t_page values (3, 3, 3, 3, 4, 3, 4, 4, 3, 4, 3, 4, 3, 4, 3, 4, 3, 4, 3, 4, 3, 4, 3, 4, 3, 4, 3, 4, 3, 4, 4, 3, 4, 3, 4, 4, 3, 4, 3, 4, 4, 3, 4, 4, 4, 3, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 6, 4, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 3, 4, 6, 6, 6, 6, 6, 6, 6, 6, 6,
Insert into t_page values (4, 4, 4 and 4)
-- get the data file corresponding to the table
Testdb=# select pg_relation_filepath ('tweepage')
Pg_relation_filepath
--
Base/16477/24801
(1 row)
-- data in Dump data file
[xdb@localhost utf8db] $hexdump-C $PGDATA/base/16477/24801
00000000 01 000000 88 20 2a 12 00000000 28 00 60 1f |. *. (.`. |
00000010 00 20 04 20 000000 00 d8 9f 4e 00 b09f 4e 00 |. . .N.N. |
00000020 88 9f 4e 00 60 9f 4e 000000 000000 000000 | .N.`.N. |
00000030 000000 000000 000000 00000000 |. |
*
00001f60 e5 1b 18 0000 0000 0000 00 |. |
00001f70 04 00 03 00 02 08 18 00 04 0000 00 13 34 20 20 | .4 |
00001f80 20 20 20 05 64 00 e4 1b 18 0000 0000 00 | .d. |
00001f90 0000 0000 0000 03 00 03 00 02 08 18 00 |. |
00001fa0 03 0000 00 13 33 20 20 20 05 63 00 | .3 c. |
00001fb0 E3 1b 18 0000 0000 0000 00 |. |
00001fc0 02 00 03 00 02 08 18 00 02 0000 00 13 32 20 20 | .2 |
00001fd0 20 20 20 05 62 00 e2 1b 18 0000 0000 00 | .b. |
00001fe0 0000 0000 0000 01 00 03 00 02 08 18 00 |. |
00001ff0 01 0000 00 13 31 20 20 20 05 61 00 | .1.a. |
00002000
II. PageHeader
PageHeaderData was mentioned in the previous section, and its data structure is as follows:
Typedef struct PageHeaderData
{
/ * XXX LSN is member of * any* block, not only page-organized ones * /
PageXLogRecPtr pd_lsn; / * LSN: next byte after last byte of xlog
* record for last change to this page * /
Uint16 pd_checksum; / * checksum * /
Uint16 pd_flags; / * flag bits, see below * /
LocationIndex pd_lower; / * offset to start of free space * /
LocationIndex pd_upper; / * offset to end of free space * /
LocationIndex pd_special; / * offset to start of special space * /
Uint16 pd_pagesize_version
TransactionId pd_prune_xid; / * oldest prunable XID, or zero if none * /
ItemIdData pd_linp [1]; / * beginning of line pointer array * /
} PageHeaderData
Let's use hexdump to view and parse one by one based on the data in the data file.
Pd_lsn (8bytes)
[xdb@localhost utf8db] $hexdump-C $PGDATA/base/16477/24801-s 0-n 8
00000000 01 000000 88 20 2a 12 |. *. |
00000008
The eight Bytes of the data file stores LSN, of which the first four Bytes are TimelineID, in this case it is\ x0000 0001 (the number 1), the last four Bytes are\ x122a2088, and the LSN is 1/122A2088
Note:
A, 000000000008 is the output of the hexdump tool, not the data content
B and X86 use small-end mode, and pay attention to high-low conversion when reading bytecode.
Pd_checksum (2bytes)
[xdb@localhost utf8db] $hexdump-C $PGDATA/base/16477/24801-s 8-n 2
00000008 0000 |.. |
0000000a
Checksum is\ x0000
Pd_flags (2bytes)
[xdb@localhost utf8db] $hexdump-C $PGDATA/base/16477/24801-s 10-n 2
0000000a 0000 |.. |
0000000c
Flags is\ x0000
Pd_lower (2bytes)
[xdb@localhost utf8db] $hexdump-C $PGDATA/base/16477/24801-s 12-n 2
0000000c 28 00 | (. |
0000000e
Lower is\ x0028, decimal value is 40
Pd_upper (2bytes)
[xdb@localhost utf8db] $hexdump-C $PGDATA/base/16477/24801-s 14-n 2
0000000e 60 1f | `. |
00000010
[xdb@localhost utf8db] $echo $((0x1f60))
8032
Upper is\ x1f60, decimal is 8032
Pd_special (2bytes)
[xdb@localhost utf8db] $hexdump-C $PGDATA/base/16477/24801-s 16-n 2
00000010 00 20 |. | |
00000012
Special Space is\ x2000, decimal value is 8192
Pd_pagesize_version (2bytes)
[xdb@localhost utf8db] $hexdump-C $PGDATA/base/16477/24801-s 18-n 2
00000012 04 20 |. | |
00000014
Pagesize_version is\ x2004, decimal is 8196 (that is, version 4)
Pd_prune_xid (4bytes)
[xdb@localhost utf8db] $hexdump-C $PGDATA/base/16477/24801-s 20-n 4
00000014 000000 00 |. |
00000018
Prune_xid is\ x0000, that is, 0
III. ItemIds
PageHeaderData is followed by an array of ItemId, with each element occupying a space of 4Bytes, the data structure:
Typedef struct ItemIdData
{
Unsigned lp_off:15,/* offset to tuple (from start of page) * /
Lp_flags:2,/* state of item pointer, see below * /
Lp_len:15;/* byte length of tuple * /
} ItemIdData
Typedef ItemIdData* ItemId
Lp_off
[xdb@localhost utf8db] $hexdump-C $PGDATA/base/16477/24801-s 24-n 2
00000018 d8 9f |.. |
0000001a
Lower 15 digits
[xdb@localhost utf8db] $echo $((0x9fd8 & ~ $((1 1)
thirty-nine
Indicates that the size of the first Item (tuple) is 39
Lp_flags
Take position 17-16, 01, that is, 1
This is the end of the page header and row data pointer analysis in PostgreSQL's Page. Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!
Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.
Views: 0
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.