All Products
Search

This Product

    Hologres:SQL diagnosis

    Document Center

    Hologres:SQL diagnosis

    Last Updated:Mar 26, 2026

    SQL diagnosis analyzes slow query logs to surface trends and details about your Hologres instance. By default, it captures all Data Manipulation Language (DML) queries that take longer than 100 ms and all Data Definition Language (DDL) queries. Use SQL diagnosis to understand instance usage, identify failed queries, and optimize performance.

    Diagnostic tools overview

    Hologres provides three complementary tools for query analysis. Choose based on your use case:

    Tool Data latency Retention Best for
    SQL Diagnosis page (HoloWeb) T+1 (previous day) Last month Trend analysis, DML/DDL ratios, user and application breakdowns
    Query Insight (SQL Editor) Real-time (in-editor) N/A Diagnosing individual failed queries as they occur
    hg_query_log table Configurable Last month Raw SQL query log with direct SQL access
    hologres.hg_query_log records only DML and DDL queries longer than 1 s by default. SQL diagnosis captures all DML and DDL queries longer than 100 ms. If hologres.hg_query_log shows fewer records than SQL diagnosis, set log_min_duration_statement to 100ms to align the two. For details, see View and analyze slow query logs.

    How it works

    SQL diagnosis collects query log data and presents it across two surfaces:

    • SQL Diagnosis page (HoloWeb): Aggregated trends and charts, updated on a T+1 basis. View data from the previous day (T-1) through the last month.

    • SQL Editor (HoloWeb): Inline diagnosis for failed queries, powered by Query Insight, which automatically explains the failure and suggests a fix.

    SQL diagnosis excludes system-generated SQL. The following filter is applied:

    WHERE
usename != 'system'
AND client_addr != '127.0.0.1'
AND (application_name IS NULL
OR application_name NOT IN ('AutoPartition', 'holoweb_system', 'HgGenInQuery'))

    Diagnosis items

    The SQL Diagnosis page shows the following items for the selected time range:

    Diagnosis item Description
    Total queries Total number of queries in the selected time range.
    Successful queries Total number of successful queries.
    Failed queries Total number of failed queries.
    Failed query details Error codes and the corresponding number of failures, including typical queries and error details. Navigate to Get query insights for a deeper view, or use the error code reference table below to identify root causes.
    Successful and failed query trends Ratio of successful to failed queries, showing overall query execution health over time.
    Query time consumption ratio trend Time consumption ratios by DML operation. Covers SELECT, INSERT, UPDATE, and DELETE by default.
    DML trend Execution trend for DML queries: SELECT, INSERT, UPDATE, and DELETE.
    DDL trend Execution trend for DDL queries: CREATE TABLE, DROP TABLE, TRUNCATE TABLE, ALTER TABLE, CALL, CREATE EXTENSION, CREATE FOREIGN TABLE, ALTER FOREIGN TABLE, IMPORT FOREIGN SCHEMA, DROP FOREIGN TABLE, CREATE SCHEMA, CREATE VIEW, DROP VIEW, GRANT, CREATE ROLE, ALTER ROLE, and COMMENT.
    Query application source ratio or trend Query ratio or trend grouped by application_name. Set distinct application_name values for different task types to make this chart actionable for monitoring abnormal tasks.
    Query ratio or trend by user Query ratio or trend grouped by usename. Use separate database users for different tasks to make per-user troubleshooting straightforward.
    Query trend by execution engine Execution trend across Hologres execution engines: HQE, PQE, SDK, and FixedQE. For engine details, see Service architecture. Minimize PQE queries by rewriting them as HQE queries for better performance.

    View SQL diagnosis data

    Prerequisites

    Before you begin, make sure you have:

    Steps

    1. Log on to the HoloWeb console.

    2. In the top navigation bar, click Diagnostics and Optimization.

    3. In the left navigation pane, choose Instance Diagnosis > SQL Diagnosis.

    4. At the top of the SQL Diagnosis page, set the following parameters:

      Parameter Required Description
      Instance name Yes The instance to diagnose. Defaults to the current instance.
      Time range Yes The time range for the query data. Defaults to Previous Day. Select data from the last month at most.

    5. Click Submit to view the results.

    Intelligent SQL error diagnosis

    When a query fails in the HoloWeb SQL Editor, Query Insight automatically analyzes the failure and returns the root cause and a recommended fix directly in the editor. This eliminates the need to manually cross-reference error codes.

    Usage notes

    • SQL diagnosis data is available for the last month only.

    • Data is updated on a T+1 basis. By default, the previous day's data is displayed. Filter by time range as needed.

    • Permissions required are the same as those for slow query logs. See Grant view permissions.

    Why can't I see certain query records?

    If a query doesn't appear in SQL diagnosis, check these common causes:

    • System query filtered out: Queries from usename = 'system', client_addr = '127.0.0.1', or internal applications (AutoPartition, holoweb_system, HgGenInQuery) are excluded by design.

    • Query is too recent: Data is updated on a T+1 basis. Queries from today won't appear until tomorrow.

    • Query is too old: Only the last month of data is retained.

    • Insufficient permissions: You need the same permissions as for slow query logs. See Grant view permissions.

    • Query ran faster than the threshold: SQL diagnosis captures only DML queries that take longer than 100 ms and all DDL queries. Faster DML queries are not recorded.

    Error code reference

    The following table lists common error codes, their descriptions, and solutions. Use this table alongside Failed query details on the SQL Diagnosis page.

    Error code Description Common error messages Solution
    HG_ERRCODE_FDW_ERROR Error importing metadata from a MaxCompute foreign table, usually caused by an unsupported table type. failed to import foreign schema from odps: Can't find file system factory See HG_ERRCODE_FDW_ERROR.
    ERRCODE_FDW_ERROR Error querying a foreign table. failed to import foreign schema from odps: Authorization Failed: xxx / failed to import foreign schema from odps:Table not found -xxx Resolve based on the specific error message. See ERRCODE_FDW_ERROR.
    ERRCODE_UNIQUE_VIOLATION / pk violates A duplicate primary key was written, violating the unique constraint. duplicate key value violates unique constraint DETAIL: xxx already exists. Remove duplicate primary keys from the source data. If the INSERT fails, rewrite it as INSERT ... ON CONFLICT. If INSERT ... ON CONFLICT still fails, the source data itself contains duplicates—see INSERT ON CONFLICT (UPSERT) for solutions.
    ERRCODE_CHECK_VIOLATION / partition constraint Data was written to a partition that doesn't match its partition value (for example, data for partition 20240229 was written to partition 20240301). new row for relation xx violates partition constraint DETAIL: Failing row contains (column1)=(xxxx). Verify that the partition data matches the defined partition value and correct any mismatches.
    ERRCODE_NOT_NULL_VIOLATION / not-null constraint / UsageProblem A null value was written to a field with a NOT NULL constraint. null value in column xxx violates not-null constraint DETAIL: Failing row contains (null). Cleanse the dirty data.
    ERRCODE_UNDEFINED_TABLE The table does not exist, usually because metadata has not updated after table creation, or the table was truncated or dropped during query execution. Dispatch query failed: Table not found Use Query Insight to check for concurrent TRUNCATE or DROP tasks, then retry.
    ERRCODE_INTERNAL_ERROR / ERPC_ERROR_CONNECTION_CLOSED An unexpected internal error occurred. The instance may have been down or the query was unexpectedly interrupted. Transaction xx is not found or it was expired and cancelled. / Query is cancelled / ERPC_ERROR_CONNECTION_CLOSED None.
    ERRCODE_QUERY_CANCELED / User canceled / CANCELLED / Query Is Cancelled / InternalQueryIsClosed The query was canceled, typically due to a client timeout or because the table was truncated or dropped. ERROR: canceling statement due to statement timeout / canceling statement due to user request See Manage queries.
    ERRCODE_FEATURE_NOT_SUPPORTED / Unsupported Feature The requested feature is not supported. Dynamic partition selector is not supported / ALTER TABLE CHANGE OWNER is not supported in SPM (Simple Permission Mode) / Feature not supported: insert into parent table See FAQ about Hologres SQL statements.
    ERRCODE_UNDEFINED_OBJECT A referenced column or table group does not exist. column xxx does not exist / Table group xxx does not exist. Create the missing object, or verify the correct object name is used in the SQL statement.
    ERRCODE_INSUFFICIENT_PRIVILEGE / permission denied The current account has insufficient permissions. ERROR: permission denied for schema xxx / ERROR: permission denied for foreign table table_info See Development permissions FAQ.
    ERRCODE_OUT_OF_MEMORY / OOM The query triggered an out-of-memory (OOM) error. Total memory used by all existing queries exceeded memory limitation See Troubleshooting guide for OOM errors.
    ERRCODE_DATATYPE_MISMATCH / Unmatched Data Row Schema Number / Dataset Schema Not Match The actual data type of a field does not match the type required by the expression. unmatched data row schema number / Datasets has different schema Check that column types in the SQL statement match the table definition.
    ERRCODE_DIVISION_BY_ZERO / division by zero The SQL statement contains a division by zero. division by zero Cleanse the dirty data, or use a GUC parameter to suppress errors from division by zero. See Function usage.
    ERRCODE_STRING_DATA_RIGHT_TRUNCATION A VARCHAR field value exceeds the length defined at table creation. value too long for type character varying(xx) Recreate the table with a longer VARCHAR length, or change the field type to TEXT.
    ERRCODE_PROGRAM_LIMIT_EXCEEDED / Exceed Odps Scan Limit A foreign table query exceeded the scan limit for partitions, rows, or bytes. number of read rows (xxxxx) exceeds limit (xxxxxxx) / number of partitions (xxx) scanned for "xxxx" exceeds the maximum allowed (xxx) / scan (xxx GB) for "xxxxx" exceeds the maximum allowed (xxx GB) See FAQ and diagnosis for connecting to MaxCompute.
    ERRCODE_SYNTAX_ERROR The SQL statement contains a syntax error. syntax error at or near "xxxxx" Check the SQL syntax.
    ERRCODE_UNDEFINED_FUNCTION An unsupported or incorrectly invoked function was used. The extension may not have been created, or the syntax is incorrect. function xxxxx does not exist / operator does not exist: xxxxxx Follow the function's syntax requirements and make sure any required extensions are created. See Hologres functions.
    ERRCODE_E_R_E_READING_SQL_DATA_NOT_PERMITTED The account has no read permission on the foreign table. check permission for foreign table scan failed: failed to check permission:MaxCompute error,Authorization Failed [4019], You have NO privilege 'odps:Select' on {xxxxxxxxxx} See MaxCompute permission issues.
    ERRCODE_DUPLICATE_OBJECT / already exist A duplicate extension, publication, or role already exists. publication "xxxxx" already exists / extension "xxxxx" already exists / role "xxxxxxxx" already exists If the object already exists, no further action is needed.
    ERRCODE_INVALID_TEXT_REPRESENTATION / invalid input A string-to-type conversion failed because the string value is invalid (for example, converting an empty string to INT). invalid input syntax for integer: xxx Cleanse the dirty data.
    ERRCODE_BAD_COPY_FILE_FORMAT The data format is incorrect for a COPY command, usually because the data contains the specified delimiter (such as a space), causing a column count mismatch. extra data after last expected column. failed to query next / missing data for column "xxx". failed to query next Cleanse the dirty data.
    ERRCODE_UNDEFINED_COLUMN The query references a column that does not exist. column xxxxx does not exist Check the SQL syntax.
    ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE A numeric value exceeds its defined range. This includes values that overflow the integer part of a decimal type (for example, decimal(4,2) cannot hold 100) or values outside the range of INT or bigint. value "xxxxx" is out of range for type bigint / numeric field overflow / bigint out of range / integer out of range Check for dirty data or incorrect type definitions and update the column type if needed.
    ERRCODE_DATETIME_FIELD_OVERFLOW A time-related field (timestamp, timestamptz, date, time, or timetz) contains a value outside the allowed range. date/time field value out of range: "xxxxxx" / date out of range: "xxxxxx" Cleanse the dirty data.
    ERRCODE_INVALID_PARAMETER_VALUE A parameter value does not meet requirements. Resolve based on the specific error message. mismatched properties: table orientation is "column" but storage format is "sst" / resharding insert select table data failed : Dispatch query failed: internal error: Failed to get available shards for query / InsertOverwrite insert select table data failed : column a.unsign_type does not exist Check the SQL syntax.
    ERRCODE_INVALID_DATETIME_FORMAT Date data does not meet the format requirements. invalid input syntax for type timestamp: "" / invalid input syntax for type date: "" / invalid value "" for "yyyy", Value must be an integer. Cleanse the dirty data.
    ERRCODE_CHARACTER_NOT_IN_REPERTOIRE The data contains a character not in the UTF-8 encoding. invalid byte sequence for encoding "UTF8": 0xe9 0x80 Cleanse the dirty data.
    ERRCODE_DUPLICATE_TABLE A table with the same name already exists. relation "xxxx" already exists If the table already exists, no further action is needed.
    ERRCODE_UNTRANSLATABLE_CHARACTER A character cannot be converted to the target encoding. character with byte sequence 0xe4 0x9e 0xab in encoding "UTF8" has no equivalent in encoding "GBK" Cleanse the dirty data.
    ERRCODE_GROUPING_ERROR A GROUP BY clause error occurred. A column appears in the SELECT list but is not included in GROUP BY or an aggregate function. column "xxx" must appear in the GROUP BY clause or be used in an aggregate function Include all non-aggregated columns in the GROUP BY clause.
    ERRCODE_INVALID_TRANSACTION_STATE / Usage Problem An operation is invalid in the current transaction state. For example, CALL SET_TABLE_PROPERTY was executed outside the same transaction as CREATE TABLE. SET_TABLE_PROPERTY and CREATE TABLE statement are not in the same transaction Wrap both CREATE TABLE and CALL SET_TABLE_PROPERTY in the same transaction using BEGIN; and COMMIT;.
    ERRCODE_AMBIGUOUS_COLUMN A column name could refer to multiple tables, creating an ambiguous reference. column reference "xxx" is ambiguous Qualify the column name with the table name (for example, t1.id instead of id).
    ERRCODE_DUPLICATE_COLUMN The same column name was declared more than once during table creation. column "xxx" specified more than once Check the SQL syntax.
    ERRCODE_AMBIGUOUS_FUNCTION A function call is ambiguous because the input data type is not explicitly specified. For example, calling to_char('2024-02-22', 'YYYY-MM-DD') fails because '2024-02-22' is not one of the required types (timestamp, double precision, or int). Cast the type explicitly: to_char('2024-02-22'::timestamptz, 'YYYY-MM-DD'). Ambiguous function call error. Explicitly cast input types to resolve the ambiguity.
    ERRCODE_INVALID_COLUMN_DEFINITION An invalid column definition was used. In Hologres, this often occurs when a Numeric or Decimal type is defined without specifying precision. invalid definition of a numeric type You can process dirty data.
    ERRCODE_INVALID_CATALOG_NAME / ERRCODE_UNDEFINED_DATABASE The specified database does not exist. None Check whether the database exists.
    ERRCODE_CANNOT_COERCE Data cannot be converted between two incompatible types. cannot cast type date to integer Check the SQL syntax.
    ERRCODE_DEPENDENT_OBJECTS_STILL_EXIST The object cannot be deleted because other objects depend on it (for example, a schema that still contains tables). None Remove or reassign dependent objects first. See Delete an account.
    ERRCODE_UNDEFINED_SCHEMA / ERRCODE_INVALID_SCHEMA_NAME The specified schema does not exist. schema "xxxx" does not exist Check whether the schema exists. If not, create it.
    ERRCODE_DUPLICATE_DATABASE A database with the same name already exists. None If the database already exists, no further action is needed.
    AutoAnalyze-Failed The Auto Analyze task failed, usually due to a backend issue. query row count from analyze table / query from analyze table Submit a ticket to investigate.
    Import Foreign Table Not Found The foreign table being accessed does not exist. failed to get foregin table split:Table not found / Failed to get odps table:Not enable acid table / failed to get foregin table split:% not found Check whether the foreign table exists.
    Cannot Acquire Lock In Time A lock acquisition failure caused by concurrent queries and drop operations on the same table, resulting in a deadlock on the backend node. internal error: Cannot acquire lock in time, current owners See Locks and lock troubleshooting.
    OTHER / QueryNextFTEFailed / QueryNextPQEFailed / ForeignSplitOrSchemaConnectionClosed / ConnectionRefused / ERPC_ERROR_TIMEOUT / ERPC_ERROR_CONNECTION_CLOSED An unexpected error occurred. kConnectError: channel is empty / ERPC_ERROR_CONNECTION_CLOSED / internal error: Connect timeout, err: std_exception: Connection refused Submit a ticket to investigate.

    What's next