Shulou(Shulou.com)05/31 Report--

This article mainly shows you "how to learn PostgreSQL Page structure from scratch", the content is easy to understand, clear, hope to help you solve doubts, the following let the editor lead you to study and learn "how to learn PostgreSQL Page structure from scratch" this article.

1. Page

The page in pg, like the data block in Oracle, refers to the block of the database, the integer multiple of the operating system block, and the default is 8K, that is, two operating system blocks (4k file system blocks). This size is specified by the-- with-blocksize parameter, in Kb, when pg compiles and installs configure.

Second, the internal structure of Page 2.1page structure

2.2 PageHeaderData data structure (header)

You can see that a Page has Pager header (header), followed by linp (row pointer), pd_lower and pd_upper are the start and end positions of free space, respectively; followed by row data (the rows in pg are tuple) and special space. The structure of the whole page is much simpler than the block structure of Oracle.

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 [flex _ ARRAY_MEMBER]; / * line pointer array * /

} PageHeaderData

The specific length and description are also described in detail:

Field

Type

Length

Description

Pd_lsn

PageXLogRecPtr

8 bytes

LSN: next byte after last byte of WAL record for last change to this page

Pd_checksum

Uint16

2 bytes

Page checksum

Pd_flags

Uint16

2 bytes

Flag bits

Pd_lower

LocationIndex

2 bytes

Offset to start of free space

Pd_upper

LocationIndex

2 bytes

Offset to end of free space

Pd_special

LocationIndex

2 bytes

Offset to start of special space

Pd_pagesize_version

Uint16

2 bytes

Page size and layout version number information

Pd_prune_xid

TransactionId

4 bytes

Oldest unpruned XMAX on page, or zero if none

To put it simply, pd_lsn refers to the lsn (log sequence number) that last modified the page, which is consistent with the lsn recorded in wal (write ahead log, same as oracle redo). When the data falls off the disk, the redo must first be scanned to wal, and this pd_lsn records the lsn of the relevant redo when the last data is dropped.

Pd_checksum is a checksum. It is enabled by the-k parameter when initdb initializes the instance. It is disabled by default and cannot be modified after initdb. It is based on the FNV-1a hash algorithm and has been changed accordingly. This checksum, like Oracle's checksum, is used to check blocks as they are read in and out of memory. For example, when we modify a data block in memory and write it to disk, we first calculate the checksum in memory, and then calculate whether the cheksum is the same as before in memory after the data block is written, so as to ensure that there are no errors in the whole writing process and protect the data structure from being destroyed.

Pd_flags has the following values:

/ *

* pd_flags contains the following flag bits. Undefined bits are initialized

* to zero and may be used in the future.

*

* PD_HAS_FREE_LINES is set if there are any LP_UNUSED line pointers before

* pd_lower. This should be considered a hint rather than the truth, since

* changes to it are not WAL-logged.

*

* PD_PAGE_FULL is set if an UPDATE doesn't find enough free space in the

* page for its new tuple version; this suggests that a prune is needed.

* Again, this is just a hint.

, /

# define PD_HAS_FREE_LINES 0x0001 / * are there any unused line pointers? * /

# define PD_PAGE_FULL 0x0002 / * not enough free space for new tuple? * /

# define PD_ALL_VISIBLE 0x0004 / * all tuples on page are visible to

* everyone * /

# define PD_VALID_FLAG_BITS 0x0007 / * OR of all valid pd_flags bits * /

Pd_lower and pd_upper represent the start and end positions of free space, respectively. Pd_special is only valid in index page. Pd_pagesize_version is the storage bit of page size and page version. Page version is different in different database versions:

Database version

Pd_pagesize_version

8.3

four

Prune_xid indicates that the transaction id on this page that first deletes or modifies tuple will be used during the vacuum operation. (pg does not have undo, and the old data is also in page. Use vacuum to clean up.)

2.3 linp structure (row pointer)

Lp_off is the offset of the beginning of the tuple; lp_flags is the marker bit; and lp_len records the length of the tuple.

Field

Length

Description

Lp_off

15 bits

Offset to tuple

Lp_flags

2 bits

State of iteam pointer

Lp_len

15 bits

Byte length of tuple

2.4 tuple header structure (headline)

Typedef struct HeapTupleFields

{

TransactionId tweexmin; / * inserting xact ID * /

TransactionId tweexmax; / * deleting or locking xact ID * /

Union

{

CommandId talkicides; / * inserting or deleting command ID, or both * /

TransactionId tweexvaca; / * old-style VACUUM FULL xact ID * /

} t_field3

} HeapTupleFields

Typedef struct DatumTupleFields

{

Int32 datum_len_; / * varlena header (do not touch directly!) * /

Int32 datum_typmod; / *-1, or identifier of a record type * /

Oid datum_typeid; / * composite type OID, or RECORDOID * /

/ *

* Note: field ordering is chosen with thought that Oid might someday

* widen to 64 bits.

, /

} DatumTupleFields

Struct HeapTupleHeaderData

{

Union

{

HeapTupleFields t_heap

DatumTupleFields t_datum

} t_choice

ItemPointerData tweetid; / * current TID of this or newer tuple (or a

* speculative insertion token) * /

/ * Fields below here must match MinimalTupleData! * /

Uint16 tasking infomask2; / * number of attributes + various flags * /

Uint16 tasking infomask; / * various flag bits, see below * /

Uint8 tweehoff; / * sizeof header incl. Bitmap, padding * /

/ * ^-23 bytes-^ * /

Bits8 t _ bits [Flexble _ ARRAY_MEMBER]; / * bitmap of NULLs * /

/ * MORE DATA FOLLOWS AT END OF STRUCT * /

}

(* this part of the code is in src/include/access/htup_details.h)

The corresponding length and description are also described in detail:

Field

Type

Length

Description

T_xmin

TransactionId

4 bytes

Insert XID stamp

T_xmax

TransactionId

4 bytes

Delete XID stamp

T_cid

CommandId

4 bytes

Insert and/or delete CID stamp (overlays with t_xvac)

T_xvac

TransactionId

4 bytes

XID for VACUUM operation moving a row version

T_ctid

ItemPointerData

6 bytes

Current TID of this or newer row version

T_infomask2

Uint16

2 bytes

Number of attributes, plus various flag bits

T_infomask

Uint16

2 bytes

Various flag bits

T_hoff

Uint8

1 byte

Offset to user data

Union is a shared structure, and the variables that work are the members of the last assignment. Take a look at the structure of tuple header.

In HeapTupleFields, the t_xmin is the transaction that inserts the row tuple. The transaction id;t_xmax that deletes or locks the tuple is the t_cid in the transaction id;union structure that deletes or inserts the tuple, that is, the command sequence number; t_xvac is the transaction id used by the vacuum full in the previous format.

In DatumTupleFields, datum_len_ refers to the length of the tuple; datum_typmod is the type;datum_typeid of the record and the id of the record.

The header HeapTupleHeaderData contains two variables HeapTupleFields and DatumTupleFields in the union structure. T_ctid is tuple id, similar to oracle's rowid, in the form (block number, line number).

T_infomask2 represents attributes and flag bits

T_infomask is the flag flag bit. The specific values are as follows:

/ *

* information stored in t_infomask:

, /

# define HEAP_HASNULL 0x0001 / * has null attribute (s) * /

# define HEAP_HASVARWIDTH 0x0002 / * has variable-width attribute (s) * /

# define HEAP_HASEXTERNAL 0x0004 / * has external stored attribute (s) * /

# define HEAP_HASOID 0x0008 / * has an object-id field * /

# define HEAP_XMAX_KEYSHR_LOCK 0x0010 / * xmax is a key-shared locker * /

# define HEAP_COMBOCID 0x0020 / * t_cid is a combo cid * /

# define HEAP_XMAX_EXCL_LOCK 0x0040 / * xmax is exclusive locker * /

# define HEAP_XMAX_LOCK_ONLY 0x0080 / * xmax, if valid, is only a locker * /

/ * xmax is a shared locker * /

# define HEAP_XMAX_SHR_LOCK (HEAP_XMAX_EXCL_LOCK | HEAP_XMAX_KEYSHR_LOCK)

# define HEAP_LOCK_MASK (HEAP_XMAX_SHR_LOCK | HEAP_XMAX_EXCL_LOCK |\

HEAP_XMAX_KEYSHR_LOCK)

# define HEAP_XMIN_COMMITTED 0x0100 / * t_xmin committed * /

# define HEAP_XMIN_INVALID 0x0200 / * t_xmin invalid/aborted * /

# define HEAP_XMIN_FROZEN (HEAP_XMIN_COMMITTED | HEAP_XMIN_INVALID)

# define HEAP_XMAX_COMMITTED 0x0400 / * t_xmax committed * /

# define HEAP_XMAX_INVALID 0x0800 / * t_xmax invalid/aborted * /

# define HEAP_XMAX_IS_MULTI 0x1000 / * t_xmax is a MultiXactId * /

# define HEAP_UPDATED 0x2000 / * this is UPDATEd version of row * /

# define HEAP_MOVED_OFF 0x4000 / * moved to another place by pre-9.0

* VACUUM FULL; kept for binary

* upgrade support * /

# define HEAP_MOVED_IN 0x8000 / * moved from another place by pre-9.0

* VACUUM FULL; kept for binary

* upgrade support * /

# define HEAP_MOVED (HEAP_MOVED_OFF | HEAP_MOVED_IN)

# define HEAP_XACT_MASK 0xFFF0 / * visibility-related bits * /

T_hoff represents the length of the tuple header

T_bits records the columns of null values in tuple

3. Install pageinspect in Lab 3.1

It's under the crontrib directory of the source code.

Postgres@cs- > cd postgresql-10.4/contrib/pageinspect

Make & & make install

Postgres@cs- > make

Postgres@cs- > make install

Create extension would be fine.

Postgres@cs- > psql

Psql

Type "help" for help.

Postgres=# CREATE EXTENSION pageinspect

CREATE EXTENSION

Postgres=#\ x

Expanded display is on.

Postgres=#\ dx

List of installed extensions

-[RECORD 1]--

Name | pageinspect

Version | 1.6

Schema | public

Description | inspect the contents of database pages ata low level

-[RECORD 2]--

Name | plpgsql

Version | 1.0

Schema | pg_catalog

Description | PL/pgSQL procedural language

3.2 create test table T1 and insert data

Here you can see that 1000 rows of data are stored in 6 blocks (here the block starts at 0), and the sixth block contains 73 records (tuple)

3.3 Pageinspect View page

Here we use two functions to see

Page_header can see the data in the header

Heap_pageitems can see the data of specific tuple

3.3.1 page_header

Postgres=#\ xExpanded display is on.postgres=# select * from page_header (get_raw_page ('t1jewelry journal 0));-[RECORD 1]-lsn | 0/1671188checksum | 0flags | 0lower | 772upper | 784special | 8192pagesize | 8192version | 4prune_xid | 0postgres=#

You can see that the pd_lsn of the 0th page is 0x1671188 checksum and flags is 0, and the start offset of checksum;tuple is 772 (pd_lower) and the end offset is 784 (pd_upper). This page is a table, so it has no special, so the sepcial we see is 8192; pagesize is 8192, which means 8K version is 4, and there is no tuple to clean up, so the id for storing the earliest transactions of tuple that need to be cleaned is 0 (prune_xid).

3.3.2 heap_page_items

If we look at a row of records, we can see that it is the first line of record (lp=1), the start offset of tuple is 8160 (lp_off), the length of tuple is 32 bytes (lp_len is 32, this tuple is the first inserted tuple, so lp_off+lp_len=8160+32=8192), the insert transaction id of this row is 557 (t_min), and the delete transaction id of tuple is 0 (tmax), where the data is not deleted, so it is all 0. We can also see that the t_ctid is (0t_ctid 1), which means that the tuple is the first tuple;tinfomask2 of the first block in the page. The second tuple;tinfomask2 is 2306, and the hexadecimal is 0x0902. We can look at the specific meaning according to the values mentioned above. The length of the header structure (header) of the 0x0800 = 0x0100 + 0x0800 + 0x0002 is 24 (t_hoff), and the t_data is the real data stored in hexadecimal.

3.4 Delete a row of data observation prune_xid

If we delete a row of tuple, we can see that prune_xid has a value of 559. this 559 is the transaction id that deletes the tuple (the current earliest transaction id that deleted or changed tuple)

Similarly, we can see that the t_xmax of the tuple with lp 1 is 559.This is the transaction id that deletes the line of tuple.

The physical structure of PostgreSQL Page is much simpler than the data blocks of Oracle, and the open source code is also easy to learn and research. Pg is a very good and powerful database, which is worth learning.

The above is all the contents of the article "how to learn PostgreSQL Page structure from scratch". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.