System views for partitioned tables-PolarDB(PolarDB)-阿里云帮助中心

PolarDB for PostgreSQL exposes partition metadata through four system catalogs and functions. Use the table below to find the right one for your query.

When you need to...	Use
Check the partitioning strategy (hash/list/range) or partition key definition	`pg_partitioned_table`
Walk the full partition tree and see parent-child relationships by name	`pg_partition_tree()`
Look up object types, inheritance flags, or partition bounds	`pg_class`
Trace partition relationships by OID or check for in-progress detaches	`pg_inherits`

pg_partitioned_table

pg_partitioned_table stores one row for each partitioned table, describing its partitioning strategy and key definition.

Columns

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

Example

SELECT * FROM pg_partitioned_table;

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

The row above shows a hash-partitioned table (OID 17124) with a single-column partition key (column 1), no default partition (partdefid = 0), and a simple column reference (partexprs is empty).

pg_partition_tree

pg_partition_tree() is supported only on PolarDB for PostgreSQL 14 clusters.

pg_partition_tree() is a set-returning function that lists all tables or indexes in the partition tree of a given partitioned table or partitioned index. Pass the table or index name as the argument. Each row represents one partition.

Return columns

Column	Type	Description
`relid`	`regclass`	Name of the partition.
`parentrelid`	`regclass`	Name of its immediate parent partition. Null for the root (the input table or index itself).
`isleaf`	`bool`	True if the partition is a leaf partition.
`level`	`int4`	Depth in the hierarchy: `0` for the root, `1` for immediate child partitions, `2` for their partitions, and so on.

Example

SELECT * FROM pg_partition_tree('idxpart');

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

The root idxpart is at level 0 with isleaf = f. Both idxpart0 and idxpart1 are leaf partitions at level 1.

pg_class

pg_class is the central system catalog for all relation-type objects: tables, indexes, sequences, views, and more. The columns below are the ones relevant to partitioned tables.

Columns

Column	Type	Description
`relkind`	`char`	Object type: `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`	`bool`	True if the table or index has (or once had) inheritance children or partitions.
`relispartition`	`bool`	True if the table or index is a partition.
`relpartbound`	`pg_node_tree`	Internal representation of the partition bound when `relispartition` is true. Use `pg_get_expr(relpartbound, oid)` to get a human-readable form.

Example

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

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

The result shows an ordinary table (relkind = r) that is a partition (relispartition = t) with a range bound starting from MINVALUE.

pg_inherits

pg_inherits records direct parent-child relationships in table and index inheritance hierarchies, including declarative partitioning. Each row represents one direct parent-child link.

Columns

Column	Type	Description
`inhrelid`	`oid`	OID of the child partition (references `pg_class.oid`).
`inhparent`	`oid`	OID of its direct parent partition (references `pg_class.oid`).
`inhseqno`	`int4`	When a child table has multiple direct parents (multiple inheritance), this indicates the order in which the inherited columns are arranged, starting at 1. Indexes support only single inheritance through declarative partitioning.
`inhdetachpending`	`bool`	True if the partition is in the process of being detached. (PolarDB for PostgreSQL 14 only)

Example

SELECT * FROM pg_inherits WHERE inhrelid = 17136;

 inhrelid | inhparent | inhseqno | inhdetachpending
----------+-----------+----------+------------------
    17136 |     17133 |        1 | f
(1 row)

The partition with OID 17136 is a direct child of the partition with OID 17133, with no pending detach operation.

上一篇: Use any column of a partitioned table as the primary key 下一篇: Optimizers