CREATE EXCEPT RULE

Function

CREATE EXCEPT RULE creates an exception rule set. You can specify specific rule thresholds and the operations (abort or penalty) to be performed after an exception rule is triggered.

In a distributed database, query tasks are executed by multiple nodes in parallel. Each node independently processes part of the data and summarizes the results. However, if any node encounters a performance bottleneck, the entire query will be slowed down even if other nodes complete the query quickly. In some extreme cases, a query statement may occupy a large number of resources, causing high node resource usage. As a result, compute or storage resources are insufficient, affecting services and even causing performance deterioration or even unavailability of the entire cluster.

When configuring exception rules in DWS, you can set resource restrictions (such as execution time, memory, and disk) for queries. When an exception rule is triggered during SQL statement execution, the execution is automatically terminated. This prevents abnormal queries from breaking down the cluster, ensuring stability and reducing the O&M workload of the database administrator. For example, if the execution time, disk space, or memory usage exceeds the threshold, the query is automatically terminated.

In DWS resource load management, exception rules take effect only after being bound to a resource pool, and then the user who executes a job is bound to a specified resource pool. If an exception rule is triggered when a user executes a job, the job will be terminated.

You can also set resource pools and exception rules on the management console. For details, see "DWS Resource Management Overview" in the User Guide.

Precautions

Exception rules within the same group work together using an AND condition. For example, if the execution time is set to 1,000 seconds (setting elapsedtime to 1000) and the memory limit is set to 500 MB (setting memsize to 500), the system terminates a job only when both conditions are met. Reaching just one threshold does not stop the job.

Syntax

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

CREATE EXCEPT RULE except_rule_name
       WITH (
            | BLOCKTIME = VALUE,
            | CPUTIME = VALUE,
            | ELAPSEDTIME = VALUE, 
            | CPUSKEWPERCENT = VALUE,
            | SPILLSIZE = VALUE,
            | BROADCASTSIZE = VALUE,
            | MEMSIZE = VALUE,
            | CPUAVGPERCENT = VALUE,
            | BANDWIDTH = VALUE,
            | ACTION = ['abort' | 'penalty']
            );

Parameter Description

Basic Example

Create an exception rule set (except_rule1) and set the blocktime to 3000 seconds and spillsize to 4000 MB. If a statement is blocked for more than 3000 seconds and the spill size exceeds 4000 MB, the statement will be terminated.

1

CREATE EXCEPT RULE except_rule1 WITH (blocktime=3000, spillsize=4000, action=abort);

Create an exception rule set (except_rule2) and set the memsize threshold to 5000 MB. The default operation following an exception is abort.

1

CREATE EXCEPT RULE except_rule2 WITH (memsize=3000);

Create a resource pool and bind it to exception rule set except_rule3.

1

CREATE resource pool resource_pool_a1 WITH (except_rule='except_rule3');

Example: Create an exception rule to terminate a job when the job runs for more than 10 seconds.

In DWS resource load management, exception rules take effect only after being bound to a resource pool, and then the user who executes a job is bound to a specified resource pool. If an exception rule is triggered when a user executes a job, the job will be terminated. Perform the following operations to simulate the termination process.

1. Create a test table and import data to the table.
   1. Connect to the database as the system administrator and run the following SQL statements to create temporary table src to provide data sources for subsequent INSERT operations:

      1 2	CREATE SCHEMA test; CREATE TABLE test.src AS SELECT 1;

   2. Run the following SQL statements to create table test.test:

      1 2	DROP TABLE IF EXISTS test.test; CREATE TABLE test.test(a int, b numeric(15,2)) WITH(orientation=column);

   3. Run the SQL statements to insert 20 million rows of data into the two tables. It takes about half a minute. Please wait.

      1	INSERT INTO test.test SELECT generate_series(1,20000000) % 1000,generate_series(1,20000000) FROM test.src;

2. Create exception rule set except_rule4 and set elapsedtime to 10 seconds.

   1	CREATE EXCEPT RULE except_rule4 WITH (elapsedtime=10);

3. Create resource pool resource_pool_a4 and bind the exception rule to it.

   1	CREATE resource pool resource_pool_a4 WITH (except_rule='except_rule4');

4. Create a job user and bind the user to resource_pool_a4. The password is configured as required.

   1	CREATE USER user4 RESOURCE POOL 'resource_pool_a4' PASSWORD 'password';

5. Grant the permission to access table test.test to user4.

   1 2	GRANT USAGE ON SCHEMA test TO user4; GRANT SELECT ON TABLE test.test TO user4;

6. Connect to the database as user4 and run the following SQL statement. Once the statement is executed for more than 10 seconds, it is terminated.

   1	SELECT * FROM test.test;

   

7. Check exception rule definitions in the PG_EXCEPT_RULE system catalog.

   1	SELECT * FROM PG_EXCEPT_RULE WHERE name = 'except_rule4';

   

Helpful Links

ALTER EXCEPT RULE, DROP EXCEPT RULE, CREATE RESOURCE POOL