Please cherrypick some enhancements made in GPDB7.1 to the gp_toolkit schema that are important for day-2 operations of the database

[lmugnano4537]

Description

Greenplum made major breaking changes between GPDB6 and 7 with regards to partitioning. One of the critical catalog tables (pg_partitions) was removed in GPDB7 which had major impacts for existing customers. In GPDB7.1 there was an important enhancement made that provided a "replacement" for pg_partitions. They introduced a new view called gp_toolkit.gp_partitions and some corresponding c-functions to support the view. In order to make these changes in a minor release, one of the things GPDB7 also did was changed gp_toolkit to now be installed as an extension so it could be enhanced in a minor release.

1. Can we see if gp_toolkit can be packaged/installed as an extension instead of built into the server?
2. Can we get the gp_toolkit functions and view for gp_partitions incuded into cloudberrydb (code is attached)
3. Can we also get the code for the check and move orphaned files? This also was a recent add to Greenplum that really comes in handy finding files that are taking up space on the filesystem but are no longer connected to anything in the catalog

I attached the latest code I found for these. In the last version of Greenplum gp_toolkit is now an extension so it resides under the gpcontrib directory.
gpcontrib_gp_toolkit.zip

Use case/motivation

The use case for having the gp_partitions view is to help enable automatic partition maintenance and also have an easier migration for code that was relying on pg_partitions in prior Greenplum releases. There is a lot of code in the customer base that queried pg_partitions to find the partition being loaded for example so they can just analyze/vaccum or truncate just that partition.

The use case for having the orphan file checker is so DBA's can automate and alert on orphan files so they can clean them up and free up disk space

Related issues

No response

Are you willing to submit a PR?

• Yes I am willing to submit a PR!

[tuhaihe]

Hi @my-ship-it, could you please help assign the engineers to look at this?

[avamingli]

I remember I have answered the similar questions at Greenplum partition issue, is it created by you?
But since GPDB is closed, we lost that info.

We do have a plan to cherry-pick commits from GPDB like PR #432.
But we have to do more to make that codes work well with CloudberryDB.
We should be careful because our CBDB codes is based on PG14.4 and GP7 is based on PG12, and it needs some time.

About the question, it's a problem of GPDB6 and GPDB7, the best way is cherry-pick what you need yourselves since CBDB is open source if you are in a hurry.
And I could provide some advices as a workaround :

Create the view pg_partitions you may need, and all partition info could be composed from pg_class and pg_inherits or other catalog.
Query the pg_class to see if a table is a partitioned table and use its oid to query pg_inherits to find its parent recursively.

Partition tables are implemented by inherits tables internal Postgres or CBDB.

[jianlirong]

Any progress? It seems this feature is very important for some customers.

[my-ship-it]

Yeah, it's already under progress. We have a on-duty plan to cherry pick GPDB commits into CBDB periodically：
#432
#448

[lmugnano4537]

Yes, this gp_partition view is very helpful for any existing Greenplum customers migrating from Greenplum 5 or 6. Many customer heavily used Pg_partitions in those older releases and losing that view in Greenplum 7 was a major obstacle for customers migrating to 7. This is why Greenplum introduced this gp_partitions view in 7.1 to give customers a reasonable alternative. Thanks

…

On Thu, Jul 11, 2024 at 10:23 PM Max Yang ***@***.***> wrote: Yeah, it's already under progress. We have a on-duty plan to cherry pick GPDB commits into CBDB periodically. — Reply to this email directly, view it on GitHub <https://github.com/orgs/cloudberrydb/discussions/466#discussioncomment-10026742>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/BDATK4O5LKUN4DMR2IHPOVTZL44ZXAVCNFSM6AAAAABJDPRG7KVHI2DSMVQWIX3LMV43URDJONRXK43TNFXW4Q3PNVWWK3TUHMYTAMBSGY3TIMQ> . You are receiving this because you authored the thread.Message ID: ***@***.*** com>

[edespino]

I can help test this functionality in a dev/pr branch when it is available. Please let me know.

[my-ship-it]

Thanks, Ed! @jiaqizho is working on it now.

[jiaqizho]

#531 hi guys, i am working on it. still have some missing PR not cherry-pick yet.

[tuhaihe]

Thanks @jiaqizho !

[my-ship-it]

Gus, PR #531 already merged, so close the discussion now.