GPDB7 gp_toolkit.gp_partitions partition_level differs from GPDB6 pg_partitions partition_level

[lmugnano4537]

Bug Report

I believe the intent of the gp_toolkit.gp_partitions table was an attempt to reduce the huge impact the elimination of pg_partitions is going to have on long term customers of Greenplum. In general it does appear to be a pretty decent "drop-in" replacement.

I did find one area where there is a difference between what I get for a partitioned table from gp_partitions vs the old pg_partitions. That is the partition_level column, in versions older than Greenplum 7 the partition level starts with 0 (so any partition level > 0 would indicate it's a sub-partition). It looks like in Greenplum 7 the partition level start with 1.

This inconsistency could cause issues for 3rd party vendors or other scripts that used that to determine how many partition levels or what partition is the highest level partition.

Greenplum version or build

7.1

OS version and uname -a

ALL

autoconf options used ( config.status --config )

Installation information ( pg_config )

Expected behavior

I think you should make gp_toolkit.gp_partitions a true "drop-in" replacement for pg_partitions or at least document those differences that do exist

Actual behavior

GPDB7: select partitiontablename,partitionlevel from gp_toolkit.gp_partitions where tablename = 'rp_date' order by partitionrank; partitonlevel starts with 1

GPDB6: select partitiontablename,partitionlevel from gp_toolkit.gp_partitions where tablename = 'rp_date' order by partitionrank; partitionlevel starts with 0

Step to reproduce the behavior

Run the above query in GPDB6 and GPDB7, note difference

[ashwinstar]

@huansong @soumyadeep2007 thoughts on this?

[soumyadeep2007]

Yes this is a known difference from the PR description. This is a discussion on the same topic: https://github.com/greenplum-db/gpdb/pull/16746/files#r1402794838 has the same discussion..

Notable differences from 6X for this view: (1) The root is level = 0, its immediate children are level 1 and so on.

I guess we missed out on documenting it. I will add it to the docs soon.