1. List of partition tables in all schema in your db. sachi=# select distinct tablename from pg_partitions; tablename ----------- cms_part cms_qlz cms_zlib cms_zlib9 sales (5 rows) sachi=# select distinct schemaname,tablename from pg_partitions; schemaname | tablename ------------+----------- public | cms_part public | cms_qlz public | cms_zlib public | cms_zlib9 public | sales (5 rows) sachi=# sachi=# select distinct tablename,count(partitiontablename) from pg_partitions group by tablename; tablename | count -----------+------- cms_part | 17 cms_qlz | 17 cms_zlib | 17 cms_zlib9 | 17 sales | 132 (5 rows) sachi=# 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; schemaname | tablename | columnname | partitionlevel | position_in_partition_key ------------+-----------+---------------------------+----------------+--------------------------- public | cms_part | car_line_cms_type_srvc_cd | 0 | 1 public | cms_qlz | car_line_cms_type_srvc_cd | 0 | 1 public | cms_zlib | car_line_cms_type_srvc_cd | 0 | 1 public | cms_zlib9 | car_line_cms_type_srvc_cd | 0 | 1 public | sales | year | 0 | 1 public | sales | month | 1 | 1 public | sales | month | 1 | 1 public | sales | region | 2 | 1 public | sales | region | 2 | 1 (9 rows) sachi=# 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; schemaname | tablename | partitionname | partitiontype | partitionlevel | partitionrank | partitionposition | partitionlistvalues | partitionrangestart | pa rtitionstartinclusive | partitionrangeend | partitionendinclusive | partitioneveryclause | partitionisdefault | partitionboundary ------------+-----------+---------------+---------------+----------------+---------------+-------------------+---------------------+---------------------+--- ----------------------+-------------------+-----------------------+----------------------+--------------------+-------------------------------------- public | sales | other_months | range | 1 | 1 | 1 | | | f | | f | | t | DEFAULT SUBPARTITION other_months public | sales | | range | 1 | 2 | 2 | | 1 | t | 2 | f | 1 | f | START (1) END (2) EVERY (1) public | sales | | range | 1 | 3 | 3 | | 2 | t | 3 | f | 1 | f | START (2) END (3) EVERY (1) public | sales | | range | 1 | 4 | 4 | | 3 | t | 4 | f | 1 | f | START (3) END (4) EVERY (1) public | sales | | range | 1 | 5 | 5 | | 4 | t | 5 | f | 1 | f | START (4) END (5) EVERY (1) public | sales | | range | 1 | 6 | 6 | | 5 | t | 6 | f | 1 | f | START (5) END (6) EVERY (1) public | sales | | range | 1 | 7 | 7 | | 6 | t | 7 | f | 1 | f | START (6) END (7) EVERY (1) public | sales | | range | 1 | 8 | 8 | | 7 | t | 8 | f | 1 | f | START (7) END (8) EVERY (1) public | sales | | range | 1 | 9 | 9 | | 8 | t | 9 | f | 1 | f | START (8) END (9) EVERY (1) public | sales | | range | 1 | 10 | 10 | | 9 | t | 10 | f | 1 | f | START (9) END (10) EVERY (1) public | sales | | range | 1 | 11 | 11 | | 10 | t | 11 | f | 1 | f | START (10) END (11) EVERY (1) public | sales | | range | 1 | 12 | 12 | | 11 | t | 12 | f | 1 | f | START (11) END (12) EVERY (1) public | sales | | range | 1 | 13 | 13 | | 12 | t | 13 | f | 1 | f | START (12) END (13) EVERY (1) public | sales | usa | list | 2 | | 1 | 'usa'::text | | | | | | f | SUBPARTITION usa VALUES('usa') public | sales | europe | list | 2 | | 2 | 'europe'::text | | | | | | f | SUBPARTITION europe VALUES('europe') public | sales | asia | list | 2 | | 3 | 'asia'::text | | | | | | f | SUBPARTITION asia VALUES('asia') public | sales | other_regions | list | 2 | | 4 | | | | | | | t | DEFAULT SUBPARTITION other_regions (17 rows) sachi=# 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; schemaname | tablename | partitionschemaname | partitiontablename | partitionname | parentpartitiont ablename | parentpartitionname | partitiontype | partitionlevel | partitionrank | partitionposition | partitionlistvalues | partitionrangestart | partitionstartinclusive | partitionrangeend | partitionendinclusive | partitioneveryclause | partitionisdefault | partitionbounda ry | parenttablespace | partitiontablespace ------------+-----------+---------------------+-----------------------------------------------------------------+----------------+--------------------------- --------------------+---------------------+---------------+----------------+---------------+-------------------+------------------------+-------------------- -+-------------------------+-------------------+-----------------------+----------------------+--------------------+----------------------------------------- -----------------------------+------------------+--------------------- public | sales | public | sales_1_prt_outlying_years | outlying_years | | | range | 0 | | 1 | | | f | | f | | t | DEFAULT PARTITION outlying_years | pg_default | pg_default public | sales | public | sales_1_prt_2 | | | | range | 0 | 1 | 2 | | 2012 | t | 2013 | f | 1 | f | START (2012) END (2013) EVERY (1) | pg_default | pg_default public | sales | public | sales_1_prt_2_2_prt_other_months | other_months | sales_1_prt_2 | | range | 1 | | 1 | | | f | | f | | t | DEFAULT SUBPARTITION other_months | pg_default | pg_default public | sales | public | sales_1_prt_2_2_prt_2 | | sales_1_prt_2 | | range | 1 | 1 | 2 | | 1 | t | 2 | f | 1 | f | START (1) END (2) EVERY (1) | pg_default | pg_default public | sales | public | sales_1_prt_2_2_prt_3 | | sales_1_prt_2 | | range | 1 | 2 | 3 | | 2 | t | 3 | f | 1 | f | START (2) END (3) EVERY (1) | pg_default | pg_default public | sales | public | sales_1_prt_2_2_prt_4 | | sales_1_prt_2 | | range | 1 | 3 | 4 | | 3 | t | 4 | f | 1 | f | START (3) END (4) EVERY (1) | pg_default | pg_default public | sales | public | sales_1_prt_2_2_prt_5 | | sales_1_prt_2 | | range | 1 | 4 | 5 | | 4 | t | 5 | f | 1 | f | START (4) END (5) EVERY (1) | pg_default | pg_default public | sales | public | sales_1_prt_2_2_prt_6 | | sales_1_prt_2 | | range | 1 | 5 | 6 | | 5 | t | 6 | f | 1 | f | START (5) END (6) EVERY (1) | pg_default | pg_default public | sales | public | sales_1_prt_2_2_prt_7 | | sales_1_prt_2 | | range | 1 | 6 | 7 | | 6 | t | 7 | f | 1 | f | START (6) END (7) EVERY (1) | pg_default | pg_default public | sales | public | sales_1_prt_2_2_prt_8 | | sales_1_prt_2 | | range | 1 | 7 | 8 | | 7 | t | 8 | f | 1 | f | START (7) END (8) EVERY (1) | pg_default | pg_default public | sales | public | sales_1_prt_2_2_prt_9 | | sales_1_prt_2 | | range | 1 | 8 | 9 | | 8 | t | 9 | f | 1 | f | START (8) END (9) EVERY (1) | pg_default | pg_default ......... ...... on=column, appendonly=true) | pg_default | pg_default public | cms_zlib9 | public | cms_zlib9_1_prt_p9 | p9 | | | list | 0 | | 9 | '8'::character varying | | | | | | f | PARTITION p9 VALUES('8') WITH (orientati on=column, appendonly=true) | pg_default | pg_default public | cms_zlib9 | public | cms_zlib9_1_prt_p10 | p10 | | | list | 0 | | 10 | '9'::character varying | | | | | | f | PARTITION p10 VALUES('9') WITH (orientat ion=column, appendonly=true) | pg_default | pg_default public | cms_zlib9 | public | cms_zlib9_1_prt_p11 | p11 | | | list | 0 | | 11 | 'D'::character varying | | | | | | f | PARTITION p11 VALUES('D') WITH (orientat ion=column, appendonly=true) | pg_default | pg_default public | cms_zlib9 | public | cms_zlib9_1_prt_p12 | p12 | | | list | 0 | | 12 | 'F'::character varying | | | | | | f | PARTITION p12 VALUES('F') WITH (orientat ion=column, appendonly=true) | pg_default | pg_default public | cms_zlib9 | public | cms_zlib9_1_prt_p13 | p13 | | | list | 0 | | 13 | 'G'::character varying | | | | | | f | PARTITION p13 VALUES('G') WITH (orientat ion=column, appendonly=true) | pg_default | pg_default public | cms_zlib9 | public | cms_zlib9_1_prt_p14 | p14 | | | list | 0 | | 14 | 'K'::character varying | | | | | | f | PARTITION p14 VALUES('K') WITH (orientat ion=column, appendonly=true) | pg_default | pg_default public | cms_zlib9 | public | cms_zlib9_1_prt_p15 | p15 | | | list | 0 | | 15 | 'M'::character varying | | | | | | f | PARTITION p15 VALUES('M') WITH (orientat ion=column, appendonly=true) | pg_default | pg_default public | cms_zlib9 | public | cms_zlib9_1_prt_p16 | p16 | | | list | 0 | | 16 | 'N'::character varying | | | | | | f | PARTITION p16 VALUES('N') WITH (orientat ion=column, appendonly=true) | pg_default | pg_default public | cms_zlib9 | public | cms_zlib9_1_prt_other | other | | | list | 0 | | 17 | | | | | | | t | DEFAULT PARTITION other WITH (orientati on=column, appendonly=true) | pg_default | pg_default (200 rows) sachi=# 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; classname | objname | objid | schemaname | usestatus | usename | actionname | subtyp e | statime | partitionlevel | parenttablename | parentschemaname | parent_relid --------------+-----------------------------------------------------------------+-------+--------------------+-----------+---------+------------+------------ --------+-------------------------------+----------------+-----------------+------------------+-------------- pg_namespace | gp_toolkit | 10890 | | CURRENT | gpadmin | PRIVILEGE | GRANT | 2013-11-27 12:54:22.584272-05 | | | | pg_class | __gp_is_append_only | 16384 | gp_toolkit | CURRENT | gpadmin | CREATE | VIEW | 2013-11-27 12:54:22.646328-05 | | | | pg_class | __gp_is_append_only | 16384 | gp_toolkit | CURRENT | gpadmin | PRIVILEGE | GRANT | 2013-11-27 12:54:22.65277-05 | | | | pg_class | __gp_fullname | 16387 | gp_toolkit | CURRENT | gpadmin | CREATE | VIEW | 2013-11-27 12:54:22.654534-05 | | | | pg_class | __gp_fullname | 16387 | gp_toolkit | CURRENT | gpadmin | PRIVILEGE | GRANT | 2013-11-27 12:54:22.656615-05 | | | | pg_class | __gp_user_namespaces | 16390 | gp_toolkit | CURRENT | gpadmin | CREATE | VIEW | 2013-11-27 12:54:22.658216-05 | | | | pg_class | __gp_user_namespaces | 16390 | gp_toolkit | CURRENT | gpadmin | PRIVILEGE | GRANT | 2013-11-27 12:54:22.660281-05 | | | | pg_class | __gp_user_tables | 16393 | gp_toolkit | CURRENT | gpadmin | CREATE | VIEW | 2013-11-27 12:54:22.662167-05 | | | | pg_class | __gp_user_tables | 16393 | gp_toolkit | CURRENT | gpadmin | PRIVILEGE | GRANT | 2013-11-27 12:54:22.665061-05 | | | | pg_class | __gp_user_data_tables | 16396 | gp_toolkit | CURRENT | gpadmin | CREATE | VIEW | 2013-11-27 12:54:22.666971-05 | | | | pg_class | __gp_user_data_tables | 16396 | gp_toolkit | CURRENT | gpadmin | PRIVILEGE | GRANT | 2013-11-27 12:54:22.670172-05 | | | | pg_class | __gp_user_data_tables_readable | 16399 | gp_toolkit | CURRENT | gpadmin | CREATE | VIEW | 2013-11-27 12:54:22.672125-05 | | | | pg_class | __gp_user_data_tables_readable | 16399 | gp_toolkit | CURRENT | gpadmin | PRIVILEGE | GRANT | 2013-11-27 12:54:22.675026-05 | | | | pg_class | __gp_localid | 16413 | gp_toolkit | CURRENT | gpadmin | CREATE | TABLE | 2013-11-27 12:54:22.677765-05 | | | | pg_class | __gp_localid | 16413 | gp_toolkit | CURRENT | gpadmin | PRIVILEGE | GRANT | 2013-11-27 12:54:22.680326-05 | | | | pg_class | __gp_masterid | 16436 | gp_toolkit | CURRENT | gpadmin | CREATE | TABLE | 2013-11-27 12:54:22.682068-05 | | | | pg_class | __gp_masterid | 16436 | gp_toolkit | CURRENT | gpadmin | PRIVILEGE | GRANT | 2013-11-27 12:54:22.683806-05 | | | | pg_class | __gp_number_of_segments | 16448 | gp_toolkit | CURRENT | gpadmin | CREATE | VIEW | 2013-11-27 12:54:22.685274-05 | | | | pg_class | __gp_number_of_segments | 16448 | gp_toolkit | CURRENT | gpadmin | PRIVILEGE | GRANT | 2013-11-27 12:54:22.687337-05 | | | | pg_class | __gp_log_segment_ext | 16462 | gp_toolkit | CURRENT | gpadmin | CREATE | TABLE | 2013-11-27 12:54:22.690038-05 | | | | pg_class | __gp_log_segment_ext | 16462 | gp_toolkit | CURRENT | gpadmin | PRIVILEGE | REVOKE | 2013-11-27 12:54:22.692811-05 | | | | pg_class | __gp_log_master_ext | 16485 | gp_toolkit | CURRENT | gpadmin | CREATE | TABLE | 2013-11-27 12:54:22.695318-05 | | | | pg_class | __gp_log_master_ext | 16485 | gp_toolkit | CURRENT | gpadmin | PRIVILEGE | REVOKE | 2013-11-27 12:54:22.698122-05 | | | | pg_class | gp_log_system | 16497 | gp_toolkit | CURRENT | gpadmin | CREATE | VIEW | 2013-11-27 12:54:22.700729-05 | | | .... ... pg_class | cms_zlib9_1_prt_p14 | 86191 | public | CURRENT | gpadmin | ALTER | INHERIT | 2014-10-16 14:56:01.520918-04 | 0 | cms_zlib9 | public | 85828 pg_class | cms_zlib9_1_prt_p15 | 86217 | public | CURRENT | gpadmin | ALTER | INHERIT | 2014-10-16 14:56:01.52336-04 | 0 | cms_zlib9 | public | 85828 pg_class | cms_zlib9_1_prt_p16 | 86243 | public | CURRENT | gpadmin | ALTER | INHERIT | 2014-10-16 14:56:01.525823-04 | 0 | cms_zlib9 | public | 85828 pg_class | cms_zlib9_1_prt_other | 86269 | public | CURRENT | gpadmin | ALTER | INHERIT | 2014-10-16 14:56:01.52822-04 | 0 | cms_zlib9 | public | 85828 pg_class | v_gp_table_storage | 86488 | public | CURRENT | gpadmin | CREATE | VIEW | 2014-10-16 15:03:45.364006-04 | | | | pg_class | v_gp_table_storage2 | 86491 | public | CURRENT | gpadmin | CREATE | VIEW | 2014-10-16 15:08:59.89954-04 | | | | pg_class | idx_bloattest_id | 86509 | public | CURRENT | gpadmin | CREATE | INDEX | 2014-10-16 17:46:10.340206-04 | | | | pg_class | bloattest | 83678 | public | CURRENT | gpadmin | ALTER | SET DISTRIB UTED BY | 2014-10-16 19:26:24.26704-04 | | | | pg_class | pg_attribute | 1249 | pg_catalog | CURRENT | gpadmin | ANALYZE | | 2014-10-16 20:04:39.935061-04 | | | | pg_class | idx_bloattest_id | 86509 | public | CURRENT | gpadmin | VACUUM | REINDEX | 2014-10-17 12:43:12.982163-04 | | | | pg_class | bloattest | 83678 | public | CURRENT | gpadmin | VACUUM | TRUNCATE | 2014-10-17 12:43:13.817459-04 | | | | pg_class | bloattest | 83678 | public | CURRENT | gpadmin | ANALYZE | | 2014-10-17 12:45:27.920074-04 | | | | (1074 rows) sachi=# |
|
|