polar_sql_mapping-PolarDB(PolarDB)-阿里云帮助中心

polar_sql_mapping is a PolarDB for PostgreSQL plug-in that rewrites SQL statements at the database layer—without touching your application code. When an application sends an incorrect or unwanted SQL statement, you define a mapping rule that transparently replaces it with the correct statement every time it runs.

Example scenario: Your application queries a table that has been renamed. You cannot redeploy the application immediately, so you configure polar_sql_mapping to redirect the old query to the new table name. The application continues to work while you plan the code update.

Prerequisites

Before you begin, make sure that:

The polar_sql_mapping plug-in is installed in your database. Newly created databases include it by default.

For older databases, install the plug-in manually by running:

set default_with_rowids to off;
create extension polar_sql_mapping;

Usage notes

Review these constraints before configuring mapping rules:

Mapping scope: All SQL statements that match a configured rule are rewritten. Set up rules precisely to avoid unintended rewrites.
Maximum rules: By default, polar_sql_mapping.max_num limits recording to 10 distinct SQL statements. Statements beyond this limit are ignored. Changing this value requires a database restart.
Prepared statements: If your application uses prepared statements, write the target SQL with $n positional placeholders—not ?. For example: select 1 from dual where 1 = $1.
Performance: Disable polar_sql_mapping.record_error_sql when you no longer need to record SQL statements. Leaving it enabled has a measurable performance impact.

Map a failing SQL statement

Use this workflow when an SQL statement fails at runtime and you want to redirect it to a working statement.

Enable the SQL statement mapping feature:

alter system set polar_sql_mapping.use_sql_mapping = on;

Enable automatic recording of failing SQL statements:

alter system set polar_sql_mapping.record_error_sql = on;

Run the failing statement so that polar_sql_mapping records it:
```
select * from emp;
```
Expected output:
```
ERROR:  relation "emp" does not exist
```

Query polar_sql_mapping.error_sql_info to find the recorded statement and its ID:

select * from polar_sql_mapping.error_sql_info;

Expected output:

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

Create a mapping rule using the statement ID and the target SQL:
```
select polar_sql_mapping.insert_mapping_id(1, ' select 1');
```
Run the original failing statement again to verify the mapping is active:
```
select * from emp;
```
Expected output:
```
 ?column?
----------
         1
(1 row)
```
After you confirm the mapping works, disable recording to avoid performance impact:
```
alter system set polar_sql_mapping.record_error_sql = off;
```

Map a statement that runs normally

Use this workflow when you want to rewrite a statement that executes without errors—for example, to redirect queries from one table to another.

Enable the SQL statement mapping feature:

alter system set polar_sql_mapping.use_sql_mapping = on;

Enable recording:

alter system set polar_sql_mapping.record_error_sql = on;

Set a match pattern to capture statements that contain a specific string. The pattern uses the same syntax as the SQL LIKE operator. The following example captures all statements that reference test_table:
```
set polar_sql_mapping.error_pattern to '%test_table%';
```

Enable the match mode:

set polar_sql_mapping.record_error_sql to true;

Run the statements you want to rewrite:

select * from test_table;
select a from test_table;
select max(a) from test_table;

Query polar_sql_mapping.error_sql_info to confirm the statements were recorded:

select * from polar_sql_mapping.error_sql_info;

Expected output:

 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)

The emessage value Error Pattern Force Record indicates a statement was captured by the match pattern, not an actual error.

Create a mapping rule for each recorded statement. Replace <id> with the statement ID and <target_sql> with the SQL statement you want to run instead:
```
select polar_sql_mapping.insert_mapping_id(<id>, '<target_sql>');
```

Reset the match pattern and disable recording:

reset polar_sql_mapping.error_pattern;
alter system set polar_sql_mapping.record_error_sql = off;

Parameters and functions

Parameters

Parameter	Default	Description
`polar_sql_mapping.use_sql_mapping`	`off`	Enables or disables SQL statement mapping.
`polar_sql_mapping.record_error_sql`	`off`	Enables or disables automatic recording of SQL statements.
`polar_sql_mapping.max_num`	`10`	Maximum number of distinct SQL statements that can be recorded. Statements beyond this limit are ignored. Changing this value requires a database restart.
`polar_sql_mapping.error_pattern`	—	A `LIKE`-style pattern. SQL statements matching this pattern are recorded in `error_sql_info`, regardless of whether they fail.

Functions

Function	Description
`polar_sql_mapping.insert_mapping_id(id, sql_text)`	Creates a mapping rule. `id` is the row ID from `polar_sql_mapping.error_sql_info`, and `sql_text` is the target SQL statement to run instead.
`polar_sql_mapping.error_sql_info_clear()`	Clears all recorded statements from `polar_sql_mapping.error_sql_info`.

Tables

Table	Description
`polar_sql_mapping.error_sql_info`	Stores recorded SQL statements. Columns: `id`, `query`, `emessage`, `calls`.
`polar_sql_mapping.polar_sql_mapping_table`	Stores active mapping rules. Columns: `id`, `source_sql`, `target_sql`.

To view current mapping rules:

select * from polar_sql_mapping.polar_sql_mapping_table;

Expected output:

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