AnalyticDB for PostgreSQL:How do I set the timeout period of SQL statements?

Problem description

The following error message 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 use one of the following methods to modify the statement_timeout value:

• Perform the following steps in the AnalyticDB for PostgreSQL console (instance-level modification):
  1. Log on to the AnalyticDB for PostgreSQL console.
  2. In the upper-left corner of the console, select the region where the instance resides.
  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 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 (session-level modification):

  SET statement_timeout = 0;

• Execute the following SQL statement (user-level modification):

  ALTER ROLE <username> SET statement_timeout TO 600000;

  Note Replace username with the database account that is used to set the timeout period of SQL statements.