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:

    
     
       
       
         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:

    
         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:

    
         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:

    
         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)