Submit Rule Examples ​

You can use a submit rule to take actions at the very beginning of the query lifecycle. You can throttle concurrency and abort queries early on.

Abort Queries Based on Type and User ​

The following example, submit_abort_truncate, is a rule that immediately aborts TRUNCATE commands when they are attempted by users in a backup group (users named backup1, backup2, and so on).

The rule logic is defined as follows:

Rule Preview:
if (w.type === 'truncate table' &&
   (String(w.user).indexOf('backup') >= 0)) {
	w.abort('Backup users cannot truncate tables!');
}

In ybsql, user backup1 sees the following error when attempting a TRUNCATE:

yellowbrick=# \c premdb backup1
Password for user backup1: 
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
You are now connected to database "premdb" as user "backup1".
premdb=> truncate matchstats;
ERROR:  canceling statement due to user request

In the SMC Rule Processing screen, the administrator sees:

Note that the search field is used here to isolate the TRUNCATE command that caused the rule to be applied.

Submit Rule That Throttles Concurrency ​

The following example, submit_throttle_dml, is a rule that limits concurrency to 1 for each user in a backup group (users named backup1, backup2, and so on) when they attempt to run INSERT, UPDATE, or DELETE statements.

The rule logic is defined as follows:

Rule Preview:
if ((String(w.user).indexOf('backup') >= 0) &&
   w.type === 'insert' ||
   w.type === 'update' ||
   w.type === 'delete') {
	wlm.throttle(1);
}

Note the use of AND (&&) and OR (||) conditions in this rule. If user backup2 tries to run concurrent INSERT, UPDATE, or DELETE statements, the system will throttle those operations and run them serially. The administrator will see the throttle rule in effect:

Parent topic:Rule Examples