How to find Append-Optimized(AO) table's associated system aosegment table (pg_aoseg_xxx or pg_aocsseg_xxx) in Greenplum

Products

• VMware Tanzu Greenplum
• Greenplum
• Pivotal Data Suite Non Production Edition
• VMware Tanzu Data Suite
• VMware Tanzu Data Suite

Issue/Introduction

System tables are used to manage AO tables.

The "pg_appendonly" catalog table, which is similar to pg_class, stores extra information regarding each AO table.

Cause

AO segments table

Segment files store user data. The AO segment table is one of three auxiliary heap tables that store metadata and helpful attributes, such as modcount.

A bearing on the number of DML operations occurs when AO table is subjected to modcount.

The AO segment table is initially named: "pg_aoseg.pg_aoseg_<oid>", where <oid> is the Object Identifier of the AO table. Applying certain DDL statements, like ALTER TABLE, may rewrite the AO table on disk.

In these cases, the AO segments table is replaced by a new table with a changed <oid> suffix. This process creates a temporary table followed by an update to the relfilenode and the AO segment table <oid>.

To find the current AO segment table of an AO table, query the "pg_catalog.pg_appendonly" catalog. The segrelid column in this table provides an oid for the current AO segment table. If you query pg_class with this <oid>, the current AO segment table name is returned.

Note: The segrelid is not equal to the <oid> suffix of the current AO segment table.

Resolution

Row Oriented (AORO) and Column Oriented (AOCO) have different name formats in the AO segment table:

• AORO table name:  [pg_aoseg.pg_aoseg_xxxxxx>]
• AOCO table name:  [pg_aoseg.pg_aocsseg_xxxxxx>]

Use the query below to find the AO segment table:

# SELECT s.nspname||'.'||c.relname as table from pg_class c,pg_namespace s where c.oid = (SELECT segrelid from pg_appendonly where relid = '[table_name]'::regclass::oid) and s.oid=c.relnamespace;

Example 1: Working on AOCO table [aoco_test]

# SELECT s.nspname||'.'||c.relname as table from pg_class c,pg_namespace s where c.oid = (SELECT segrelid from pg_appendonly where relid = 'aoco_test'::regclass::oid) and s.oid=c.relnamespace;

          table

---------------------------

pg_aoseg.pg_aocsseg_24692

Example 2: Working on AORO table [aoro_test]

# SELECT s.nspname||'.'||c.relname as table from pg_class c,pg_namespace s where c.oid = (SELECT segrelid from pg_appendonly where relid = 'aoro_test'::regclass::oid) and s.oid=c.relnamespace;

         table

-------------------------

pg_aoseg.pg_aoseg_24706

Below is a step by step explanation for the query above:

1. Suppose we have an AOCO table named aoco_test:

gpadmin=# \d aoco_test

Append-Only Columnar Table "public.aoco_test"

2. Get the segrelid from pg_appendonly, which maps to the aoco_test AO table:

# SELECT segrelid from pg_appendonly where relid = 'aoco_test'::regclass::oid;

segrelid

----------

   24697

3. Search the segrelid as an <oid> in pg_class to find the AO segment table:

gpadmin=# SELECT relname from pg_class where oid = 24697;

    relname

------------------

pg_aocsseg_24692