ERROR: unrecognised parameter "appendoptimized" for partition definition

Products

• VMware Tanzu Greenplum

Issue/Introduction

This article applies to Greenplum version 6.15. If you try to create a partition table with a storage_type appendoptimized defined in a partition definition, it errors out:

create table ak_ao_f(i integer) distributed randomly PARTITION BY RANGE(i) (
PARTITION p100 START (1::integer) INCLUSIVE END (100::integer) INCLUSIVE  WITH (appendoptimized=true)  ,
PARTITION p101 START (101::integer) INCLUSIVE END (200::integer) INCLUSIVE
);
NOTICE:  CREATE TABLE will create partition "ak_ao_f_1_prt_p100" for table "ak_ao_f"
NOTICE:  CREATE TABLE will create partition "ak_ao_f_1_prt_p101" for table "ak_ao_f"
ERROR:  unrecognized parameter "appendoptimized"

Environment

Product Version: 6.15

Resolution

According to 6.x documentation, the storage_parameter now is
appendoptimized={TRUE|FALSE}

• Resource https://docs.vmware.com/en/VMware-Greenplum/7/greenplum-database/ref_guide-sql_commands-CREATE_TABLE.html 

In 5.x, the storage_parameter is
APPENDONLY={TRUE|FALSE}

• Resource. https://docs.vmware.com/en/VMware-Greenplum/7/greenplum-database/ref_guide-sql_commands-CREATE_TABLE.html 

 

If you move the appendoptimized statement to the main table definition from partition definition it works but then the entire table with all partitions is defined as per appendoptimized value:

create table ak_ao_f(i integer)  WITH (appendoptimized=true) distributed randomly PARTITION BY RANGE(i) (
PARTITION p100 START (1::integer) INCLUSIVE END (100::integer) INCLUSIVE  ,
PARTITION p101 START (101::integer) INCLUSIVE END (200::integer) INCLUSIVE
);
NOTICE:  CREATE TABLE will create partition "ak_ao_f_1_prt_p100" for table "ak_ao_f"
NOTICE:  CREATE TABLE will create partition "ak_ao_f_1_prt_p101" for table "ak_ao_f"
CREATE TABLE
blackrock_277479=#
blackrock_277479=#
blackrock_277479=# \d
                      List of relations
 Schema |        Name        | Type  |  Owner  |   Storage
--------+--------------------+-------+---------+-------------
 public | ak_ao_f            | table | gpadmin | append only
 public | ak_ao_f_1_prt_p100 | table | gpadmin | append only
 public | ak_ao_f_1_prt_p101 | table | gpadmin | append only
(3 rows)

 

Workaround:

If you only want to have a certain partition with append optimize, the old parameter documented in 5.x still works in 6.x despite the fact that it's not documented anymore:

create table ak_ao_f(i integer) distributed randomly PARTITION BY RANGE(i) (
PARTITION p100 START (1::integer) INCLUSIVE END (100::integer) INCLUSIVE  WITH (APPENDONLY=true)  ,
PARTITION p101 START (101::integer) INCLUSIVE END (200::integer) INCLUSIVE
);
NOTICE:  CREATE TABLE will create partition "ak_ao_f_1_prt_p100" for table "ak_ao_f"
NOTICE:  CREATE TABLE will create partition "ak_ao_f_1_prt_p101" for table "ak_ao_f"
CREATE TABLE
blackrock_277479=# \d
                      List of relations
 Schema |        Name        | Type  |  Owner  |   Storage
--------+--------------------+-------+---------+-------------
 public | ak_ao_f            | table | gpadmin | heap
 public | ak_ao_f_1_prt_p100 | table | gpadmin | append only
 public | ak_ao_f_1_prt_p101 | table | gpadmin | heap
(3 rows)

WITH (APPENDONLY=true) continues to work in newer versions and there are currently no plans to deprecate it. WITH (appendoptimized=true)is a thin alias for the same starting GPDB 6 which can be used as replacement for it.