There are 7 partition tables and views which can be used to see all the details about partition tables. Tables ======== 1. pg_partition -> catalog table is used to track partitioned tables and their inheritance level relationships. Each row ofpg_partition represents either the level of a partitioned table in the partition hierarchy, or a subpartition template description. The value of the attribute paristemplate determines what a particular row represents.sachi=# \d pg_partition Table "pg_catalog.pg_partition" Column | Type | Modifiers ---------------+------------+----------- parrelid | oid | not null parkind | "char" | not null parlevel | smallint | not null paristemplate | boolean | not null parnatts | smallint | not null paratts | int2vector | not null parclass | oidvector | not null Indexes: "pg_partition_oid_index" UNIQUE, btree (oid) "pg_partition_parrelid_index" btree (parrelid) "pg_partition_parrelid_parlevel_istemplate_index" btree (parrelid, parlevel, paristemplate) sachi=# 2. pg_partition_encoding -> catalog table describes the available column compression options for a partition template. Table "pg_catalog.pg_partition_encoding" Column | Type | Modifiers ------------------+----------+----------- parencoid | oid | not null parencattnum | smallint | not null parencattoptions | text[] | Indexes: "pg_partition_encoding_parencoid_parencattnum_index" UNIQUE, btree (parencoid, parencattnum) "pg_partition_encoding_parencoid_index" btree (parencoid) 3. pg_partition_rule -> catalog table is used to track partitioned tables, their check constraints, and data containment rules. Each row of pg_partition_rule represents either a leaf partition (the bottom level partitions that contain data), or a branch partition (a top or mid-level partition that is used to define the partition hierarchy, but does not contain any data). sachi=# \d pg_partition_rule Table "pg_catalog.pg_partition_rule" Column | Type | Modifiers -------------------+----------+----------- paroid | oid | not null parchildrelid | oid | not null parparentrule | oid | not null parname | name | not null parisdefault | boolean | not null parruleord | smallint | not null parrangestartincl | boolean | not null parrangeendincl | boolean | not null parrangestart | text | parrangeend | text | parrangeevery | text | parlistvalues | text | parreloptions | text[] | partemplatespace | oid | Indexes: "pg_partition_rule_oid_index" UNIQUE, btree (oid) "pg_partition_rule_parchildrelid_index" btree (parchildrelid) "pg_partition_rule_parchildrelid_parparentrule_parruleord_index" btree (parchildrelid, parparentrule, parruleord) "pg_partition_rule_paroid_parentrule_ruleord_index" btree (paroid, parparentrule, parruleord) Views ======== 1. pg_partition_columns ->view is used to show the partition key columns of a partitioned table. View "pg_catalog.pg_partition_columns" Column | Type | Modifiers ---------------------------+----------+----------- schemaname | name | tablename | name | columnname | name | partitionlevel | smallint | position_in_partition_key | integer | View definition: SELECT n.nspname AS schemaname, c.relname AS tablename, a.attname AS columnname, p.parlevel AS partitionlevel, p.i + 1 AS position_in_partition_key FROM pg_namespace n, pg_class c, pg_attribute a, ( SELECT p.parrelid, p.parlevel, p.paratts[i.i] AS attnum, i.i FROM pg_partition p, generate_series(0, ( SELECT max(array_upper(pg_partition.paratts, 1)) AS max FROM pg_partition)) i(i) WHERE p.paratts[i.i] IS NOT NULL) p WHERE p.parrelid = c.oid AND c.relnamespace = n.oid AND p.attnum = a.attnum AND a.attrelid = c.oid; 2. pg_partition_templates -> view is used to show the subpartitions that were created using a subpartition template. sachi=# \d pg_partition_templates View "pg_catalog.pg_partition_templates" Column | Type | Modifiers -------------------------+----------+----------- schemaname | name | tablename | name | partitionname | name | partitiontype | text | partitionlevel | smallint | partitionrank | bigint | partitionposition | smallint | partitionlistvalues | text | partitionrangestart | text | partitionstartinclusive | boolean | partitionrangeend | text | partitionendinclusive | boolean | partitioneveryclause | text | partitionisdefault | boolean | partitionboundary | text | View definition: SELECT p1.schemaname, p1.tablename, p1.partitionname, p1.partitiontype, p1.partitionlevel, CASE WHEN p1.partitiontype <> 'range'::text THEN NULL::bigint WHEN p1.partitionnodefault > 0 THEN p1.partitionrank WHEN p1.partitionrank = 1 THEN NULL::bigint ELSE p1.partitionrank - 1 END AS partitionrank, p1.partitionposition, p1.partitionlistvalues, p1.partitionrangestart, CASE WHEN p1.partitiontype = 'range'::text THEN p1.partitionstartinclusive ELSE NULL::boolean END AS partitionstartinclusive, p1.partitionrangeend, CASE WHEN p1.partitiontype = 'range'::text THEN p1.partitionendinclusive ELSE NULL::boolean END AS partitionendinclusive, p1.partitioneveryclause, p1.parisdefault AS partitionisdefault, p1.partitionboundary FROM ( SELECT n.nspname AS schemaname, cl.relname AS tablename, pr1.parname AS partitionname, p.parlevel AS partitionlevel, pr1.parruleord AS partitionposition, rank() OVER( PARTITION BY p.oid, cl.relname, p.parlevel ORDER BY pr1.parruleord) AS partitionrank, pg_get_expr(pr1.parlistvalues, p.parrelid) AS partitionlistvalues, pg_get_expr(pr1.parrangestart, p.parrelid) AS partitionrangestart, pr1.parrangestartincl AS partitionstartinclusive, pg_get_expr(pr1.parrangeend, p.parrelid) AS partitionrangeend, pr1.parrangeendincl AS partitionendinclusive, pg_get_expr(pr1.parrangeevery, p.parrelid) AS partitioneveryclause, min(pr1.parruleord) OVER( PARTITION BY p.oid, cl.relname, p.parlevel ORDER BY pr1.parruleord) AS partitionnodefault, pr1.parisdefault, CASE WHEN p.parkind = 'h'::"char" THEN 'hash'::text WHEN p.parkind = 'r'::"char" THEN 'range'::text WHEN p.parkind = 'l'::"char" THEN 'list'::text ELSE NULL::text END AS partitiontype, pg_get_partition_rule_def(pr1.oid, true) AS partitionboundary FROM pg_namespace n, pg_class cl, pg_partition p, pg_partition_rule pr1 WHERE p.parrelid = cl.oid AND pr1.paroid = p.oid AND cl.relnamespace = n.oid AND p.paristemplate = true) p1; 3. pg_partitions -> view is used to show the structure of a partitioned table. sachi=# \d pg_partitions View "pg_catalog.pg_partitions" Column | Type | Modifiers --------------------------+----------+----------- schemaname | name | tablename | name | partitionschemaname | name | partitiontablename | name | partitionname | name | parentpartitiontablename | name | parentpartitionname | name | partitiontype | text | partitionlevel | smallint | partitionrank | bigint | partitionposition | smallint | partitionlistvalues | text | partitionrangestart | text | partitionstartinclusive | boolean | partitionrangeend | text | partitionendinclusive | boolean | partitioneveryclause | text | partitionisdefault | boolean | partitionboundary | text | parenttablespace | name | partitiontablespace | name | View definition: SELECT p1.schemaname, p1.tablename, p1.partitionschemaname, p1.partitiontablename, p1.partitionname, p1.parentpartitiontablename, p1.parentpartitionname, p1.partitiontype, p1.partitionlevel, CASE WHEN p1.partitiontype <> 'range'::text THEN NULL::bigint WHEN p1.partitionnodefault > 0 THEN p1.partitionrank WHEN p1.partitionrank = 0 THEN NULL::bigint ELSE p1.partitionrank END AS partitionrank, p1.partitionposition, p1.partitionlistvalues, p1.partitionrangestart, CASE WHEN p1.partitiontype = 'range'::text THEN p1.partitionstartinclusive ELSE NULL::boolean END AS partitionstartinclusive, p1.partitionrangeend, CASE WHEN p1.partitiontype = 'range'::text THEN p1.partitionendinclusive ELSE NULL::boolean END AS partitionendinclusive, p1.partitioneveryclause, p1.parisdefault AS partitionisdefault, p1.partitionboundary, p1.parentspace AS parenttablespace, p1.partspace AS partitiontablespace FROM ( SELECT n.nspname AS schemaname, cl.relname AS tablename, n2.nspname AS partitionschemaname, cl2.relname AS partitiontablename, pr1.parname AS partitionname, cl3.relname AS parentpartitiontablename, pr2.parname AS parentpartitionname, CASE WHEN pp.parkind = 'h'::"char" THEN 'hash'::text WHEN pp.parkind = 'r'::"char" THEN 'range'::text WHEN pp.parkind = 'l'::"char" THEN 'list'::text ELSE NULL::text END AS partitiontype, pp.parlevel AS partitionlevel, pr1.parruleord AS partitionposition, CASE WHEN pp.parkind <> 'r'::"char" OR pr1.parisdefault THEN NULL::bigint ELSE rank() OVER( PARTITION BY pp.oid, cl.relname, pp.parlevel, cl3.relname ORDER BY pr1.parisdefault, pr1.parruleord) END AS partitionrank, pg_get_expr(pr1.parlistvalues, pr1.parchildrelid) AS partitionlistvalues, pg_get_expr(pr1.parrangestart, pr1.parchildrelid) AS partitionrangestart, pr1.parrangestartincl AS partitionstartinclusive, pg_get_expr(pr1.parrangeend, pr1.parchildrelid) AS partitionrangeend, pr1.parrangeendincl AS partitionendinclusive, pg_get_expr(pr1.parrangeevery, pr1.parchildrelid) AS partitioneveryclause, min(pr1.parruleord) OVER( PARTITION BY pp.oid, cl.relname, pp.parlevel, cl3.relname ORDER BY pr1.parruleord) AS partitionnodefault, pr1.parisdefault, pg_get_partition_rule_def(pr1.oid, true) AS partitionboundary, COALESCE(sp.spcname, d.dfltspcname) AS parentspace, COALESCE(sp3.spcname, d.dfltspcname) AS partspace FROM pg_namespace n, pg_namespace n2, pg_class cl LEFT JOIN pg_tablespace sp ON cl.reltablespace = sp.oid, pg_class cl2 LEFT JOIN pg_tablespace sp3 ON cl2.reltablespace = sp3.oid, pg_partition pp, pg_partition_rule pr1 LEFT JOIN pg_partition_rule pr2 ON pr1.parparentrule = pr2.oid LEFT JOIN pg_class cl3 ON pr2.parchildrelid = cl3.oid, ( SELECT s.spcname FROM pg_database, pg_tablespace s WHERE pg_database.datname = current_database() AND pg_database.dattablespace = s.oid) d(dfltspcname) WHERE pp.paristemplate = false AND pp.parrelid = cl.oid AND pr1.paroid = pp.oid AND cl2.oid = pr1.parchildrelid AND cl.relnamespace = n.oid AND cl2.relnamespace = n2.oid) p1; sachi=# 4. pg_stat_partition_operations -> view shows details about the last operation performed on a partitioned table. sachi=# \d pg_stat_partition_operations View "pg_catalog.pg_stat_partition_operations" Column | Type | Modifiers ------------------+--------------------------+----------- classname | text | objname | name | objid | oid | schemaname | name | usestatus | text | usename | name | actionname | name | subtype | text | statime | timestamp with time zone | partitionlevel | smallint | parenttablename | name | parentschemaname | name | parent_relid | oid | View definition: SELECT pso.classname, pso.objname, pso.objid, pso.schemaname, pso.usestatus, pso.usename, pso.actionname, pso.subtype, pso.statime, CASE WHEN pr.parlevel IS NOT NULL THEN pr.parlevel ELSE pr2.parlevel END AS partitionlevel, pcns.relname AS parenttablename, pcns.nspname AS parentschemaname, pr.parrelid AS parent_relid FROM pg_stat_operations pso LEFT JOIN pg_partition_rule ppr ON pso.objid = ppr.parchildrelid LEFT JOIN pg_partition pr ON pr.oid = ppr.paroid LEFT JOIN ( SELECT min(prx.parlevel) AS parlevel, prx.parrelid FROM pg_partition prx GROUP BY prx.parrelid) pr2 ON pr2.parrelid = pso.objid LEFT JOIN ( SELECT pc.oid, pc.relname, pc.relnamespace, pc.reltype, pc.relowner, pc.relam, pc.relfilenode, pc.reltablespace, pc.relpages, pc.reltuples, pc.reltoastrelid, pc.reltoastidxid, pc.relaosegrelid, pc.relaosegidxid, pc.relhasindex, pc.relisshared, pc.relkind, pc.relstorage, pc.relnatts, pc.relchecks, pc.reltriggers, pc.relukeys, pc.relfkeys, pc.relrefs, pc.relhasoids, pc.relhaspkey, pc.relhasrules, pc.relhassubclass, pc.relfrozenxid, pc.relacl, pc.reloptions, ns.nspname, ns.nspowner, ns.nspacl FROM pg_class pc FULL JOIN pg_namespace ns ON ns.oid = pc.relnamespace) pcns ON pcns.oid = pr.parrelid; sachi=# nmsp_parent.nspname AS parent_schema, parent.relname AS parent, nmsp_child.nspname AS child, child.relname AS child_schema FROM pg_inherits JOIN pg_class parent ON pg_inherits.inhparent = parent.oid JOIN pg_class child ON pg_inherits.inhrelid = child.oid JOIN pg_namespace nmsp_parent ON nmsp_parent.oid = parent.relnamespace JOIN pg_namespace nmsp_child ON nmsp_child.oid = child.relnamespace; sachi=# SELECT sachi-# nmsp_parent.nspname AS parent_schema, sachi-# parent.relname AS parent, sachi-# COUNT(*) sachi-# FROM pg_inherits sachi-# JOIN pg_class parent ON pg_inherits.inhparent = parent.oid sachi-# JOIN pg_class child ON pg_inherits.inhrelid = child.oid sachi-# JOIN pg_namespace nmsp_parent ON nmsp_parent.oid = parent.relnamespace sachi-# JOIN pg_namespace nmsp_child ON nmsp_child.oid = child.relnamespace sachi-# GROUP BY sachi-# parent_schema, sachi-# parent; parent_schema | parent | count ---------------+-----------------------------------------------+------- public | sales_1_prt_2_2_prt_11 | 4 public | sales_1_prt_outlying_years_2_prt_13 | 4 public | sales_1_prt_2_2_prt_3 | 4 public | sales_1_prt_outlying_years_2_prt_3 | 4 public | sales | 2 public | sales_1_prt_2_2_prt_5 | 4 public | sales_1_prt_outlying_years_2_prt_6 | 4 public | sales_1_prt_outlying_years_2_prt_10 | 4 public | sales_1_prt_2_2_prt_6 | 4 public | sales_1_prt_2_2_prt_13 | 4 public | sales_1_prt_2_2_prt_4 | 4 public | sales_1_prt_2_2_prt_12 | 4 public | sales_1_prt_2_2_prt_10 | 4 public | sales_1_prt_2_2_prt_7 | 4 public | sales_1_prt_outlying_years_2_prt_5 | 4 public | sales_1_prt_2_2_prt_8 | 4 public | sales_1_prt_2_2_prt_2 | 4 public | sales_1_prt_outlying_years_2_prt_7 | 4 public | sales_1_prt_outlying_years_2_prt_other_months | 4 public | sales_1_prt_outlying_years_2_prt_2 | 4 public | sales_1_prt_outlying_years_2_prt_12 | 4 public | sales_1_prt_2_2_prt_other_months | 4 public | sales_1_prt_outlying_years_2_prt_11 | 4 public | sales_1_prt_outlying_years_2_prt_4 | 4 public | sales_1_prt_outlying_years | 13 public | sales_1_prt_2 | 13 public | sales_1_prt_outlying_years_2_prt_9 | 4 public | sales_1_prt_2_2_prt_9 | 4 public | sales_1_prt_outlying_years_2_prt_8 | 4 (29 rows) List of functions Schema | Name | Result data type | Argument data types | Type ------------+-------------------------------+------------------+-----------------------+-------- pg_catalog | pg_get_partition_def | text | oid | normal pg_catalog | pg_get_partition_def | text | oid, boolean | normal pg_catalog | pg_get_partition_def | text | oid, boolean, boolean | normal pg_catalog | pg_get_partition_rule_def | text | oid | normal pg_catalog | pg_get_partition_rule_def | text | oid, boolean | normal pg_catalog | pg_get_partition_template_def | text | oid, boolean, boolean | normal pg_catalog | pg_partition_oid | oid[] | oid, record | agg pg_catalog | pg_partition_oid_finalfn | oid[] | internal | normal pg_catalog | pg_partition_oid_transfn | internal | internal, oid, record | normal (9 rows) |
|
|