Hologres:FAQ about Hologres SQL statements

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

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

• Solution: Publications are used for subscription to binary logs. You can create a publication only for a table for which binary logging is enabled.

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

• 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 error message "too many shards in this instance" is reported?

• 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 error message "Feature not supported: insert into parent table" is reported?

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

• Solution: Write data to a specified child partitioned table.

What do I do if the error message "timestamp out of range" is reported?

• Cause: Values of the fields of the TIMESTAMP type exceed the specified precision.

• Solution: Process the dirty data to ensure that values of the fields of the TIMESTAMP type are within the specified precision.

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

• 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 error message "value too long for type character varying(256)" is reported?

• 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.

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

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

• Solution: Perform only the SELECT operation on secondary instances. For other operations, perform them on primary instances.

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

• 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 error message "ALTER TABLE CHANGE OWNER is not supported in SLPM (Schema-Level Permission Mode)" is reported?

• 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, permissions of table owners are granted to a user group. To change the table owner, you can remove the original owner from the user group.

What do I do if the error message "ERROR: HGERR_msge division by zero" is reported?

• Cause: Division by 0 is not supported.

• Solution:

  • Use the CASE WHEN expression to prevent division by 0.

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

    -- Create an extension that is compatible with MySQL as a superuser. You need to create the extension only once for each database.
    create extension if not exists mysql_compatible;
    -- Set the mysql_compatible.enable parameter to on. After the parameter is configured, the divisor can be 0 in data query language (DQL) statements.
    set mysql_compatible.enable = on;

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

• 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 error message "Build desc failed: Column type: information_schema.sql_identifier can not translate into hologres type" is reported?

• 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 error message "ERROR: permission denied for table pg_subscription" is reported?

• Cause: You can use a non-superuser account to execute the select hg_dump_script statement only if required permissions are granted to the non-superuser account.

• Solution:

  • Execute the select hg_dump_script ('Table name') statement as the superuser to obtain the DDL statement of the table.

  • Use a superuser account to grant the SELECT permission on the pg_subscription table to the non-superuser account. Sample statement:

    grant select on pg_subscription to "xxxx";

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

• Cause: You cannot create internal tables in Hologres Shared Cluster instances.

• Solution: Create internal tables in general-purpose instances. You can create only foreign tables in Hologres Shared Cluster instances. For more information about the syntax for creating foreign tables, see CREATE FOREIGN TABLE.

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

• 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 error message "ERROR: commit ddl phase1 failed: DDLWrite is not allowed on replica" is reported?

• 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 error message "Feature not supported: INSERT ON CONFLICT RETURNING" is reported?

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

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

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

• Cause: A timeout period is configured for queries, 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, see Manage queries.

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

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

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

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

• 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: Execute the INSERT operation outside the transaction.

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

• 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.

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

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

• Solution: Make sure that only primary key columns are specified in the ON CONFLICT clause of the INSERT ON CONFLICT statement. For more information, see INSERT ON CONFLICT(UPSERT).

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

• 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 cancelled.

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

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

• Cause: You cannot use the ALTER TABLE statement to modify the data types of columns in a table.

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

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

• 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 error message "Feature not supported: UPDATE with shard keys" is reported?

• 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 error message "query length xxxxx exceeded the maximum 102400" is reported?

• 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 error message "The specified partition does not exist" is reported?

• Cause: The partition on which the INSERT or UPDATE operation is performed does not exist.

• Solution: Create the required partition in advance.

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

• 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 error message "ERROR:function xxx does not exist" is reported?

• 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 error message "Update row with Key (id)=(xxxx) multiple times" is reported?

• Cause: The primary key values are duplicate when you perform the UPDATE operation due to the following reasons:

  • The source data contains duplicate primary key values.

  • Time to live (TTL) is configured for the table. The TTL expires but the data is not deleted.

• Solution:

  • If the source data contains duplicate primary key values, process the duplicate data before you perform the UPDATE operation.

  • For tables on which TTL is configured, use the hg_remove_duplicated_pk function to clear expired data. For more information, see INSERT ON CONFLICT(UPSERT).

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

• 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 error message "null value in column "xxx" violates not-null constraint" is reported?

• Cause: A NULL value is inserted into a column that is defined as NOT NULL in a table.

• Solution: Remove the NULL value and insert valid data.

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

• 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.

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 specifications for the NUMERIC data type.

• Solution: Handle the dirty data.

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

• 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.

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

• Cause: Data records to be written into a partition are invalid. For example, the following code writes data records whose sale_date is later than 20240110 into the partition 20240110.

  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.

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

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

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

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

• 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.

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

• 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;

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.

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

What do I do if the error message "cannot drop table" is reported when I execute the DROP TABLE statement?

• Cause

  Some objects depend on the table. For example, this table is used in a view, and the table fails to be dropped.

• Solution

  Execute the following statement to drop the table and dependencies on the table:

  DROP TABLE <table_name> CASCADE;

  Replace table_name with the name of the table that you want to drop.

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

• Cause

  The maximum size of a single 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 error message "CREATE TABLE is not supported for current instance" is reported when I create a table?

• Cause

  A Hologres Shared Cluster instance is used. You can create only foreign tables in a Hologres Shared Cluster instance. An instance whose endpoint starts with hgmc, such as hgmc-cn-xxwwwkkk, can be determined as a Hologres Shared Cluster instance. You can check the instance endpoint in the Hologres console.

• Solution

  • Create a foreign table based on the syntax. For more information, see CREATE FOREIGN TABLE.

  • Create internal tables in Hologres exclusive instances.

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

• 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 error message "internal error: Sequence is used out" is reported when I execute an SQL statement?

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

• Solutions:

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

  • Solution 2: Do not use the SERIAL data type. When you write data of the SERIAL data type, a table lock is acquired.

What do I do if the error message "ERROR: ORCA failed to produce a plan: Lookup of object [failed to get table cluster index ] in cache failed" is reported?

• Cause: A column name contains spaces when you create a table.

• Solution: Drop the table and create another one.