Automatic Retry upon SQL Statement Execution Errors

With automatic retry (referred to as CN retry), GaussDB(DWS) retries an SQL statement when the execution of a statement fails. If an SQL statement sent from the gsql client, JDBC driver, or ODBC driver fails to be executed, the CN can automatically identify the error reported during execution and re-deliver the task to retry.

The restrictions of this function are as follows:

• Functionality restrictions:
  • CN retry increases execution success rate but does not guarantee success.
  • CN retry is enabled by default. In this case, the system records logs about temporary tables. If it is disabled, the system will not record the logs. Therefore, do not repeatedly enable and disable CN retry when temporary tables are used. Otherwise, data inconsistency may occur after a CN retry following a primary/standby switchover.
  • CN retry is enabled by default. In this case, the unlogged keyword is ignored in the statement for creating unlogged tables and thereby ordinary tables will be created by using this statement. If CN retry is disabled, the system records logs about unlogged tables. Therefore, do not repeatedly enable and disable CN retry when unlogged tables are used. Otherwise, data inconsistency may occur after a CN retry following a primary/standby switchover.
  • When GDS is used to export data, CN retry is supported. The existing mechanism checks for duplicate files and deletes duplicate files during data export. Therefore, you are advised not to repeatedly export data for the same foreign table unless you are sure that files with the same name in the data directory need to be deleted.
• Error type restrictions:

  Only the error types in Table 1 are supported.

• Statement type restrictions:

  Support single-statement CN retry, stored procedures, functions, and anonymous blocks. Statements in transaction blocks are not supported.

• Statement restrictions of a stored procedure:
  • If an error occurs during the execution of a stored procedure containing EXCEPTION (including statement block execution and statement execution in EXCEPTION), the stored procedure can be retried. If an internal error occurs, the stored procedure will retry first, but if the error is captured by EXCEPTION, the stored procedure cannot be retried.
  • Packages that use global variables are not supported.
  • DBMS_JOB is not supported.
  • UTL_FILE is not supported.
  • If the stored procedure has printed information (such as dbms_output.put_line or raise info), the printed information will be output repeatedly when retry occurs, and "Notice: Retry triggered, some message may be duplicated. " will be output before the repeated information.
• Cluster status restrictions:
  • Only DNs or GTMs are faulty.
  • The cluster can be recovered before the number of CN retries reaches the allowed maximum (controlled by max_query_retry_times). Otherwise, CN retry may fail.
  • CN retry is not supported during scale-out.
• Data import restrictions:
  • The COPY FROM STDIN statement is not supported.
  • The gsql \copy from metacommand is not supported.
  • JDBC CopyManager copyIn is not supported.

Table 1 lists the error types supported by CN retry and the corresponding error codes. You can use the GUC parameter retry_ecode_list to set the list of error types supported by CN retry. You are not advised to modify this parameter. To modify it, contact the technical support.