SQL to monitor partition information in Greenplum

The gadget spec URL could not be found

-- List of partition tables in all schema in your db.

 

The gadget spec URL could not be found

select distinct tablename from pg_partitions;

 

select distinct schemaname,tablename from pg_partitions;

 

select distinct tablename,count(partitiontablename) as total_no_of_partitions from pg_partitions group by tablename;


- List the tables that are partitioned and provide the total number of partitions and subpartitions in the table.

SELECT distinct m.schemaname||'.'||m.tablename "Relation Name", 

CASE 

WHEN p.tolpart IS NULL THEN 0

ELSE p.tolpart

END AS "Total Parition", 

CASE 

WHEN s.tolsubpart IS NULL THEN 0

ELSE s.tolsubpart

END AS  "Total Subpartitions"

FROM pg_partitions m 

LEFT JOIN (SELECT schemaname,tablename,count(*) tolpart FROM pg_partitions WHERE parentpartitiontablename IS NULL group by schemaname,tablename ) p

ON p.schemaname=m.schemaname AND p.tablename=m.tablename

LEFT JOIN (SELECT schemaname,tablename,count(*) tolsubpart FROM pg_partitions WHERE parentpartitiontablename IS NOT NULL group by schemaname,tablename) s

ON s.schemaname=m.schemaname

AND s.tablename=m.tablename

ORDER BY 1;


The gadget spec URL could not be found

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;

 

The gadget spec URL could not be found

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;

 

The gadget spec URL could not be found

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;

The gadget spec URL could not be found
The gadget spec URL could not be found

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;


-- List the tables that are partitioned and provide the total number of partitions and subpartitions in the table.


SELECT distinct m.schemaname||'.'||m.tablename "Relation Name", 

CASE 

WHEN p.tolpart IS NULL THEN 0

ELSE p.tolpart

END AS "Total Parition", 

CASE 

WHEN s.tolsubpart IS NULL THEN 0

ELSE s.tolsubpart

END AS  "Total Subpartitions"

FROM pg_partitions m 

LEFT JOIN (SELECT schemaname,tablename,count(*) tolpart FROM pg_partitions WHERE parentpartitiontablename IS NULL group by schemaname,tablename ) p

ON p.schemaname=m.schemaname AND p.tablename=m.tablename

LEFT JOIN (SELECT schemaname,tablename,count(*) tolsubpart FROM pg_partitions WHERE parentpartitiontablename IS NOT NULL group by schemaname,tablename) s

ON s.schemaname=m.schemaname

AND s.tablename=m.tablename

ORDER BY 1;

Tables without sub-partitions


If the table you want to query does not have subpartitions, perform the following queries:


NOTES:Ensure that you replace the <schemaname> and <tablename> with the  schema and table names you want to query.

The queries below assume that you are interested in partition information for a specific table.


-- List all partition information for the table specified and provide its structural information.


SELECT p.schemaname||'.'||p.tablename "Relation Name",

p.partitionschemaname||'.'||p.partitiontablename "Partition Table",

p.partitionname "Name",

p.partitiontype "Type",

c.columnname "Partition Column",

p.partitionlevel "Level",

p.partitionrank "Rank",

p.partitionposition "Position",

CASE 

WHEN p.partitionisdefault='t' then 'YES'

ELSE 'NO'

END AS "Default"

FROM pg_partitions p , pg_partition_columns c

WHERE c.schemaname=p.schemaname

AND c.tablename=p.tablename

AND c.partitionlevel=p.partitionlevel

AND (p.schemaname,p.tablename)=('<schemaname>','<tablename>');


-- If the partition is of the type "Range", use the query below to access its partition criteria.


SELECT p.schemaname||'.'||p.tablename "Relation Name",

p.partitionschemaname||'.'||p.partitiontablename "Partition Table",

p.partitionname "Name",

p.partitiontype "Type",

c.columnname "Partition Column",

p.partitionrangestart "Start Range",

CASE

WHEN p.partitionstartinclusive='t' then 'YES'

ELSE 'NO'

END AS "Start Include",

p.partitionrangeend "End Range",

CASE

WHEN p.partitionendinclusive='t' then 'YES'

ELSE 'NO'

END AS "End Include",

CASE 

WHEN p.partitionisdefault='t' then 'YES'

ELSE 'NO'

END AS "Default"

FROM pg_partitions p, pg_partition_columns c

WHERE c.schemaname=p.schemaname

AND c.tablename=p.tablename

AND c.partitionlevel=p.partitionlevel

AND (p.schemaname,p.tablename)=('<schemaname>','<tablename>');


-- If the partition is of the type "List", use the query below to access its partition criteria.


SELECT p.schemaname||'.'||p.tablename "Relation Name",

p.partitionschemaname||'.'||p.partitiontablename "Partition Table",

p.partitionname "Name",

p.partitiontype "Type",

c.columnname "Partition Column",

p.partitionlistvalues "List Values",

CASE 

WHEN p.partitionisdefault='t' then 'YES'

ELSE 'NO'

END AS "Default"

FROM pg_partitions p , pg_partition_columns c

WHERE c.schemaname=p.schemaname

AND c.tablename=p.tablename

AND c.partitionlevel=p.partitionlevel

AND (p.schemaname,p.tablename)=('<schemaname>','<tablename>');

Tables with sub-partitions


If the table you want to query does have subpartitions, use the queries below.


NOTES:Ensure that you replace the <schemaname> and <tablename> with the schema and table names you want to query.

For queries that request for <schemaname> and <partition tablename>, provide the schema name and partition name of the parent partition that holds the sub-partition.


The queries below assume that you are interested in partition information and subpartitions for a specific table.


-- List the partitions in the table, their partition criteria and the total counts of sub-partitions.


SELECT distinct m.schemaname||'.'||m.tablename "Relation Name", 

m.partitionschemaname||'.'||m.partitiontablename "Partition Table", 

m.partitiontype "Type",

c.columnname "Column",

m.partitionrangestart "Start Range",

CASE

WHEN m.partitionstartinclusive='t' then 'YES'

ELSE 'NO'

END AS "Start Include",

m.partitionrangeend "End Range",

CASE

WHEN m.partitionendinclusive='t' then 'YES'

ELSE 'NO'

END AS "End Include",

m.partitionlistvalues "List Values",

CASE 

WHEN m.partitionisdefault='t' then 'YES'

ELSE 'NO'

END AS "Default",

CASE 

WHEN s.tolsubpart IS NULL THEN 0

ELSE s.tolsubpart

END AS  "Total Subpart's"

FROM pg_partitions m 

LEFT JOIN (SELECT schemaname,tablename,parentpartitiontablename,count(*) tolsubpart FROM pg_partitions WHERE parentpartitiontablename IS NOT NULL group by schemaname,tablename,parentpartitiontablename) s

ON s.schemaname=m.schemaname

AND s.tablename=m.tablename

AND s.parentpartitiontablename=m.partitiontablename

LEFT JOIN pg_partition_columns c

ON  c.schemaname=m.schemaname

AND c.tablename=m.tablename

AND c.partitionlevel=m.partitionlevel

WHERE m.parentpartitiontablename is NULL 

AND (m.schemaname,m.tablename)=('<schemaname>','<tablename>') 

ORDER BY 5;

-- Subpartition parent partitions structure information


SELECT p.schemaname||'.'||p.tablename "Relation Name",

p.partitionschemaname||'.'||p.partitiontablename "Partition Table",

p.partitionname "Name",

p.partitiontype "Type",

c.columnname "Partition Column",

p.partitionlevel "Level",

p.partitionrank "Rank",

p.partitionposition "Position",

CASE 

WHEN p.partitionisdefault='t' then 'YES'

ELSE 'NO'

END AS "Default"

FROM pg_partitions p , pg_partition_columns c

WHERE p.parentpartitiontablename is NULL 

AND c.schemaname=p.schemaname

AND c.tablename=p.tablename

AND c.partitionlevel=p.partitionlevel

AND (p.schemaname,p.tablename)=('<schemaname>','<tablename>');

-- Subpartition structure information


SELECT distinct p.schemaname||'.'||p.tablename "Relation Name",

p.partitionschemaname||'.'||p.partitiontablename "Partition Table",

s.partitionschemaname||'.'||s.partitiontablename "Subpartition Table",

s.partitionname "Name",

s.partitiontype "Type",

s.columnname "Column",

s.partitionlevel "Level",

s.partitionrank "Rank",

s.partitionposition "Position",

CASE 

WHEN s.partitionisdefault='t' then 'YES'

ELSE 'NO'

END AS "Default"

FROM pg_partitions p 

LEFT JOIN (SELECT s.schemaname,

s.tablename,

partitionschemaname,

partitiontablename,

parentpartitiontablename,

partitionname,

partitiontype,

s.partitionlevel,

partitionrank,

partitionposition,

partitionisdefault,

columnname 

FROM pg_partitions s 

LEFT JOIN pg_partition_columns c

ON  c.schemaname=s.schemaname

AND c.tablename=s.tablename

AND c.partitionlevel=s.partitionlevel

WHERE s.parentpartitiontablename is NOT NULL) s

ON s.schemaname=p.schemaname

AND s.tablename=p.tablename

AND s.parentpartitiontablename=p.partitiontablename

WHERE p.parentpartitiontablename is NULL 

AND (p.schemaname,p.tablename)=('<schemaname>','<tablename>')

AND (p.schemaname,p.partitiontablename)=('<schemaname>','<partition tablename>')

ORDER BY 8;


-- If subpartition is of the type "Range", use the query below to access its partition criteria.


SELECT distinct p.schemaname||'.'||p.tablename "Relation Name",

p.partitionschemaname||'.'||p.partitiontablename "Partition Table",

s.partitionschemaname||'.'||s.partitiontablename "Subpartition Table",

s.partitionname "Name",

s.partitiontype "Type",

s.columnname "Column",

s.partitionrangestart "Start Range",

CASE

WHEN s.partitionstartinclusive='t' then 'YES'

ELSE 'NO'

END AS "Start Include",

s.partitionrangeend "End Range",

CASE

WHEN s.partitionendinclusive='t' then 'YES'

ELSE 'NO'

END AS "End Include",

CASE 

WHEN s.partitionisdefault='t' then 'YES'

ELSE 'NO'

END AS "Default"

FROM pg_partitions p 

LEFT JOIN (SELECT s.schemaname,

s.tablename,

partitionschemaname,

partitiontablename,

parentpartitiontablename,

partitionname,

partitiontype,

s.partitionlevel,

partitionrangestart,

partitionstartinclusive,

partitionrangeend,

partitionendinclusive,

partitionisdefault,

columnname 

FROM pg_partitions s 

LEFT JOIN pg_partition_columns c

ON  c.schemaname=s.schemaname

AND c.tablename=s.tablename

AND c.partitionlevel=s.partitionlevel

WHERE s.parentpartitiontablename is NOT NULL) s

ON s.schemaname=p.schemaname

AND s.tablename=p.tablename

AND s.parentpartitiontablename=p.partitiontablename

WHERE p.parentpartitiontablename is NULL 

AND (p.schemaname,p.tablename)=('<schemaname>','<tablename>')

AND (p.schemaname,p.partitiontablename)=('<schemaname>','<partition tablename>')

ORDER BY 3;

-- If subpartition is of the type "List", use the query below to access its partition criteria


SELECT distinct p.schemaname||'.'||p.tablename "Relation Name",

p.partitionschemaname||'.'||p.partitiontablename "Partition Table",

s.partitionschemaname||'.'||s.partitiontablename "Subpartition Table",

s.partitionname "Name",

s.partitiontype "Type",

s.columnname "Column",

s.partitionlistvalues "List Values", 

CASE 

WHEN s.partitionisdefault='t' then 'YES'

ELSE 'NO'

END AS "Default"

FROM pg_partitions p 

LEFT JOIN (SELECT s.schemaname,

s.tablename,

partitionschemaname,

partitiontablename,

parentpartitiontablename,

partitionname,

partitiontype,

s.partitionlevel,

partitionlistvalues,

partitionisdefault,

columnname 

FROM pg_partitions s 

LEFT JOIN pg_partition_columns c

ON  c.schemaname=s.schemaname

AND c.tablename=s.tablename

AND c.partitionlevel=s.partitionlevel

WHERE s.parentpartitiontablename is NOT NULL) s

ON s.schemaname=p.schemaname

AND s.tablename=p.tablename

AND s.parentpartitiontablename=p.partitiontablename

WHERE p.parentpartitiontablename is NULL 

AND (p.schemaname,p.tablename)=('<schemaname>','<tablename>')

AND (p.schemaname,p.partitiontablename)=('<schemaname>','<partition tablename>')

ORDER BY 3;

The gadget spec URL could not be found

The gadget spec URL could not be found

The gadget spec URL could not be found
Comments