Using Query Filters

Scenarios

During the development and O&M of enterprise-level database services, the quality of SQL statements directly determines the database stability, data security, and response efficiency of service systems.

As business grows, development team members change, and business scenarios become more complex, non-standard, low-performance, and high-risk poor-quality SQL statements frequently appear and can hardly be avoided through manual review.

1. Due to unfamiliarity with underlying database principles or urgent service iteration, Developers may write SQL statements that involve excessive associated tables, full table scans without indexes, large transaction queries, or unauthorized access.
2. When such SQL statements are executed, they can cause a sharp increase in database CPU or I/O usage, query response timeout, and disruptions to normal business processes. In severe cases, database tables may be locked, transactions may be blocked, and data may be leaked or deleted by mistake, causing irreversible loss.
3. Traditional post-event checks (such as SQL slow query log analysis) detect performance issues caused by slow SQL statements only after they have occurred, making it impossible to prevent risks.
4. Manual code review cannot cover all scenarios and is inefficient and prone to omission especially in multi-team collaboration and high-frequency iteration.

DWS query filters can address these pain points. This pre-emptive SQL control mechanism allows O&M personnel or DBAs to identify the core characteristics of poor-quality SQL statements in advance (such as the number of table joins, SQL types, execution duration threshold, and permission scope). These characteristics can be abstracted into feasible filtering rules using DDL statements and stored in the database.

When a service system submits an SQL statement for execution, the database first triggers the filtering rule verification. Requests that match the characteristics of poor-quality SQL statements are directly intercepted, and an error message is returned. Only valid SQL statements that pass the verification can enter the execution process, thereby implementing proactive SQL quality control.

Notes and Constraints

This function is supported only by clusters of version 9.1.0.100 or later.

Prerequisites

If you cannot create a query filtering rule as a common user, you need to grant the user required permissions as system administrator dbadmin using the following syntax. You are advised to narrow down the application scope when creating a query filtering rule to improve performance.

1

GRANT gs_role_block TO user;

Creating Query Filtering Rules

You can create query filtering rules in either of the following ways:

• Console: See Using DWS Query Filters to Intercept Slow SQL Statements.
• DDL syntax: The syntax format is as follows. For details, see CREATE BLOCK RULE.

  1 2 3 4 5 6

  CREATE BLOCK RULE [ IF NOT EXISTS ] block_name [ [ TO user_name@'host' ] | [ TO user_name ] | [ TO 'host' ] ] | [ FOR UPDATE | SELECT | INSERT | DELETE | MERGE ] | FILTER BY { SQL ( 'text' ) | TEMPLATE ( template_parameter = value ) } [ WITH ( { with_parameter = value }, [, ... ] ) ];

  For example, during service development, if you want to disable join queries for more than two tables, you can use the following DDL statements to create filtering rules:

  1 2 3 4

  CREATE TABLE test_block_rule1 (c1 int,c2 int); CREATE TABLE test_block_rule2 (c1 int,c2 int); CREATE TABLE test_block_rule3 (c1 int,c2 int); CREATE BLOCK RULE forbid_2_t_sel FOR SELECT FILTER BY SQL('test_block_rule') WITH(table_num='2');

  table_num indicates the number of tables in a statement. In this case, no statements can be used to query more than two tables.

  1 2 3

  ----Direct join query of two tables, which can be executed properly SET enable_fast_query_shipping=off; SELECT * FROM test_block_rule1 t1 JOIN test_block_rule2 t2 ON t1.c1=t2.c2;

  

  1 2	----Direct join query of three tables, which is intercepted SELECT * FROM test_block_rule1 t1 JOIN test_block_rule2 t2 ON t1.c1=t2.c2 JOIN test_block_rule3 t3 ON t2.c1=t3.c1;

  

  All options support secondary changes. To remove the restrictions on some fields, you can specify the default keyword. The following is an example:

  1 2 3 4

  --Change the value to allow query of only one table. ALTER BLOCK RULE forbid_2_t_sel with(table_num='1'); SELECT * FROM test_block_rule1 t1 JOIN test_block_rule2 t2 ON t1.c1=t2.c2; SELECT * FROM test_block_rule1 t1;

  

  1 2 3 4

  --Remove the restrictions on the number of tables that can be queried. ALTER BLOCK RULE forbid_2_t_sel with(table_num=default); --An error is reported for the next query. SELECT * FROM test_block_rule1 t1;

  

Filtering Query Results Using Keywords

CREATE BLOCK RULE bl1
To block_user
FOR SELECT
FILTER BY SQL ('tt')
WITH(partition_num='2',
     table_num='1',
     estimate_row='5'
     );

SELECT * FROM tt;
ERROR:  hit block rule bl1(user_name: block_user, block_type: SELECT, regexp_sql: tt, partition_num: 2(3), table_num: 1(1), estimate_row: 5(1))

The preceding query statement contains the tt keyword, and more than two partitions are scanned. The statement is filtered and intercepted. Note that the number of scanned partitions is not always accurate. Only the number of statically pruned partitions can be identified. Dynamic pruning during execution cannot be identified.

When using keywords for filtering, you can first use regular expression matching operator ~* for testing. Regular expression matching is case-insensitive.

In addition, rules of the query filter are directly applied to user block_user. When you delete this user, there will be a prompt indicating that there are dependencies. In this case, you can add CASCADE to the end of the statement to delete the user. The query filter rules applied to this user will also be deleted.

A query filter rule is matched when with_parameter matches any item and the characteristics of other fields also meet requirements.

Some fields may not be intercepted as expected in different plans. The following is an example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

gaussdb=# CREATE BLOCK RULE test FILTER BY sql('test')WITH(estimate_row='3');
CREATE BLOCK RULE
gaussdb=# SELECT * FROm test;
 c1 | c2
----+----
  1 |  2
  1 |  2
  1 |  2
  1 |  2
  1 |  2
(5 rows)

In this case, the statement keyword is matched, and the number of rows queried exceeds the upper limit of three rows, but the statement cannot be intercepted.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

gaussdb=# EXPLAIN VERBOSE SELECT * FROM test;
                                          QUERY PLAN
-----------------------------------------------------------------------------------------------
  id |                  operation                   | E-rows | E-distinct | E-width | E-costs
 ----+----------------------------------------------+--------+------------+---------+---------
   1 | ->  Data Node Scan on "__REMOTE_FQS_QUERY__" |      0 |            |       0 | 0.00

      Targetlist Information (identified by plan id)
 --------------------------------------------------------
   1 --Data Node Scan on "__REMOTE_FQS_QUERY__"
         Output: test.c1, test.c2
         Node/s: All datanodes (node_group, bucket:16384)
         Remote query: SELECT c1, c2 FROM public.test

This is a fast_query_shipping (FQS) plan without estimation information, so the statement cannot be intercepted. A CN lightweight plan will cause the same result. A statement that is forced to use the stream plan can be intercepted.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

gaussdb=# SET enable_stream_operator=on;
SET
gaussdb=# SET enable_fast_query_shipping=off;
SET
gaussdb=# SELECT * FROM test;
ERROR:  hit block rule test(regexp_sql: test, estimate_row: 3(5))
gaussdb=#  EXPLAIN VERBOSE SELECT * FROM test;
                                       QUERY PLAN
-----------------------------------------------------------------------------------------
  id |               operation                | E-rows | E-distinct | E-width | E-costs
 ----+----------------------------------------+--------+------------+---------+---------
   1 | ->  Row Adapter                        |      5 |            |       8 | 69.00
   2 |    ->  Vector Streaming (type: GATHER) |      5 |            |       8 | 69.00
   3 |       ->  CStore Scan on public.test   |      5 |            |       8 | 59.01

      Targetlist Information (identified by plan id)
 --------------------------------------------------------
   1 --Row Adapter
         Output: c1, c2
   2 --Vector Streaming (type: GATHER)
         Output: c1, c2
         Node/s: All datanodes (node_group, bucket:16384)
   3 --CStore Scan on public.test
         Output: c1, c2
         Distribute Key: c1

In conclusion, if the estimation information is inaccurate, false interception or missing interception may occur. Plan information is obtained through estimation, so this error cannot be avoided.

Filtering Query Results Using Normalized Statement Feature Values

Normalized statement feature values include unique_sql_id and sql_hash. Both of them are obtained through hash calculation on the query tree. The former is a 64-bit hash value, and the latter is an MD5 value. The former has a higher probability of repetition than the latter. It is recommended that you use sql_hash for filtering.

To obtain the two values, use either of the following methods:

Method 1: View the explain result.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

gaussdb=> EXPLAIN VERBOSE SELECT * FROM tt WHERE a>1;
                                              QUERY PLAN
 ----------------------------------------------------------------------------------------------------
   id |                     operation                     | E-rows | E-distinct | E-width | E-costs
  ----+---------------------------------------------------+--------+------------+---------+---------
    1 | ->  Row Adapter                                   |      1 |            |       8 | 16.00
    2 |    ->  Vector Streaming (type: GATHER)            |      1 |            |       8 | 16.00
    3 |       ->  Vector Partition Iterator               |      1 |            |       8 | 6.00
    4 |          ->  Partitioned CStore Scan on public.tt |      1 |            |       8 | 6.00

    Predicate Information (identified by plan id)
  -------------------------------------------------
    3 --Vector Partition Iterator
          Iterations: 3
    4 --Partitioned CStore Scan on public.tt
          Filter: (tt.a > 1)
          Pushdown Predicate Filter: (tt.a > 1)
          Partitions Selected by Static Prune: 1..3

  Targetlist Information (identified by plan id)
  ----------------------------------------------
    1 --Row Adapter
          Output: a, b
    2 --Vector Streaming (type: GATHER)
          Output: a, b
          Node/s: datanode1
    3 --Vector Partition Iterator
          Output: a, b
    4 --Partitioned CStore Scan on public.tt
          Output: a, b

               ====== Query Summary =====
  -----------------------------------------------------
  Parser runtime: 0.029 ms
  Planner runtime: 0.286 ms
  Unique SQL Id: 2229243778
  Unique SQL Hash: sql_aae71adfaa3d91bfe75499d92ad969e8
 (34 rows)

Method 2: View the TopSQL record.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

queryid                     | 95701492082350773
 query                       | select * from tt where a>10;
 query_plan                  | 1 | Row Adapter  (cost=14.00..14.00 rows=1 width=8)
                             | 2 |  ->Vector Streaming (type: GATHER)  (cost=0.06..14.00 rows=1 width=8)
                             | 3 |   ->Vector Partition Iterator  (cost=0.00..4.00 rows=1 width=8)
                             |   |     Iterations: 2
                             | 4 |    ->Partitioned CStore Scan on public.tt  (cost=0.00..4.00 rows=1 width=8)
                             |   |      Filter: (tt.a > 10)
                             |   |      Pushdown Predicate Filter: (tt.a > 10)
                             |   |      Partitions Selected by Static Prune: 2..3
 node_group                  | installation
 pid                         | 139803379566936
 lane                        | fast
 unique_sql_id               | 2229243778
 session_id                  | 1732413324.139803379566936.coordinator1
 min_read_bytes              | 0
 max_read_bytes              | 0
 average_read_bytes          | 0
 min_write_bytes             | 0
 max_write_bytes             | 0
 average_write_bytes         | 0
 recv_pkg                    | 2
 send_pkg                    | 2
 recv_bytes                  | 3297
 send_bytes                  | 57
 stmt_type                   | SELECT
 except_info                 |
 unique_plan_id              | 0
 sql_hash                    | sql_aae71adfaa3d91bfe75499d92ad969e8

Both methods can be used to obtain the normalized feature values of the two statements. The explain method can obtain the values in advance, while the TopSQL method can obtain them after the statements are executed.

Note that changes in the conditions of the statements do not affect the normalized feature values, because the impact of constants is removed during normalization. In the above example, the constant values in the conditions of the two statements are different, but the normalized feature values are the same.

Example of the Circuit Breaker Mechanism

Ensure that the CCN is running properly. A user-defined exception rule or default exception rules are in effect. query_exception_count_limit is set to a value greater than or equal to 0.

Viewing the Filtering Time and Interception Records

You can set the GUC parameter analysis_options to view the time required for executing normal statements with query filtering rules.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

SET analysis_options='on(BLOCK_RULE)';

-- explain performance + query

                    User Define Profiling
-----------------------------------------------------------------
Segment Id: 3  Track name: Datanode build connection
      datanode1 (time=0.288 total_calls=1 loops=1)
      datanode2 (time=0.301 total_calls=1 loops=1)
      datanode3 (time=0.321 total_calls=1 loops=1)
      datanode4 (time=0.268 total_calls=1 loops=1)
Segment Id: 3  Track name: Datanode wait connection
      datanode1 (time=0.016 total_calls=1 loops=1)
      datanode2 (time=0.038 total_calls=1 loops=1)
      datanode3 (time=0.021 total_calls=1 loops=1)
      datanode4 (time=0.017 total_calls=1 loops=1)
Segment Id: 1  Track name: block rule check time
      coordinator1 (time=0.028 total_calls=1 loops=1)

After a query filtering rule is created, many bad SQL statements are intercepted. You can view the intercepted SQL statements using TopSQL. abort_info records the interception information, that is, the query error information.

1
2
3
4
5

gaussdb=# SELECT abort_info,query from GS_WLM_SESSION_INFO WHERE abort_info LIKE '%hit block rule test%';
                        abort_info                         |        query
-----------------------------------------------------------+---------------------
 hit block rule test(regexp_sql: test, estimate_row: 3(5)) | select * from test;
(1 rows)

Concurrency Control

When creating a query filtering rule, you can set max_active_num to control the concurrency of statements that match the filtering rule. Note that the maximum value of max_active_num is 1,000.

gaussdb=# CREATE BLOCK RULE bl1 filter by sql('t1') WITH (max_active_num='2');
CREATE BLOCK RULE
-- \parallel Simulate a concurrency scenario.
gaussdb=# \parallel on
Parallel is on with scale default 1024.
gaussdb=# SELECT * FROM t1;
gaussdb=# SELECT * FROM t1;
gaussdb=# SELECT * FROM t1;
gaussdb=# SELECT * FROM t1;
gaussdb=# \parallel
Parallel is off.
ERROR:  hit block rule bl1(regexp_sql: t1, max_active_num: 2)
ERROR:  hit block rule bl1(regexp_sql: t1, max_active_num: 2)
 a
---
(0 rows)
 a
---
(0 rows)

A query filtering rule with a concurrency limit of 2 is created. Statements that exceed the maximum concurrency will be intercepted. Concurrency control applies only to the main statement and does not limit the concurrency of sub-statements.

Learn More

• CREATE BLOCK RULE
• Using DWS Query Filters to Intercept Slow SQL Statements