Understand Partitioned Table System Views in PolarDB - PolarDB

PolarDB for PostgreSQL (Compatible with Oracle) provides system views and functions to inspect the structure and hierarchy of partitioned tables.

pg_partitioned_table

pg_partitioned_table stores one row for each partitioned table and describes how that table is partitioned.

Columns

Column	Type	Description
`partrelid`	oid	The Object Identifier (OID) of the `pg_class` entry for this partitioned table.
`partstrat`	char	The partitioning strategy. Valid values: `h` (hash-partitioned table), `l` (list-partitioned table), `r` (range-partitioned table).
`partnatts`	int2	The number of columns in the partition key.
`partdefid`	oid	The OID of the `pg_class` entry for the default partition of this partitioned table, or zero if the table has no default partition.
`partattrs`	int2vector	An array of `partnatts` values indicating which table columns form the partition key. For example, `1 3` means the first and third columns make up the partition key. A zero entry indicates that the corresponding partition key column is an expression rather than a simple column reference.
`partclass`	oidvector	For each column in the partition key, the OID of the operator class to use.
`partcollation`	oidvector	For each column in the partition key, the OID of the collation to use for partitioning, or zero if the column is not of a collatable data type.
`partexprs`	pg_node_tree	Expression trees (`nodeToString()`) for partition key columns that are not simple column references. This is a list with one element per zero entry in `partattrs`. Null if all partition key columns are simple references.

Example

SELECT * FROM pg_partitioned_table;

Output:

 partrelid | partstrat | partnatts | partdefid | partattrs | partclass | partcollation | partexprs
-----------+-----------+-----------+-----------+-----------+-----------+---------------+-----------
     17124 | h         |         1 |         0 | 1         | 10028     | 0             |
(1 row)

pg_partition_tree

pg_partition_tree is supported only on PolarDB for PostgreSQL (Compatible with Oracle) 2.0 clusters.

pg_partition_tree lists the tables or indexes in the partition tree of the given partitioned table or partitioned index, with one row for each partition. The argument of the function is the table name.

Columns

Column	Type	Description
`relid`	regclass	The name of the partition.
`parentrelid`	regclass	The name of the immediate parent partition. Null if the partition is the root.
`isleaf`	boolean	True if the partition is a leaf partition.
`level`	integer	The level of the partition in the hierarchy. The root is level `0`, its immediate children are level `1`, their children are level `2`, and so on.

Example

SELECT * FROM pg_partition_tree('idxpart');

Output:

  relid   | parentrelid | isleaf | level
----------+-------------+--------+-------
 idxpart  |             | f      |     0
 idxpart0 | idxpart     | t      |     1
 idxpart1 | idxpart     | t      |     1
(3 rows)

pg_class

pg_class is a system catalog that stores metadata about tables, indexes, sequences, and other database objects. The following columns are relevant when working with partitioned tables.

Columns

Column	Type	Description
`relkind`	char	The object type. Valid values: `r` (ordinary table), `i` (index), `S` (sequence), `t` (TOAST table), `v` (view), `m` (materialized view), `c` (composite type), `f` (foreign table), `p` (partitioned table), `I` (partitioned index).
`relhassubclass`	boolean	True if the table or index has (or once had) any inheritance children or partitions.
`relispartition`	boolean	True if the table or index is a partition.
`relpartbound`	pg_node_tree	The internal representation of the partition bound, if the table is a partition (see `relispartition`).

Example

The following query retrieves partition metadata for a table named sales_q1_2012:

SELECT relkind, relhassubclass, relispartition,
       pg_catalog.pg_get_expr(relpartbound, oid) AS relpartbound, relpartname
FROM pg_class
WHERE relname = 'sales_q1_2012';

Output:

 relkind | relhassubclass | relispartition |                     relpartbound                     | relpartname
---------+----------------+----------------+------------------------------------------------------+-------------
 r       | f              | t              | FOR VALUES FROM (MINVALUE) TO ('01-APR-12 00:00:00') | q1_2012
(1 row)

pg_inherits

pg_inherits records table and index inheritance hierarchies. Each row represents one direct parent-child relationship.

Columns

Standard columns:

Column	Type	Description
`inhrelid`	oid	The OID of the child partition.
`inhparent`	oid	The OID of the direct parent partition.
`inhseqno`	int4	When a child table has more than one direct parent (multiple inheritance), this indicates the order in which inherited columns are arranged, starting at 1. Indexes cannot have multiple inheritance; they can only inherit through declarative partitioning.

PolarDB for PostgreSQL (Compatible with Oracle) 2.0 extensions:

Column	Type	Description
`inhdetachpending`	boolean	True for a partition that is in the process of being detached. Available only on PolarDB for PostgreSQL (Compatible with Oracle) 2.0 clusters.
`inhfinparent`	oid	The OID of the top-level parent table of this partition. Available only on PolarDB for PostgreSQL (Compatible with Oracle) 2.0 clusters.
`partname`	name	The name of the partition in the partitioning structure. Available only on PolarDB for PostgreSQL (Compatible with Oracle) 2.0 clusters.

Example

SELECT * FROM pg_inherits WHERE inhrelid = 19318;

Output:

 inhrelid | inhparent | inhseqno | inhdetachpending | inhfinparent | partname
----------+-----------+----------+------------------+--------------+----------
    19318 |     19192 |        1 | f                |            0 |
(1 row)