All Products
Search
Document Center

Hologres:FAQ about Hologres SQL statements

Last Updated:Jun 24, 2024

This topic provides answers to some frequently asked questions about common error codes in Hologres. This topic also provides solutions for business personnel to quickly locate issues and perform troubleshooting.

HG_ERRCODE_FDW_ERROR

What do I do if the following error message is reported: failed to import foreign schema from odps: Can't find file system factory?

  • Cause: Hologres does not support queries of MaxCompute external tables.

  • Solution: Create a MaxCompute internal table.

ERRCODE_FDW_ERROR

What do I do if the following error message is reported: failed to import foreign schema from odps: Authorization Failed: xxx?

  • Cause: Your account does not have the required permissions on the MaxCompute table.

  • Solution: Grant the required permissions to the user account. For more information, see Manage user permissions by using commands.

What do I do if the following error message is reported: failed to import foreign schema from odps:Table not found -xxx?

  • Cause: The table that you want to query does not exist in MaxCompute.

  • Solution: Check whether the table exists in MaxCompute.

ERRCODE_UNIQUE_VIOLATION or pk violates

What do I do if the following error message is reported: Update row with Key (id)=(xxxx) multiple times or duplicate key value violates unique constraint?

  • Cause: A uniqueness constraint violation exists. Some primary key values are duplicate when you perform the UPDATE, INSERT ON CONFLICT, or INSERT operation.

  • Solution:

    • If the error message is reported when you perform the INSERT operation, use the INSERT INTO xx ON CONFLICT syntax to deduplicate primary key values. For more information, see INSERT ON CONFLICT(UPSERT).

    • If the error message is reported when you use the INSERT INTO xx ON CONFLICT syntax, the data source contains duplicate data. In this case, perform troubleshooting by following the instructions in the "Errors and troubleshooting" section in INSERT ON CONFLICT(UPSERT).

    • Another reason is that Time to live (TTL) is configured for the table. The TTL is expired but the data is not deleted. In this case, use the hg_remove_duplicated_pk function to delete the data. For more information, see the "Errors and troubleshooting" section in INSERT ON CONFLICT(UPSERT).

ERRCODE_CHECK_VIOLATION or partition constraint

What do I do if the following error message is reported: ERROR: new row for relation violates partition constraint?

  • Cause: A check constraint violation exists. The partition key value of the data record that is written is different from the partition key value specified in the statement. For example, the partition key value specified in the statement is 20240110, but a data record with a different partition key value is written. This results in partition conflicts. Sample statements:

    CREATE TABLE public.tbl_20240110 PARTITION OF public.tbl FOR VALUES IN('20240110');
    
    INSERT INTO public.tbl_20240110 SELECT * FROM odps_tbl where sale_date >'20240110';
    
    
    error: new row for relation "tbl_20240110" violates partition constraint
    
  • Solution: Write valid data records to partitions.

ERRCODE_NOT_NULL_VIOLATION, not-null constraint, or UsageProblem

What do I do if the following error message is reported: null value in column "xxx" violates not-null constraint?

  • Cause: A NOT NULL constraint violation exists. NULL values are written to NOT NULL columns.

  • Solution: Remove the NULL values and rewrite data.

ERRCODE_UNDEFINED_TABLE

What do I do if the following error message is reported: Dispatch query failed: Table not found?

  • Cause: The table does not exist. In most cases, this error message is reported when the table is created but the metadata is not updated or if the TRUNCATE or DROP operation is performed on the table during a query.

  • Solution: Use the Query Insight feature of HoloWeb to check whether the TRUNCATE or DROP operation is performed on the table during a query. Then, retry the query. For more information, see Query Insight. Do not execute data definition language (DDL) statements that may cause conflicts during queries.

ERRCODE_QUERY_CANCELED or Query Is Cancelled

What do I do if the following error message is reported: ERROR: canceling statement due to statement timeout?

  • Cause: A timeout period is configured on the client, and the query times out.

  • Solution: Optimize the query to shorten the runtime or set the timeout period to a larger value. For more information about how to configure the timeout period, see Manage queries.

What do I do if the following error message is reported: ERROR: Query:[xxx] Get result failed: canceling statement due to user request?

  • Cause: The query is canceled. In most cases, this error message is reported if the TRUNCATE or DROP operation is performed on the table.

  • Solution: Use the Query Insight feature of HoloWeb to check whether DDL statements that conflict with the query are executed. For more information, see Query Insight. Do not execute DDL statements that may cause conflicts during queries.

What do I do if the following error message is reported: query is cancelled Cannot find index full ID:xxx (table id: x, index id: x) in storages or it is deleting?

  • Cause: The query is performed on a table on which the TRUNCATE or DROP operation is performed. As a result, the table ID is changed, and the query is canceled.

  • Solution: Use the Query Insight feature to check whether the TRUNCATE or DROP operation is performed during the query. For more information, see Query Insight.

ERRCODE_FEATURE_NOT_SUPPORTED or Unsupported Feature

What do I do if the following error message is reported: Feature not supported: insert into parent table?

  • Cause: Data cannot be directly written to a parent partitioned table.

  • Solution: When you execute the INSERT statement, write data to the specified child partitioned table. For more information, see CREATE PARTITION TABLE.

What do I do if the following error message is reported: SELECT INTO is not supported now?

  • Cause: Hologres does not support the SELECT INTO syntax.

  • Solution: Execute the INSERT INTO SELECT statement to insert data. For more information, see INSERT.

What do I do if the following error message is reported: ALTER TABLE CHANGE OWNER is not supported in SLPM (Schema-Level Permission Mode)?

  • Cause: The ALTER TABLE statement cannot be executed to change the table owner when you use the schema-level permission model (SLPM) for the table.

  • Solution: In SLPM, the permissions of table owners are granted to user groups. To revoke permissions from a user, you can remove the user from a user group. For more information, see the "Remove a user from a user group" section in Use the SLPM.

What do I do if the following error message is reported: Feature not supported: INSERT ON CONFLICT RETURNING?

  • Cause: Hologres does not support the INSERT ON CONFLICT RETURNING syntax.

  • Solution: Use INSERT ON CONFLICT. For more information, see INSERT ON CONFLICT(UPSERT).

What do I do if the following error message is reported: ERROR: INSERT in ddl transaction is not supported now?

  • Cause: The INSERT operation is not supported in transactions. Sample code:

    BEGIN;
    INSERT xxx
    commit;
    ERROR: INSERT in ddl transaction is not supported now
  • Solution: Perform the INSERT operation outside a transaction, or start a transaction that supports data manipulation language (DML) statements. For more information, see SQL transaction capabilities.

What do I do if the following error message is reported: Creating publication with table that without binlog is not supported now?

  • Cause: A publication is created for a table for which binary logging is not enabled.

  • Solution: Publications are used to subscribe to binary logs. You can create a publication only for a table for which binary logging is enabled. For more information, see Use JDBC to consume Hologres binary logs.

What do I do if the following error message is reported: Feature not supported: INSERT on conflict contains un-unique column?

  • Cause: Non-primary key columns are specified in the CONFLICT clause of the INSERT ON CONFLICT statement.

  • Solution: Make sure that only primary key columns are specified in the CONFLICT clause of the INSERT ON CONFLICT statement.

What do I do if the following error message is reported: Feature not supported: UPDATE with shard keys?

  • Cause: Hologres does not support the UPDATE operation on primary keys or distribution keys.

  • Solution: Modify the SQL statement and execute it again.

What do I do if the following error message is reported: time before 1925 not supported?

  • Cause: Time-related functions support years from 1925 to 2282 by default. For years beyond the range, an error is reported.

  • Solution: If you use a function such as to_char, to_date, or to_timestamp, configure the Grand Unified Configuration (GUC) parameter hg_experimental_functions_use_pg_implementation before the SQL statements that you want to execute to extend the time range. For more information, see Date and time functions.

What do I do if the following error message is reported: Group by key is type of imprecise not supported?

  • Cause: The field specified in the GROUP BY clause is of an approximate numeric type.

  • Solution: Do not use fields of approximate numeric types, such as FLOAT, in the GROUP BY clause.

What do I do if the following error message is reported: CREATE TABLE is not supported for current instance?

  • Cause: A Hologres Shared Cluster instance or a secondary instance is used. Endpoints of Hologres Shared Cluster instances start with hgmc. For example, hgmc-cn-xxwwwkkk. You can view the instance endpoint in the Hologres console. For Hologres Shared Cluster instances, you can create foreign tables but not internal tables. For secondary instances, you can query data but cannot create tables.

  • Solution:

    • If you use a Hologres Shared Cluster instance, create a foreign table. For more information, see CREATE FOREIGN TABLE. If you want to create internal tables, use exclusive Hologres instances.

    • If you want to create tables, use the primary instance.

What do I do if the following error message is reported: ALTER TABLE ALTER COLUMN SET TYPE is not supported now?

  • Cause: The ALTER TABLE statement cannot be used to modify data types of columns.

  • Solution: Create a table and modify data types of columns.

What do I do if the following error message is reported: ERROR: Currently materialized view does not support aggregate on expressions, only support single column?

  • Cause: Materialized views that are defined based on a single table do not support expressions.

  • Solution: Do not use expressions for materialized views that are defined based on a single table. For more information, see Real-time materialized view.

ERRCODE_UNDEFINED_OBJECT

What do I do if the following error message is reported: column xxx does not exist?

  • Cause: The column does not exist.

  • Solution: Check the SQL statement.

What do I do if the following error message is reported: Table group xxx does not exist?

  • Cause: The table group does not exist.

  • Solution: Specify an existing table group when you create a table, or create a table group before you create a table.

What do I do if the following error message is reported: The specified partition does not exist?

  • Cause: The partition on which the data write or update operation is performed does not exist.

  • Solution: Create the required partition in advance.

ERRCODE_INSUFFICIENT_PRIVILEGE or permission denied

For more information about permission-related errors, see the following topics:

ERRCODE_OUT_OF_MEMORY or OOM

What do I do if the following error message is reported: Total memory used by all existing queries exceeded memory limitation?

  • Cause: An out of memory (OOM) issue occurs during the query.

  • Solution: For more information, see FAQ about OOM.

ERRCODE_DATATYPE_MISMATCH

What do I do if the following error message is reported: internal error: Datasets has different schema?

  • Cause: The columns specified in the query statement do not match the columns in the table.

  • Solution: Check the SQL statement.

ERRCODE_DIVISION_BY_ZERO

  • Cause: The SQL statement specifies the division by zero, which is not supported.

  • Solution:

    • Do not specify the division by zero. You can also use the CASE WHEN expression to prevent division by zero.

    • Create an extension that is compatible with MySQL and enable division by zero. For more information, see Migrate data from MySQL to Hologres.

ERRCODE_STRING_DATA_RIGHT_TRUNCATION

What do I do if the following error message is reported: value too long for type character varying(xxx)?

  • Cause: The length of field values of the VARCHAR type exceeds the upper limit.

  • Solution: Process the field values or set the upper limit for the length of field values of the VARCHAR type to a larger value. You can also change the field type to TEXT.

ERRCODE_PROGRAM_LIMIT_EXCEEDED or Exceed Odps Scan Limit

What do I do if the following error message is reported: Exceeds the partition limitation of 512, current match xxx partitions?

  • Cause: The number of partitions to be queried in a MaxCompute table exceeds 512.

  • Solution: Add partition filter conditions or configure the following GUC parameter to adjust the maximum number of partitions allowed in a query. For more information, see FAQ about integration with MaxCompute.

    set hg_foreign_table_max_partition_limit =xx;

What do I do if the following error message is reported: Build desc failed: Exceeds the scan limitation of 200 GB, current scan xxx GB?

  • Cause: The amount of data to be scanned in a MaxCompute table exceeds 200 GB.

  • Solution:

    • Add filter conditions to reduce the amount of data to be scanned.

    • Import data to a Hologres internal table. No upper limit is imposed on the amount of data to be scanned in a Hologres internal table.

ERRCODE_SYNTAX_ERROR

What do I do if the following error message is reported: syntax error at or near "xxxxx"?

  • Cause: The syntax of the SQL statement is invalid.

  • Solution: Check and modify the syntax of the SQL statement.

ERRCODE_UNDEFINED_FUNCTION

What do I do if the following error message is reported: DISTINCT is not implemented for window functions?

  • Cause: Hologres does not allow you to use the DISTINCT keyword in window functions.

  • Solution: Remove the DISTINCT keyword from window functions.

What do I do if the following error message is reported: ERROR:function xxx does not exist?

  • Cause: No extension is created when you use the function, or the function syntax is invalid.

  • Solution: Use the function by following the requirements of Hologres.

What do I do if the following error message is reported: ERROR: function jsonb_set(json, text[], jsonb, boolean) does not exist?

  • Cause: The data type of a parameter in the JSONB_SET function is invalid.

  • Solution: Specify a value of the JSONB data type for the first parameter in the JSONB_SET function. For more information, see JSON and JSONB data types.

ERRCODE_E_R_E_READING_SQL_DATA_NOT_PERMITTED

What do I do if the following error message is reported: You have NO privilege 'odps:Select' on xxx?

  • Cause: Your account does not have query permissions on the MaxCompute table.

  • Solution: Grant the required permissions to the user account in the MaxCompute console.

What do I do if the following error message is reported: The sensitive label of column 'xxx' is 2, but your effective label is 0?

  • Cause: Your account has query permissions only on specific columns in the MaxCompute table.

  • Solution: Grant more permissions to the user account in the MaxCompute console, or query data only from the specific columns. For more information about how to grant permissions, see Manage user permissions by using commands.

ERRCODE_DUPLICATE_OBJECT

What do I do if the following error message is reported: extension "xxxxx" already exists?

  • Cause: The extension already exists. You do not need to recreate the extension.

  • Solution: Do not create an existing extension.

ERRCODE_INVALID_TEXT_REPRESENTATION or invalid input

What do I do if the following error message is reported: invalid input syntax for type numeric: \"\"?

  • Cause: Fields of the NUMERIC type contain dirty data and do not comply with the specifications of the NUMERIC data type.

  • Solution: Handle the dirty data.

What do I do if the following error message is reported: invalid input syntax for integer: xxx?

  • Cause: Fields of the INT type contain dirty data and do not comply with the specifications of the INT data type.

  • Solution: Handle the dirty data.

ERRCODE_BAD_COPY_FILE_FORMAT

What do I do if the following error message is reported: missing data for column "xxx". failed to query next?

  • Cause: The format of files or data on which the COPY operation is performed is invalid. In most cases, this error message is reported when the source data contains a delimiter, such as a space, that is specified in the COPY statement. As a result, the number of columns in the source table is different from the number of columns in the destination table.

  • Solution: Handle the dirty data.

ERRCODE_UNDEFINED_COLUMN

What do I do if the following error message is reported: ERROR: INSERT has more expressions than target columns?

  • Cause: The number of columns to be written by executing the INSERT statement is inconsistent with that in the destination table.

  • Solution: Make sure that the number of columns to be written by executing the INSERT statement is consistent with that in the destination table.

ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE

What do I do if the following error message is reported: bigint out of range?

  • Cause: The length of the data of the BIGINT type exceeds the upper limit.

  • Solution: Handle dirty data to ensure that the length of the data of the BIGINT type does not exceed the upper limit.

What do I do if the following error message is reported: numeric field overflow in function round?

  • Cause: The values of the fields of the NUMERIC type exceed the specified precision.

  • Solution: Handle the dirty data.

ERRCODE_DATETIME_FIELD_OVERFLOW

What do I do if the following error message is reported: InsertOverwrite insert select table data failed : column a.unsign_type does not exist?

  • Cause: The column does not exist, or the parameter value is invalid.

  • Solution: Check and modify the syntax of the SQL statement.

What do I do if the following error message is reported: mismatched properties: table orientation is "column" but storage format is "sst"?

  • Cause: The table property that is specified during the table creation is invalid. The column-oriented storage mode is specified but SST is selected.

  • Solution: Check and modify the syntax of the SQL statement.

ERRCODE_INVALID_DATETIME_FORMAT

What do I do if the following error message is reported: invalid value "" for "yyyy", Value must be an integer?

  • Cause: The data does not comply with the specifications of the specific data type.

  • Solution: Handle the dirty data.

ERRCODE_CHARACTER_NOT_IN_REPERTOIRE

What do I do if the following error message is reported: invalid byte sequence for encoding "UTF8": 0xe9 0x80?

  • Cause: Specific characters are not supported by the encoding format. In most cases, this error message is reported when a character is not supported by UTF-8.

  • Solution: Handle the dirty data.

ERRCODE_DUPLICATE_TABLE

What do I do if the following error message is reported: relation "xxxx" already exists?

  • Cause: The table already exists.

  • Solution: Do not create duplicate tables.

ERRCODE_UNTRANSLATABLE_CHARACTER

What do I do if the following error message is reported: character with byte sequence 0xe4 0x9e 0xab in encoding "UTF8" has no equivalent in encoding "GBK"?

  • Cause: Characters encoded in UTF-8 have no equivalent characters in GBK.

  • Solution: Handle the dirty data.

ERRCODE_GROUPING_ERROR

What do I do if the following error message is reported: column "xxx" must appear in the GROUP BY clause or be used in an aggregate function?

  • Cause: The column must be specified in the GROUP BY clause.

  • Solution: Modify the syntax of the SQL statement.

ERRCODE_INVALID_TRANSACTION_STATE

What do I do if the following error message is reported: SET_TABLE_PROPERTY and CREATE TABLE statement are not in the same transaction for table?

  • Cause: When you create a table, the CREATE TABLE statement and the CALL statements are not in the same transaction.

  • Solution: When you create a table, execute the CREATE TABLE statement and the CALL statements in the same transaction. Sample code:

    begin;
    CREATE TABLE tbl (
     xxxx
    );
    call set_table_property('tbl', 'orientation', 'xx');
    call set_table_property('tbl', 'distribution_key', 'xxx');
    commit;

ERRCODE_AMBIGUOUS_COLUMN

What do I do if the following error message is reported: column reference "xxx" is ambiguous?

  • Cause: In most cases, this error message is reported when a column name in the SQL statement may indicate columns in different tables. For example, in the SELECT id FROM t1 INNER JOIN t2 ON t1.id=t2.id statement, whether id indicates the id column in table t1 or table t2 is not clarified.

  • Solution: Modify the syntax of the SQL statement.

ERRCODE_DUPLICATE_COLUMN

What do I do if the following error message is reported: column "xxx" specified more than once?

  • Cause: A field is declared multiple times in the table creation statement.

  • Solution: Modify the syntax of the SQL statement.

ERRCODE_AMBIGUOUS_FUNCTION

The function is ambiguous. A function supports input parameters of various data types. In most cases, this error code is returned when the data type of input parameters is not defined.

ERRCODE_INVALID_COLUMN_DEFINITION

What do I do if the following error message is reported: invalid definition of a numeric type?

  • Cause: No precision is specified for fields of the NUMERIC or DECIMAL type during the table creation.

  • Solution: Modify the syntax of the SQL statement, and specify precisions for fields of the NUMERIC or DECIMAL type.

ERRCODE_CANNOT_COERCE

What do I do if the following error message is reported: cannot cast type date to integer?

  • Cause: Data of the DATE type cannot be converted into the INT type.

  • Solution: Modify the syntax of the SQL statement.

ERRCODE_UNDEFINED_SCHEMA or ERRCODE_INVALID_SCHEMA_NAME

What do I do if the following error message is reported: schema "xxxx" does not exist?

  • Cause: The schema does not exist.

  • Solution: Check whether the syntax of the SQL statement is valid.

ERRCODE_INTERNAL_ERROR

An unexpected error is reported.

What do I do if the following error message is reported: internal error: Connect timeout, err: std_exception: Connection refused?

  • Cause: In most cases, this error message is reported because an instance breaks down.

  • Solution: Submit a ticket to check the cause of the Hologres instance breakdown.

What do I do if the following error message is reported: too many shards in this instance?

  • Cause: The number of shards configured for an instance exceeds the upper limit.

  • Solution: We recommend that you configure a shard count that is less than the upper limit. For more information about how to configure a shard count, see Instance specifications.

What do I do if the following error message is reported: internal error: Write is not allowed in readonly mode?

  • Cause: The INSERT, UPDATE, or DELETE operation cannot be performed on secondary instances.

  • Solution: Perform only the SELECT operation on secondary instances. Perform the other operations on primary instances.

What do I do if the following error message is reported: remaining connection slots are reserved for non-replication superuser connections?

  • Cause: The number of connections reaches the upper limit. As a result, you cannot create a connection by using a non-superuser account.

  • Solution: Use the superuser account to release idle connections. For more information, see Manage connections.

What do I do if the following error message is reported: Build desc failed: Column type: information_schema.sql_identifier can not translate into hologres type?

  • Cause: The field types of some system tables are complex and are not supported by Hologres.

  • Solution: We recommend that you do not perform join queries on system tables with complex data types and Hologres tables.

What do I do if the following error message is reported: ERROR: commit ddl phase1 failed: DDLWrite is not allowed on replica?

  • Cause: You cannot modify the IP address whitelist in a secondary instance.

  • Solution: Modify the IP address whitelist in the primary instance.

What do I do if the following error message is reported: current transaction is aborted, commands ignored until end of transaction block?

  • Cause: Statements are executed before the statements in the previous transaction are complete. Sample code:

    begin;
    create xxxx
    
    begin;
  • Solution: Run the rollback; command to terminate the current transaction and roll back all uncommitted changes.

What do I do if the following error message is reported: query length xxxxx exceeded the maximum 102400?

  • Cause: The length of the query exceeds the upper limit of 102,400 bytes.

  • Solution: Rewrite the query to keep its length within the specified range.

What do I do if the following error message is reported: Modify record by primary key is not on this table?

  • Cause: You specify an update mode when you write data by using Flink. However, no primary key is configured for the destination table. As a result, the data update fails.

  • Solution: Configure a primary key for the destination table. For more information, see PK.

What do I do if the following error message is reported: ERROR: xxx for fe, should not be evaluated?

  • Cause: In most cases, this error message is reported when you use a function to query a system table. Execution of the function uses the Hologres engine. However, computing on the system table does not use the Hologres engine. When you use the function to perform computing on the system table, the Hologres engine is not used.

  • Solution: We recommend that you do not use this function for Hologres system tables.

How do I specify a field name that starts with a digit in an SQL statement?

Hologres is compatible with PostgreSQL, uses the same syntax as PostgreSQL, and does not support the query of a field whose name starts with a digit.

If a field starts with a digit, you must enclose the field in double quotation marks ("). Sample statement:

select bizdate,"1_day_active_users","7_day_active_users" from t_active_users;

What do I do if the following error message is reported: cannot drop table because other objects depend on it?

  • Cause: Specific objects depend on the table that you want to delete. For example, a view is defined based on the table.

  • Solution: Delete the objects that depend on the table before you delete the table.

What do I do if the following error message is reported when I write or query data by joining multiple tables: BinaryArray cannot contain more than 2147483646 bytes?

  • Cause:

    The maximum size of a field in Hologres is 2 GB. If the statistics of the tables are not updated in time, an improper execution plan is generated for table joins. As a result, the size of a single field exceeds 2 GB.

  • Solution:

    Execute the following statement to update the statistics of the tables to be joined:

    analyze <tablename>;

    If the error persists after the statistics of the tables are updated, the tables contain fields whose size is greater than 2 GB. In this case, add the following statement before the SQL statement that is used to write or query data:

    set hg_experimental_query_batch_size = 1024;

What do I do if the following error message is reported when I import or query data: Cannot reserve capacity larger than 2^31 - 1 for binary?

  • Possible causes and solutions

    • Cause 1: The maximum size of a single field in Hologres is 2 GB. If the statistics of the table are not updated in time, an improper execution plan is generated. As a result, this error message is reported.

      Solution: Execute the analyze statement on the table to update the statistics of the table.

      analyze <tablename>;
    • Cause 2: The amount of data in a field in the table exceeds 2 GB. As a result, this error message is reported.

      Solution: Add the following statement before the SQL statement that is used to import or query data to reduce the amount of data to be read each time.

      set hg_experimental_query_batch_size = 1024;

What do I do if the following error message is reported when I execute an SQL statement: internal error: Sequence is used out?

  • Cause: The table contains fields of the SERIAL type, and the values of the fields are beyond the valid range.

  • Solution:

    • Create a table and change the data type from SERIAL to BIGSERIAL.

    • Do not use the SERIAL data type. If you write data of the SERIAL data type, a table lock is acquired.