GPDB7 REGRESSION: Removal of appendonly from reloptions, how to tell now that a parent table of a partitioned table was created with appendonly?

[lmugnano4537]

Bug Report

It appears that certain information was removed from pg_class.reloptions in GPDB7, namely the appendonly=true and orientation= <row/column> clauses. We have queries that were using reloptions in GPDB6 to help us determine how a table got created. For the most part I was able to find other ways to get at the information (ex. just joining to pg_appendonly to determine that it is AO).

One problem though is that it appears that pg_appendonly table DOES NOT have the oid associated with the parent table of a partitioned table whereas pg_class.reloptions used to provide this information so I can tell that the original create table of the partitioned table created it as appendonly.

How do I get this information now in Greenplum 7? The release notes mention eliminating reloptions from pg_appendonly but DOES NOT mention that Greenplum 7 changed what is actually stored in the pg_class.reloptions column nor does it tell the user how to get the information we used to be able to get from that column in the past.

We need this information documented since it's a breaking change.

Greenplum version or build

7.1

OS version and uname -a

ALL

autoconf options used ( config.status --config )

Installation information ( pg_config )

Expected behavior

Understand Greenplum 7 thoroughly changed very key aspects of the catalog and understand VMware Greenplum provides no standard API to the catalog, and never had, which is why lower level catalog tables are heavily used in long time legacy Greenplum applications. However, since there was no published API and we know the catalog tables needed to be used to determine very basic things then you have to document these changes much better and PROVIDE ALTERNATIVES (a standard API FINALLY would be ideal, why not update information_schema to have stuff specific to Greenplum in it instead of just being a copy/paste of Postgres information_schema which we know isn't right).

Actual behavior

Can't do what I was doing in older releases of Greenplum, need to know how to get my functionality back

Step to reproduce the behavior

1. create a partitioned table with appendoptimized=true.
2. Try to determine from the catalog that the parent table was created with appendoptimized=true

I know about polymorphic storage so do know that different partitions under that parent table could be AO or heap or external but ultimately what I want to know is what the original table was created with.

I feel like there has to be a way but I can't find it

[kainwen]

relam field pg_class:

CREATE TABLE rank_new (id int, rank int, year int, gender
        char(1), count int) with(appendonly=true)
DISTRIBUTED BY (id)
PARTITION BY RANGE (year)
( START (2006) END (2016) EVERY (1),
          DEFAULT PARTITION extra );

gpadmin=# select relname, amname from pg_class PC , 
                    pg_am pa where pc.oid = 'rank_new'::regclass and pa.oid = pc.relam
;
 relname  | amname
----------+--------
 rank_new | ao_row
(1 row)

[lmugnano4537]

Thanks, I’ll try that. Unfortunately the same doesn’t work in older releases, my code is trying to support multiple Greenplum releases with as few differences as possible but it is what it is I guess.

By the way, the documentation has no information at all about the pg_am table and it seems like it’s become a much more important catalog table in Greenplum 7, should probably highlight it somewhere in the doc.

On Sun, Apr 14, 2024 at 11:17 AM Zhenghua Lyu @.***> wrote:

relam field pg_class:

CREATE TABLE rank_new (id int, rank int, year int, gender char(1), count int) with(appendonly=true) DISTRIBUTED BY (id) PARTITION BY RANGE (year) ( START (2006) END (2016) EVERY (1), DEFAULT PARTITION extra );

gpadmin=# select relname, amname from pg_class PC , pg_am pa where pc.oid = 'rank_new'::regclass and pa.oid = pc.relam ; relname | amname ----------+-------- rank_new | ao_row (1 row)

— Reply to this email directly, view it on GitHub https://github.com/greenplum-db/gpdb/issues/17350#issuecomment-2054094851, or unsubscribe https://github.com/notifications/unsubscribe-auth/BDATK4K7IX3ZEQF2NDNSYITY5KM2LAVCNFSM6AAAAABGGG2MNSVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDANJUGA4TIOBVGE . You are receiving this because you authored the thread.Message ID: @.***>

[kainwen]

Thanks, I’ll try that. Unfortunately the same doesn’t work in older releases, my code is trying to support multiple Greenplum releases with as few differences as possible but it is what it is I guess. By the way, the documentation has no information at all about the pg_am table and it seems like it’s become a much more important catalog table in Greenplum 7, should probably highlight it somewhere in the doc. … On Sun, Apr 14, 2024 at 11:17 AM Zhenghua Lyu @.> wrote: relam field pg_class: CREATE TABLE rank_new (id int, rank int, year int, gender char(1), count int) with(appendonly=true) DISTRIBUTED BY (id) PARTITION BY RANGE (year) ( START (2006) END (2016) EVERY (1), DEFAULT PARTITION extra ); gpadmin=# select relname, amname from pg_class PC , pg_am pa where pc.oid = 'rank_new'::regclass and pa.oid = pc.relam ; relname | amname ----------+-------- rank_new | ao_row (1 row) — Reply to this email directly, view it on GitHub <#17350 (comment)>, or unsubscribe https://github.com/notifications/unsubscribe-auth/BDATK4K7IX3ZEQF2NDNSYITY5KM2LAVCNFSM6AAAAABGGG2MNSVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDANJUGA4TIOBVGE . You are receiving this because you authored the thread.Message ID: @.>

Here is a similar issue and wider discussion: https://github.com/greenplum-db/gpdb/issues/16093

[avamingli]

One problem though is that it appears that pg_appendonly table DOES NOT have the oid associated with the parent table of a partitioned table whereas pg_class.reloptions used to provide this information so I can tell that the original create table of the partitioned table created it as appendonly

query pg_inherits to get a partition's parent oid (if exists) and use that to query pg_class(if parent is a partitioned table) to get the am.

[lmugnano4537]

Thanks all for your help and recommendations, I think I got enough now to do what I need.

On Mon, Apr 15, 2024 at 1:04 AM Zhang Mingli @.***> wrote:

One problem though is that it appears that pg_appendonly table DOES NOT have the oid associated with the parent table of a partitioned table whereas pg_class.reloptions used to provide this information so I can tell that the original create table of the partitioned table created it as appendonly

query pg_inherits to get a partition's parent oid (if exists and is a partitioned table) and use that to query pg_class to get the am.

— Reply to this email directly, view it on GitHub https://github.com/greenplum-db/gpdb/issues/17350#issuecomment-2055176777, or unsubscribe https://github.com/notifications/unsubscribe-auth/BDATK4JGW2LPYCMKHSHA7LDY5NNULAVCNFSM6AAAAABGGG2MNSVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDANJVGE3TMNZXG4 . You are receiving this because you authored the thread.Message ID: @.***>

[lmugnano4537]

Before we close this one can I recommend that something be put into the release notes about the change in content on the reloptions column in pg_class. We do have a section mentioned reloptions was removed from pg_appendonly so perhaps right under that line you can mention this change to pg_class.reloptions since it is a change that could impact people migrating to 7. Thanks

[ashwinstar]

@avamingli Please can you help to work with Documentation team to make the enhancement happen to improve the context as mentioned. Thanks.