How to find tables with distribution keys that are not valid in Tanzu Greenplum 6.x

Products

• VMware Tanzu Greenplum

Issue/Introduction

From the Migrating or Upgrading to Greenplum 6 documentation:

Columns of type abstime, reltime, tinterval, money, or anyarray are not supported as distribution keys in Greenplum 6.

The following is stated in the "Release Notes." 

Columns with geometric or user-defined data types are not eligible as Greenplum distribution key columns.

This article explains how to find any tables in the database that are distributed using any of the above types.

 

Environment

Product Version: 6.17

Resolution

Note: The following queries help in finding tables with incorrect data types in their distribution keys. The queries may NOT give a full definitive list of such tables in the database, but will help in finding many.

To find tables which have the disallowed types in the distribution key run the following query in each database.

The data types it searches for are those listed in the Migrating or Upgrading to Greenplum 6 document and the geometric types 'point', 'line', 'lseg', 'box', 'path', 'polygon' and 'circle'. See PostgreSQL documentation on Geometric Types for more information.

SELECT n.nspname,
       c.relname,
       d.localoid,
       d.attrnums,
       a.attname,
       a.atttypid,
       t.typname
FROM   gp_distribution_policy d
       JOIN pg_attribute a
         ON ( a.attrelid = d.localoid )
       JOIN pg_type t
         ON ( a.atttypid = t.oid )
       JOIN pg_class c
         ON ( d.localoid = c.oid )
       JOIN pg_namespace n
         ON ( c.relnamespace = n.oid )
WHERE  a.attnum = ANY ( d.attrnums )
       AND t.typname IN ( 'abstime', 'reltime', 'tinterval', 'money', 'anyarray', 'point', 'line', 'lseg', 'box', 'path', 'polygon', 'circle')
;

To help find tables with user defined types in their distribution keys, the following query searches for distribution keys where the data types are NOT in the "pg_catalog" schema:

SELECT n.nspname,
       c.relname,
       d.localoid,
       d.attrnums,
       a.attname,
       a.atttypid,
       n1.nspname AS type_namespace,
       t.typname
FROM   gp_distribution_policy d
       JOIN pg_attribute a
         ON ( a.attrelid = d.localoid )
       JOIN pg_type t
         ON ( a.atttypid = t.oid )
       JOIN pg_class c
         ON ( d.localoid = c.oid )
       JOIN pg_namespace n
         ON ( c.relnamespace = n.oid )
       JOIN pg_namespace n1
         ON ( t.typnamespace = n1.oid )
WHERE  a.attnum = ANY ( d.attrnums )
       AND n1.nspname <> 'pg_catalog';