Несовместимости SQL между Greengage DB 6 и 7

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

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;

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

INSERT INTO sales_asia_copy
SELECT *
FROM sales_1_prt_asia;

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

DROP TABLE sales_asia_copy;

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

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;

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

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

CREATE UNIQUE INDEX sales_id_idx ON sales (id);

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

DROP INDEX sales_id_idx;

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

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

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

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

CREATE TABLE customer_activity
(
    customer_id      INT,
    activity_date    DATE,
    activity_time    TIMESTAMP,
    region_id        INT,
    customer_segment VARCHAR(20),
    channel          VARCHAR(20)
)
    WITH (appendoptimized = true, orientation = column)
    PARTITION BY RANGE (activity_date)
        SUBPARTITION BY LIST (region_id)
            SUBPARTITION TEMPLATE
            (
            SUBPARTITION europe VALUES (1,2),
            SUBPARTITION asia VALUES (3,4)
            )
        SUBPARTITION BY LIST (customer_segment)
            SUBPARTITION TEMPLATE
            (
            SUBPARTITION retail VALUES ('Retail'),
            SUBPARTITION enterprise VALUES ('Enterprise')
            )
        SUBPARTITION BY LIST (channel)
            SUBPARTITION TEMPLATE
            (
            SUBPARTITION webchannel VALUES ('Web'),
            SUBPARTITION mobileapp VALUES ('Mobile')
            )
        (
        START (DATE '2020-01-01')
            END (DATE '2023-01-01') EVERY (INTERVAL '1 year')
        );

ALTER TABLE public.customer_activity ALTER PARTITION FOR (RANK(1)) ALTER PARTITION europe
SET SUBPARTITION TEMPLATE
          (
          SUBPARTITION webchannel VALUES('Web') WITH (tablename='customer_activity'),
          SUBPARTITION mobileapp VALUES('Mobile') WITH (tablename='customer_activity')
          );

ALTER TABLE public.customer_activity ALTER PARTITION FOR (RANK(1))
SET SUBPARTITION TEMPLATE
          (
          SUBPARTITION retail VALUES('Retail') WITH (tablename='customer_activity') ,
          SUBPARTITION enterprise VALUES('Enterprise') WITH (tablename='customer_activity')
          );

ALTER TABLE public.customer_activity
SET SUBPARTITION TEMPLATE
          (
          SUBPARTITION europe VALUES(1, 2) WITH (tablename='customer_activity') ,
          SUBPARTITION asia VALUES(3, 4) WITH (tablename='customer_activity')
          );

SELECT schemaname,
       tablename,
       partitionname,
       partitiontype,
       partitionlevel,
       partitionposition,
       partitionlistvalues,
       partitionboundary
FROM pg_partition_templates
WHERE partitionlevel > 1;

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

CREATE SCHEMA __ggmigrate_tmp;

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;

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

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;

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

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

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

CREATE EXTENSION plpython2u;

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

SELECT integer_division_demo(10, 3);

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

DROP EXTENSION plpython2u CASCADE;

\c demo gpadmin

CREATE EXTENSION plpython3u;

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

SELECT integer_division_demo(10, 3);

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

SELECT get_segment_ip();

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

DROP FUNCTION get_segment_ip();

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

SELECT get_segment_ip();

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

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

CREATE TRIGGER raise_notice_for_each_statement
    AFTER INSERT
    ON customers
    FOR EACH STATEMENT
    EXECUTE PROCEDURE raise_notice_after_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');

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;

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

DROP TRIGGER raise_notice_for_each_statement ON customers;

CREATE TRIGGER raise_notice_for_each_row
    AFTER INSERT
    ON customers
    FOR EACH ROW
    EXECUTE PROCEDURE raise_notice_after_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');

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

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

DROP VIEW table_partitions;

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