Catalog tables are showing relfilenode as 0 in Pivotal Greenplum 6.x

Products

• VMware Tanzu Greenplum

Issue/Introduction

In Pivotal Greenplum 6.x, the way to check for relfilenode ID for catalog tables has changed due to the way pg_class is storing the the filename information. According to the Postgres documentation, this is how the relfilenode column is being used.

relfilenode - oid         

Name of the on-disk file of this relation; zero means this is a "mapped" relation whose disk file name is determined by low-level state

List of catalog tables affected

gpadmin=# select oid,relfilenode,relname from pg_class where relfilenode = 0;
 oid  | relfilenode |                       relname                       
------+-------------+-----------------------------------------------------
 2658 |           0 | pg_attribute_relid_attnam_index
 2659 |           0 | pg_attribute_relid_attnum_index
 2676 |           0 | pg_authid_rolname_index
 2677 |           0 | pg_authid_oid_index
 6029 |           0 | pg_authid_rolresqueue_index
 6440 |           0 | pg_authid_rolresgroup_index
 2694 |           0 | pg_auth_members_role_member_index
 2695 |           0 | pg_auth_members_member_role_index
 6449 |           0 | pg_auth_time_constraint_authid_index
 2662 |           0 | pg_class_oid_index
 2663 |           0 | pg_class_relname_nsp_index
 3455 |           0 | pg_class_tblspc_relfilenode_index
 2671 |           0 | pg_database_datname_index
 2672 |           0 | pg_database_oid_index
 2397 |           0 | pg_shdescription_o_c_index
 1137 |           0 | pg_pltemplate_name_index
 2690 |           0 | pg_proc_oid_index
 2691 |           0 | pg_proc_proname_args_nsp_index
 1232 |           0 | pg_shdepend_depender_index
 1233 |           0 | pg_shdepend_reference_index
 2697 |           0 | pg_tablespace_oid_index
 2698 |           0 | pg_tablespace_spcname_index
 2703 |           0 | pg_type_oid_index
 2704 |           0 | pg_type_typname_nsp_index
 2965 |           0 | pg_db_role_setting_databaseid_rol_index
 3593 |           0 | pg_shseclabel_object_index
 6057 |           0 | pg_statlastshop_classid_objid_index
 6058 |           0 | pg_statlastshop_classid_objid_staactionname_index
 6027 |           0 | pg_resqueue_oid_index
 6028 |           0 | pg_resqueue_rsqname_index
 6061 |           0 | pg_resourcetype_oid_index
 6062 |           0 | pg_resourcetype_restypid_index
 6063 |           0 | pg_resourcetype_resname_index
 6441 |           0 | pg_resqueuecapability_oid_index
 6442 |           0 | pg_resqueuecapability_resqueueid_index
 6443 |           0 | pg_resqueuecapability_restypid_index
 6447 |           0 | pg_resgroup_oid_index
 6444 |           0 | pg_resgroup_rsgname_index
 6448 |           0 | pg_resgroupcapability_oid_index
 6445 |           0 | pg_resgroupcapability_resgroupid_reslimittype_index
 6446 |           0 | pg_resgroupcapability_resgroupid_index
 6106 |           0 | gp_segment_config_content_preferred_role_index
 6107 |           0 | gp_segment_config_dbid_index
 1260 |           0 | pg_authid
 1247 |           0 | pg_type
 1249 |           0 | pg_attribute
 1255 |           0 | pg_proc
 2837 |           0 | pg_toast_1255_index
 2836 |           0 | pg_toast_1255
 1259 |           0 | pg_class
 1262 |           0 | pg_database
 2964 |           0 | pg_db_role_setting
 2967 |           0 | pg_toast_2964_index
 2966 |           0 | pg_toast_2964
 1213 |           0 | pg_tablespace
 1136 |           0 | pg_pltemplate
 1261 |           0 | pg_auth_members
 1214 |           0 | pg_shdepend
 2396 |           0 | pg_shdescription
 2847 |           0 | pg_toast_2396_index
 2846 |           0 | pg_toast_2396
 3592 |           0 | pg_shseclabel
 6026 |           0 | pg_resqueue
 6060 |           0 | pg_resqueuecapability
 6059 |           0 | pg_resourcetype
 6436 |           0 | pg_resgroup
 6439 |           0 | pg_resgroupcapability
 5006 |           0 | gp_configuration_history
 5001 |           0 | gp_id
 5003 |           0 | gp_version_at_initdb
 5036 |           0 | gp_segment_configuration
 6093 |           0 | pg_toast_5036_index
 6092 |           0 | pg_toast_5036
 6070 |           0 | pg_auth_time_constraint
 6056 |           0 | pg_stat_last_shoperation
(75 rows)

Environment

Product Version: 6.4

Resolution

This change was introduced as part of Postgres 9.0, therefore it affects only Pivotal Greenplum 6.x and was made to solve two issues:

1. The new CLUSTER-based VACUUM FULL can be applied safely to all catalogs.
2. We no longer have to use an unsafe reindex-in-place approach for reindexing shared catalogs.

In Pivotal Greenplum 4.3.x and 5.x, reindexing a shared catalog table would fail with:

gpadmin=# reindex table pg_shdepend;
ERROR: shared table "pg_shdepend" can only be reindexed in stand-alone mode

In Pivotal Greenplum 6.x, this is no longer a problem. Shared catalogs are now treated the same as non-shared catalogs.

In order to find the relfilenode of the catalog tables, you now have to use the following function:

gpadmin=# select pg_relation_filenode('pg_class');
 pg_relation_filenode 
----------------------
        12561
(1 row)