AnalyticDB:Compatibility comparisons between AnalyticDB for PostgreSQL V4.3 and V6.0

Pre-upgrade checklist

Review the following items before upgrading. Each links to the relevant section for details and remediation steps.

• [ ] Query optimizers — The default optimizer changes from Legacy to ORCA. Test your queries.

• [ ] Escape characters — Backslashes in strings no longer act as escape characters.

• [ ] Data type conversion — V6.0 does not implicitly convert numeric types to TEXT. Rewrite affected SQL.

• [ ] External table error logs — The INTO error_table clause is removed. Switch to built-in functions.

• [ ] Data types — Five data types can no longer be used as distribution keys.

• [ ] Keywords — Some keywords changed categories. Check for conflicts with object names.

• [ ] System tables — Six system table columns are renamed or removed. Update code that references them.

• [ ] Built-in function parameters — int4_avg_accum and string_agg have changed signatures.

Query optimizers

Both versions support Legacy and ORCA query optimizers. The default switches from Legacy to ORCA in V6.0. For details on choosing an optimizer, see Choose a query optimizer.

Action: Test your workloads with the ORCA optimizer before upgrading. If you encounter issues, switch back to Legacy at the session level.

Escape characters

In V6.0, backslashes (\) in strings are treated as literal characters, not escape characters (standard_conforming_strings = on by default).

To re-enable backslash escaping at the session level:

SET standard_conforming_strings = off;

This setting applies to the current session only. To configure it at the instance level, submit a ticket to request support.

Action: Audit your SQL for backslash escape sequences and update them to use SQL-standard escapes (for example, '' for a literal single quote).

Data type conversion

V6.0 removes two implicit conversions that V4.3 supported:

1. `YYYYMMDDHH24MISS` strings are not auto-converted to timestamps.

Use to_timestamp or to_char explicitly:

-- Convert a YYYYMMDDHH24MISS string to a timestamp
SELECT to_timestamp('20240101123045', 'YYYYMMDDHH24MISS');

2. Numeric types are not implicitly converted to TEXT.

Add wrapper functions to restore compatibility for affected SQL statements:

CREATE OR REPLACE FUNCTION substr(numeric, integer, integer) RETURNS text AS $$
  SELECT substr($1::text, $2, $3);
$$ LANGUAGE sql IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION pg_catalog.btrim(str numeric) RETURNS text AS $$
  RETURN $_[0];
$$ LANGUAGE plperl IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION to_date(timestamp, text) RETURNS date AS $$
  SELECT to_date($1::text, $2);
$$ LANGUAGE sql IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION to_date(integer, text) RETURNS date AS $$
  SELECT to_date($1::text, $2);
$$ LANGUAGE sql IMMUTABLE STRICT;

Action: Identify SQL statements and functions that rely on implicit numeric-to-TEXT conversion. Add the wrapper functions above or rewrite the SQL to use explicit casts (value::text).

Error logs of external tables

The INTO error_table clause is removed from CREATE EXTERNAL TABLE and COPY statements in V6.0.

Use the following built-in functions instead:

-- Read the error log for an external table
SELECT * FROM gp_read_error_log('<external_table_name>');

-- Clear the error log for an external table
SELECT gp_truncate_error_log('<external_table_name>');

Action: Remove INTO error_table from all CREATE EXTERNAL TABLE and COPY statements. Update error-handling logic to use gp_read_error_log and gp_truncate_error_log.

Data types

Two storage-level changes affect disk space after upgrading:

• The NUMERIC storage format changes, which affects disk space for columns of this type.

• The MONEY data type changes from 32-bit to 64-bit, which also affects disk space.

In addition, the following data types can no longer be used as distribution keys in V6.0:

• abstime

• reltime

• tinterval

• money

• anyarray

Action: If any table uses one of these types as its distribution key, redefine the distribution key before upgrading. For NUMERIC and MONEY columns, account for the increased disk space after the upgrade.

Keywords

V6.0 changes the category of several keywords. Object names (tables, views, functions, indexes, columns, types) that match newly reserved keywords cause errors.

To view all keywords and their categories in your current instance:

SELECT * FROM pg_get_keywords();

-- Find tables and columns whose names conflict with newly reserved keywords
SELECT n.nspname AS schema, c.relname AS table_name, a.attname AS column_name
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
JOIN pg_catalog.pg_attribute a ON a.attrelid = c.oid
WHERE c.relkind IN ('r', 'v', 'm')
  AND NOT a.attisdropped
  AND a.attname IN ('collation', 'concurrently', 'lateral', 'reindex', 'variadic')
ORDER BY schema, table_name, column_name;

System tables

V6.0 renames and removes several system table columns. Update any application code or queries that reference the following:

Action: Search your codebase for references to the V4.3 column names in the left column and update them to the V6.0 equivalents.

Built-in function parameters

Two built-in functions have changed signatures in V6.0:

Action: Update all calls to string_agg to include a delimiter argument. If int4_avg_accum is referenced in custom aggregates, update the function signature accordingly.

New capabilities in V6.0

V6.0 adds the following capabilities that were not available in V4.3:

Post-upgrade steps

After upgrading, run ANALYZE on all databases to regenerate statistics. The query optimizer relies on current statistics, which are not carried over during a major version upgrade.

ANALYZE VERBOSE;

For help with the upgrade process, see the AnalyticDB for PostgreSQL upgrade guide, or submit a ticket for technical support.