We have migrated to new Google sites!
Post date: Oct 12, 2014 9:59:51 PM
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.
sachi=# \d pg_partition_encoding
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.
sachi=# \d pg_partition_columns
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=#
--- SQL to find names of the table partitions
SELECT
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)
Partition Functions
sachi=# \df *partition*
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)