How to find tables distributed by legacy hash operators

Products

• VMware Tanzu Greenplum

Issue/Introduction

The hash operators used to distribute data across the segments changed in Greenplum 6.x.
See "Working With Hash Operator Classes in Greenplum 6" in 
Migrating Data from Greenplum 4.3 or 5 to Greenplum 6 for more information.

This knowledge article gives steps on how to find tables that are distributed using the legacy hash operators.

Environment

Product Version: 6.24

Resolution

Check that the setting "gp_use_legacy_hashops" is set to "off":

show gp_use_legacy_hashops;

If it is set to "on", then set it to "off":

set gp_use_legacy_hashops=off;

Run query to find tables distributed by legacy hash operators:

SELECT n.nspname,
       c.relname,
       dp.*,
       (SELECT pg_get_table_distributedby(localoid)) AS distributed_by
FROM   gp_distribution_policy dp
       LEFT JOIN pg_class c
              ON ( dp.localoid = c.oid )
       LEFT JOIN pg_namespace n
              ON ( c.relnamespace = n.oid )
WHERE  pg_get_table_distributedby(localoid) LIKE '%cdbhash_%';

If there are tables using the legacy operatos then the output should look similar to:

 nspname | relname | localoid | policytype | numsegments | distkey |  distclass  |                     distributed_by                      
---------+---------+----------+------------+-------------+---------+-------------+---------------------------------------------------------
 public  | tab03   |    16391 | p          |           4 | 3       | 10166       | DISTRIBUTED BY (c cdbhash_int4_ops)
 public  | tab04   |    16394 | p          |           4 | 3 2     | 10166 10166 | DISTRIBUTED BY (c cdbhash_int4_ops, b cdbhash_int4_ops)