Gpcheckcat Utility [ERROR] executing: Missing or extraneous entries

Products

• VMware Tanzu Greenplum

Issue/Introduction

In GPDB 6.x, gpcheckcat may error out in missing_extraneous test with:

[ERROR] executing: Missing or extraneous entries check for gp_distribution_policy
  Execution error: ERROR:  CASE types svec and integer[] cannot be matched
LINE 4:                       when segids is null then array[master....
                                                       ^


          SELECT localoid,
                 case when master is null then segids
                      when segids is null then array[master.segid]
                      else master.segid || segids end as segids
          FROM
          (
            SELECT localoid, array_agg(gp_segment_id order by gp_segment_id) as segids
            FROM gp_dist_random('gp_distribution_policy')  GROUP BY localoid
          ) as seg
          FULL OUTER JOIN
          (
            SELECT gp_segment_id as segid, localoid FROM gp_distribution_policy 
          ) as master
          USING (localoid)
          WHERE master.segid is null
             OR segids is null
             OR NOT segids @> (select array_agg(content::int4) from gp_segment_configuration WHERE content >= 0)

This error will repeat for all catalog tables in the missing_extraneous section.

Environment

Product Version: 6.10

Resolution

This is not a catalog issue, but an issue with the gpcheckcat utility when gp_sparse_vector extension is installed in the database. You can confirm by checking pg_extension catalog table. If it's installed, you can try running the underlying sql from the gpcheckcat log:

[gpadmin@gpdb-m ~]$ source /usr/local/src/gpdbinstall/env/env_6.11.1_20200925204630
 
[gpadmin@gpdb-m ~]$ psql
psql (9.4.24)
Type "help" for help.
 
gpadmin=# create extension gp_sparse_vector;
CREATE EXTENSION
gpadmin=# SELECT localoid,
gpadmin-#                  case when master is null then segids
gpadmin-#                       when segids is null then array[master.segid]
gpadmin-#                       else master.segid || segids end as segids
gpadmin-#           FROM
gpadmin-#           (
gpadmin(#             SELECT localoid, array_agg(gp_segment_id order by gp_segment_id) as segids
gpadmin(#             FROM gp_dist_random('gp_distribution_policy')  GROUP BY localoid
gpadmin(#           ) as seg
gpadmin-#           FULL OUTER JOIN
gpadmin-#           (
gpadmin(#             SELECT gp_segment_id as segid, localoid FROM gp_distribution_policy 
gpadmin(#           ) as master
gpadmin-#           USING (localoid)
gpadmin-#           WHERE master.segid is null
gpadmin-#              OR segids is null
gpadmin-#              OR NOT segids @> (select array_agg(content::int4) from gp_segment_configuration WHERE content >= 0);
ERROR:  CASE types svec and integer[] cannot be matched
LINE 3:                       when segids is null then array[master....

This issue is fixed in 6.11.1. In the meantime if gp_sparse_vector is not used, then you can drop the extension and gpcheckcat will run fine.