Updated on 2025-05-29 GMT+08:00

SQL Statement Concurrency Control Functions

gs_add_workload_rule(rule_type, rule_name, databases, start_time, end_time, max_workload, option_val)

Description: Creates an SQL statement concurrency control rule. Users must have the SYSADMIN permission.

Parameters: For details, see Table 1.

Return type: int8

Table 1 gs_add_workload_rule parameters

Parameter

Type

Description

Value Range

rule_type

text

Type of the concurrency control rule, which is case-insensitive.

"sqlid": Concurrency control is based on the unique SQL ID.

"select", "insert", "update", "delete", and "merge": Concurrency control is based on the query type and keyword.

"resource": Instance-level concurrency control is based on the system resource usage.

rule_name

name

Name of a concurrency control rule, which is used to search for the concurrency control rule.

Any character string or NULL.

databases

name[]

Array of database names for which the concurrency control rule takes effect. The value is case-sensitive.

List of names of created databases. The value can be NULL, indicating that the configuration takes effect in all databases.

Currently, the database list takes effect only when rule_type is set to a query type because a unique SQL ID is bound to a database and belongs to only one database. The concurrency control rules based on resource usage take effect for instances, that is, all databases.

start_time

timestamptz

Start time of a concurrency control rule.

The value can be NULL, indicating that it takes effect from now on.

end_time

timestamptz

End time of a concurrency control rule.

The value can be NULL, indicating that the rule is always effective.

max_workload

int8

Maximum number of concurrent requests set in a concurrency control rule.

-

option_val

text[]

Supplementary information about the concurrency control rule.

It matches rule_type. The matching relationship is as follows:

  • "sqlid": specifies the unique ID of the SQL statement whose concurrency is to be controlled and slow SQL control rule. The format is '{id=1234, time_limit=100, max_execute_time=500, max_iops=1}', in which id indicates the unique SQL ID and is required. You can obtain it from the dbe_perf.statement or pg_stat_activity view. Others are optional. For details about their meanings, see section "Hint for Setting Slow SQL Control Rules."
  • "select", "insert", "update", "delete", and "merge": keyword sequence for concurrency control, which is case-insensitive and can be NULL.
  • "resource": resource threshold for triggering instance-level concurrency control. The value is in the format of '{cpu-80, memory-70}', indicating the OS resource threshold for triggering instance-level concurrency control. The value can be NULL, indicating that concurrency control is performed regardless of the resource usage.

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
gaussdb=#  SELECT gs_add_workload_rule('sqlid', 'rule for one query', '', now(), '', 20, '{id=32413214}');
 gs_add_workload_rule 
----------------------
                   1
(1 row)
gaussdb=#  CREATE database db1;
gaussdb=#  CREATE database db2;
gaussdb=#  SELECT gs_add_workload_rule('select', 'rule for select', '{db1, db2}', '', '', 100, '{tb1, tb2}');
 gs_add_workload_rule 
----------------------
                   2
(1 row)
gaussdb=#  SELECT gs_add_workload_rule('resource', 'rule for resource', '{}', '', '', 20, '{cpu-80}');
 gs_add_workload_rule 
----------------------
                   3
(1 row)

gs_update_workload_rule(rule_id, rule_name, databases, start_time, end_time, max_workload, option_val)

Description: To update an SQL statement concurrency control rule, users need to reset all parameters instead of only some parameters. Users must have the SYSADMIN permission.

Parameters: For details, see Table 2.

Return type: Boolean

Table 2 gs_update_workload_rule parameters

Parameter

Type

Description

Value Range

rule_id

int8

ID of the concurrency control rule to be updated.

-

rule_name

name

Name of a concurrency control rule, which is used to search for the concurrency control rule.

Any character string or NULL.

databases

name[]

Array of database names for which the concurrency control rule takes effect. The value is case-sensitive.

List of names of created databases. The value can be NULL, indicating that the configuration takes effect in all databases.

Currently, the database list takes effect only when rule_type is set to a query type because a unique SQL ID is bound to a database and belongs to only one database. The concurrency control rules based on resource usage take effect for instances, that is, all databases.

start_time

timestamptz

Start time of a concurrency control rule.

The value can be NULL, indicating that it takes effect from now on.

end_time

timestamptz

End time of a concurrency control rule.

The value can be NULL, indicating that the rule is always effective.

max_workload

int8

Maximum number of concurrent requests set in a concurrency control rule.

-

option_val

text[]

Supplementary information about the concurrency control rule.

It matches rule_type. The matching relationship is as follows:

  • "sqlid": specifies the unique ID of the SQL statement whose concurrency is to be controlled and slow SQL control rule. The format is '{id=1234, time_limit=100, max_execute_time=500, max_iops=1}', in which id indicates the unique SQL ID and is required. You can obtain it from the dbe_perf.statement or pg_stat_activity view. Others are optional. For details about their meanings, see section "Hint for Setting Slow SQL Control Rules."
  • "select", "insert", "update", "delete", and "merge": keyword sequence for concurrency control, which is case-insensitive and can be NULL.
  • "resource": resource threshold for triggering instance-level concurrency control. The value is in the format of '{cpu-80, memory-70}', indicating the OS resource threshold for triggering instance-level concurrency control. The value can be NULL, indicating that concurrency control is performed regardless of the resource usage.

Example:

1
2
3
4
5
6
gaussdb=#  CREATE database db1;
gaussdb=#  SELECT gs_update_workload_rule(2, 'rule for select 2', '{db1}', now(), '', 50, '{tb1}');
 gs_update_workload_rule 
-------------------------
 t
(1 row)

gs_delete_workload_rule(rule_id int8)

Description: Deletes an SQL statement concurrency control rule. Users must have the SYSADMIN permission.

Parameter: rule_id indicates the ID of the concurrency control rule to be updated. The type is int8.

Return type: Boolean

Example:

1
2
3
4
5
gaussdb=#  SELECT gs_delete_workload_rule(3);
 gs_delete_workload_rule 
-------------------------
 t
(1 row)

gs_get_workload_rule_stat(rule_id)

Description: Queries the number of times that SQL statements are blocked by SQL statement concurrency control rules. Users must have the SYSADMIN permission. In the multi-tenancy scenario, this function runs properly if it is called in a non-PDB, and an error is reported if it is called in a PDB.

Parameter: rule_id indicates the ID of the concurrency control rule to be queried. The type is int8. You can set rule_id to –1, indicating that all SQL statement concurrency control rules are queried.

Table 3 Return value types

Name

Type

Description

rule_id

int8

ID of the SQL statement concurrency control rule.

validate_count

int8

Number of SQL statements intercepted by the SQL statement concurrency control rule.

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
gaussdb=#  SELECT * FROM gs_get_workload_rule_stat(1);
 rule_id | validate_count 
---------+----------------
       1 |              0
(1 row)
gaussdb=#  SELECT * FROM gs_get_workload_rule_stat(-1);
 rule_id | validate_count 
---------+----------------
       1 |              0
       2 |              0
(2 rows)