GitHub user vovik0134 added a comment to the discussion: [Ideas] Description of
backward-incompatible changes between Greenplum 6 and Cloudberry in the
documentation.
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:
```sql
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:
```sql
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:
```sql
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:
```sql
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:
```sql
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:
```sql
CREATE OR REPLACE FUNCTION example_func()
RETURNS integer
AS $$
BEGIN
RETURN 42;
END;
$$ LANGUAGE plpgsql
EXECUTE ON ALL SEGMENTS;
```
This stops working in Cloudberry:
```sql
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:
```sql
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:
```sql
SELECT 5! AS factorial;
factorial
-----------
120
```
In Cloudberry, the same query fails with an error:
```sql
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:
```sql
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.
GitHub link:
https://github.com/apache/cloudberry/discussions/1816#discussioncomment-17260286
----
This is an automatically sent email for [email protected].
To unsubscribe, please send an email to: [email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
