How to find tables distributed by legacy hash operators

Products

• VMware Tanzu Greenplum
• VMware Tanzu Greenplum / Gemfire
• VMware Tanzu Data Intelligence
• VMware Tanzu Data Suite
• VMware Tanzu Data Suite

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)

In general, gp_use_legacy_hashops implies whether GPDB5 hash ops was in use or not, and how the distribution policy would looks like can vary depends on the current session level gp_use_legacy_hashops settings:

Session gp_use_legacy_hashops	\d+ <tablename> Output	Actual Table Creation Context	Logic
ON	DISTRIBUTED BY (c)	Created with GPDB5 Ops	Matches session default; the operator class is hidden.
ON	DISTRIBUTED BY (c int4_ops)	Created with GPDB6 Ops	Differs from session legacy default; explicitly labels the GPDB6 opclass.
OFF	DISTRIBUTED BY (c)	Created with GPDB6 Ops	Matches session default; the operator class is hidden.
OFF	DISTRIBUTED BY (c cdbhash_int4_ops)	Created with GPDB5 Ops	Differs from session standard default; explicitly labels the legacy opclass.