SQL incompatibilities between Greengage DB 6 and 7

Andrey Aksenov

This topic covers SQL-level incompatibilities between Greengage DB 6 and 7, with examples showing how to identify and resolve each issue before data migration.

Prerequisites

The examples in this topic are designed to be run on Greengage DB 6 to help you detect and resolve incompatibilities before migrating to Greengage DB 7. Each scenario demonstrates SQL that succeeds on version 6 but whose resulting objects cause errors when restored from a logical backup on version 7.

To follow the examples in this topic, connect to the Greengage DB master host using psql as described in Connect to Greengage DB via psql. Create a new database and connect to it:

CREATE DATABASE demo;
\c demo

Partitioned tables in Greengage DB allow different storage types to be defined for individual partitions. Append-optimized (AO) partitions can also specify additional storage parameters, such as compresstype and compresslevel.

In Greengage DB 6, if an AO parameter is not specified for a partition, the system default is applied.

In Greengage DB 7, if an AO parameter is not explicitly specified, it is inherited from the parent table. This change may affect dependent parameters and can result in unsupported storage configurations.

The following example defines a partitioned table where the parent table specifies compresstype = zstd, while the child partition does not define compresstype but sets compresslevel = 0:

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')
        );

Sample data

INSERT INTO sales(id, date, region, amount)
VALUES (1, '2024-01-10', 'Asia', 100.00),
       (2, '2024-01-11', 'Europe', 200.00),
       (3, '2024-01-12', 'Asia', 150.00),
       (4, '2024-01-13', 'Europe', 300.00),
       (5, '2024-01-14', 'Asia', 250.00);

In Greengage DB 6, the asia partition uses the default compresstype = none. In Greengage DB 7, the asia partition inherits compresstype = zstd from the parent table. As a result, the following error may occur when creating the table:

ERROR:  compresstype "zstd" can't be used with compresslevel 0

To identify partitioned tables where child partitions do not explicitly define storage parameters configured on the parent table, run the following query:

SELECT schemaname,
       tablename,
       partitionschemaname,
       partitiontablename,
       par.reloptions AS parent_options,
       c.reloptions   AS child_options,
       po             AS parent_option
FROM pg_catalog.pg_partitions p
         JOIN pg_catalog.pg_class par ON par.oid = format('%s.%s', schemaname, tablename)::regclass
         JOIN pg_catalog.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;

The query returns parent table options that are not defined for the corresponding child partitions. Example output:

-[ RECORD 1 ]-------+----------------------------------------------------
schemaname          | public
tablename           | sales
partitionschemaname | public
partitiontablename  | sales_1_prt_asia
parent_options      | {appendonly=true,compresstype=zstd,compresslevel=1}
child_options       | {appendonly=true,compresslevel=0}
parent_option       | compresstype=zstd

TIP

The example output is displayed in expanded table format, which makes each column appear on a separate line. You can enable this mode in psql by using the \x meta-command.

Create a copy of the affected partition with compresstype = none explicitly set:

CREATE TABLE sales_asia_copy
(
    LIKE sales_1_prt_asia INCLUDING ALL
)
    WITH (compresstype = none);

Copy data from the original partition:

INSERT INTO sales_asia_copy
SELECT *
FROM sales_1_prt_asia;

Replace the original partition with the updated copy:

ALTER TABLE sales
    EXCHANGE PARTITION asia
        WITH TABLE sales_asia_copy
        WITH VALIDATION;

Drop the temporary table:

DROP TABLE sales_asia_copy;

Starting with Greengage DB 7, multi-column list partitioning is no longer supported, which results in the following error:

ERROR: cannot use "list" partition strategy with more than one column

The following example uses a two-column partitioning key based on the region and channel columns:

CREATE TABLE sales
(
    id      INT,
    region  TEXT,
    channel TEXT,
    amount  DECIMAL(10, 2)
)
    DISTRIBUTED BY (id)
    PARTITION BY LIST (region, channel)
        (
        VALUES (('Asia', 'Online'), ('Asia', 'Retail')),
        VALUES (('Europe', 'Online'), ('Europe', 'Retail'))
        );

Sample data

INSERT INTO sales (id, region, channel, amount)
VALUES (1, 'Asia', 'Online', 100.00),
       (2, 'Asia', 'Retail', 120.00),
       (3, 'Europe', 'Online', 180.00),
       (4, 'Europe', 'Retail', 200.00),
       (5, 'Asia', 'Online', 150.00);

To find tables that use the LIST partition strategy with more than one partitioning column, use the following query:

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

Example output:

 nspname | relname
---------+---------
 public  | sales
(1 row)

Recreate the partitioned table using a structure supported in Greengage DB 7. The following example uses a single array column (region_channel) as the partition key:

CREATE TABLE sales
(
    id             INT,
    region_channel TEXT[],
    amount         DECIMAL(10, 2)
)
    DISTRIBUTED BY (id)
    PARTITION BY LIST (region_channel)
        (
        VALUES ('{Asia,Online}', '{Asia,Retail}'),
        VALUES ('{Europe,Online}', '{Europe,Retail}')
        );

Insert data using the array format:

INSERT INTO sales (id, region_channel, amount)
VALUES (1, '{Asia,Online}', 100.00),
       (2, '{Asia,Retail}', 120.00),
       (3, '{Europe,Online}', 180.00),
       (4, '{Europe,Retail}', 200.00),
       (5, '{Asia,Online}', 150.00);

In Greengage DB 7, a unique index on a partitioned table must include all partitioning columns. Omitting a partitioning column from a unique index causes the following error:

ERROR: unique constraint on partitioned table must include all partitioning keys

The following table uses the id column as the distribution key and the region column as the partition key:

CREATE TABLE sales
(
    id     INT NOT NULL,
    region TEXT,
    amount DECIMAL(10, 2)
)
    DISTRIBUTED BY (id)
    PARTITION BY LIST (region)
        (
        VALUES ('Asia'),
        VALUES ('Europe')
        );

Sample data

INSERT INTO sales (id, region, amount)
VALUES (1, 'Asia', 100.00),
       (1, 'Europe', 150.00),
       (2, 'Asia', 200.00);

The following command creates a unique index on id only, without including the partition key. It succeeds in Greengage DB 6 but fails in Greengage DB 7:

CREATE UNIQUE INDEX sales_id_idx ON sales (id);

To find unique indexes on partitioned tables that do not include all partitioning columns, run the following query:

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

Example output:

 nspname | relname |  indexname
---------+---------+--------------
 public  | sales   | sales_id_idx
(1 row)

Drop the index that does not include all partitioning columns:

DROP INDEX sales_id_idx;

Create a new index that includes all partitioning columns:

CREATE UNIQUE INDEX sales_id_region_idx ON sales (id, region);

In Greengage DB 7, range partitions do not support START EXCLUSIVE or END INCLUSIVE for columns of text and numeric types (such as float4, float8, numeric). The following error may occur:

ERROR:  START EXCLUSIVE not supported for partition key data type: numeric
HINT:  Specify an inclusive START value and remove the EXCLUSIVE keyword

or:

ERROR:  END INCLUSIVE not supported for partition key data type: numeric
HINT:  Specify an exclusive END value and remove the INCLUSIVE keyword

The following example creates a table with an EXCLUSIVE lower range bound on the amount column of type decimal:

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

To find tables that use a RANGE partition strategy with numeric or text partition keys, run the following query:

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

Example output:

 nspname | relname | typname | parchildrelid
---------+---------+---------+---------------
 public  | sales   | numeric | sales_1_prt_1
(1 row)

Recreate the table using a supported partition key type or without EXCLUSIVE bounds:

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

The following data types were removed in Greengage DB 7:

unknown
reltime
tinterval
abstime

Using these types in Greengage DB 7 causes the following errors:

ERROR: type "unknown" does not exist
ERROR: type "reltime" does not exist
ERROR: type "tinterval" does not exist
ERROR: type "abstime" does not exist

The following example creates a table with columns of these types:

CREATE TABLE user_activity_log
(
    event_id       INT,
    user_note      unknown,
    session_length reltime,
    active_window  tinterval,
    created_at     abstime
);

Sample data

INSERT INTO user_activity_log
VALUES (1, 'login from mobile', '45 minutes',
        tinterval('2024-01-10 08:00:00'::abstime, '2024-01-10 08:45:00'::abstime),
        '2024-01-10 08:45:00'),
       (2, 'password change', '5 minutes',
        tinterval('2024-01-10 09:10:00'::abstime, '2024-01-10 09:15:00'::abstime),
        '2024-01-10 09:15:00'),
       (3, 'checkout completed', '12 minutes',
        tinterval('2024-01-10 10:00:00'::abstime, '2024-01-10 10:12:00'::abstime),
        '2024-01-10 10:12:00');

Create a helper schema:

CREATE SCHEMA __ggmigrate_tmp;

Create a function that recursively finds all objects depending on the given types:

CREATE FUNCTION __ggmigrate_tmp.data_type_checks(base_oids REGTYPE[])
    RETURNS TABLE
            (
                nspname NAME,
                relname NAME,
                attname NAME
            )
AS
$$
DECLARE
    result_oids    REGTYPE[];
    dependent_oids REGTYPE[];
BEGIN
    dependent_oids = base_oids;
    result_oids = base_oids;
    WHILE array_length(dependent_oids, 1) IS NOT NULL
        LOOP
            dependent_oids := ARRAY(
                    SELECT t.oid
                    FROM (SELECT t.oid
                          FROM pg_catalog.pg_type t,
                               unnest(dependent_oids) AS x(oid)
                          WHERE typbasetype = x.oid
                            AND typtype = 'd'
                          UNION ALL
                          SELECT t.oid
                          FROM pg_catalog.pg_type t,
                               unnest(dependent_oids) AS x(oid)
                          WHERE typelem = x.oid
                            AND typtype = 'b'
                          UNION ALL
                          SELECT t.oid
                          FROM pg_catalog.pg_type t
                                   JOIN pg_catalog.pg_class c ON t.oid = c.reltype
                                   JOIN pg_catalog.pg_attribute a ON c.oid = a.attrelid
                          WHERE t.typtype = 'c'
                            AND NOT a.attisdropped
                            AND a.atttypid = ANY (dependent_oids)
                          UNION ALL
                          SELECT t.oid
                          FROM pg_catalog.pg_type t,
                               pg_catalog.pg_range r,
                               unnest(dependent_oids) AS x(oid)
                          WHERE t.typtype = 'r'
                            AND r.rngtypid = t.oid
                            AND r.rngsubtype = x.oid) AS t
                              );
            result_oids := result_oids || dependent_oids;
        END LOOP;
    RETURN QUERY
        SELECT n.nspname, c.relname, a.attname
        FROM pg_catalog.pg_class c
                 JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
                 JOIN pg_catalog.pg_attribute a ON c.oid = a.attrelid
        WHERE NOT a.attisdropped
          AND a.atttypid = ANY (result_oids)
          AND c.relkind IN ('r', 'm', 'i')
          AND n.nspname !~ '^pg_temp_'
          AND n.nspname !~ '^pg_toast_temp_'
          AND n.nspname NOT IN ('pg_catalog', 'information_schema');
END;
$$ LANGUAGE plpgsql;

Find all tables with columns that use these removed types:

SELECT *
FROM __ggmigrate_tmp.data_type_checks(
        ARRAY [
            'unknown'::regtype,
            'reltime'::regtype,
            'tinterval'::regtype,
            'abstime'::regtype
            ]
     );

Example output:

 nspname |      relname      |    attname
---------+-------------------+----------------
 public  | user_activity_log | user_note
 public  | user_activity_log | session_length
 public  | user_activity_log | active_window
 public  | user_activity_log | created_at
(4 rows)

Convert each deprecated column to a supported type:

ALTER TABLE user_activity_log
    ALTER COLUMN user_note TYPE text;
ALTER TABLE user_activity_log
    ALTER COLUMN session_length TYPE interval;
ALTER TABLE user_activity_log
    ALTER COLUMN active_window TYPE text;
ALTER TABLE user_activity_log
    ALTER COLUMN created_at TYPE timestamp;

The following extensions were removed in Greengage DB 7. Their functionality is now built into the core:

gp_parallel_retrieve_cursor
gp_array_agg
gp_percentile_agg

Attempting to install them in Greengage DB 7 causes the following errors:

ERROR:  could not open extension control file "/usr/local/gpdb/share/postgresql/extension/gp_parallel_retrieve_cursor.control": No such file or directory
ERROR:  could not open extension control file "/usr/local/gpdb/share/postgresql/extension/gp_array_agg.control": No such file or directory
ERROR:  could not open extension control file "/usr/local/gpdb/share/postgresql/extension/gp_percentile_agg.control": No such file or directory

The following example installs these extensions in Greengage DB 6, where they are still available:

CREATE EXTENSION gp_parallel_retrieve_cursor;
CREATE EXTENSION gp_array_agg;
CREATE EXTENSION gp_percentile_agg;

Check which of the removed extensions are installed:

SELECT extname,
       extversion
FROM pg_catalog.pg_extension
WHERE extname IN ('gp_parallel_retrieve_cursor', 'gp_array_agg', 'gp_percentile_agg');

Example output:

           extname           | extversion
-----------------------------+------------
 gp_parallel_retrieve_cursor | 1.0
 gp_array_agg                | 1.0.0
 gp_percentile_agg           | 1.0.0
(3 rows)

Drop the extensions before migration:

DROP EXTENSION IF EXISTS gp_parallel_retrieve_cursor;
DROP EXTENSION IF EXISTS gp_array_agg;
DROP EXTENSION IF EXISTS gp_percentile_agg;

In Greengage DB 6, functions could be implemented in either Python 2 (plpython2u) or Python 3 (plpython3u).

Starting with Greengage DB 7, only Python 3 is supported for function definitions. Attempting to use Python 2 causes the following error:

ERROR:  could not open extension control file "/usr/local/gpdb/share/postgresql/extension/plpython2u.control": No such file or directory

The following example registers the plpython2u extension:

CREATE EXTENSION plpython2u;

The integer_division_demo() example function uses the / operator, which performs integer division in Python 2 when both operands are integers:

CREATE OR REPLACE FUNCTION integer_division_demo(a int, b int)
    RETURNS text
AS
$$
    result = a / b
    return str(result)
$$ LANGUAGE plpython2u;

In Python 3, / always returns a float — use // for integer division instead.

Call the function:

SELECT integer_division_demo(10, 3);

Example output:

 integer_division_demo
-----------------------
 3
(1 row)

To find plpython2u functions and view their definitions, run the following query:

SELECT n.nspname,
       p.oid,
       p.proname,
       p.prosrc
FROM pg_catalog.pg_proc p
         JOIN pg_namespace n ON n.oid = p.pronamespace
         JOIN pg_language l ON l.oid = p.prolang
         JOIN pg_pltemplate t ON t.tmplname = l.lanname
WHERE t.tmpllibrary = '$libdir/plpython2';

Example output:

 nspname |  oid  |        proname         |         prosrc
---------+-------+------------------------+------------------------
 public  | 16461 | integer_division_demo  |                       +
         |       |                        |     result = a / b    +
         |       |                        |     return str(result)+

You can get the definition of a function using pg_get_functiondef(func_oid).

IMPORTANT

Before dropping the plpython2u extension, make sure to export and save the definitions of all functions implemented in Python 2.

Drop the plpython2u extension and all dependent functions:

DROP EXTENSION plpython2u CASCADE;

Reconnect to the database, because all PL/Python functions within a single Greengage DB session must use the same PL/Python variant (plpython2u or plpython3u):

\c demo gpadmin

CREATE EXTENSION plpython3u;

Recreate the function using Python 3, replacing / with // for integer division:

CREATE OR REPLACE FUNCTION integer_division_demo(a int, b int)
    RETURNS text
AS
$$
    result = a // b
    return str(result)
$$ LANGUAGE plpython3u;

Verify the result:

SELECT integer_division_demo(10, 3);

Example output:

 integer_division_demo
-----------------------
 3
(1 row)

Starting with Greengage DB 7, functions defined with the EXECUTE ON attribute must return SETOF. Using a scalar return type results in the following error:

ERROR: EXECUTE ON ALL SEGMENTS is only supported for set-returning functions

The example below demonstrates a function with EXECUTE ON ALL SEGMENTS that returns a scalar value. This definition works in Greengage DB 6 but fails in Greengage DB 7:

CREATE FUNCTION get_segment_ip()
    RETURNS TEXT
AS
$$
BEGIN
    RETURN inet_server_addr();
END;
$$
    LANGUAGE plpgsql
    NO SQL
    EXECUTE ON ALL SEGMENTS;

In Greengage DB 6, calling the function produces one row per segment execution:

SELECT get_segment_ip();

Example output:

 get_segment_ip
----------------
 192.168.1.40
 192.168.1.30
 192.168.1.40
 192.168.1.30
(4 rows)

To identify user-defined functions that specify the EXECUTE ON attribute but do not return a set (SETOF), run the following query:

SELECT n.nspname,
       p.proname
FROM pg_catalog.pg_proc p
         JOIN pg_catalog.pg_language l ON l.oid = p.prolang
         JOIN pg_catalog.pg_type t ON t.oid = p.prorettype
         JOIN pg_catalog.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');

Example output:

 nspname |    proname
---------+----------------
 public  | get_segment_ip
(1 row)

Since the return type cannot be altered in-place, first drop the existing function:

DROP FUNCTION get_segment_ip();

Then, recreate it with RETURNS SETOF and RETURN NEXT:

CREATE FUNCTION get_segment_ip()
    RETURNS SETOF TEXT
AS
$$
BEGIN
    RETURN NEXT inet_server_addr();
END;
$$
    LANGUAGE plpgsql
    NO SQL
    EXECUTE ON ALL SEGMENTS;

Verify the function execution:

SELECT get_segment_ip();

Example output:

 get_segment_ip
----------------
 192.168.1.30
 192.168.1.30
 192.168.1.40
 192.168.1.40
(4 rows)

Prerequisites

Before working with statement-level triggers, you need an external PostgreSQL database to act as the foreign server:

Use the following examples/foreign_tables/script_pg.sql script to create the sample database, tables, and user:

DROP DATABASE IF EXISTS customers;
CREATE DATABASE customers;
\c customers
CREATE TABLE customers
(
    id INTEGER,
    name VARCHAR(50),
    email VARCHAR(100),
    address VARCHAR(255)
);
INSERT INTO customers (id, name, email, address)
VALUES (1,'John Doe','john.doe@example.com','123 Elm Street'),
       (2,'Jane Smith','jane.smith@example.com','456 Oak Street'),
       (3,'Bob Brown','bob.brown@example.com','789 Pine Street'),
       (4,'Rob Stuart','rob.stuart@example.com','119 Willow Street');
CREATE USER "user" WITH PASSWORD 'password';
GRANT ALL ON TABLE customers TO "user";

From Greengage DB, execute the script to configure access to the external PostgreSQL instance:
```
\! PGPASSWORD=password psql -h postgres -U postgres -f examples/foreign_tables/script_pg.sql
```
Install the postgres_fdw extension, which allows access to foreign tables:
```
CREATE EXTENSION postgres_fdw;
```

Create a foreign server pointing to the PostgreSQL instance:

CREATE SERVER postgres
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host 'postgres', port '5432', dbname 'customers');

Create a user mapping for authentication:

CREATE USER MAPPING FOR gpadmin
    SERVER postgres
    OPTIONS (user 'user', password 'password');

In Greengage DB 7, statement-level triggers are not supported. The following error may occur:

ERROR: Triggers for statements are not yet supported

Create a foreign table that references the PostgreSQL table:

CREATE FOREIGN TABLE customers
(
    id      INTEGER,
    name    VARCHAR(50),
    email   VARCHAR(100),
    address VARCHAR(255)
) SERVER postgres;

Define a trigger function that executes an action, such as raising a notice:

CREATE OR REPLACE FUNCTION raise_notice_after_insert()
    RETURNS TRIGGER
AS
$$
BEGIN
    RAISE NOTICE 'Trigger fired';
    RETURN NULL;
END
$$ LANGUAGE plpgsql;

Attach a FOR EACH STATEMENT trigger to the table. This type of trigger executes once per SQL statement, regardless of how many rows are affected:

CREATE TRIGGER raise_notice_for_each_statement
    AFTER INSERT
    ON customers
    FOR EACH STATEMENT
    EXECUTE PROCEDURE raise_notice_after_insert();

Insert multiple rows. In Greengage DB 6, the statement-level trigger fires only once for the entire insert:

INSERT INTO customers (id, name, email, address)
VALUES (5, 'Alice Johnson', 'alice.johnson@example.com', '221 Maple Drive'),
       (6, 'David Lee', 'david.lee@example.com', '333 Birch Lane'),
       (7, 'Emily Wilson', 'emily.wilson@example.com', '444 Cedar Court');

Expected output:

NOTICE:  Trigger fired
INSERT 0 3

To confirm the statement-level trigger exists, query the system catalog:

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

Example output:

 nspname |  relname  |             tgname
---------+-----------+---------------------------------
 public  | customers | raise_notice_for_each_statement
(1 row)

Remove the previously inserted rows:

DELETE
FROM customers
WHERE id IN (5, 6, 7);

Drop the existing statement-level trigger:

DROP TRIGGER raise_notice_for_each_statement ON customers;

Recreate the trigger as FOR EACH ROW so it executes for every affected row:

CREATE TRIGGER raise_notice_for_each_row
    AFTER INSERT
    ON customers
    FOR EACH ROW
    EXECUTE PROCEDURE raise_notice_after_insert();

Reinsert data. Now, the trigger fires individually for each row inserted:

INSERT INTO customers (id, name, email, address)
VALUES (5, 'Alice Johnson', 'alice.johnson@example.com', '221 Maple Drive'),
       (6, 'David Lee', 'david.lee@example.com', '333 Birch Lane'),
       (7, 'Emily Wilson', 'emily.wilson@example.com', '444 Cedar Court');

Expected output:

NOTICE:  Trigger fired
NOTICE:  Trigger fired
NOTICE:  Trigger fired
INSERT 0 3

In Greengage DB 7, a number of system catalog objects have been removed or changed, including tables, views, and certain columns. Below is a set of system tables and views that have been removed:

gp_toolkit.gp_size_of_partition_and_indexes_disk
gp_toolkit.__gp_user_data_tables
pg_catalog.pg_partition
pg_catalog.pg_partition_columns
pg_catalog.pg_partition_encoding
pg_catalog.pg_partition_rule
pg_catalog.pg_partitions
pg_catalog.pg_partition_templates
pg_catalog.pg_stat_partition_operations

Any user-defined objects (including views and functions) that reference these system catalog items will fail after migration. During a logical backup restore, the creation of views that reference missing objects will fail with an error.

For example, the following view references the removed pg_partitions view:

CREATE VIEW table_partitions AS
SELECT schemaname,
       tablename,
       partitionname
FROM pg_partitions;

To get a list of user-defined views that reference system objects in their definitions, execute the following command:

WITH RECURSIVE system_objects AS
    (SELECT
         n.nspname,
         c.relname,
         format('%s.%s', n.nspname, c.relname) AS system_object
     FROM pg_class c
         JOIN pg_namespace n
             ON c.relnamespace = n.oid WHERE nspname IN ('pg_catalog', 'gp_toolkit')
    UNION ALL
    SELECT
        v.schemaname,
        v.viewname,
        c.system_object
    FROM pg_views v
        JOIN system_objects c ON v.definition ~* ('[^\.]\y' || c.relname || '\y')
    WHERE schemaname NOT IN ('pg_catalog', 'information_schema', 'gp_toolkit'))
SELECT *
FROM system_objects
WHERE nspname NOT IN ('pg_catalog', 'information_schema', 'gp_toolkit');

Example output:

 nspname |     relname      |      system_object
---------+------------------+--------------------------
 public  | table_partitions | pg_catalog.pg_partitions
(1 row)

Drop the affected view:

DROP VIEW table_partitions;

After reviewing the updated system table structure in Greengage DB 7, recreate the view if needed.

In Greengage DB 7, several resource group parameters have changed, for example:

CPU_RATE_LIMIT was renamed to CPU_MAX_PERCENT.
MEMORY_SHARED_QUOTA, MEMORY_SPILL_RATIO, and MEMORY_AUDITOR were removed.

The example below creates a resource group that uses Greengage DB 6 parameters:

CREATE RESOURCE GROUP rg_analysts WITH (
    CPU_RATE_LIMIT = 20,
    MEMORY_LIMIT = 20,
    CONCURRENCY = 30
    );

Run gpbackup with the --without-globals option when backing up data of the old cluster. Using --without-globals ensures that global objects — such as resource groups or tablespace definitions — are not included in the backup. These global objects must be pre-created manually on the new cluster before restoring the data.

Upgrade a cluster

Databases

SQL incompatibilities between Greengage DB 6 and 7

Partitioning: parameters of append-optimized tables

Scenario

Detect

Resolve

Partitioning: multi-column list partitioning

Scenario

Detect

Resolve

Partitioning: keys for unique constraints

Scenario

Detect

Resolve

Partitioning: EXCLUSIVE/INCLUSIVE modifiers for numeric and text ranges

Scenario

Detect

Resolve

Removed data types

Scenario

Detect

Resolve

Removed extensions

Scenario

Detect

Resolve

Removed Python 2 support

Scenario

Detect

Resolve

Return values of EXECUTE ON functions

Scenario

Detect

Resolve

Statement-level triggers

Scenario

Detect

Resolve

User-defined objects depending on removed system catalog objects

Scenario

Detect

Resolve

Resource groups

Scenario

Resolve