AnalyticDB:Compatibility comparisons between AnalyticDB for PostgreSQL V6.0 and V7.0

Data types

• AnalyticDB for PostgreSQL V7.0 removes the following data types that are replaced with standard SQL types such as TIMESTAMP:

  • ABSTIME

  • REALTIME

  • TINTERVAL

  • TIMEINTERVAL

• AnalyticDB for PostgreSQL V7.0 reserves the UNKNOWN keyword, but no longer supports the UNKNOWN type.

  • AnalyticDB for PostgreSQL V7.0 does not support the UNKNOWN type as the data type of columns.

    --- If you specify UNKNOWN as the data type of columns, an error is returned.
    CREATE TABLE test(a INT, b UNKNOWN);
    ERROR:  COLUMN "b" has pseudo-type UNKNOWN

  • If you specify a string constant in the CREATE TABLE AS SELECT (CTAS) statement, AnalyticDB for PostgreSQL V7.0 no longer recognizes the data type as UNKNOWN, but automatically converts the data type into TEXT or into the data type of the nearest column based on the nearest neighbor rule.

    --- An ordinary string constant or NULL used in the CTAS statement is recognized as the UNKNOWN type in AnalyticDB for PostgreSQL V6.0 but is automatically converted into the TEXT type in AnalyticDB for PostgreSQL V7.0.
     CREATE TABLE test AS SELECT 1 AS a, NULL AS b;
    
                    TABLE "public.test"
     Column |  Type   | Collation | Nullable | Default 
    --------+---------+-----------+----------+---------
     a      | integer |           |          | 
     b      | text    |           |          | 
    Distributed randomly
    
    --- If a UNION clause is used in the CTAS statement, the UNKNOWN type is converted into the data type of the nearest column.
    CREATE TABLE test AS SELECT 1::INT UNION SELECT NULL;
    
                   TABLE "public.test"
     Column |  Type   | Collation | Nullable | Default 
    --------+---------+-----------+----------+---------
     int4   | integer |           |          | 
    Distributed randomly

    If a string constant cannot obtain the data type to be converted based on the nearest neighbor rule due to specific positions, the string constant is automatically converted into the TEXT type. In this case, a text-related error is returned.

    CREATE TABLE test(a INT);
    
    --- The string constant in the inner SELECT statement cannot find the INT type in the test table due to the nested SELECT statements.
    INSERT INTO test SELECT a FROM (SELECT '1' AS a) t;
    ERROR:  COLUMN "a" IS OF type INTEGER but expression IS OF type text 

  • If you specify invalid parameters for specific functions, an UNKNOWN type error may be returned for string constants.

    --- Input parameters of the string_agg() function support only the following types:
    string_agg(text,text)
    string_agg(bytea,bytea)
    --- If you specify a different type and a string constant as input parameters, an UNKNOWN type error may be returned.
    CREATE TABLE test(a INT, b INT);
    SELECT a, string_agg(b,',') FROM test GROUP BY a;
    ERROR:  FUNCTION string_agg(INTEGER, UNKNOWN) does NOT exist 

  Note

  AnalyticDB for PostgreSQL V6.3.11 does not support the UNKNOWN type in the same manner that AnalyticDB for PostgreSQL V7.0 supports the UNKNOWN type. AnalyticDB for PostgreSQL V6.6.2 uses the adbpg_enable_resolve_unknowns parameter to determine whether to support the UNKNOWN type. By default, the UNKNOWN type is not supported.

SQL syntax

• Comparisons of the ALTER TABLE ... ADD PRIMARY KEY statement:

  • In AnalyticDB for PostgreSQL V7.0, if you execute the ALTER TABLE ... ADD PRIMARY KEY statement to add a primary key column that has the NOT NULL property to a primary table, the NOT NULL property is added to the primary key columns of the child tables.

  • In AnalyticDB for PostgreSQL V6.0, if you execute the ALTER TABLE ... ADD PRIMARY KEY statement to add a primary key column that has the NOT NULL property to a primary table, the NOT NULL property is not added to the primary key columns of the child tables.

• Comparisons of the CREATE FUNCTION statement:

  • In AnalyticDB for PostgreSQL V7.0, the CREATE FUNCTION statement does not contain a WITH clause.

  • In AnalyticDB for PostgreSQL V6.0, the CREATE FUNCTION statement contains a WITH clause.

• Comparisons of the object identifier (OID) column:

  • In AnalyticDB for PostgreSQL V7.0, system tables contain ordinary OID columns.

  • In AnalyticDB for PostgreSQL V6.0, system tables contain hidden OID columns. When you create a table in an AnalyticDB for PostgreSQL V6.0 instance, you can use a WITH OIDS clause to specify a hidden OID column. This feature is disabled in AnalyticDB for PostgreSQL V7.0. In AnalyticDB for PostgreSQL V7.0, you can explicitly declare columns as of the OID type. To create a table that has OID columns, you must use a different CREATE TABLE statement.

System tables

Specific system tables are changed in AnalyticDB for PostgreSQL V7.0. If your business logic references the system tables, you must modify the tables to prevent errors.

• Deleted system tables:

  • pg_exttable

    AnalyticDB for PostgreSQL V7.0 combines the system tables named external and foreign into the pg_foreign_table table. In the pg_foreign_table table, the name of the server column in the original external table is changed to gp_exttable_server, and related properties of the external table are written to the ftoptions column.

  • pg_partition

  • pg_partition_encoding

  • pg_partition_rule

    The gp_partition_template and pg_partitioned_table tables are used as system tables related to partitioned tables.

• Added system tables related to partitioned tables:

  • gp_partition_template

    The gp_partition_template table defines the template of each partition level in a partitioned table. The following table describes the structure of the gp_partition_template table.

    Column name	Type	Description
    relid	oid	The OID of the parent partitioned table.
    level	int16	The level of a partition in the partitioned table.
    template	pg_node_tree	The template structure.

  • pg_partitioned_table

    The pg_partitioned_table table stores information about how tables are partitioned. The following table describes the structure of the pg_partitioned_table table.

    Column name	Type	Reference	Description
    partrelid	oid	pg_class.oid	The OID of the pg_class entry for the partitioned table.
    partstrat	char	None	The partitioning strategy. Valid values: h: hash partitioning. l: list partitioning. r: range partitioning.
    partnatts	int2	None	The number of columns in the partition key.
    partdefid	oid	pg_class.oid	The OID of the pg_class entry for the default partition of the partitioned table. If the partitioned table does not have a default partition, the value of the column is 0.
    partattrs	int2vector	pg_attribute.attnum	An array of partnatts values that indicate which table columns are parts of the partition key. For example, a value of 1 3 indicates that the first and third columns form the partition key. A value of 0 in this array indicates that the corresponding partition key column is an expression rather than a simple column reference.
    partclass	oidvector	pg_opclass.oid	The OID of the operator class to use in each column of the partition key. For more information, see pg_opclass.
    partcollation	oidvector	pg_opclass.oid	The OID of the collation to use for partitioning in each column of the partition key. If the column is not of a collatable data type, the value of the column is 0.
    partexprs	pg_node_tree	None	The expression trees in the nodeToString() function for partition key columns that are not simple column references. The column is a list. If all partition key columns are simple column references, the column is null.

• Modified system tables:

  System table	Modification description
  pg_attribute	The atthasmissing column is added to indicate whether a column has missing values. Valid values: true: The column has missing values and can contain NULL values. false: The column does not have missing values and cannot contain NULL values. The attidentity column is added to indicate whether a column is defined as an identifier column. Valid values: 'a': The column is an identifier column and a value is always generated for the column. 'd': The column is an identifier column and a value is automatically generated for the column by using a sequence or another mechanism. ''(zero bytes): The column is not an identifier column. The attgenerated column is added to indicate whether a column is a generated column. Valid values: 's': The column is a generated column and the value of the column is STORED. ''(zero bytes): The column is not a generated column.
  pg_class	The relstorage column that indicates the physical storage mode of a table is deleted. In AnalyticDB for PostgreSQL V7.0, the relstorage column of the external table is replaced by the relkind column and the value of the relkind column is changed from 'r' to 'f'. The relhasoids column that indicates whether to generate an OID for each row of a table is deleted. Hidden OID columns are removed in AnalyticDB for PostgreSQL V7.0. The relhaskey column that indicates whether a table has a primary key index is deleted. In AnalyticDB for PostgreSQL V7.0, you can check whether a data table of your application has a primary key index from the pg_index system table. The relrowsecurity column is added to indicate whether row level security (RLS) is enabled for a table. Valid values: true: RLS is enabled for the table and security policies can be applied to the table. false: RLS is disabled for the table. By default, all users can access all data of the table. The felforcerowsecurity column is added to check whether RLS is forcibly enabled for a table. If the enabled RLS is applicable to the table owner, the value of the felforcerowsecurity column is true. The relispartition column is added to indicate whether a table is a partitioned table. Valid values: true: The table is a partition of the partitioned table. false: The table is not a partitioned table or is the parent table of a partitioned table. The relrewrite column is added to indicate whether a table defines rewrite rules for DDL operations. Valid values: true: The table defines rewrite rules for DDL operations. false: The table does not define rewrite rules for DDL operations or rewrite rules are not applicable to DDL operations. The relpartbound column that is of the pg_node_tree type is added to indicate the internal representation of the partition bound.
  pg_index	The indnkeyatts column is added to indicate the number of key columns in an index. In AnalyticDB for PostgreSQL V7.0, an INCLUDE clause is contained in the statement for creating an index. This way, you can add non-indexed columns to leaf nodes to facilitate search.
  pg_proc	The protransform column is renamed as prosupport. The function specified by the prosupport column is easier than the function in the corresponding row. The proisagg column that indicates whether a function is an aggregate function is deleted. The proiswindow column that indicates whether a function is a window function is deleted. The prokind column is added to indicate the type of a function. Valid values: f: common function. p: stored procedure. a: aggregate function. w: window function. The proparallel column is added to indicate whether a function can be safely executed in parallel mode. Valid values: s: The function can be safely executed in parallel mode without restrictions. r: The function can be executed in parallel mode but can be executed only by the leader of the parallel group. u: The function cannot be safely executed in parallel mode and the existence of such a function in an SQL statement forces a serial execution plan. The protrftypes column is added to store the OIDs of the data type conversion rules that need to be applied.
  pg_statistic	The stacoll column is added to indicate how statistics are collected. Valid values: N: Collation is used to export statistics stored in slot N. For example, a histogram slot for a collatable column shows the collation that defines the sort order of data. 0: Statistics cannot be collected.

Extensions

The following table describes the extensions that may be temporarily unavailable in AnalyticDB for PostgreSQL V7.0.

Keywords

AnalyticDB for PostgreSQL V7.0 adds, modifies, and deletes specific keywords. The names of database objects cannot be the same as keywords.

You can execute the following statement to query the keywords and their meanings in AnalyticDB for PostgreSQL V6.0 and V7.0:

SELECT * FROM pg_get_keywords();

The following table describes the keyword categories in AnalyticDB for PostgreSQL V7.0.

The following table lists the keywords that belong to different categories in AnalyticDB for PostgreSQL V7.0 and AnalyticDB for PostgreSQL V6.0.

Function-related comparisons

• In AnalyticDB for PostgreSQL V7.0, the write-ahead log directory pg_xlog is renamed as pg_wal and the transaction status directory pg_clog is renamed as pg_xact. The xlog strings that are referenced in SQL functions, tools, and options are all renamed as wal. For example, pg_switch_xlog() is renamed as pg_switch_wal(), pg_receivexlog is renamed as pg_receivewal, and --xlogdir is renamed as --waldir.

• In AnalyticDB for PostgreSQL V7.0, the SQL-style substring() function is changed to have standard-compliant greediness behavior. When a pattern can be matched in multiple ways, the initial sub-pattern is treated as matching the smallest possible amount of text instead of the largest. For example, a pattern such as %#"aa*#"% selects the first group of a from the input parameter instead of the last group.

Other comparisons

To improve security, AnalyticDB for PostgreSQL V7.0 uses the SHA256 algorithm to encrypt database passwords. The SHA256 algorithm is more secure than the default MD5 algorithm of AnalyticDB for PostgreSQL V6.0. To upgrade from AnalyticDB for PostgreSQL V6.0 to AnalyticDB for PostgreSQL V7.0, you can use the data migration feature to migrate data between instances of the two versions. After you migrate data to an AnalyticDB for PostgreSQL V7.0 instance, reset the database passwords. For more information, see Migrate data between AnalyticDB for PostgreSQL instances.