VMware Greenplum 7 retains most aspects of the partitioning syntax of prior versions of Greenplum, now referred to as the classic partitioning syntax. Version 7 also introduces support for a modern syntax, derived from the PostgreSQL declarative partitioning syntax.

This topic describes the Greenplum 7 partitioning syntax and behavior changes, and is geared toward existing users of Greenplum 6.

(Refer to Partitioning Large Tables for information about using both syntaxes to create and manage partitioned tables.)

Parent topic: Defining Database Objects

What's New?

The following partitioning-related features are new in Greenplum 7:

• Support for PostgreSQL declarative partitioning syntax, which introduces these new features for the modern syntax only:

  • Partitions are first-class tables.
  • The table name of a partition is just that, not an alias.
  • Support for the hash partitioning strategy.
  • Support for multi-column range partitioning.
  • Support for specifying an expression in the partition key.
  • A partitioned table may have zero partitions.
  • Support for a heterogeneous partition hierarchy that allows child partitions of different levels and allows for different partitioning strategies amongst the child partitions.

• Less restrictive locking in ATTACH PARTITION. You can now attach a partition to a partition hierarchy without disrupting many normal query executions on the partition.

• GRANT ... ONLY and REVOKE ... ONLY syntaxes that direct Greenplum to apply the operation to the named table only (does not recurse the operation to child tables).

• ALTER TABLE ONLY syntax that directs Greenplum to apply the operation to the named table only (does not recurse the operation to child tables).

What Hasn't Changed With Classic Syntax?

The following classic partitioning syntax and behaviors have not changed in Greenplum 7:

• Greenplum 7 supports the classic partitioning syntax of Greenplum 6 except where called out in the What Has Changed? section.
• Support for range and list partition strategies.
• Support for sub-partition templates.
• A partitioned table must be defined with at least one partition.
• Only leaf partitions contain data.
• The name that you assign a partition table is an alias.
• By default, a GRANT or REVOKE operation on a parent partitioned table recurses to its child tables.
• By default, an ALTER TABLE operation on a parent partitioned table recurses to its child tables.

What Has Changed?

The following items describe the Greenplum 7 changes to classic partitioning syntax and behaviors compared to Greenplum 6:

• New internal data structures and catalogs are used to represent partitioned tables.

  Greenplum 7 represents and operates on partitioned tables using the new internal data structures and catalog, regardless of the partitioning syntax used to create the table. Operations that you invoke using the classic partitioning syntax are internally mapped to the new data structures, and any output generated by Greenplum, such as DDL, is displayed in that format.

• These partitioning-related catalog tables, views, and functions are removed:

  • pg_partition
  • pg_partition_rule
  • pg_partition_columns
  • pg_partition_encoding
  • pg_partition_rule
  • pg_partition_templates
  • pg_partitions
  • pg_stat_partition_operations
  • pg_partition_def()

  The new pg_partitioned_table catalog table and pg_partition_tree(), pg_partition_ancestors(), and pg_partition_root() functions provide similar information. Refer to About Viewing Your Partition Design for more information on these new functions.

• The FOR (RANK(<value>)) clause is no longer supported. When creating or altering a partitioned table, you must locate a partition by VALUE.

• Partition boundaries are no longer represented as CHECK constraints, but rather internally-constructed partition constraints.

• The level of a partition in the partition hierarchy differs in Greenplum 6 and Greenplum 7. In Greenplum 6, the level of the immediate child of a partitioned table is 0. In Greenplum 7, the level of the partitioned table itself is 0, and the level of its immediate child is 1.

• The interpretation of START/END and EXCLUSIVE/INCLUSIVE clauses for range partition boundaries has changed:

  • When a clause is not specified, the start boundary is always inclusive and the end boundary exclusive (same behaviour as Greenplum 6).
  • When START EXCLUSIVE <n> is specified, Greenplum now implicitly converts this to a START INCLUSIVE <n>+1.
  • When an END INCLUSIVE <n> is specified, Greenplum now implicitly converts this to an END EXCLUSIVE <n>+1.
  • Because of the implicit conversion mentioned in the previous points, START EXCLUSIVE and END INCLUSIVE boundaries are now permitted only for data types that have a suitable + operator like integer and timestamp (but not float or text).

• Partition-specific information displayed in psql \d+ output has changed:

  • Partition boundaries are now tagged Partition constraint.

  • Instead of Inherits, the parent table is now identified with the tag Partition of

  • Range partition boundaries defined with START/END are displayed using the (modern syntax) FOR VALUES [ FROM/TO | IN | WITH ] keywords.

  • Example:

    \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)
    

• The EXCHANGE PARTITION command has changed:

  • This command is now internally implemented with the DETACH PARTITION and ATTACH PARTITION commands introduced with the modern syntax.
  • The partition-to-exchange is no longer required to have the same owner as the parent table.
  • The partition-to-exchange is no longer required to have the same index as the parent table. The command will create one if it is missing.
  • The partition-to-exchange must have the same constraint as the parent table.
  • Previous versions renamed the tables and also renamed the index and constraints, in Greenplum 7, this command only renames the table.

• Property inheritance changes:

  • A created/added partition inherits the properties of its parent.
  • The partitions created from a SPLIT PARTITION inherit the properties of the split child.
  • An attached partition maintains its original properties.

The Greenplum query optimizer (GPORCA) does not support multi-level partitioned tables.

Additional Considerations

Additional factors to consider:

• You must change any scripts that you wrote that depend on DDL or psql \d output.
• Greenplum 7 dumps and restores the DDL of partitioned tables defined with classic syntax using the new catalog structures.