polar_sql_mapping - PolarDB - Alibaba Cloud Documentation Center

In scenarios where an application sends an incorrect SQL statement and the statement cannot be immediately modified, you can configure the SQL statement mapping feature to map the incorrect SQL statement to a correct statement. After you configure the SQL statement mapping feature, an SQL statement can be mapped to the correct SQL statement even if the SQL statement sent by the application is incorrect.

Precautions

After you configure the SQL statement mapping feature, all SQL statements that match the specified strings are mapped to the specified SQL statement.

Prerequisites

Make sure that shared_preload_libraries in the Postgresql.conf configuration file contains polar_sql_mapping. By default, this configuration is loaded. If this configuration is not loaded, restart your database.
Make sure that the polar_sql_mapping plug-in is created for your database. By default, this plug-in is created automatically for newly created databases. For previous databases, you still need to manually create the plug-in.
To manually create the plug-in, run the following commands:
```
set default_with_rowids to off;
create extension polar_sql_mapping;
```

Usage

Set the polar_sql_mapping.use_sql_mapping parameter to enable the SQL statement mapping feature. By default, this parameter is set to off.
```
alter system set polar_sql_mapping.use_sql_mapping=on;
select pg_reload_conf();
```
Set the polar_sql_mapping.record_error_sql parameter to enable automatic recording of incorrect SQL statements. By default, this parameter is set to off.
```
alter system set polar_sql_mapping.record_error_sql=on;
select pg_reload_conf();
```
Note To minimize the impact on performance, we recommend that you disable the recording of incorrect SQL statements if you no longer need to use the SQL statement mapping feature.
```
alter system set polar_sql_mapping.record_error_sql=off;
select pg_reload_conf();
```
Query incorrect SQL statements.
```
select * from emp;
```
A similar output is displayed:
```
ERROR:  relation "emp" does not exist
```

After incorrect SQL statements are recorded in polar_sql_mapping.error_sql_info, run the following command to query the details of polar_sql_mapping.error_sql_info:

select * from polar_sql_mapping.error_sql_info ;

A similar output is displayed:

id  |       query        |           emessage            | calls 
----+--------------------+-------------------------------+-------
  1 | select * from emp; | relation "emp" does not exist |     1

Establish a mapping to map the SQL statement whose ID is 1 in error_sql_info to the specified SQL statement.
```
select polar_sql_mapping.insert_mapping_id(1, ' select 1');
```

Query the incorrect SQL statements again.

select * from emp;

A similar output is displayed:

 ?column? 
----------
        1
(1 row)

Note Most applications use PreparedStmt. When you write a new SQL statement, use placeholders $n instead of '?'. Example:

select * from polar_sql_mapping.polar_sql_mapping_table ;

A similar output is displayed:

id |            source_sql            |           target_sql
----+----------------------------------+---------------------------------
4 | select 1 from dual where a = $1; | select 1 from dual where 1 = $1
(1 row)

Parameters


Parameter	Description
polar_sql_mapping.max_num	Used to control the maximum number of distinct SQL statements that can be recorded. Default value: 10. Distinct SQL statements in excess of the specified number are ignored. To change the specified value, restart your database.
polar_sql_mapping.error_sql_info_clear()	Used to clear recorded incorrect SQL statements.

Replace SQL statements that run normally

To use the SQL statement mapping feature to replace a SQL statement that runs normally, perform the following operations:

Set the polar_sql_mapping.use_sql_mapping parameter. Enable the SQL statement mapping feature. Default value: off.
```
alter system set polar_sql_mapping.use_sql_mapping=on;
select pg_reload_conf();
```
Set the polar_sql_mapping.record_error_sql parameter. Enable automatic recording of incorrect SQL statements. Default value: off.
```
alter system set polar_sql_mapping.record_error_sql=on;
select pg_reload_conf();
```
Note To minimize the impact on performance, we recommend that you disable the recording of incorrect SQL statements if you no longer need to use the SQL statement mapping feature.
```
alter system set polar_sql_mapping.record_error_sql=off;
select pg_reload_conf();
```
Configure the match node. All SQL statements that match the match mode are stored in polar_sql_mapping.error_sql_info.
The following example shows that SQL statements that contain test_table are recorded (the match logic is consistent with that of the LIKE operator in SQL syntax).
1. Configure the match mode.
```
set polar_sql_mapping.error_pattern to '%test_table%';
```
2. Enable the match mode.
```
set polar_sql_mapping.record_error_sql to true;
```
3. Create the SQL statements to record.
```
select * from test_table;

select a from test_table;

select max(a) from test_table;
```
4. View the results recorded and stored in polar_sql_mapping.error_sql_info.
```
select * from polar_sql_mapping.error_sql_info;
```
  A similar output is displayed:
```
id  |             query              |          emessage          | calls
----+--------------------------------+----------------------------+-------
  1 | select * from test_table;      | Error Pattern Force Record |     1
  2 | select a from test_table;      | Error Pattern Force Record |     1
  3 | select max(a) from test_table; | Error Pattern Force Record |     1
(3 rows)
```
  All SQL statements that contain test_table are recorded and stored in polar_sql_mapping.error_sql_info.
Replace the recorded SQL statements with the specified SQL statement.
```
select polar_sql_mapping.insert_mapping_id(x, 'text');
```
Note In the preceding statement, x indicates the ID of the correct statement, and text indicates the text of the SQL statement with which you want to replace the recorded SQL statement. Replace SQL statements based on your actual conditions.
To prevent performance degradation, restore the configurations when you no longer need to use the SQL statement mapping feature.
```
reset polar_sql_mapping.error_pattern;
```