Accessing the Workload Configuration Programmatically
The Greenplum Database workload management extension gp_wlm creates a table in the postgres database to store the workload management rules, and user-defined functions to get or set the workload management rules.
The gpcc.workload_config table stores the workload management rules as a JSON value. You can use the gpcc.get_workload_config() and gpcc.set_workload_config() functions to read and write this JSON value.
This topic is a reference for the workload management configuration JSON document and the get and set functions.
Warning! The gpcc.workload_config table should only be accessed by using the gpcc.get_workload_config() and gpcc.set_workload_config() functions or the Command Center user interface. Do not drop the table while the workload management extension is enabled.
The gpcc.set_workload_config() function requires valid JSON syntax, but does not validate the workload management rules. You must ensure that the JSON value contains a version variable, and correctly specified assignment and idle session timeout rules.
Workload Management Rules JSON Format
This section describes the JSON object that stores the resource group assignment and idle session timeout rules. The object has three members:
- a
versionkey/value pair - an
assignmentRulesarray containing one element for each assignment rule - an
idleSessionKillRuleslist containing one key/value pair for each resource group
version pair
version value is an integer. It is reserved for future use. It can be set to 1. assignmentRules array
assignmentRules array has one element for each assignment rule. Each element maps the rule to a resource group in Greenplum Database and defines the conditions that assign a transaction to that resource group.
Greenplum Command Center evaluates each rule in the assignmentRules array from top to bottom and stops at the first match, so the order is important.
The elements in the assignmentRules array can have the following key/value pairs.
CREATE RESOURCE GROUP SQL statement.SET ROLE SQL command.
If no role is specified in the assignment rule and the query tags match, the transaction is assigned to the specified resource group. If the roleName value is present, however, the current database user must match the specified role.
gpcc.query_tags parameter in the Greenplum Database session. A query tag is a user-defined <name>=<value> pair. Separate multiple query tags with semicolons. For example, the following statement, executed in the Greenplum Database session, sets the appName and appUser query tags to “tableau” and “bi_sales”, respectively.
=# SET gpcc.query_tags TO 'appName=tableau;appUser=bi_sales';
To match, all tags in the assignment rule’s queryTags value must be present in the gpcc.query_tags parameter in the database session. The order of the tags is not significant, and the gpcc.query_tags parameter can be a superset of the tags defined in the queryTags value.
If the queryTags value is empty, or the parameter omitted, the rule will match every session for the roleName database role.
If you set the gpcc.query_tags parameter inside of a transaction, you must commit the transaction before the new query tags are used to evaluate assignment rules.
The gpcc.query_tags parameter can be specified using the SET command, as above, or as a connection parameter with database clients that support it, such as psql. Here are two examples that show how to specify query tags on the psql command line:
$ PGOPTIONS="-c gpcc.query_tags='appName=tableau;appUser=bi_sales'" psql
$ psql postgresql://mdw:5432/postgres?options="-c gpcc.query_tags%3D'appName%3Dtableau;appUser%3Dbi_sales'"
In the second example, it is necessary to code the equals signs as %3D to prevent psql from interpreting the query tags as command-line arguments.
true, the assignment rule is ignored when Command Center evaluates rules to assign transactions to workloads. This parameter is optional and its default value is "false".
gpcc.get_workload_config()
Retrieves the current workload assignment rules as a JSON value.
Example
postgres=# SELECT gpcc.get_workload_config();
get_workload_config
--------------------------------------------------------
{
"version": 1,
"assignmentRules": [
{
"resourceGroupName": "default_group",
"roleName": "gpadmin",
"queryTags": "appName=tableau;appUser=bi_sales"
},
{
"resourceGroupName": "admin_group",
"roleName": "gpadmin",
"queryTags": "appName=tableau;appUser=bi_acct",
"disabled": true
}
],
"idleSessionKillRules": {
"admin_group": {
"timeoutSeconds": 600
},
"default_group": {
"timeoutSeconds": 3600
}
}
}
(1 row)
gpcc.set_workload_config()
Sets the workload assignment rules. The argument is a valid JSON value containing the assignment rules an. See JSON Parameters for descriptions of the parameters.
The gpcc.set_workload_config() function accepts any valid JSON value. You must ensure that the value contains a version element and properly specified assignment and idle session timeout rules.
If you call gpcc.set_workload_config() within a transaction, you must commit the transaction before the workload managment extension applies the new rules.
Example
postgres=# SELECT gpcc.set_workload_config(
'
{ "version": 1,
"assignmentRules":
[
{
"resourceGroupName": "default_group",
"roleName": "gpadmin",
"queryTags": "appName=tableau;appUser=bi_sales"
},
{
"resourceGroupName": "admin_group",
"roleName": "gpadmin",
"queryTags": "appName=tableau;appUser=bi_acct",
"disabled": true
}
],
"idleSessionKillRules":
{
"admin_group":
{ "timeoutSeconds": 600 },
"default_group":
{ "timeoutSeconds": 3600 }
}
}
'
);
set_workload_config
---------------------
t
(1 row)