Help Center/ GaussDB/ Developer Guide(Distributed_8.x)/ SQL Reference/ Functions and Operators/ System Administration Functions/ Segment-Page Storage Functions

Updated on 2024-08-20 GMT+08:00

View PDF

Segment-Page Storage Functions

To create a segment-page table on Astore, you need to set segment to on when creating the table. Example:
```
CREATE TABLE t1(id int) WITH (segment=on, storage_type=astore);
```

The values of the columns related to the segment-page storage function are described as follows:
1. forknum: data file fork.
  Value range: [0: mainfork; 1: fsmfork; 2: vm fork].
2. file id: data file ID.
  Value range: [1: metadata file; 2–5: data file].
3. blocks: extent size.
  Value range: [1: No.1 file; 8: No.2 file; 128: No.3 file; 1024: No.4 file; 4096: No.5 file].
4. file_block_id/head_block_id/block_id: offset page number of the physical page in the data file and other fields that indicate the page number.
  Value range: [0,4294967294]
5. page_ type: page type.
  Value range:
  
  Meta pages: file head/file_header: file header; spc head/spc_header: space header; map head/map_header: mapping header; map page/map_pages: mapping page; reverse pointer page/inverse pointer page/ip pages: reverse pointer page; segment head page/segment head: segment header page; level1 page: level-1 page; data_pages/data extent: data page; fork head: fork header.
  
  Data pages: heap, uheap, btree, and ubtree.
  
  Unknown pages: unknown(data extent): all-zero segment page whose type cannot be determined; unknown(fsm indexurq): fsm or indexurq page.
6. contents: storage content of a data file.
  Value range: permanent (permanent), unlogged (no log), temporary (global temporary), and temporary2 (local temporary)
local_space_shrink(tablespacename TEXT, databasename TEXT)
Description: Shrinks specified physical segment-page space on the current node. Only the currently connected database can be shrank.

Return value: empty
gs_space_shrink(tablespace int4, database int4, extent_type int4, forknum int4)
Description: Works similar to local_space_shrink. That is, shrinks specified physical segment-page space. However, the parameters are different. The input parameters are the OIDs of the tablespace and database, and the value of extent_type is an integer ranging from 2 to 5. The value 1 of extent_type indicates segment-page metadata. Currently, the physical file that contains the metadata cannot be shrunk. This function is used only by tools. You are advised not to use it directly.

Return value: empty
global_space_shrink(tablespacename TEXT, databasename TEXT)
Description: Compresses segment-page storage space on all DNs in a cluster. This is performed on a CN.

Note: If global_space_shrink locks the cluster. DDL operations cannot be performed. local_space_shrink does not lock the cluster.

gs_stat_remain_segment_info()

Description: Runs on the CN to query extents on DNs with residual data due to faults. By default, only initial users, users with the sysadmin permission, and users with the O&M administrator permission in the O&M mode can view the information. Other users can view the information only after being granted with permissions. This function can be executed only on the primary node. Residual extents are classified into two types: segments that are allocated but not used and extents that are allocated but not used. The main difference is that a segment contains multiple extents. During reclamation, all extents in the segment need to be recycled.

Return type

Name	Description
node_name	Node name
space_id	Tablespace ID
db_id	Database ID
block_id	Extent ID
type	Extent type. The options are as follows: ALLOC_SEGMENT, DROP_SEGMENT, and SHRINK_EXTENT.

The values of type are described as follows:

ALLOC_SEGMENT: When a user creates a segment-page table and the segment is just allocated but the transaction of creating a table is not committed, the node is faulty. As a result, the segment is not used after being allocated.
DROP_SEGMENT: When a user deletes a segment-page table and the transaction is successfully committed, the bit corresponding to the segment page of the table is not reset and a fault, such as power failure, occurs. As a result, the segment is not used or released.
SHRINK_EXTENT: When a user shrinks a segment-page table and does not release the idle extent, a fault, such as power failure, occurs. As a result, the extent remains and cannot be reused.

Example:

gaussdb=# SELECT * FROM gs_stat_remain_segment_info();

     node_name     | space_id | db_id | block_id |     type      
-------------------+----------+-------+----------+---------------
 dn_6001_6002_6003 |    16804 | 16803 |     4157 | ALLOC_SEGMENT
(1 row)

gs_free_remain_segment()
Description: Frees all segments queried by using the gs_stat_remain_segment_info function in the current database. By default, only initial users, users with the sysadmin permission, and users with the O&M administrator permission in the O&M mode can execute the function. Other users can use the function only after being granted with permissions. This function can be executed only on the primary node.

Return type: Boolean
gs_local_stat_remain_segment_info()
Description: Runs on the primary DN to query the residual segment page information of the current node. For details about user permissions and return values, see gs_stat_remain_segment_info.
gs_local_free_remain_segment()
Description: Runs on the primary DN to free segments queried by using the gs_local_stat_remain_segment_info function in the current database. For details about user permissions and return values, see gs_free_remain_segment.

gs_seg_dump_page(tablespace_name name, file_id int4, bucketnode int4, file_block_id bigint, forknum int4 default 0)

Description: Parses a specified page in segment-page mode and returns the parsed content. Only users with the sysadmin attribute and users with the O&M administrator attribute in O&M mode can execute this function. This function is used to parse physical pages. The parsing result of one page is returned each time. The returned result does not contain the actual user data. This function does not require users to enter the page type. During implementation, the system attempts to determine the page type. If the page type cannot be determined, the system outputs possible parsing results.

Parameter description:

Name	Type	Description
table_space_name	NAME	Tablespace to which a segment object belongs. Value range: a valid tablespace name.
file_id	INTEGER	Data file ID. Value range: an int4 value in the range [1,5].
bucketnode	INTEGER	0 to 1023 indicate the bucket nodes of a hash bucket table. 1024 indicates the bucket node of a segment-page ordinary table. 1025 indicates the bucket node of a segment-page global temporary table. 1026 indicates the bucket node of a segment-page unlogged table. 1027 indicates the bucket node of a segment-page local temporary table.
file_block_id	BIGINT	Offset page number of the physical page in the data file. Value range: [0,4294967294]
forknum	INTEGER DEFAULT 0	Fork number of a data file. The default value is 0. Value range: 0: main fork. 1: FSM fork. 2: VM fork.

Return type

Name	Type	Description
page_type	TEXT	Page type. Value range: Service pages: heap, uheap, btree, and ubtree. Segment-page metapages: bucket main head, file head, spc head, map head, map page, reverse pointer page, segment head page and level1 page. Unknown pages: unknown(data extent): all-zero segment page whose type cannot be determined. unknown(fsm indexurq): fsm or indexurq page.
result	TEXT	Parsing result.

Name

Type

Description

page_type

TEXT

Page type. Value range:

Service pages: heap, uheap, btree, and ubtree.
Segment-page metapages: bucket main head, file head, spc head, map head, map page, reverse pointer page, segment head page and level1 page.
Unknown pages:
- unknown(data extent): all-zero segment page whose type cannot be determined.
- unknown(fsm indexurq): fsm or indexurq page.

result

TEXT

Parsing result.

For example, perform the following operations after creating a segment-page ordinary table:

gaussdb=# SELECT * FROM gs_seg_dump_page('pg_default', 1, 1024, 4157);
     page_type     |                                   result                                    
-------------------+-----------------------------------------------------------------------------
 segment head page | Page information of block 4157/4157                                        +
                   |         pd_lsn: 0/2C90608 ,len 8 ,offset 0                                 +
                   |                                                                            +
                   |         pd_checksum: 0x8A7F, verify success,len 2, offset 8                +
                   |         pd_flags:                                                          +
                   |         pd_lower: 24, empty, len 2, offset 12                              +
                   |         pd_upper: 8192, old, len 2, offset 14                              +
                   |         pd_special: 8192, size 0, len 2, offset 16                         +
                   |         Page size & version: 8192, 8, len 2, offset 18                     +
                   |         pd_xid_base: 0, len 8, offset 24 pd_multi_base: 0, len 8, offset 32+
                   |         pd_prune_xid: 0, len 4 ,offset 20                                  +
                   |                                                                            +
                   |         Segment head information on this page                              +
                   |                 magic: 44414548544E454D                                    +
                   |                 lsn is: 0/2C90540                                          +
                   |                 nblocks: 0                                                 +
                   |                 total_blocks: 8                                            +
                   |                 reserved: 0                                                +
                   |                 Level 0 slots information on this page                     +
                   |                         The BlockNumber of level0 slots 0 is: 4157         +
                   |                 fork head information on this page                         +
                   |                         4157(valid)                                        +
                   |                         4294967295(invalid)                                +
                   |                         4294967295(invalid)
(1 row)

gs_seg_dump_page(relid oid, bucketnode int, block_id bigint, partition bool default false, forknum int4 default 0)

Description: Parses a specified page in segment-page mode and returns the parsed content. This function is executed on DNs. Only users with the sysadmin attribute and users with the O&M administrator attribute in O&M mode can execute this function. This function is used to parse logical pages. The parsing result of one page is returned each time. The returned result does not contain the actual user data. This function does not require users to enter the page type. During implementation, the system attempts to determine the page type. If the page type cannot be determined, the system outputs possible parsing results.

Parameter description:

Name	Type	Description
relid	OID	Segment object ID. Value range: a valid segment-page object ID. Otherwise, an error is reported.
bucketnode	INTEGER	0 to 1023 indicate the bucket nodes of a hash bucket table. 1024 indicates the bucket node of a segment-page ordinary table. 1025 indicates the bucket node of a segment-page global temporary table. 1026 indicates the bucket node of a segment-page unlogged table. 1027 indicates the bucket node of a segment-page local temporary table.
block_id	BIGINT	Logical page number. Value range: [0,4294967294]
partition	BOOLEAN DEFAULT FALSE	Specifies whether the segment object is a partition. The default value is false. Value range: bool, indicating whether the object corresponding to the input OID is a partition.
forknum	INTEGER DEFAULT 0	Fork number of a data file. The default value is 0. Value range: 0: main fork. 1: FSM fork. 2: VM fork.

Return value description:

Name	Type	Description
page_type	TEXT	Page type. Value range: Service pages: heap, uheap, btree, and ubtree. Segment-page metapages: bucket_main_head, file head, spc head, map head, map page, reverse pointer page, segment head page, and level1 page. Unknown pages: unknown(data extent): all-zero segment page whose type cannot be determined. unknown(fsm indexurq): fsm or indexurq page.
result	TEXT	Parsing result.

Name

Type

Description

page_type

TEXT

Page type. Value range:

Service pages: heap, uheap, btree, and ubtree.
Segment-page metapages: bucket_main_head, file head, spc head, map head, map page, reverse pointer page, segment head page, and level1 page.
Unknown pages:
- unknown(data extent): all-zero segment page whose type cannot be determined.
- unknown(fsm indexurq): fsm or indexurq page.

result

TEXT

Parsing result.

For example, perform the following operations on a DN, and ensure that relid must be a valid OID of the segment-page object on the DN, and the object must have data so that the page information can be queried:

gaussdb=# SELECT * FROM gs_seg_dump_page(16788, 1024, 0);
 page_type |                                                         result
-----------+-------------------------------------------------------------------------------------------------------------------------
 heap      | Page information of block 6021/6021                                                                                    +
           |         pd_lsn: 0/4463418 ,len 8 ,offset 0                                                                             +
           |                                                                                                                        +
           |         pd_checksum: 0xD4CD, verify success,len 2, offset 8                                                            +
           |         pd_flags:                                                                                                      +
           |         pd_lower: 44, non-empty, len 2, offset 12                                                                      +
           |         pd_upper: 8160, old, len 2, offset 14                                                                          +
           |         pd_special: 8192, size 0, len 2, offset 16                                                                     +
           |         Page size & version: 8192, 6, len 2, offset 18                                                                 +
           |         pd_xid_base: 17049, len 8, offset 24 pd_multi_base: 0, len 8, offset 32                                        +
           |         pd_prune_xid: 17049, len 4 ,offset 20                                                                          +
           |                                                                                                                        +
           |         Heap tuple information on this page                                                                            +
           |                                                                                                                        +
           |                 Tuple #1 is normal: length 28, offset 8160                                                             +
           |                         (uint64)xmin/xmax/t_cid: 17052/0/0                                                             +
           |                         (uint32)t_xmin/t_xmax: 3/3(check ilm flag to indicate whether t_xmin/t_xmax is xid or ilm time)+
           |                         ctid:(block 0/0, offset 1)                                                                     +
           |                         t_infomask: HEAP_XMAX_INVALID HEAP_HAS_NO_UID                                                  +
           |                         t_infomask2: Attrs Num: 1                                                                      +
           |                         t_hoff: 24                                                                                     +
           |                         t_bits: NNNNNNNN                                                                               +
           |         Summary (1 total):  1 normal, 0 unused, 0 dead                                                                 +
           |                                                                                                                        +
           | Normal Heap Page, special space is 0                                                                                   +
           |                                                                                                                        +
           |
(1 row)

gs_seg_get_spc_location(tablespace_name NAME, bucketnode INTEGER, head_block_id BIGINT, block_id BIGINT)

Description: Calculates the physical location given a segment and logical page number. Only an administrator can query the information.

Parameter description

Name	Type	Description
tablespace_name	NAME	Tablespace to which a segment object belongs.
bucketnode	INTEGER	0 to 1023 indicate the bucket nodes of a hash bucket table. 1024 indicates the bucket node of a segment-page ordinary table. 1025 indicates the bucket node of a segment-page global temporary table. 1026 indicates the bucket node of a segment-page unlogged table. 1027 indicates the bucket node of a segment-page local temporary table.
head_block_id	BIGINT	Page number of a segment header.
block_id	BIGINT	Logical page number.

Return value description

Name	Type	Description
extent_id	INTEGER	Logical extent number of the logical page.
extent_size	INTEGER	Size of the logical extent where the logical page is located.
file_id	INTEGER	ID of the data file where the physical page is located.
file_block_id	BIGINT	Offset page number of the physical page in the data file.

For example, perform the following operations on the DN after creating a segment-page ordinary table in the tablespace and inserting data:

gaussdb=# SELECT * FROM gs_seg_get_spc_location('pg_default', 1024, 4157, 0);
 extent_id | extent_size | file_id | file_block_id
-----------+-------------+---------+---------------
         0 |           8 |       2 |          4157
(1 row)

gs_seg_get_spc_location(relid OID, bucketnode INTEGER, block_id BIGINT, partition BOOLEAD DEFAULT FALSE, forknum INTEGER DEFAULT 0)

Description: Calculates the physical location given a segment and logical page number. Only an administrator can query the information.

Parameter description

Name	Type	Description
relid	OID	Segment object ID.
bucketnode	INTEGER	0 to 1023 indicate the bucket nodes of a hash bucket table. 1024 indicates the bucket node of a segment-page ordinary table. 1025 indicates the bucket node of a segment-page global temporary table. 1026 indicates the bucket node of a segment-page unlogged table. 1027 indicates the bucket node of a segment-page local temporary table.
block_id	BIGINT	Logical page number.
partition	BOOLEAD DEFAULT FALSE	Specifies whether the segment object is a partition. The default value is FALSE.
forknum	INTEGER DEFAULT 0	Fork of a segment object. The default value is 0. Value range: 0: main fork. 1: FSM fork. 2: VM fork.

Return value description

Name	Type	Description
extent_id	INTEGER	Logical extent number of the logical page.
extent_size	INTEGER	Size of the logical extent where the logical page is located.
file_id	INTEGER	ID of the data file where the physical page is located.
file_block_id	BIGINT	Offset page number of the physical page in the data file.

For example, perform the following operations on a DN, and ensure that relid must be a valid OID of the segment-page object on the DN, and the object must have data:

gaussdb=# SELECT * FROM gs_seg_get_spc_location(24578,1024, 0);
 extent_id | extent_size | file_id | file_block_id
-----------+-------------+---------+---------------
         0 |           8 |       2 |          4157
(1 row)

gs_seg_get_location(block_id BIGINT, as_extent BOOLEAN DEFAULT FALSE)

Description: Calculates the physical location given a segment and logical page number. Only an administrator can query the information.

Parameter description

Name	Type	Description
block_id	BIGINT	Logical page number.
as_extent	BOOLEAN DEFAULT FALSE	Specifies whether the block_id parameter is an extent number. The default value is FALSE.

Return value description

Name	Type	Description
extent_id	BIGINT	Extent number.
extent_size	INTEGER	Extent size.
extent_offset	INTEGER	Extent offset block number.
level0_slots_idx	INTEGER	Index of the extent in the level-0 slot array in the segment header.
level1_slots_idx	INTEGER	Index of the extent in the level-1 slot array in the segment header.
level1_page_offset	INTEGER	Offset of the extent on the level-1 slot page in the segment header.
segment_blocks	BIGINT	Number of pages containing this page or extended segments.
relative_fno	INTEGER	Number of the relative file that contains this page or extent.

Example:

gaussdb=# SELECT * FROM gs_seg_get_location(4157);
 extent_id | extent_size | extent_offset | level0_slots_idx | level1_slots_idx | level1_page_offset | segment_blocks | relative_fno
-----------+-------------+---------------+------------------+------------------+--------------------+----------------+--------------
        47 |         128 |            61 |               47 |                  |                    |           4158 |            3
(1 row)

gs_seg_get_segment_layout()

Description: Outputs the static segment layout. Only an administrator can query the information.

Return value description

Name	Type	Description
version	TEXT	Segment-page version. The default value is 1.0.
section_id	INTEGER	ID of the data section divided by a segment.
section_type	TEXT	Extent type of the segment data section. Value range: meta: segment header. data: data.
extent_size	INTEGER	Extent size. The unit is byte.
extent_page_count	INTEGER	Number of extent pages.
extent_count_start	BIGINT	Start extent number.
extent_count_end	BIGINT	End extent number.
total_size	BIGINT	Size of the segment data section. The unit is byte.

Example:

gaussdb=# SELECT * FROM gs_seg_get_segment_layout();
 version | section_id | section_type | extent_size | extent_page_count | extent_count_start | extent_count_end |   total_size
---------+------------+--------------+-------------+-------------------+--------------------+------------------+----------------
 1.0     |          1 | meta         |        8192 |                 1 |                  0 |                0 |           8192
 1.0     |          2 | data         |       65536 |                 8 |                  1 |               16 |        1048576
 1.0     |          3 | data         |     1048576 |               128 |                 17 |              143 |      134217728
 1.0     |          4 | data         |     8388608 |              1024 |                144 |              255 |     1073741824
 1.0     |          5 | data         |    33554432 |              4096 |                256 |          1025255 | 34394366541824
(5 rows)

gs_seg_get_datafile_layout()

Description: Queries the static layout of data files 1 to 5. Only an administrator can query the information.

Return value description

Name	Type	Description
version	TEXT	Segment-page version. The default value is 1.0.
seg_storage_type	TEXT	segment indicates common segment-page data. hashbucket indicates hash bucket data.
file_id	INTEGER	Data file ID.
section_id	INTEGER	Data section ID of a data file.
section_type	TEXT	Type of the data file section. Value range: file_header indicates the file header. spc_header indicates the space header. map_header indicates the mapping header. map_pages indicates the mapping page. ip_pages(inverse pointer pages) indicates the reverse pointer page. data_pages indicates the data page.
page_start	BIGINT	Start page number of the data section.
page_end	BIGINT	End page number of the data section.
page_count	BIGINT	Number of pages in the data section.
total_size	BIGINT	Size of the data section. The unit is byte.

Example:

gaussdb=# SELECT * FROM gs_seg_get_datafile_layout();
 version | seg_storage_type | file_id | section_id | section_type |  page_start  |   page_end   | page_count  |   total_size
---------+------------------+---------+------------+--------------+--------------+--------------+-------------+-----------------
 1.0     | segment          |       1 |          0 | file_header  |            0 |            0 |           1 |            8192
 1.0     | segment          |       1 |          1 | spc_header   |            1 |            1 |           1 |            8192
 1.0     | segment          |       1 |          2 | map_header   |            2 |            2 |           1 |            8192
 1.0     | segment          |       1 |          3 | map_pages    |            3 |           66 |          64 |          524288
 1.0     | segment          |       1 |          4 | ip_pages     |           67 |         4156 |        4090 |        33505280
 1.0     | segment          |       1 |          5 | data_pages   |         4157 |      4147260 |     4143104 |     33940307968

... (There is a large amount of data. Only part of the data is displayed.)

gs_seg_get_slice_layout(file_id INTEGER, bucketnode INTEGER,slice_id INTEGER)

Description: Outputs the static layout of a given data file fragment. Only an administrator can query the information.

Parameter description

Name	Type	Description
file_id	INTEGER	Data file ID.
bucketnode	INTEGER	0 to 1023 indicate the bucket nodes of a hash bucket table. 1024 indicates the bucket node of a segment-page ordinary table. 1025 indicates the bucket node of a segment-page global temporary table. 1026 indicates the bucket node of a segment-page unlogged table. 1027 indicates the bucket node of a segment-page local temporary table.
slice_id	INTEGER	Slice file ID.

Name

Type

Description

file_id

INTEGER

Data file ID.

bucketnode

INTEGER

0 to 1023 indicate the bucket nodes of a hash bucket table.

1024 indicates the bucket node of a segment-page ordinary table.
1025 indicates the bucket node of a segment-page global temporary table.
1026 indicates the bucket node of a segment-page unlogged table.
1027 indicates the bucket node of a segment-page local temporary table.

slice_id

INTEGER

Slice file ID.

Return value description

Name	Type	Description
version	TEXT	Segment-page version. The default value is 1.0.
section_id	INTEGER	Data section ID of a data file.
section_type	INTEGER	Type of the data file section. Value range: file_header indicates the file header. spc_header indicates the space header. map_header indicates the mapping header. map_pages indicates the mapping page. ip_pages(inverse pointer pages) indicates the reverse pointer page. data_pages indicates the data page.
page_start	BIGINT	Start page number of the data section.
page_end	BIGINT	End page number of the data section.
page_count	BIGINT	Number of pages in the data section.
total_size	BIGINT	Size of the data section. The unit is byte.

Example:

gaussdb=# SELECT * FROM gs_seg_get_slice_layout(1,1024,0);
 version | section_id | section_type | page_start | page_end | page_count | total_size
---------+------------+--------------+------------+----------+------------+------------
 1.0     |          0 | file_header  |          0 |        0 |          1 |       8192
 1.0     |          1 | spc_header   |          1 |        1 |          1 |       8192
 1.0     |          2 | map_header   |          2 |        2 |          1 |       8192
 1.0     |          3 | map_pages    |          3 |       66 |         64 |     524288
 1.0     |          4 | ip_pages     |         67 |     4156 |       4090 |   33505280
 1.0     |          5 | data_pages   |       4157 |   131071 |     126915 | 1039687680
(6 rows)

gs_seg_get_segment(tablespace_name NAME, bucketnode INTEGER, head_block_id BIGINT)

Description: Outputs the segment header information of the segment header page in the segment header file of the tablespace. Only an administrator can query the information.

Parameter description

Name	Type	Description
tablespace_name	NAME	Tablespace to which a segment object belongs.
bucketnode	INTEGER	0 to 1023 indicate the bucket nodes of a hash bucket table. 1024 indicates the bucket node of a segment-page ordinary table. 1025 indicates the bucket node of a segment-page global temporary table. 1026 indicates the bucket node of a segment-page unlogged table. 1027 indicates the bucket node of a segment-page local temporary table.
head_block_id	BIGINT	Page number of the segment header.

Return value description

Name	Type	Description
blocks	BIGINT	Number of logical pages of a segment.
total_blocks	BIGINT	Number of physical pages of a segment.
extents	INTEGER	Number of logical extents of a segment.
total_extents	INTEGER	Number of physical extents of a segment.
head_lsn	TEXT	Segment header LSN.
level0_slots	BIGINT[]	Level-0 slot array of segment extent mapping.
level1_slots	BIGINT[]	Level-1 slot array of segment extent mapping.
fork_head	BIGINT[]	Fork head array of a segment.

For example, perform the following operations after creating a segment-page ordinary table:

gaussdb=# SELECT * FROM gs_seg_get_segment('pg_default', 1024, 4157);
 blocks | total_blocks | extents | total_extents | head_lsn | level0_slots | level1_slots |   fork_head
--------+--------------+---------+---------------+----------+--------------+--------------+----------------
      9 |           16 |       2 |             2 | 62211744 | {4157,4165}  | {}           | {4157,4158,-1}
(1 row)

gs_seg_get_segment(relid OID, bucketnode INTEGER, partition BOOLEAD DEFAULT FALSE, forknum INTEGER DEFAULT 0)

Description: Outputs the corresponding segment header information based on relid and bucketnode. Only an administrator can query the information.

Parameter description

Name	Type	Description
relid	OID	Table OID.
bucketnode	INTEGER	0 to 1023 indicate the bucket nodes of a hash bucket table. 1024 indicates the bucket node of a segment-page ordinary table. 1025 indicates the bucket node of a segment-page global temporary table. 1026 indicates the bucket node of a segment-page unlogged table. 1027 indicates the bucket node of a segment-page local temporary table.
partition	BOOLEAN DEFAULT FALSE	Specifies whether the segment object is a partition. The default value is false. Value range: bool, indicating whether the object corresponding to the input OID is a partition.
forknum	INTEGER DEFAULT 0	Fork number of a data file. The default value is 0. Value range: 0: main fork. 1: FSM fork. 2: VM fork.

Return value description

Name	Type	Description
blocks	BIGINT	Number of logical pages of a segment.
total_blocks	BIGINT	Number of physical pages of a segment.
extents	INTEGER	Number of logical extents of a segment.
total_extents	INTEGER	Number of physical extents of a segment.
head_lsn	TEXT	Segment header LSN.
level0_slots	BIGINT[]	Level-0 slot array of segment extent mapping.
level1_slots	BIGINT[]	Level-1 slot array of segment extent mapping.
fork_head	BIGINT[]	Fork head array of a segment.

For example, perform the following operations on a DN, and ensure that relid must be the OID of the queried segment page object on the DN:

gaussdb=# SELECT * FROM gs_seg_get_segment(16768, 1024);
 blocks | total_blocks | extents | total_extents | head_lsn | level0_slots | level1_slots |   fork_head
--------+--------------+---------+---------------+----------+--------------+--------------+----------------
      9 |           16 |       2 |             2 | 62211744 | {4157,4165}  | {}           | {4157,4158,4294967295}
(1 row)

gs_seg_get_extents(tablespace_name NAME, bucketnode INTEGER, head_block_id BIGINT)

Description: Outputs all extents of the segment object on the segment header page in the segment header file of the tablespace, including segment head, fork head, level1 page in file 1, and data extents in files 2 to 5. Only an administrator can query the information.

Parameter description

Name

Type

Description

tablespace_name

NAME

Tablespace to which a segment object belongs.

bucketnode

INTEGER

0 to 1023 indicate the bucket nodes of a hash bucket table.

1024 indicates the bucket node of a segment-page ordinary table.
1025 indicates the bucket node of a segment-page global temporary table.
1026 indicates the bucket node of a segment-page unlogged table.
1027 indicates the bucket node of a segment-page local temporary table.

head_block_id

BIGINT

Page number of the segment header.

Return value description

Name	Type	Description
extent_id	INTEGER	Logical extent number.
file_id	INTEGER	ID of the data file where the extent is located.
forknum	INTEGER	Fork of a segment object. Value range: 0: main fork. 1: FSM fork. 2: VM fork.
block_id	BIGINT	Start page number in the data file where the extent is located.
blocks	INTEGER	Extent size. Value range: 1 indicates file 1. 8 indicates file 2. 128 indicates file 3. 1024 indicates file 4. 4096 indicates file 5.
usage_type	TEXT	Usage type of an extent. Value range: segment head indicates the segment header. fork head indicates the fork header. level1 page indicates the level-1 page. data extent indicates data extents.

For example, perform the following operations after creating a segment-page ordinary table:

gaussdb=# SELECT * FROM gs_seg_get_extents('pg_default', 1024, 4157);
 extent_id | file_id | forknum | block_id | blocks |  usage_type
-----------+---------+---------+----------+--------+--------------
           |       1 |       0 |     4157 |      1 | segment head
         0 |       2 |       0 |     4157 |      8 | data extent
         1 |       2 |       0 |     4165 |      8 | data extent
(3 rows)

gs_seg_get_extents(relid OID, bucketnode INTEGER, partition BOOLEAD DEFAULT FALSE, forknum INTEGER DEFAULT 0)

Description: Outputs all extents of the segment object on the segment header page in the corresponding segment header file, including segment head, fork head, level1 page in file 1 and, data extents in files 2 to 5. Only an administrator can query the information.

Parameter description

Name	Type	Description
relid	OID	Table OID.
bucketnode	INTEGER	0 to 1023 indicate the bucket nodes of a hash bucket table. 1024 indicates the bucket node of a segment-page ordinary table. 1025 indicates the bucket node of a segment-page global temporary table. 1026 indicates the bucket node of a segment-page unlogged table. 1027 indicates the bucket node of a segment-page local temporary table.
partition	BOOLEAN DEFAULT FALSE	Specifies whether the segment object is a partition. The default value is false. Value range: Boolean, indicating whether the object corresponding to the input OID is a partition.
forknum	INTEGER DEFAULT 0	Fork number of a data file. The default value is 0. Value range: 0: main fork. 1: FSM fork. 2: VM fork.

Return value description

Name	Type	Description
extent_id	INTEGER	Logical extent number.
file_id	INTEGER	ID of the data file where the extent is located.
forknum	INTEGER	Fork of a segment object. Value range: 0: main fork. 1: FSM fork. 2: VM fork.
block_id	BIGINT	Start page number in the data file where the extent is located.
blocks	INTEGER	Extent size. Value range: 1 indicates file 1. 8 indicates file 2. 128 indicates file 3. 1024 indicates file 4. 4096 indicates file 5.
usage_type	TEXT	Usage type of an extent. Value range: segment head indicates the segment header. fork head indicates the fork header. level1 page indicates the level-1 page. data extent indicates data extents.

In the following example, relid must be a valid OID of a multipart page object:

gaussdb=# SELECT * FROM gs_seg_get_extents(16768, 1024);
 extent_id | file_id | forknum | block_id | blocks |  usage_type
-----------+---------+---------+----------+--------+--------------
           |       1 |       0 |     4157 |      1 | segment head
         0 |       2 |       0 |     4157 |      8 | data extent
         1 |       2 |       0 |     4165 |      8 | data extent
(3 rows)

gs_seg_free_spc_remain_segment(tablespace_name NAME, head_file_id INTEGER, bucketnode INTEGER, head_block_id BIGINT)

Description: Releases the page occupied by the segment page-based residual segment in file 1 in a specified tablespace. You can query residual segments in the GS_SEG_SPC_REMAIN_SEGMENTS view. Only an administrator can query the information. This query can be executed only on the primary node.

This function has a defect and is planned to be reconstructed in later versions to completely solve the residual segment-page problem. This function is an offline segment-page clearing method.
To ensure the consistency between the queried residual segments and residual extents and the consistency of residual clearing, this function and the GS_SEG_SPC_REMAIN_SEGMENTS view must be executed in the DDL- or DML-restricted state. The current version does not provide the DML or DDL restriction capability. Therefore, when using this feature, ensure that the operating environment is in the DDL- or DML-restricted state.
This function can be used only when enable_segment_remain_cleanup is set to off. For details about how to enable it, see "Configuring GUC Parameters > GUC Parameters > Developer Options" in Administrator Guide.

Parameter description

Name	Type	Description
tablespace_name	NAME	Tablespace name
head_file_id	INTEGER	ID of a data file in which a segment header of a segment-page-type residual segment is located. Value range: 1
bucketnode	INTEGER	0 to 1023 indicate the bucket nodes of a hash bucket table. 1024 indicates the bucket node of a segment-page ordinary table. 1025 indicates the bucket node of a segment-page global temporary table. 1026 indicates the bucket node of a segment-page unlogged table. 1027 indicates the bucket node of a segment-page local temporary table.
head_block_id	BIGINT	Page number of a segment header.

Return type: void

Example:

gaussdb=# SELECT * FROM gs_seg_free_spc_remain_segment('pg_default', 1, 1024, 4159);
 gs_seg_free_spc_remain_segment 
--------------------------------

(1 row)

gs_seg_free_spc_remain_extent(tablespace_name NAME, file_id INTEGER, bucketnode INTEGER, forknum INTEGER, block_id BIGINT)

Description: Releases residual segment-page-type isolated extents in a specified tablespace. Residual isolated extents can be queried in the GS_SEG_SPC_REMAIN_EXTENTS view. Only an administrator can query the information. This query can be executed only on the primary node.

This function can be used only when enable_segment_remain_cleanup is set to off. For details about how to enable it, see "Configuring GUC Parameters > GUC Parameters > Developer Options" in Administrator Guide.

Parameter description

Name	Type	Description
tablespace_name	NAME	Tablespace name
file_id	INTEGER	ID of the data file where the segment-page-type residual extent is located. Value range: files 1 to 5.
bucketnode	INTEGER	0 to 1023 indicate the bucket nodes of a hash bucket table. 1024 indicates the bucket node of a segment-page ordinary table. 1025 indicates the bucket node of a segment-page global temporary table. 1026 indicates the bucket node of a segment-page unlogged table. 1027 indicates the bucket node of a segment-page local temporary table.
forknum	INTEGER	Fork number of a data file. Value range: 0: main fork. 1: FSM fork. 2: VM fork.
block_id	BIGINT	Page number of a segment header.

Return type: void

Example:

gaussdb=# SELECT * FROM gs_seg_free_spc_remain_extent('pg_default', 1, 1024, 0, 4159);
 gs_seg_free_spc_remain_extent 
-------------------------------

(1 row)

gs_seg_get_datafiles(database_name NAME)

Description: Displays information about all data files of an instance. Only an administrator can query the information.

Parameter description

Name	Type	Description
database_name	NAME	Database name The default value is current_database(), indicating the name of the current database.

Return value description

Name	Type	Description
file_name	TEXT	Data file name, for example, base/17467/2_fsm.
file_id	INTEGER	Data file ID. Value range: files 1 to 5.
bucketnode	INTEGER	0 to 1023 indicate the bucket nodes of a hash bucket table. 1024 indicates the bucket node of a segment-page ordinary table. 1025 indicates the bucket node of a segment-page global temporary table. 1026 indicates the bucket node of a segment-page unlogged table. 1027 indicates the bucket node of a segment-page local temporary table.
forknum	INTEGER	Fork type of a data file.
tablespace_name	NAME	Name of the tablespace to which a data file belongs.
contents	TEXT	Storage content of a data file. Value range: permanent: permanent. unlogged: no log. temporary: global temporary temporary2: local temporary
extent_size	INTEGER	Extent size of a data file.
meta_blocks	BIGINT	Number of allocated metadata pages of a data file.
data_blocks	BIGINT	Number of allocated data pages of a data file.
total_blocks	BIGINT	Total number of physical pages in a data file.
high_water_mark	BIGINT	High watermark of the number of pages used by a data file.
utilization	REAL	Percentage of used blocks to the total number of blocks, that is, (data_blocks + meta_blocks)/total_blocks.

For example, perform the following operations after creating a segment-page table:

gaussdb=# SELECT * FROM gs_seg_get_datafiles();
  file_name   | file_id | bucketnode | forknum | tablespace_name | contents  | extent_size | meta_blocks | data_blocks | total_blocks | high_water_mark | utilization
--------------+---------+------------+---------+-----------------+-----------+-------------+-------------+-------------+--------------+-----------------+-------------
 base/15949/1 |       1 |       1024 |       0 | pg_default      | permanent |           1 |        4157 |           1 |        16384 |      1048702976 | 2.24208e-44
 base/15949/2 |       2 |       1024 |       0 | pg_default      | permanent |           8 |        4157 |           8 |        16384 |      1048717312 | 2.24208e-44
(2 rows)

gs_seg_get_spc_extents(tablespace_name NAME, file_id INTEGER, bucketnode INTEGER, forknum INTEGER, skip_unused BOOLEAN DEFAULT TRUE)

Description: Obtains all extent information of a specified tablespace. Only an administrator can query the information.

Parameter description

Name	Type	Description
tablespace_name	NAME	Tablespace name
file_id	INTEGER	Data file ID.
bucketnode	INTEGER	0 to 1023 indicate the bucket nodes of a hash bucket table. 1024 indicates the bucket node of a segment-page ordinary table. 1025 indicates the bucket node of a segment-page global temporary table. 1026 indicates the bucket node of a segment-page unlogged table. 1027 indicates the bucket node of a segment-page local temporary table.
forknum	INTEGER	Data file fork. Value range: 0: main fork. 1: FSM fork. 2: VM fork.
skip_unused	BOOLEAN DEFAULT TRUE	Specifies whether to output only allocated extents. The default value is TRUE, indicating that only allocated extents are output.

Return value description

Name	Type	Description
block_id	BIGINT	Start page number of a data extent.
blocks	INTEGER	Data extent size. Value range: 1 indicates file 1. 8 indicates file 2. 128 indicates file 3. 1024 indicates file 4. 4096 indicates file 5.
contents	TEXT	Stores content. Value range: permanent: permanent. unlogged: no log. temporary: global temporary. temporary2: local temporary.
in_used	TEXT	Specifies whether a resource has been allocated. Value range: Y: allocated. N: not allocated.
mapblock_location	TEXT	Position of the extent in the map block. Format: (page_id, offset).
head_file_id	INTEGER	ID of the data file where the segment header is located.
head_block_id	BIGINT	Page number of the segment header.
usage_type	TEXT	Usage type of an extent. Value range: segment head indicates the segment header. fork head indicates the fork header. level1 page indicates the level-1 page. data extent indicates data extents.
remain_flag	TEXT	Specifies whether it is a residual extent after the SHRINK operation. Value range: Y: residual extent upon SHRINK. N: not a residual extent upon SHRINK.
special_data	INTEGER	Special data section of the reverse pointer corresponding to an extent.
ipblock_location	TEXT	Position of the reverse pointer corresponding to an extent. Format: (block_id, offset).

Example:

gaussdb=# SELECT * FROM gs_seg_get_spc_extents('pg_default', 1,1024, 0);
 block_id | blocks | contents  | in_used | mapblock_location | head_file_id | head_block_id |  usage_type  | remain_flag | special_data | ipblock_location
----------+--------+-----------+---------+-------------------+--------------+---------------+--------------+-------------+--------------+------------------
     4157 |      1 | permanent | Y       | (4157,0)          |            1 |          4157 | segment head | N           |            0 | (67,0)
     4158 |      1 | permanent | Y       | (4157,1)          |            1 |          4158 | fork head    | N           |            1 | (67,1)
(2 rows)

Parent topic: System Administration Functions

Previous topic: Logical Replication Functions

Next topic: Hash Bucket System Functions

Feedback

Was this page helpful?

Helpful Not helpful

Provide feedback

Thank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.

The system is busy. Please try again later.

Which of the following issues have you encountered?

Content is inconsistent with the product UI

Unclear descriptions

Lack of examples or code

Incorrect steps

Can't find what I need

Lack of best practices

Feedback (optional)

0/500

Select at least one type of issue, and enter your comments or suggestions.

Enter a maximum of 500 characters.

Submit Cancel

For any further questions, feel free to contact us through the chatbot.

Chatbot