Help Center/ Data Warehouse Service / More Documents/ Developer Guide (Ally Region)/ SQL Syntax Reference/ DDL Syntax/ Resource Pools and Filtering Rules/ ALTER EXCEPT RULE
Updated on 2025-12-12 GMT+08:00
View PDF
Share

ALTER EXCEPT RULE

Function

This syntax modifies an exception rule set. You can modify one or more specific rule thresholds in a rule set.

Precautions

When ALTER is used to add a new rule threshold, the original rules in the WITH parameter are retained and are not cleared. For example, if a rule set contains only one rule, that is, the maximum duration of job queue blocking (blocktime) is 100 seconds, and you want to add another rule, that is, the maximum job execution time (elapsedtime) is 1000 seconds, the rule set will contain two rules. For details, see the following example.

Syntax

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
 
ALTER 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

Table 1 ALTER EXCEPT RULE parameters

Parameter

Description

Value Range

rule_name

Name of an exception rule set.

A string compliant with the identifier naming rules.

blocktime

Maximum duration of job queue blocking, in seconds.

Numeric type. The value can be –1 or ranges from 1 to INT64_MAX.

elapsedtime

Maximum job execution duration, in seconds.

Numeric type. The value can be –1 or ranges from 1 to INT64_MAX.

allcputime

Maximum CPU time used during job running, in seconds.

Numeric type. The value can be –1 or ranges from 1 to INT64_MAX.

cpuskewpercent

CPU usage skew during job execution, in percentage.

Numeric type. The value can be –1 or ranges from 1 to 100.

cpuavgpercent

Average CPU usage during job execution, in percentage.

Numeric type. The value can be –1 or ranges from 1 to 100.

spillsize

Maximum size of data spilled to disks during job execution, in MB.

Numeric type. The value can be –1 or ranges from 1 to INT64_MAX.

broadcastsize

Maximum broadcast size of a job, in MB.

Numeric type. The value can be –1 or ranges from 1 to INT64_MAX.

memsize

Maximum memory size used for job execution, in MB.

Numeric type. The value can be –1 or ranges from 1 to INT64_MAX.

bandwidth

Maximum network bandwidth that can be used for job execution, in MB.

Numeric type. The value can be –1 or ranges from 1 to INT64_MAX.

Examples

Create an exception rule except_rule1.

1
 
CREATE EXCEPT RULE except_rule_1 WITH (blocktime=2000, spillsize=3000, action=abort);

Change the blocktime threshold of exception rule set except_rule1 to 3000s and the space for spilled data to 4000 MB.

1
 
ALTER EXCEPT RULE except_rule_1 WITH (blocktime=3000, spillsize=4000);

Check whether the exception rule is modified successfully by querying the PG_EXCEPT_RULE system catalog.

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

Add a new rule, that is, the maximum job execution time (elapsedtime) is 2000 seconds.

1
 
ALTER EXCEPT RULE except_rule_1 WITH (elapsedtime=2000);

Check the exception rule again. The rule is modified successfully. Note that the original rule is retained instead of being cleared.

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

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

1
 
CREATE resource pool resource_pool_a1 WITH (except_rule='except_rule_1');

Unbind the exception rule set from the resource pool resource_pool_a1.

1
 
ALTER resource pool resource_pool_a1 WITH (except_rule='None');

Helpful Links

CREATE EXCEPT RULE and DROP EXCEPT RULE