Pivotal Knowledge Base

Follow

Script - How To Access Partition Information

Environment

Product Version
 Pivotal GreenPlum DB  All version

Purpose

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

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

Procedure

-- 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;

Additional Information

For more information on the columns please refer to the Pivotal documentation

Comments

Powered by Zendesk