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

This article mainly introduces "row data analysis in PostgreSQL data page Page". In daily operation, I believe that many people have doubts about row data analysis in PostgreSQL data page Page. Xiaobian consulted all kinds of data and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "row data analysis in PostgreSQL data page Page". Next, please follow the editor to study!

1. Test data

See the previous section for details, and the contents of the data file are as follows:

[xdb@localhost utf8db] $hexdump-C $PGDATA/base/16477/2480100000000 01000000 88 20 2a 12 00000000 28 00 60 1f |. *. (.`. | 00000010 00 20 04 20 000000 00 d8 9f 4e 00 b09f 4e 00 |. . .N. | 00000020 88 9f 4e 00 60 9f 4e 000000 000000 000000 | .N.`N. | 00000030 0000000000000000000000000 |. | * 00001f60 e5 1b 1800000000000000000000000 |. | 00001f70 04 0003 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 000 00 13 33 20 20 20 05 63 00 | .3 | .c. | 00001fb0 e3 1b 18 0000 0000 0000 00 |. | 00001fc0 02 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 08 18 00 |. | | 00001ff0 01 01 00 13 31 20 20 20 05 61 00 | .1 .a. | 0000 2000 2. Items (Tuples)

Each Tuple consists of two parts, the first part is the Tuple header information, and the second part is the actual data.

1 、 HeapTupleHeader

The relevant data structures are as follows:

/-src/include/storage/off.h/** OffsetNumber:** this is a 1-based index into the linp (ItemIdData) array in the* header of each disk page.*/typedef uint16 OffsetNumber;//- src/include/storage/block.h/** BlockId:** this is a storage type for BlockNumber. In other words, this type* is used for on-disk structures (e.g.in HeapTupleData) whereas* BlockNumber is the type on which calculations are performed (e.g.in access method code). * * there doesn't appear to be any reason to have separate types except* for the fact that BlockIds can be SHORTALIGN'd (and therefore any* structures that contains them, such as ItemPointerData, can also be* SHORTALIGN'd). This is an important consideration for reducing the* space requirements of the line pointer (ItemIdData) array on each* page and the header of each heap or index tuple, so it doesn't seem* wise to change this without good reason.*/typedef struct BlockIdData {uint16 bi_hi; uint16 bi_lo;} BlockIdData;typedef BlockIdData * BlockId / * block identifier * / /-src/include/storage/itemptr.h/* * ItemPointer: * * This is a pointer to an item within a disk page of a known file * (for example, a cross-link from an index to its parent table). * blkid tells us which block, posid tells us which entry in the linp * (ItemIdData) array we want. * * Note: because there is an item pointer in each tuple header and index * tuple header on disk, it's very important not to waste space with * structure padding bytes. The struct is designed to be six bytes long * (it contains three int16 fields) but a few compilers will pad it to * eight bytes unless coerced. We apply appropriate persuasion where * possible. If your compiler can't be made to play along, you'll waste * lots of space. * / typedef struct ItemPointerData {BlockIdData ip_blkid; OffsetNumber ip_posid;} / /-src/include/access/htup_details.htypedef struct HeapTupleFields {TransactionId tweexmin; / * inserting xact ID * / TransactionId tweexmax; / * deleting or locking xact ID * / union {CommandId t_cid / * inserting or deleting command ID, or both * / TransactionId troomxvaca; / * old-style VACUUM FULL xact ID * /} troomfield3;} 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 * / * * datum_typeid cannot be a domain over composite, only plain composite, * even if the datum is meant as a value of a domain-over-composite type. * This is in line with the general principle that CoerceToDomain does not * change the physical representation of the base type value. * * Note: field ordering is chosen with thought that Oid might someday * widen to 64 bits. * /} DatumTupleFields;struct HeapTupleHeaderData {union {HeapTupleFields tasking heap; DatumTupleFields tasking data;} tweechoice; ItemPointerData tweectid; / * current TID of this or newer tuple (or a * speculative insertion token) * / * Fields below here must match MinimalTupleData! * / # define FIELDNO_HEAPTUPLEHEADERDATA_INFOMASK2 2 uint16 t_infomask2 / * number of attributes + various flags * / # define FIELDNO_HEAPTUPLEHEADERDATA_INFOMASK 3 uint16 tasking infomation; / * various flag bits, see below * / # define FIELDNO_HEAPTUPLEHEADERDATA_HOFF 4 uint8 tweehoff; / * sizeof header incl. Bitmap, padding * / / * ^-23 bytes-^ * / # define FIELDNO_HEAPTUPLEHEADERDATA_BITS 5 bits8 tbits [flex _ ARRAY_MEMBER]; / * bitmap of NULLs * / / * MORE DATA FOLLOWS AT END OF STRUCT * /}

Expand the structure, as shown in the following table:

Field Type Length Offset Descriptiont_xmin TransactionId 4 bytes 0 insert XID stampt_xmax TransactionId 4 bytes 4 delete XID stampt_cid CommandId 4 bytes 8 insert and/or delete CID stamp (overlays with t_xvac) t_xvac TransactionId 4 bytes 8 XID for VACUUM operation moving a row versiont_ctid ItemPointerData 6 bytes 12 current TID of this or Newer row versiont_infomask2 uint16 2 bytes 18 number of attributes Plus various flag bitst_infomask uint16 2 bytes 20 various flag bitst_hoff uint8 1 byte 22 offset to user data// Note: t_cid and t_xvac are a combination Shared storage space

From the previous section, we have concluded that the offset of the first Tuple is 8152. Let's use hexdump to parse the data one by one:

T_xmin

[xdb@localhost ~] $hexdump-C $PGDATA/base/16477/24801-s 8152-n 400001fd8 e2 1b 18 00 | 00001fdc [XDB @ localhost ~] $echo $((0x00181be2)) 1580002

T_xmax

[xdb@localhost ~] $hexdump-C $PGDATA/base/16477/24801-s 8156-n 400001fdc 0000 0000 |.... | 00001fe0

T_cid/t_xvac

[xdb@localhost ~] $hexdump-C $PGDATA/base/16477/24801-s 8160-n 400001fe0 0000 0000 |.... | 00001fe4

T_ctid

[xdb@localhost ~] $hexdump-C $PGDATA/base/16477/24801-s 8164-n 600001fe4 0000 0000 01 00 |. | 00001feaAccording to ipkids blkidp =\ x0000, that is, blockid=0//ip_posid=\ x0001, that is posid=1, the first tuple

T_infomask2

[xdb@localhost ~] $hexdump-C $PGDATA/base/16477/24801-s 8170-n 200001fea 03 00 |.. | what does 00001fectionbank tweak infomask2 =\ x0003jue 3 mean? Let's take a look at the description of t_infomask2 / * * information stored in t_infomask2: * / # define HEAP_NATTS_MASK 0x07FF / * 11 bits for number of attributes * / / * bits 0x1800 are available * / # define HEAP_KEYS_UPDATED 0x2000 / * tuple was updated and key cols * modified Or tuple deleted * / # define HEAP_HOT_UPDATED 0x4000 / * tuple was HOT-updated * / # define HEAP_ONLY_TUPLE 0x8000 / * this is heap-only tuple * / # define HEAP2_XACT_MASK 0xE000 / * visibility-related bits * / / Root converts hexadecimal values to binary display 11111111111 # define HEAP_NATTS_MASK 0x07FF 10000000000000 # define HEAP_KEYS_UPDATED 0x2000 100000000000000 # define HEAP_HOT_UPDATED 0x4000 10000000000000 # define HEAP_ONLY_TUPLE 0x8000 1110000000000000 # define HEAP2_XACT_MASK 0xE000 111111111111111110 # define SpecTokenOffsetNumber 0xfffe// before 11 bits are the number of attributes 3 means there are 3 properties (fields)

T_infomask

[xdb@localhost ~] $hexdump-C $PGDATA/base/16477/24801-s 8172-n 200001fec 02 08 |.. | 00001 [XDB @ localhost ~] $echo $((0x0802)) 2050 [xdb@localhost ~] $echo "obase=2 2050 "| bc100000000010//t_infomask=\ x0802, with a decimal value of 2050 Binary value is 100000000010//t_infomask description 1 # define HEAP_HASNULL 0x0001 / * has null attribute (s) * / 10 # define HEAP_HASVARWIDTH 0x0002 / * has variable-width attribute (s) * / 100 # define HEAP_HASEXTERNAL 0x0004 / * has external stored attribute (s) * / 1000 # define HEAP_HASOID 0x0008 / * has an object-id field * / 10000 # define HEAP_XMAX_KEYSHR_LOCK 0x0010 / * xmax is a key-shared locker * / 100000 # define HEAP_COMBOCID 0x0020 / * t_cid is a combo cid * / 1000000 # define HEAP_XMAX_EXCL_LOCK 0x0040 / * xmax is exclusive locker * / 10000000 # 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) 100000000 # define HEAP_XMIN_COMMITTED 0x0100 / * t_xmin committed * / 1000000000 # define HEAP_XMIN_INVALID 0x0200 / * t_xmin invalid/aborted * / # define HEAP_XMIN_FROZEN (HEAP_XMIN_COMMITTED | HEAP_XMIN_INVALID) 10000000000 # define HEAP_XMAX_COMMITTED 0x0400 / * t_xmax committed * / 100000000000 # define HEAP_XMAX_INVALID 0x0800 / * t_xmax invalid / aborted * / 1000000000000 # define HEAP_XMAX_IS_MULTI 0x1000 / * t_xmax is a MultiXactId * / 10000000000000 # define HEAP_UPDATED 0x2000 / * this is UPDATEd version of row * / 100000000000000 # define HEAP_MOVED_OFF 0x4000 / * moved to another place by pre-9.0 * VACUUM FULL Kept for binary * upgrade support * / 1000000000000000 # 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) 1111111111110000 # define HEAP_XACT_MASK 0xFFF0 / * visibility-related bits * /\ x0802, binary 100000000010 means bit 2 and bit 12 are 1 HEAP_HASVARWIDTH / means there is a variable length attribute (HEAP_HASVARWIDTH) and XMAX is invalid (HEAP_XMAX_INVALID)

T_hoff

[xdb@localhost ~] $hexdump-C $PGDATA/base/16477/24801-s 8174-n 100001fee 18 |. | 00001fef [XDB @ localhost ~] $echo $((0x18)) 24pm / user data begins to offset to 24, that is, 8152 to 24

2 、 Tuple

After talking about the header data of Tuple, let's take a look at the actual data storage. In the previous section, we found that the total length of Tuple is 39, and the calculated data size is 39-24-15.

[xdb@localhost ~] $hexdump-C $PGDATA/base/16477/24801-s 8176-n 1500001ff0 01 0000 00 13 31 20 20 20 05 61 | .1.a | 00001fff reviews our table structure: create table t_page (id int,c1 char (8), c2 varchar (16)); the first field is int, the second field is a fixed length character, and the third field is a variable length character. The corresponding data: id=\ x00000001, the number 1C1 =\ x13312020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020 The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.