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
version
key/value pair - an
assignmentRules
array containing one element for each assignment rule - an
idleSessionKillRules
list 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)