Pivotal Knowledge Base

Follow

Script - Table distribution policy information (via Database Functions)

Goal

The distribution policy on any Pivotal Greenplum Database can be easily found with "\d <TableName>" , or can be retrieved from gp_distribution_policy table and matching with pg_attributes, but there is no direct SQL query that is available which can be queried to obtain distribution policy on each table from catalog tables.

In this document we will create a function that can help you create a simple SQL to retrieve information of the tables distribution policy.

Note: Please verify the script on a test cluster , before running it on production .

Solution

-- Create "commacat" functions

CREATE or replace FUNCTION commacat(acc text, instr text) RETURNS text AS $$
BEGIN
IF acc IS NULL OR acc = '' THEN
RETURN instr;
ELSE
RETURN acc || ',' || instr;
END IF;
END;
$$ LANGUAGE plpgsql
;

-- Create "textcat_all" Aggregate

CREATE AGGREGATE textcat_all(
basetype    = text,
sfunc       = commacat,stype       = text,
initcond    = ''
);

-- Run the following query from a psql session to retreive the table distribution policy.

select nspname||'.'||relname as "Table Name",
  textcat_all(attname) as " Distribution Policy"
from pg_attribute 
join (SELECT pg_class.oid,nspname,relname,unnest(attrnums) as col 
    from gp_distribution_policy  
    join  pg_class  on localoid=oid 
    join pg_namespace nsp on relnamespace=nsp.oid) def on def.col=attnum and oid=attrelid group by 1
union
select nspname||'.'||relname as "Table Name",
       'Random' as " Distribution Policy"
from pg_class c 
join pg_namespace n on n.oid=c.relnamespace
join gp_distribution_policy d on d.localoid=c.oid
and d.attrnums is NULL;

Alternative script to retreive the same information is available here

Comments

Powered by Zendesk