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

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.

Parent topic:Rule Examples