SQL Statement Concurrency Control Function

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

Description: Only users with the SYSADMIN permission can create an SQL statement concurrency control rule.

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": specify the keyword sequence for concurrency control, which are case-insensitive and can be NULL. "resource": specifies the 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)
gaussdb=#  drop database db1;
DROP DATABASE
gaussdb=#  drop database db2;
DROP DATABASE

         

       

     
    

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": specify the keyword sequence for concurrency control, which are case-insensitive and can be NULL. "resource": specifies the 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)
gaussdb=#  drop database db1;
DROP DATABASE

gs_delete_workload_rule(rule_id int8)

Description: Only users with the SYSADMIN permission can delete an SQL statement concurrency control rule.

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: Only users with the SYSADMIN permission can query the number of SQL statements blocked by an SQL statement concurrency control rule.

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.

Return type:

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)