[Ideas] Description of backward-incompatible changes between Greenplum 6 and Cloudberry in the documentation. #1816

Here are the backward-incompatible changes blocking the upgrade that we were able to identify.

Upgrade blockers

Incompatible change in the internal format of the jsonb type

In PostgreSQL kernel versions newer than 9.4, the internal representation of the jsonb type was changed without backward compatibility.

How to detect

Find all tables that use jsonb:

SELECT n.nspname, c.relname, a.attname
FROM pg_attribute a
 JOIN pg_class c ON c.oid = a.attrelid
 JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE a.atttypid = 'jsonb'::regtype
 AND a.attnum > 0;

How to fix

Recreate the problematic columns, replacing jsonb with json or text. If necessary, migrate the data before dropping the column with jsonb.

Removal of the abstime, reltime, tinterval, and unknown types

Starting with PostgreSQL kernel version 10, the unknown type became a pseudo-type.
Starting with PostgreSQL kernel version 12, the abstime, reltime, and tinterval types were removed.

In Greenplum 6, the following queries work without errors:

CREATE TABLE example_abstime (x abstime);
CREATE TABLE example_reltime (x reltime);
CREATE TABLE example_tinterval (x tinterval);
CREATE TABLE example_unknown (x unknown);
WARNING: column "x" has type "unknown"

In Cloudberry, the same queries fail with errors:

CREATE TABLE example_abstime (x abstime);
ERROR: type "abstime" does not exist
CREATE TABLE example_reltime (x reltime);
ERROR: type "reltime" does not exist
CREATE TABLE example_tinterval (x tinterval);
ERROR: type "tinterval" does not exist
CREATE TABLE example_unknown (x unknown);
ERROR: column "x" has pseudo-type unknown

How to detect

All of these data types have been completely removed and are no longer available for use:

• in SQL queries;
• in table definitions;
• in PL/pgSQL functions.

Tables using the removed types can be found with the following query:

SELECT n.nspname, c.relname, a.attname, t.typname
FROM pg_attribute a
 JOIN pg_class c ON c.oid = a.attrelid
 JOIN pg_namespace n ON n.oid = c.relnamespace
 JOIN pg_type t ON t.oid = a.atttypid
WHERE t.typname IN ('abstime', 'reltime', 'tinterval', '_abstime', '_reltime', '_tinterval', 'unknown')
 AND a.attnum > 0
 AND NOT a.attisdropped;

It is also worth checking user-defined type definitions, views, and PL/pgSQL functions separately.

How to fix

Replace each use of a deprecated type with a current equivalent:

• abstime → timestamp / timestamptz
• reltime → interval
• tinterval → a pair of timestamp/timestamptz values, or text
• unknown → the most appropriate type depending on the data, or text

Prohibition on using the pg_ prefix in role names

Starting with PostgreSQL kernel version 9.6, the pg_ prefix is reserved for system role and user names and is no longer available.

In Greenplum 6, the following query works without errors:

CREATE ROLE pg_example;

In Cloudberry, this query fails with an error:

CREATE ROLE pg_example;
ERROR: role name "pg_example" is reserved
DETAIL: Role names starting with "pg_" are reserved.

How to detect

SELECT rolname FROM pg_catalog.pg_roles WHERE rolname ~ '^pg_';

How to fix

ALTER ROLE pg_example RENAME TO another_example;

The fix can be applied before the upgrade.

Removal of the tsearch2 and timetravel extensions

The following extensions have been completely removed in Cloudberry:

• tsearch2;
• timetravel.

How to detect

Check whether these extensions are installed and whether they are used in your codebase:

SELECT extname, extversion
FROM pg_extension
WHERE extname IN ('tsearch2', 'timetravel');

How to fix

Stop using the removed extensions.

Support for tables created with the WITH OIDS option has been removed

In PostgreSQL kernel versions newer than 12, support for tables created with the WITH OIDS option was removed.

In Greenplum 6, the following query works without errors:

CREATE TABLE example (value text) with oids;

In Cloudberry, the same query fails with an error:

CREATE TABLE example (value text) with oids;
ERROR: syntax error at or near "oids"

How to detect

All tables defined with the WITH OIDS option can be found with the following query:

SELECT n.nspname, c.relname
FROM pg_class c
 JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relhasoids
 AND n.nspname NOT IN ('pg_catalog', 'information_schema');

How to fix

For each found table, run ALTER TABLE ... SET WITHOUT OIDS;. If OID was used as a business key, additionally add a bigserial column, or any other suitable column.

The following backward-incompatible changes were identified in the behavior of functions and operators.

Changes in function and operator behavior

Change in the behavior of set-returning functions

Starting with PostgreSQL kernel version 10, the behavior of Set Returning Functions, such as generate_series, unnest, regexp_split_to_table, json_each, and json_array_elements, changed in the SELECT list.

The difference can be seen in the following example.

Greenplum 6:

SELECT 'example' AS value, generate_series(1, 3), generate_series(1, 2);
 value | generate_series | generate_series
---------+-----------------+-----------------
 example | 1 | 1
 example | 2 | 2
 example | 3 | 1
 example | 1 | 2
 example | 2 | 1
 example | 3 | 2
(6 rows)

Cloudberry:

SELECT 'example' AS value, generate_series(1, 3), generate_series(1, 2);
 value | generate_series | generate_series
---------+-----------------+-----------------
 example | 1 | 1
 example | 2 | 2
 example | 3 |
(3 rows)

How to detect

Check the application codebase for uses of Set Returning Functions in the SELECT list.

How to fix

Replace calls to Set Returning Functions in the SELECT list with the corresponding CROSS JOIN LATERAL.

The return type of the extract() function has been changed to numeric

Starting with PostgreSQL kernel version 14, the extract() function returns a value of type numeric instead of double precision.

Example for Greenplum 6:

SELECT pg_typeof(extract(epoch FROM now()));
 pg_typeof
------------------
 double precision

Example for Cloudberry:

SELECT pg_typeof(extract(epoch FROM now()));
 pg_typeof
-----------
 numeric

If an application expects double precision, its behavior may become unpredictable.

How to detect

Check the application codebase for uses of the extract() function.

How to fix

Modify the application or the problematic queries by adding an explicit type cast with ::double precision.

The behavior of the to_number() function has been fixed for correct format handling

In Greenplum 6, the to_number() function "swallowed" extra characters:

SELECT to_number('1234', '9,999');
 to_number
-----------
 134
(1 row)

In Cloudberry, this behavior has been fixed:

SELECT to_number('1234', '9,999');
 to_number
-----------
 1234
(1 row)

How to detect

Check the application codebase for uses of the to_number() function and for possible issues caused by "swallowing" extra characters.

How to fix

Adapt the application to the changed, correct behavior of to_number().

The evaluation order of some operators has changed

In Cloudberry, compared to Greenplum 6, the evaluation order of the <=, >=, <>, IS, NOT BETWEEN, and other compound operators has changed.

For example, in Greenplum 6, the following query is invalid:

SELECT 1 <> 2 IS TRUE;
ERROR: argument of IS TRUE must be type boolean, not type integer

But in Cloudberry, the query works:

SELECT 1 <> 2 IS TRUE;
?column?
----------
t
(1 row)

How to detect

Check the application codebase for expressions with ambiguous operator evaluation order.

How to fix

Explicitly specify the operator evaluation order using parentheses.

Behavior of SIMILAR TO with ESCAPE NULL has changed

Starting with PostgreSQL kernel version 13, the behavior of the SIMILAR TO ... ESCAPE NULL construct changed.

Example for Greenplum 6:

SELECT 'abc' SIMILAR TO 'abc' ESCAPE NULL;
?column?
----------
t
(1 row)

Example for Cloudberry:

SELECT 'abc' SIMILAR TO 'abc' ESCAPE NULL;
?column?
----------
(1 row)

How to detect

Check the application codebase for uses of the ESCAPE NULL construct.

How to fix

Replace ESCAPE NULL with ESCAPE <some value>.

We also identified backward-incompatible changes in syntactic constructs.

Syntax changes and removed constructs

Explicit ROW() is required in UPDATE statements with row constructors

Starting with PostgreSQL kernel version 10, when updating a single column via a ROW constructor, the ROW keyword must be specified explicitly.

In Greenplum 6, the following query works without errors:

CREATE TABLE example (value text);
INSERT INTO example (value) VALUES ('a');
-- this query
UPDATE example SET (value) = (value || '_updated');

In Cloudberry, the same query fails with an error:

CREATE TABLE example (value text);
INSERT INTO example (value) VALUES ('a');
-- this query
UPDATE example SET (value) = (value || '_updated');
ERROR: source for a multiple-column UPDATE item must be a sub-SELECT or ROW() expression

How to detect

Check the application codebase for uses of a ROW constructor for a single column.

How to fix

Modify the problematic queries so that they either do not use a ROW constructor, for example, UPDATE ... SET col = expr, or explicitly specify the ROW keyword: UPDATE ... SET (col) = ROW(expr).

Statement-level triggers are not supported

Unlike Greenplum 6, Cloudberry does not support statement-level triggers.

The following queries work correctly in Greenplum 6:

CREATE TABLE example (
 id bigserial,
 value text
) DISTRIBUTED BY (id);
CREATE OR REPLACE FUNCTION example_trigger_func()
RETURNS TRIGGER AS $$
BEGIN
 RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER example_trigger
 AFTER INSERT OR UPDATE ON example
 FOR EACH STATEMENT
 EXECUTE PROCEDURE example_trigger_func();

But they do not work in Cloudberry:

CREATE TABLE example (
 id bigserial,
 value text
) DISTRIBUTED BY (id);
CREATE OR REPLACE FUNCTION example_trigger_func()
RETURNS TRIGGER AS $$
BEGIN
 RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER example_trigger
 AFTER INSERT OR UPDATE ON example
 FOR EACH STATEMENT
 EXECUTE PROCEDURE example_trigger_func();
ERROR: Triggers for statements are not yet supported

How to detect

All statement-level triggers can be found with the following query:

SELECT n.nspname, c.relname, tgname
FROM pg_trigger t
 JOIN pg_class c ON t.tgrelid = c.oid
 JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE tgtype & 1 = 0 AND NOT tgisinternal;

How to fix

Stop using statement-level triggers: move the logic to the application side or use row-level triggers.

Functions with EXECUTE ON must return sets (SETOF)

In Cloudberry, functions with the EXECUTE ON ALL SEGMENTS / COORDINATOR / INITPLAN attributes must now return SETOF.

A valid example for Greenplum 6:

CREATE OR REPLACE FUNCTION example_func()
RETURNS integer
AS $$
BEGIN
 RETURN 42;
END;
$$ LANGUAGE plpgsql
EXECUTE ON ALL SEGMENTS;

This stops working in Cloudberry:

CREATE OR REPLACE FUNCTION example_func()
RETURNS integer
AS $$
BEGIN
 RETURN 42;
END;
$$ LANGUAGE plpgsql
EXECUTE ON ALL SEGMENTS;
ERROR: EXECUTE ON ALL SEGMENTS is only supported for set-returning functions

How to detect

Functions with the EXECUTE ON ALL SEGMENTS / COORDINATOR / INITPLAN attributes that do not return SETOF can be found with the following query:

SELECT n.nspname, p.proname
FROM pg_proc p
 JOIN pg_language l ON l.oid = p.prolang
 JOIN pg_type t ON t.oid = p.prorettype
 JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE NOT proretset
 AND proexeclocation IN ('s', 'm', 'i')
 AND l.lanname NOT IN ('internal')
 AND t.typname NOT IN ('record');

How to fix

Recreate the problematic functions with RETURNS SETOF ... and RETURN NEXT.

Support for postfix operators has been removed

Starting with PostgreSQL kernel version 14, support for postfix operators has been removed, both system-defined and user-defined ones.

In Greenplum 6, the following query works without errors:

SELECT 5! AS factorial;
 factorial
-----------
 120

In Cloudberry, the same query fails with an error:

SELECT 5! AS factorial;
ERROR: syntax error at or near "AS"
LINE 1: SELECT 5! AS factorial;

How to detect

In Greenplum 6, there is only one system postfix operator: !. However, user-defined operators may also exist. The full list of postfix operators can be obtained with the following query:

SELECT o.oid, n.nspname, o.oprname, o.oid < 16384 AS is_system
FROM pg_operator o
 JOIN pg_namespace n ON o.oprnamespace = n.oid
WHERE o.oprright = 0;

How to fix

• Replace uses of the ! operator with an explicit call to the factorial(...) function.
• Replace user-defined postfix operators with prefix operators or with explicit calls to the corresponding functions.

A large block of backward-incompatible changes related to partitioning.

Changes in partitioning mechanisms

Requirement to include all partition keys in unique indexes

Unlike Greenplum 6, in Cloudberry a unique index must include all partition keys.

Example of a query that works in Greenplum 6:

CREATE TABLE sales (
 id int NOT NULL,
 region text,
 amount decimal(10, 2)
)
 DISTRIBUTED BY (id)
 PARTITION BY LIST (region)
 (
 VALUES ('Asia'),
 VALUES ('Europe')
 );
INSERT INTO sales (id, region, amount)
VALUES
 (1, 'Asia', 100.00),
 (1, 'Europe', 150.00),
 (2, 'Asia', 200.00);
CREATE UNIQUE INDEX sales_id_idx ON sales (id);

The same query does not work in Cloudberry:

CREATE TABLE sales (
 id int NOT NULL,
 region text,
 amount decimal(10, 2)
)
 DISTRIBUTED BY (id)
 PARTITION BY LIST (region)
 (
 VALUES ('Asia'),
 VALUES ('Europe')
 );
INSERT INTO sales (id, region, amount)
VALUES
 (1, 'Asia', 100.00),
 (1, 'Europe', 150.00),
 (2, 'Asia', 200.00);
CREATE UNIQUE INDEX sales_id_idx ON sales (id);
ERROR: unique constraint on partitioned table must include all partitioning columns
DETAIL: UNIQUE constraint on table "sales" lacks column "region" which is part of the partition key.

How to detect

All unique indexes that do not include all partition keys can be found with the following query:

WITH partitions AS (
SELECT DISTINCT n.nspname, c.relname, c.oid, p.paratts
FROM pg_partition p
 JOIN pg_class c ON p.parrelid = c.oid
 JOIN pg_namespace n ON n.oid = c.relnamespace
)
SELECT p.nspname, p.relname, ir.relname AS indexname
FROM pg_index i
 JOIN partitions p ON p.oid = i.indrelid
 JOIN pg_class ir ON ir.oid = i.indexrelid
WHERE (i.indisunique OR i.indisprimary)
 AND NOT (p.paratts <@ i.indkey);

How to fix

Recreate the found indexes, adding the partition keys.

Limitation on list partitioning by multiple columns

In Cloudberry, list partitioning by multiple columns is implemented through subpartitioning: the standard Cloudberry syntax assumes one column or expression at each level.

This limitation is related to the fact that list-based partitioning by multiple columns, for example PARTITION BY LIST (col1, col2), often makes it impossible to use the GPORCA optimizer.

The following example works in Greenplum 6:

CREATE TABLE example (a int, b int, c int)
 PARTITION BY LIST (b, c)
 (
 DEFAULT PARTITION other
 );

But it does not work in Cloudberry:

CREATE TABLE example (a int, b int, c int)
 PARTITION BY LIST (b, c)
 (
 DEFAULT PARTITION other
 );
ERROR: cannot use "list" partition strategy with more than one column

How to detect

Tables with list-based partitioning by multiple columns can be found with the following query:

SELECT n.nspname, c.relname
FROM pg_partition p
 JOIN pg_class c ON p.parrelid = c.oid
 JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE parkind = 'l'
 AND parnatts > 1;

How to fix

In Cloudberry, subpartitioning should be used for this:

CREATE TABLE example (a int, b int, c int)
PARTITION BY LIST (b)
 SUBPARTITION BY LIST (c)
 SUBPARTITION TEMPLATE (
 DEFAULT SUBPARTITION other_c
 )
 (
 DEFAULT PARTITION other
 );

Support for FOR (RANK(...)) in partition DDL has been removed

Cloudberry does not support the FOR (RANK(...)) construct when working with partitions.

Working example for Greenplum 6:

CREATE TABLE sales (
 id int,
 date date,
 amount decimal(10, 2)
)
WITH (appendoptimized = true, compresstype = zstd, compresslevel = 1)
DISTRIBUTED BY (id)
PARTITION BY RANGE (date) (
 START ('2026年01月01日') END ('2027年01月01日') EVERY (INTERVAL '1 month')
);
ALTER TABLE sales
 ALTER PARTITION FOR (RANK(2))
 TRUNCATE PARTITION FOR (RANK(2));

Does not work in Cloudberry:

CREATE TABLE sales (
 id int,
 date date,
 amount decimal(10, 2)
)
WITH (appendoptimized = true, compresstype = zstd, compresslevel = 1)
DISTRIBUTED BY (id)
PARTITION BY RANGE (date) (
 START ('2026年01月01日') END ('2027年01月01日') EVERY (INTERVAL '1 month')
);
ALTER TABLE sales
 ALTER PARTITION FOR (RANK(2))
 TRUNCATE PARTITION FOR (RANK(2));
ERROR: addressing partition by RANK is no longer supported
HINT: Use partition name or FOR (<partition key value>) instead.

How to detect

Check the application codebase and scripts for uses of the FOR (RANK(...)) construct.

How to fix

Replace FOR (RANK(...)) with FOR (VALUE ...) or FOR VALUES.

Inheritance of storage parameters for AO tables has changed

If an AO parameter is not explicitly specified for a partition, the behavior differs:

• in Greenplum 6, the default value is applied;
• in Cloudberry, the parameter is inherited from the parent table.

The following query works in Greenplum 6:

CREATE TABLE sales
(
 id int,
 date date,
 region text,
 amount decimal(10, 2)
)
 WITH (appendoptimized = true, compresstype = zstd, compresslevel = 1)
 DISTRIBUTED BY (id)
 PARTITION BY LIST (region)
 (
 PARTITION asia VALUES ('Asia')
 WITH (appendoptimized = true, compresslevel = 0),
 PARTITION europe VALUES ('Europe')
 );

But it does not work in Cloudberry:

CREATE TABLE sales
(
 id int,
 date date,
 region text,
 amount decimal(10, 2)
)
 WITH (appendoptimized = true, compresstype = zstd, compresslevel = 1)
 DISTRIBUTED BY (id)
 PARTITION BY LIST (region)
 (
 PARTITION asia VALUES ('Asia')
 WITH (appendoptimized = true, compresslevel = 0),
 PARTITION europe VALUES ('Europe')
 );
ERROR: compresstype "zstd" can't be used with compresslevel 0

How to detect

Partitions created without explicit options can be found with the following query:

SELECT
 schemaname,
 tablename,
 partitionschemaname,
 partitiontablename,
 par.reloptions AS parent_options,
 c.reloptions AS child_options,
 po AS parent_option
FROM pg_partitions p
 JOIN pg_class par ON par.oid = format('%s.%s', schemaname, tablename)::regclass
 JOIN pg_class c ON c.oid = format('%s.%s', partitionschemaname, partitiontablename)::regclass
 JOIN unnest(par.reloptions) po ON TRUE
 LEFT JOIN unnest(c.reloptions) co ON split_part(po, '=', 1) = split_part(co, '=', 1)
WHERE co IS NULL;

How to fix

Create a copy of the problematic partition with explicitly specified options, move the data into it, and replace the original partition with the updated one.

Limitations on EXCLUSIVE / INCLUSIVE boundaries for float / numeric / text types

Unlike Greenplum 6, in Cloudberry the START EXCLUSIVE / END INCLUSIVE constructs work only for types with the + operator, such as integer and timestamp, but not for float, numeric, or text.

Correct example for Greenplum 6:

CREATE TABLE sales
(
 id int,
 date date,
 amount decimal(10, 2)
)
 WITH (appendoptimized = true)
 DISTRIBUTED BY (id)
 PARTITION BY RANGE (amount)
 (
 START (0) EXCLUSIVE
 END (1000)
 EVERY (250)
 );

Does not work in Cloudberry:

CREATE TABLE sales
(
 id int,
 date date,
 amount decimal(10, 2)
)
 WITH (appendoptimized = true)
 DISTRIBUTED BY (id)
 PARTITION BY RANGE (amount)
 (
 START (0) EXCLUSIVE
 END (1000)
 EVERY (250)
 );
ERROR: START EXCLUSIVE not supported for partition key data type: numeric
HINT: Specify an inclusive START value and remove the EXCLUSIVE keyword

How to detect

Tables with START EXCLUSIVE / END INCLUSIVE on unsupported types can be found with the following query:

SELECT n.nspname, c.relname, t.typname, pr.parchildrelid::regclass
FROM pg_partition p
 JOIN pg_partition_rule pr ON p.oid = pr.paroid
 JOIN pg_class c ON p.parrelid = c.oid
 JOIN pg_namespace n ON n.oid = c.relnamespace
 JOIN pg_attribute a ON a.attrelid = c.oid AND a.attnum = ANY(p.paratts)
 JOIN pg_type t ON t.oid = a.atttypid
WHERE t.typname IN ('text', 'float8', 'float4', 'numeric')
 AND (NOT pr.parrangestartincl OR pr.parrangeendincl);

How to fix

Recreate the problematic tables without EXCLUSIVE / INCLUSIVE on these types.

The level system for partitioned tables has changed

In Greenplum 6, immediate child partitions have pg_partition.parlevel = 0. In Cloudberry, immediate child partitions have pg_partition_tree().level = 1. The pg_partition view has been removed in Cloudberry, so the pg_partition_tree() function should be used instead.

This change may break queries that rely on partition levels.

How to detect

Check the application codebase and scripts for uses of partition levels.

How to fix

Adapt the application codebase and scripts to account for the changed partition levels and the removal of the pg_partition view. Instead of pg_partition, use the pg_partition_tree() function or the pg_partitioned_table view.

Partition constraints have been moved out of pg_constraint

In Greenplum 6, partition boundaries are displayed as CHECK CONSTRAINT entries in the pg_constraint system view.

In Cloudberry, partition boundaries are no longer CHECK CONSTRAINT entries and are not displayed in pg_constraint. Similar information can now be obtained from gp_toolkit.gp_partitions.

How to detect

Check the application codebase and scripts for uses of pg_constraint to work with partition constraints.

How to fix

Use the gp_toolkit.gp_partitions view instead of pg_constraint to work with partition constraints.

Most of the changes were identified by analyzing the Greenplum 6, Greenplum 7, and Cloudberry documentation, after which each change was tested.

It is likely that not all backward-incompatible changes have been identified.

My proposal is to create a page or section in the documentation where we can describe such changes and update the documentation as new ones are discovered.

I am ready to submit a PR to the documentation if there is conceptual agreement on adding such a section. It would be great if you could suggest the right place in the documentation where such a section should be created.