GitHub user vovik0134 added a comment to the discussion: [Ideas] Description of
backward-incompatible changes between Greenplum 6 and Cloudberry in the
documentation.
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`:
```sql
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:
```sql
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:
```sql
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:
```sql
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:
```sql
CREATE ROLE pg_example;
```
In Cloudberry, this query fails with an error:
```sql
CREATE ROLE pg_example;
ERROR: role name "pg_example" is reserved
DETAIL: Role names starting with "pg_" are reserved.
```
**How to detect**
```sql
SELECT rolname FROM pg_catalog.pg_roles WHERE rolname ~ '^pg_';
```
**How to fix**
```sql
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:
```sql
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:
```sql
CREATE TABLE example (value text) with oids;
```
In Cloudberry, the same query fails with an error:
```sql
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:
```sql
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.
GitHub link:
https://github.com/apache/cloudberry/discussions/1816#discussioncomment-17260246
----
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]
