Product DocumentationAppearance
Rule Conditions
Rules consist of one or more conditions. When satisfied, these conditions trigger rule actions. You can define different kinds of conditions by selecting directly from drop-down lists or by using filter operators. The following table describes these operators.
| Filter Operator | Description | Examples |
|---|---|---|
| Is True, Not True | Boolean condition | Query is/is not a Single Worker query |
| Equals, Not Equals | Simple equality condition | Schema does not equal public; Application equals ybsql |
| Greater Than, Less Than | > or < | Cost >10000 |
| Greater Than or Equal To, Less Than or Equal To | >= or <= | Memory Usage Estimate <=100 |
| Contains String, Not Contains String | Simple substring operations | SQL contains union all; User Name does not contain bob |
| Matches, Not Matches | Regular expression (regex) pattern matching. Escaping is applied only when items are selected from a drop-down list (for quick rules). Regular expression validation is applied when entries are typed; warnings are displayed. | SQL matches sys\.const; SQL matches sum\(substr |
| Matches Ignore Case, Not Matches Ignore Case | Pattern matching as above, but with respect to case |
The following table lists the specific rule conditions that you can set up, shows which rule types support each condition, and lists the accepted values for each condition.
| Filter Condition | Description | Rule Types | Accepted Values |
|---|---|---|---|
| Application | Application name, as sent by the application itself, or as defined for a session with a SQL SET command: set application_name <name>; | Prepare, Runtime, Completion | Any string that does or does not match an application name, such as ybsql or smc. |
| Average CPU Usage | You can use Query Details > Utilization > Average CPU % in the SMC or the sys.log_query view to find this value for past executions of a query. | Runtime, Completion | Any percentage value |
| Bytes Network | Bytes moved internally over the network on the cluster (manager node to workers and vice versa). This number does not include data being returned to the client.Use Query Details > Statistics > Network I/O Data in the SMC to find this value for past executions of a query. | Runtime, Completion | Positive whole numbers |
| Bytes Read | Use Query Details > Statistics > I/O Data Read in the SMC or io_read_bytes in sys.log_query to find this value for past executions of a query. | Runtime, Completion | Positive whole numbers |
| Bytes Written | Use Query Details > Statistics > I/O Data Written in the SMC or io_write_bytes in sys.log_query to find this value for past executions of a query. | Runtime, Completion | Positive whole numbers |
| Command | Filter on a SQL command: select, update, insert, delete, or utility. | Prepare, Runtime, Completion | Select from the drop-down list. Filter on utility for CREATE TABLE commands. Note: You can define rules for INSERT INTO...SELECT statements but not for INSERT INTO...VALUES statements. INSERT INTO...VALUES operations are not executed by the backend database. |
| Cost | A relative value that is set by the planner. Cost for a past run of a query is recorded in the Query Details > Query tab, and in the sys.log_query view. | Prepare, Runtime, Completion | Any number |
| Database | Database name | Prepare, Runtime, Completion | Select from the drop-down list. |
| Execution Duration (in ms) | Number of milliseconds the query spent during execution on the worker nodes. | Runtime, Completion | Any number |
| Hostname/IP Address | Name or IP address of client machine | Prepare, Runtime, Completion | Any string |
| Max Memory (in bytes) | Maximum runtime memory. Use Query Details > Statistics > Memory Used Maximum in the SMC to find this value for past executions of a query. | Runtime, Completion | Any number |
| Memory Estimate (in MB) | Estimated runtime memory. Use Query Details > Statistics > Memory Used Estimate in the SMC or memory_estimated_bytes in sys.log_query to find this value for past executions of a query. | Prepare, Runtime, Completion | Any number |
| Memory Estimate Confidence | Estimated degree of confidence in the memory estimate value. | Prepare, Runtime, Completion | Unknown, None, Low, High |
| Priority | The CPU priority of queries starts at Normal and is only settable by rules. Priority is not an attribute of a profile or pool. Based on a condition, you can trigger an action to set the priority to a lower (Low) or a higher (High or Critical) priority. Priority settings are only meaningful when multiple queries are running concurrently. If only one query is running, it is allocated 100% of the available CPU, regardless of its priority. When queries are running concurrently, the percentage of CPU time increases 2-3x by priority level. For example, if you have concurrent queries set to Low and Normal, the Normal query takes approximately 75% of the CPU time, and the Low query approximately 25% (the remainder). | Prepare, Runtime, Completion | Low, Normal, High, Critical |
| Query Tags | Use query tags to classify queries, then take actions. (You can also set a tag as an action). See Query Tags. | Prepare, Runtime, Completion | Any string |
| Query is Movable | The query may be moved to another resource pool (and continue its execution there or be restarted there). See Moving a Query to Another Pool. | Prepare, Runtime | Select a pool from the drop-down list. |
| Referenced Tables | Name of a table that is referenced somewhere in a query. | Prepare, Runtime, Completion | Contains or Not Contains <table_name> (select from list) |
| Resource Pool | Name of a WLM resource pool. | Prepare, Runtime, Completion | Select a pool from the drop-down list. |
| Rows Deleted | Number of rows deleted by a DELETE operation. | Runtime, Completion | Any number |
| Rows Inserted | Number of rows inserted by an INSERT operation. | Runtime, Completion | Any number |
| Rows Returned | Number of rows returned to the client application. | Runtime, Completion | Any number |
| SQL | Any piece of SQL text that may be found in a query. You can also use this condition to check for a comment placed in a query, rather than filter on the SQL itself. | Prepare, Runtime, Completion | Any string |
| Schema | Name of a schema. | Prepare, Runtime, Completion | Select from the drop-down list. The search_path for the current session determines whether the condition applies. If a named schema is in the search_path, that is a match. |
| Single Worker | Queries that only involve replicated tables are marked as “single worker” queries. | Prepare, Runtime, Completion | Is True, Not True |
| Temporary Bytes Read | Number of bytes read from disk because spilling occurs during the query. Use Query Details > Statistics in the SMC or io_spill_read_bytes in sys.log_query to find this value for past executions of a query. | Runtime, Completion | Any number |
| Temporary Bytes Written | Number of bytes written to disk because spilling occurs during the query. Use Query Details > Statistics in the SMC or io_spill_write_bytes in sys.log_query to find this value for past executions of a query. | Runtime, Completion | Any number |
| Total Duration (in ms) | The total time a query took to run (end time - submit time). This includes planning, compilation, and queue time. Use Query Details > Statistics > Total Time in the SMC or total_ms in sys.log_query to find this value for past executions of a query. | Runtime, Completion | Any number |
| Total Memory (in bytes) | The total amount of memory used across the cluster to run the query. Use Query Details > Statistics > Memory Used Total in the SMC or memory_total_bytes in sys.log_query to find this value for past executions of a query. | Runtime, Completion | Any number |
| Type | Type of administration operation, such as create, delete, drop, backup, restore, analyze, and ycopy. These values correspond to the type values in the sys.query and sys.log_query views. | Prepare, Runtime, Completion | Select from the drop-down list.Note: You can define rules for INSERT INTO...SELECT statements but not for INSERT INTO...VALUES statements. INSERT INTO...VALUES operations are not executed by the backend database. See also Managing the Row Store. |
| User Name | Database user name | Prepare, Runtime, Completion | Select from the drop-down list. |
| User Roles | Database roles (in which a user has membership) | Prepare, Runtime, Completion | Select from the drop-down list. |
Parent topic:Creating Rules