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 SQL statement timeout in AnalyticDB for PostgreSQL is three hours. The service cancels any SQL statement that runs longer than this duration.

Solutions

AnalyticDB for PostgreSQL provides the statement_timeout parameter to modify the SQL timeout duration. You can modify the value of the statement_timeout parameter in the following two ways:

• Method 1: Change the value in the console (instance level)

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

  5. On the Parameters page, find the statement_timeout parameter and click the icon in the Running Value column.

  6. Enter a value for the parameter.

     • Valid values: 0 to 2147483647.

     • A value of 0 disables the SQL statement timeout.

     • Unit: milliseconds (ms).

     Note

     Set the timeout to a value greater than 1 minute (60,000 ms) to avoid affecting other tasks.

  7. Click OK.

  8. In the upper-right corner of the page, click Submit.

  9. In the Modify Parameters dialog box, click OK.

• Method 2: Use an SQL statement

  • Session level:

    SET statement_timeout = 0;

  • User level:

    ALTER ROLE <username> SET statement_timeout TO 600000;

    Note

    Replace username with the target database account.