> [color=#7ddb3b] This blog is one of a series of blogs to introduce partitioning in Greenplum 7:
> * What's New: https://hackmd.io/FnJGS2d_RW-aNAm8K6mHeQ
> * Recursion and Inheritance: https://hackmd.io/x6zxrkXkQ_2LILkI-UxM1w
> * [WIP] Subpartition template: https://hackmd.io/IV9BKBcUSXibyVfwwReD0A

# Partition in Greenplum 7: Recursion and Inheritance

The partition hierarchy is often large and complex, and many times need hybrid table properties among different partions. It is important to understand the recursion behavior in order to get the right partition paradigm that one would like it to be.

Similar to our [previous blog](https://hackmd.io/FnJGS2d_RW-aNAm8K6mHeQ), this blog will mainly focus on the new commands in Greenplum 7 and the changes in legacy Greenplum commands.

# 1. The `ONLY` keyword
Firstly, one can always specify the `ONLY` keyword if no recursion is intended. For example assuming we would like to [change the access method](https://greenplum.org/gpdb7-alter-your-tables-storage/) of future partitions, but don't want it to apply to the existing ones. We can simply do the following:

-- Assuming partitioned table 'sales' and all 
-- of its child partitions are heap tables.
ALTER TABLE ONLY sales SET ACCESS METHOD ao_row;

The partitioned table will be set with the desired access method:

\d+ sales
                                Partitioned table "public.sales"
 Column |     Type      | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------------+-----------+----------+---------+---------+--------------+-------------
 id     | integer       |           |          |         | plain   |              |
 date   | date          |           |          |         | plain   |              |
 amt    | numeric(10,2) |           |          |         | main    |              |
Partition key: RANGE (date)
Partitions: jan_sales FOR VALUES FROM ('2023-01-01') TO ('2023-02-01')
Distributed by: (id)
Access method: ao_row

But the existing child partition of it, will not be affected:

\d+ jan_sales
                                    Table "public.jan_sales"
 Column |     Type      | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------------+-----------+----------+---------+---------+--------------+-------------
 id     | integer       |           |          |         | plain   |              |
 date   | date          |           |          |         | plain   |              |
 amt    | numeric(10,2) |           |          |         | main    |              |
Partition of: sales FOR VALUES FROM ('2023-01-01') TO ('2023-02-01')
Partition constraint: ((date IS NOT NULL) AND (date >= '2023-01-01'::date) AND (date < '2023-02-01'::date))
Distributed by: (id)
Access method: heap

## `GRANT ONLY`|`REVOKE ONLY`
Also in Greenplum 7, you can even specify the `ONLY` keyword for `GRANT`/`REVOKE`, which is not there in [PostgreSQL](https://www.postgresql.org/docs/12/sql-grant.html). Note that, Greenplum historically has had the behavior that `GRANT`|`REVOKE` on a parent partitioned table would recurse into its child partitions, which is also different than PostgreSQL. Greenplum 7 keeps that behavior, but is adding the `ONLY` option to provide the flexibility if one does not wish to recurse.

A simple illustration of the usage:

-- Let's say at some point you want two roles with read permission 
-- on our 'sale' partitioned table, but one for existing partitions
-- and another for future partitions.

-- 1. Grant only on the parent table for just future partitions.
GRANT SELECT ON ONLY sales TO role_that_can_read_only_future_partitions;

-- 2. W/o "ONLY", this will grant for all existing partitions.
-- Then, revoke only for parent to limit permission for future partitions.
GRANT SELECT ON sales TO roles_that_can_read_only_existing_partitions;
REVOKE SELECT ON ONLY sales FROM roles_that_can_read_only_existing_partitions

# 2. Creating a new child table
In general, creating a new table as a child partition **will inherit all of its parent table's properties**. For example, let's still start with our `sales` table:

CREATE TABLE sales (id int, date date, amt decimal(10,2))
USING ao_row
DISTRIBUTED BY (id)
PARTITION BY RANGE (date);

We can use two types of syntaxes to create a new table as a child partition:

-- Create child partition using the new Greenplum 7 syntax
CREATE TABLE jan_sales PARTITION OF sales FOR VALUES FROM ('2023-01-01') TO ('2023-02-01');

-- Create child partition using the legacy syntax
ALTER TABLE sales ADD PARTITION feb_sales START ('2023-02-01') END ('2023-03-01');

Both child partitions will inherit the parent table's access method which is append-optimized row-oriented (`ao_row`):

\d+ sales
                                Partitioned table "public.sales"
 Column |     Type      | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------------+-----------+----------+---------+---------+--------------+-------------
 id     | integer       |           |          |         | plain   |              |
 date   | date          |           |          |         | plain   |              |
 amt    | numeric(10,2) |           |          |         | main    |              |
Partition key: RANGE (date)
Partitions: jan_sales FOR VALUES FROM ('2023-01-01') TO ('2023-02-01'),
            sales_1_prt_feb_sales FOR VALUES FROM ('2023-02-01') TO ('2023-03-01')
Distributed by: (id)
Access method: ao_row

\d+ jan_sales
                                    Table "public.jan_sales"
 Column |     Type      | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------------+-----------+----------+---------+---------+--------------+-------------
 id     | integer       |           |          |         | plain   |              |
 date   | date          |           |          |         | plain   |              |
 amt    | numeric(10,2) |           |          |         | main    |              |
Partition of: sales FOR VALUES FROM ('2023-01-01') TO ('2023-02-01')
Partition constraint: ((date IS NOT NULL) AND (date >= '2023-01-01'::date) AND (date < '2023-02-01'::date))
Compression Type: None
Compression Level: 0
Block Size: 32768
Checksum: t
Distributed by: (id)
Access method: ao_row

\d+ sales_1_prt_feb_sales
                              Table "public.sales_1_prt_feb_sales"
 Column |     Type      | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------------+-----------+----------+---------+---------+--------------+-------------
 id     | integer       |           |          |         | plain   |              |
 date   | date          |           |          |         | plain   |              |
 amt    | numeric(10,2) |           |          |         | main    |              |
Partition of: sales FOR VALUES FROM ('2023-02-01') TO ('2023-03-01')
Partition constraint: ((date IS NOT NULL) AND (date >= '2023-02-01'::date) AND (date < '2023-03-01'::date))
Compression Type: None
Compression Level: 0
Block Size: 32768
Checksum: t
Distributed by: (id)
Access method: ao_row

## `SPLIT PARTITION`

`SPLIT PARTITION` is a special case where it creates new child partitions out from an existing child partition. In that case, the new partitions will inherit **not** from the parent partitioned table, but the splitted child. For example, let's say for some reason we have made the `feb_sales` partition in our example to have different access method than the parent:

ALTER TABLE sales_1_prt_feb_sales SET ACCESS METHOD ao_column;

Now we split it into two new partitions:

ALTER TABLE sales 
SPLIT PARTITION feb_sales AT ('2023-02-15') INTO 
(partition feb_first_half, partition feb_second_half);

Then the new partitions will have different access method than its parent (note the original `feb_sales` partition is gone):

\d+ sales
                                Partitioned table "public.sales"
 Column |     Type      | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------------+-----------+----------+---------+---------+--------------+-------------
 id     | integer       |           |          |         | plain   |              |
 date   | date          |           |          |         | plain   |              |
 amt    | numeric(10,2) |           |          |         | main    |              |
Partition key: RANGE (date)
Partitions: jan_sales FOR VALUES FROM ('2023-01-01') TO ('2023-02-01'),
            sales_1_prt_feb_first_half FOR VALUES FROM ('2023-02-01') TO ('2023-02-15'),
            sales_1_prt_feb_second_half FOR VALUES FROM ('2023-02-15') TO ('2023-03-01')
Distributed by: (id)
Access method: ao_row

\d+ sales_1_prt_feb_first_half
                                                     Table "public.sales_1_prt_feb_first_half"
 Column |     Type      | Collation | Nullable | Default | Storage | Stats target | Compression Type | Compression Level | Block Size | Description
--------+---------------+-----------+----------+---------+---------+--------------+------------------+-------------------+------------+-------------
 id     | integer       |           |          |         | plain   |              | none             | 0                 | 32768      |
 date   | date          |           |          |         | plain   |              | none             | 0                 | 32768      |
 amt    | numeric(10,2) |           |          |         | main    |              | none             | 0                 | 32768      |
Partition of: sales FOR VALUES FROM ('2023-02-01') TO ('2023-02-15')
Partition constraint: ((date IS NOT NULL) AND (date >= '2023-02-01'::date) AND (date < '2023-02-15'::date))
Distributed by: (id)
Access method: ao_column
Options: blocksize=32768, compresslevel=0, compresstype=none, checksum=true

This inheritance behavior in `SPLIT PARTITION` remains the same as Greenplum 6.

# 3. Attaching an existing table
Then let's consider the case when we do not create a new table, but just attach an existing table as the child partition. In that case, **the original table properties are generally preserved after becoming a child partition**. For example, say we initially to have a `recent_sales` table that we keep updating frequently, and it is using the `heap` access method by default:

CREATE TABLE recent_sales (id int, date date, amt decimal(10,2));

\d+ recent_sales;
                                  Table "public.recent_sales"
 Column |     Type      | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------------+-----------+----------+---------+---------+--------------+-------------
 id     | integer       |           |          |         | plain   |              |
 date   | date          |           |          |         | plain   |              |
 amt    | numeric(10,2) |           |          |         | main    |              |
Distributed by: (id)
Access method: heap

At some point we want to attach this table to the `sales` partitioned table. After that, it will still maintain its original table properties, including the access method:

ALTER TABLE sales ATTACH PARTITION recent_sales 
    FOR VALUES FROM ('2023-12-01') TO ('2030-12-31');

\d+ recent_sales
                                  Table "public.recent_sales"
 Column |     Type      | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------------+-----------+----------+---------+---------+--------------+-------------
 id     | integer       |           |          |         | plain   |              |
 date   | date          |           |          |         | plain   |              |
 amt    | numeric(10,2) |           |          |         | main    |              |
Partition of: sales FOR VALUES FROM ('2023-12-01') TO ('2030-12-31')
Partition constraint: ((date IS NOT NULL) AND (date >= '2023-12-01'::date) AND (date < '2030-12-31'::date))
Distributed by: (id)
Access method: heap

## `EXCHANGE PARTITION`

Another special case, `EXCHANGE PARTITION` attaches an existing outside table to be the child partition. There are a few changes in Greenplum 7 regarding how the new child partition inherits table properties in this case.

As mentioned in our [previous blog](https://hackmd.io/FnJGS2d_RW-aNAm8K6mHeQ), `EXCHANGE PARTITION` in Greenplum 7 now composes of `DETACH PARTITION` and `ATTACH PARTITION`. As a result, the `EXCHANGE PARTITION` is now more alike `ATTACH PARTITION` for inheritance behavior. Here is a detailed list of them:

1. Table owner: `EXCHANGE PARTITION` now doesn't require the partition-to-be table to have the same owner as the parent table.
2. Index: `EXCHANGE PARTITION` now doesn't require the partition-to-be to have the same index as the parent. The command will create one if it's missing.
3. Table constraint: `EXCHANGE PARTITION` now requires the partition-to-be to have whatever constraint its parent has.

# Summary

In general, most of `ALTER TABLE` and `GRANT|REVOKE` commands will recurse into its child partitions unless `ONLY` keyword is specified. And, whether a new child partition inherits or not will be decided by whether it is *created* or *attached*: if created it will inherit from the parent, otherwise it maintains its own original properties.