Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

Analysis of header and Row data pointers in Page of PostgreSQL

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.

Share To

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report