This topic describes how to set the timeout period of SQL statements in AnalyticDB for PostgreSQL.

Problem description

The following error is returned after an SQL statement is executed for more than 3 hours:

ERROR:  canceling statement due to statement timeoutTime: 10801445.540 ms (03:00:01.446)

Causes

By default, the timeout period of SQL statements in AnalyticDB for PostgreSQL is set to 3 hours. If an SQL statement is executed for more than 3 hours, the execution of the statement is terminated.

Solutions

AnalyticDB for PostgreSQL provides the statement_timeout parameter to modify the timeout period of SQL statements. You can modify the statement_timeout value by using the AnalyticDB for PostgreSQL console or executing an SQL statement.

  • Perform the following steps in the AnalyticDB for PostgreSQL console:
    1. Log on to the AnalyticDB for PostgreSQL console.
    2. In the upper-left corner of the console, select a region.
    3. Find the instance that you want to manage and click the instance ID.
    4. In the left-side navigation pane, click Parameter Configuration.
    5. On the Parameter Configuration page, find the statement_timeout parameter and click the Edit parameters icon in the Running Value column.
    6. Enter a new parameter value.
      • Valid values: 0 to 2147483647.
      • If you set the value to 0, the timeout feature of SQL statements is disabled.
      • Unit: milliseconds.
      Note We recommend that you set the timeout period of SQL statements to a value greater than 60000 milliseconds to prevent impacts on other tasks.
    7. Click OK.
    8. Click Submit in the upper-right corner.
    9. In the Parameter Modification message, click OK.
  • Execute the following SQL statement:
    SET statement_timeout = 0;
    Note If you execute an SQL statement to modify the parameter value, the new value is valid only for the current session. To make the new value take effect for a specific instance, we recommend that you modify the parameter value in the AnalyticDB for PostgreSQL console.