What is the polar_sql_mapping plug-in and what are its basic concepts-PolarDB(PolarDB)-阿里云帮助中心

When an application sends incorrect SQL statements that cannot be immediately fixed, you can use the SQL mapping feature to map the incorrect SQL statements to correct ones. After the SQL mapping feature is configured, incorrect SQL statements sent by the application are automatically mapped to the correct ones.

Precautions

The SQL mapping feature maps all SQL statements that match the specified string to the specified SQL statement. Use this feature with caution.

Prerequisites

Make sure that the polar_sql_mapping plug-in is created in your database. Newly created databases have this plug-in created by default. For existing databases, you must create it manually.

Run the following command to manually create the plug-in.

set default_with_rowids to off;
create extension polar_sql_mapping;

Usage guide

Set the polar_sql_mapping.use_sql_mapping parameter to enable the SQL mapping feature. The default value is off.
```
alter database [dbname] set polar_sql_mapping.use_sql_mapping=on;
```
Set the polar_sql_mapping.record_error_sql parameter to enable the feature of automatically collecting incorrect SQL statements. The default value is off.
```
alter database [dbname] set polar_sql_mapping.record_error_sql=on;
```
Note
After you finish using the SQL mapping feature, we recommend that you disable the error recording feature to reduce the impact on performance.
```
alter database [dbname] set polar_sql_mapping.record_error_sql=off;
```
Query the incorrect SQL statement.
```
select * from emp;
```
The following result is returned:
```
ERROR:  relation "emp" does not exist
```

The incorrect SQL statement is recorded in polar_sql_mapping.error_sql_info. Run the following command to query the detailed information of polar_sql_mapping.error_sql_info.

select * from polar_sql_mapping.error_sql_info ;

The following result is returned:

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

Create a mapping relationship 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 statement again.

select * from emp;

The following result is returned:

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

Note

Most applications use PreparedStmt. When you write new SQL statements, take note that the placeholder must be $n instead of '?'. The following example is provided:

select * from polar_sql_mapping.polar_sql_mapping_table ;

The following result is returned:

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

Parameter description

Parameter	Description
polar_sql_mapping.max_num	Controls the maximum number of distinct incorrect SQL statements that can be collected. SQL statements that exceed the specified number are directly ignored. A restart is required if you want to modify this value. Default value: 10.
polar_sql_mapping.error_sql_info_clear()	Clears the recorded incorrect SQL statements.

Replace an SQL statement that runs normally

If you want to replace an SQL statement that runs without errors, perform the following steps:

Set the polar_sql_mapping.use_sql_mapping parameter to enable the SQL mapping feature. The default value is off.
```
alter database [dbname] set polar_sql_mapping.use_sql_mapping=on;
```
Set the polar_sql_mapping.record_error_sql parameter to enable the feature of automatically collecting incorrect SQL statements. The default value is off.
```
alter database [dbname] set polar_sql_mapping.record_error_sql=on;
```
Note
After you finish using the SQL mapping feature, we recommend that you disable the error recording feature to reduce the impact on performance.
```
alter database [dbname] set polar_sql_mapping.record_error_sql=off;
```

Set a match pattern. All SQL statements that match the pattern are saved to polar_sql_mapping.error_sql_info.

In the following example, all SQL statements that contain test_table are captured (the matching logic is consistent with the LIKE operator in SQL).

Set the match pattern.

set polar_sql_mapping.error_pattern to '%test_table%';

Enable the match pattern.

set polar_sql_mapping.record_error_sql to true;

Create the SQL statements to be captured.

select * from test_table;

select a from test_table;

select max(a) from test_table;

View the capture results in polar_sql_mapping.error_sql_info.

select * from polar_sql_mapping.error_sql_info;

The following result is returned:

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 captured to polar_sql_mapping.error_sql_info.

Replace the incorrect SQL statement with the correct SQL statement.
```
select polar_sql_mapping.insert_mapping_id(x, 'text');
```
Note
In this command, x indicates the ID of the SQL statement, and text indicates the text of the SQL statement. Replace them based on your actual requirements.
After you finish using this feature, perform the restore operation to avoid impact on performance.
```
reset polar_sql_mapping.error_pattern;
```