Redirect SQL Queries via polar_sql_mapping Without Code Changes - PolarDB

When an application sends a SQL statement that fails or uses outdated syntax, modifying application code is not always an option. The polar_sql_mapping plug-in lets you redirect those statements to correct replacements at the database level, with no application changes required.

How it works

The plug-in uses three objects that form a pipeline:

Object	Role
`polar_sql_mapping.error_sql_info`	Captures statements that failed or matched a pattern. Acts as the input queue for building mapping rules.
`polar_sql_mapping.insert_mapping_id(id, text)`	Creates a mapping rule from a captured statement to a replacement SQL.
`polar_sql_mapping.polar_sql_mapping_table`	Stores active mapping rules. The plug-in reads this table at query time to rewrite matching statements.

Data flows in one direction: statements are captured into error_sql_info, you create rules from those entries using insert_mapping_id(), and the plug-in applies those rules from polar_sql_mapping_table going forward.

After you create a mapping, every statement that matches the source pattern is redirected — not just the one you originally recorded. Review your mappings carefully before enabling them in a production environment.

Prerequisites

Before you begin, make sure that:

shared_preload_libraries in postgresql.conf contains polar_sql_mapping. This is loaded by default. If it is not loaded, restart your database.
The polar_sql_mapping extension exists in your database. It is created automatically for new databases. For existing databases, create it manually.

To create the extension manually, run:

set default_with_rowids to off;
create extension polar_sql_mapping;

Map a statement that returns an error

Use this workflow when an application sends a statement that returns an error and you cannot modify the application code immediately.

Step 1: Enable SQL mapping and error recording

alter system set polar_sql_mapping.use_sql_mapping = on;
select pg_reload_conf();

alter system set polar_sql_mapping.record_error_sql = on;
select pg_reload_conf();

Disable record_error_sql when you no longer need to capture statements, to reduce performance overhead.

alter system set polar_sql_mapping.record_error_sql = off;
select pg_reload_conf();

Step 2: Trigger the failing statement

Run the statement that fails. For example:

select * from emp;

Expected output:

ERROR:  relation "emp" does not exist

Step 3: Find the recorded statement

Query error_sql_info to find the 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

Step 4: Create the mapping

Pass the statement ID and the replacement SQL to insert_mapping_id():

select polar_sql_mapping.insert_mapping_id(1, 'select 1');

This creates a rule in polar_sql_mapping_table that redirects select * from emp to select 1.

Step 5: Verify the mapping

Run the original statement again. The plug-in now executes the mapped replacement:

select * from emp;

Expected output:

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

To view all active mapping rules:

select * from polar_sql_mapping.polar_sql_mapping_table;

Map a statement that runs without errors

Use this workflow when you need to redirect a statement that executes successfully but returns incorrect results, or when you want to capture a specific pattern of statements for replacement.

Step 1: Enable SQL mapping and error recording

alter system set polar_sql_mapping.use_sql_mapping = on;
select pg_reload_conf();

alter system set polar_sql_mapping.record_error_sql = on;
select pg_reload_conf();

Step 2: Set a match pattern

Set polar_sql_mapping.error_pattern to a LIKE pattern. Statements that match are recorded in error_sql_info with the message Error Pattern Force Record.

set polar_sql_mapping.error_pattern to '%test_table%';
set polar_sql_mapping.record_error_sql to true;

Step 3: Run the statements you want to capture

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

Step 4: View the captured statements

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)

Step 5: Create the mappings

For each captured statement, call insert_mapping_id() with the statement's ID and the replacement SQL:

select polar_sql_mapping.insert_mapping_id(x, 'text');

In the preceding statement, x indicates the ID of the statement you want to replace, and text indicates the SQL statement with which you want to replace it. Repeat for each ID you want to map.

Step 6: Clear the match pattern

After creating your mappings, clear the pattern to stop capturing additional statements:

reset polar_sql_mapping.error_pattern;

Manage mapping rules

View active rules

select * from polar_sql_mapping.polar_sql_mapping_table;

Clear all captured statements

To remove all entries from error_sql_info:

select polar_sql_mapping.error_sql_info_clear();

Prepared statements

Most applications use prepared statements with parameter placeholders. When the source statement contains a parameter placeholder, the plug-in stores it as $1, $2, and so on in PostgreSQL format.

When writing the replacement SQL, use the same PostgreSQL-style placeholders ($1, $2, etc.) to match the corresponding parameter positions in the incoming prepared statement.

To see how a mapping is stored for a prepared statement:

select * from polar_sql_mapping.polar_sql_mapping_table;

Example output:

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

The $1 placeholder in the source pattern matches the first parameter position in the incoming prepared statement.

Parameters and functions

Parameters

Parameter	Description	Default
`polar_sql_mapping.use_sql_mapping`	Enables or disables the SQL mapping feature.	`off`
`polar_sql_mapping.record_error_sql`	Enables or disables automatic recording of statements that fail or match `error_pattern`.	`off`
`polar_sql_mapping.max_num`	Maximum number of distinct statements that can be recorded. Statements beyond this limit are ignored. Changing this value requires a database restart.	`10`
`polar_sql_mapping.error_pattern`	A LIKE pattern for capturing statements that run without errors. Matching statements are recorded with `emessage = 'Error Pattern Force Record'`.	—

Functions

Function	Description
`polar_sql_mapping.insert_mapping_id(id, text)`	Creates a mapping rule from the statement with the given ID in `error_sql_info` to the SQL specified by `text`.
`polar_sql_mapping.error_sql_info_clear()`	Removes all entries from `error_sql_info`.