Script - How to Access Partition Information (Greennplum 6.x)
search cancel

Script - How to Access Partition Information (Greennplum 6.x)

book

Article ID: 295387

calendar_today

Updated On:

Products

VMware Tanzu Greenplum VMware Tanzu Data Suite VMware Tanzu Data Suite Pivotal Data Suite Non Production Edition Greenplum

Issue/Introduction

This article provides a script to help administrators identify partition information in the database.

Note: Verify the script before running it on a production cluster.


Environment

This KB only applies to Greenplum 6.x.

Resolution

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.


Note:
 

  • 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 contains 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 the parent partition 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 the 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 the 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;


Additional Information

For more information on the columns in pg_partitions, please refer to the Greenplum documentation.

 

Powered by Wolken Software