Built-in Tool Functions Related to Partitioned Tables

Information About Table Creation

Create a table.

CREATE TABLE test_range_pt (a INT, b INT, c INT)
PARTITION BY RANGE (a)
(
    PARTITION p1 VALUES LESS THAN (2000),
    PARTITION p2 VALUES LESS THAN (3000),
    PARTITION p3 VALUES LESS THAN (4000),
    PARTITION p4 VALUES LESS THAN (5000),
    PARTITION p5 VALUES LESS THAN (MAXVALUE)
)ENABLE ROW MOVEMENT;

View the OID of the partitioned table.

SELECT oid FROM pg_class WHERE relname = 'test_range_pt';
  oid
-------
 49290
(1 row)

View the partition information.

SELECT oid,relname,parttype,parentid,boundaries FROM pg_partition WHERE parentid = 49290;
  oid  |    relname    | parttype | parentid | boundaries
-------+---------------+----------+----------+------------
 49293 | test_range_pt | r        |    49290 |
 49294 | p1            | p        |    49290 | {2000}
 49295 | p2            | p        |    49290 | {3000}
 49296 | p3            | p        |    49290 | {4000}
 49297 | p4            | p        |    49290 | {5000}
 49298 | p5            | p        |    49290 | {NULL}
(6 rows)

Create an index.

CREATE INDEX idx_range_a ON test_range_pt(a) LOCAL;
CREATE INDEX
-- Check the OID of the partitioned index.
SELECT oid FROM pg_class WHERE relname = 'idx_range_a';
  oid
-------
 90250
(1 row)

View the index partition information.

SELECT oid,relname,parttype,parentid,boundaries,indextblid FROM pg_partition WHERE parentid = 90250;
  oid  | relname  | parttype | parentid | boundaries | indextblid
-------+----------+----------+----------+------------+------------
 90255 | p5_a_idx | x        |    90250 |            |      49298
 90254 | p4_a_idx | x        |    90250 |            |      49297
 90253 | p3_a_idx | x        |    90250 |            |      49296
 90252 | p2_a_idx | x        |    90250 |            |      49295
 90251 | p1_a_idx | x        |    90250 |            |      49294
(5 rows)

Example of Tool Functions

pg_get_tabledef is used to obtain the definition of a partitioned table. The input parameter can be the table OID or table name.

SELECT pg_get_tabledef('test_range_pt');
                                                                                                     pg_get_tabledef                                                                                                     
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 SET search_path = public;                                                                                                                                                                                              +
 CREATE TABLE test_range_pt (                                                                                                                                                                                           +
     a integer,                                                                                                                                                                                                         +
     b integer,                                                                                                                                                                                                         +
     c integer                                                                                                                                                                                                          +
 )                                                                                                                                                                                                                      +
 WITH (orientation=row, compression=no, storage_type=USTORE, segment=off)                                                                                                                                               +
 PARTITION BY RANGE (a)                                                                                                                                                                                                 +
 (                                                                                                                                                                                                                      +
     PARTITION p1 VALUES LESS THAN (2000) TABLESPACE pg_default,                                                                                                                                                        +
     PARTITION p2 VALUES LESS THAN (3000) TABLESPACE pg_default,                                                                                                                                                        +
     PARTITION p3 VALUES LESS THAN (4000) TABLESPACE pg_default,                                                                                                                                                        +
     PARTITION p4 VALUES LESS THAN (5000) TABLESPACE pg_default,                                                                                                                                                        +
     PARTITION p5 VALUES LESS THAN (MAXVALUE) TABLESPACE pg_default                                                                                                                                                     +
 )                                                                                                                                                                                                                      +
 ENABLE ROW MOVEMENT;                                                                                                                                                                                                   +
 CREATE INDEX idx_range_a ON test_range_pt USING ubtree (a) LOCAL(PARTITION p1_a_idx, PARTITION p2_a_idx, PARTITION p3_a_idx, PARTITION p4_a_idx, PARTITION p5_a_idx)  WITH (storage_type=USTORE) TABLESPACE pg_default;
(1 row)

pg_stat_get_partition_tuples_hot_updated is used to return the number of hot updated tuples in a partition with a specified partition ID.

Insert 10 data records into partition p1 and update the data. Count the number of hot updated tuples in partition p1.

INSERT INTO test_range_pt VALUES(generate_series(1,10),1,1);
INSERT 0 10
SELECT pg_stat_get_partition_tuples_hot_updated(49294);
pg_stat_get_partition_tuples_hot_updated
------------------------------------------
0
(1 row)
UPDATE test_range_pt SET b = 2;
UPDATE 10
SELECT pg_stat_get_partition_tuples_hot_updated(49294);
pg_stat_get_partition_tuples_hot_updated
------------------------------------------
10
(1 row)

pg_partition_size(oid,oid) is used to specify the disk space used by the partition with a specified OID. The first oid is the OID of the table and the second oid is the OID of the partition.
Check the disk space of partition p1.
```
SELECT pg_partition_size(49290, 49294);
pg_partition_size
-------------------
90112
(1 row)
```

pg_partition_size(text, text) is used to specify the disk space used by the partition with a specified name. The first text is the table name and the second text is the partition name.
Check the disk space of partition p1.
```
SELECT pg_partition_size('test_range_pt', 'p1');
pg_partition_size
-------------------
90112
(1 row)
```

pg_partition_indexes_size(oid,oid) is used to specify the disk space used by the index of the partition with a specified OID. The first oid is the OID of the table and the second oid is the OID of the partition.
Check the disk space of the index partition of partition p1.
```
SELECT pg_partition_indexes_size(49290, 49294);
pg_partition_indexes_size
---------------------------
204800
(1 row)
```

pg_partition_indexes_size(text,text) is used to specify the disk space used by the index of the partition with a specified name. The first text is the table name and the second text is the partition name.
Check the disk space of the index partition of partition p1.
```
SELECT pg_partition_indexes_size('test_range_pt', 'p1');
pg_partition_indexes_size
---------------------------
204800
(1 row)
```

pg_partition_filenode(partition_oid) is used to obtain the file node corresponding to the OID of the specified partitioned table.
Check the file node of partition p1.
```
SELECT pg_partition_filenode(49294);
pg_partition_filenode
-----------------------
49294
(1 row)
```

pg_partition_filepath(partition_oid) is used to specify the file path name of the partition.
Check the file path of partition p1.
```
SELECT pg_partition_filepath(49294);
pg_partition_filepath
-----------------------
base/16521/49294
(1 row)
```

Parent topic: System Views & DFX Related to Partitioned Tables

Previous topic: System Views Related to Partitioned Tables

Next topic: Storage Engine