使用数据库方言sql_dialect插件实现通用语法和功能转换

绑定方言插件

1. sql_dialect插件已经正确安装部署。
2. 自动执行插件中的sql_dialect_mysql.sql文件，并确认在__mysql__的schema下有插件自带函数。当前会话可直接使用这些插件函数。
3. 执行以下命令绑定方言插件。

   1	ALTER DATABASE database_name SET sql_dialect='mysql';

4. 发送信号，通知其它在线会话优先使用__mysql__的schema下的函数，优先级高于pg_catalog。

   1	SELECT pg_reload_conf();

5. 使用PG_EXTENSION系统表查询自动安装的sql_dialect插件。

   1 2 3 4 5

   SELECT * FROM pg_extension WHERE extname='sql_dialect'; extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition -------------------+----------+--------------+----------------+------------+-----------+-------------- sql_dialect | 10 | 11 | f | 1.0.0 | | (1 row)

6. 使用PG_NAMESPACE系统表查询自动创建的schema。

   1 2 3 4 5

   SELECT * FROM pg_namespace WHERE nspname='__dialect_mysql__'; nspname | nspowner | nsptimeline | nspacl | permspace | usedspace | nsptype -------------------+----------+-------------+--------+-----------+-----------+--------- __dialect_mysql__ | 10 | 0 | | -1 | 0 | i (1 row)

7. 使用PG_PROC系统表查询方言插件自带的函数，方言插件自带函数的选取优先级会高于数据库内核函数，且用户使用时无需指定schema。

   1 2 3 4

   SELECT proname,nspname,prosrc FROM pg_proc,pg_namespace n WHERE pronamespace=n.oid and nspname='__dialect_mysql__'; proname | nspname | prosrc ---------------+-------------------+--------------------------------------------- rlike | __dialect_mysql__ | select $1 ~ $2

卸载方言插件

使用DROP EXTENSION卸载当前数据库的方言插件。

1

DROP EXTENSION sql_dialect;

升降级方言插件

1. 确认当前数据库支持的扩展版本。

   1	SELECT * FROM pg_extension;

2. 升级扩展到最新版本。

   1	ALTER EXTENSION extension_name UPDATE;

3. 升级扩展到指定版本。

   1	ALTER EXTENSION extension_name UPDATE TO 'x.x.x';

函数属性

可以参考CREATE FUNCTION章节了解函数的属性及函数是否下推。

inline优化提高性能

inline优化，是数据库查询优化器的一项功能，类似C++中的inline能力，如果是简短的计算或转换函数，且符合inline优化条件时，优化器会将函数调用优化为表达式执行，避免函数调用的额外开销，显著提升执行性能。

inline优化示例：

1
2
3

CREATE FUNCTION func_add_sql(integer, integer) RETURNS integer
AS 'select $1 + $2;'
LANGUAGE SQL IMMUTABLE;

该函数执行加法操作，符合inline优化条件，优化器可将其替换为表达式“$1 + $2”。

执行计划显示Output字段为优化后的表达式 (a + b)，而非函数调用。

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

EXPLAIN VERBOSE SELECT func_add_sql(a, b) FROM t1;
                                        QUERY PLAN                                        
------------------------------------------------------------------------------------------
  id |          operation           | E-rows | E-distinct | E-memory | E-width | E-costs 
 ----+------------------------------+--------+------------+----------+---------+---------
   1 | ->  Streaming (type: GATHER) |      1 |            |          |       8 | 9.01    
   2 |    ->  Seq Scan on public.t1 |      1 |            | 1MB      |       8 | 1.01    

      Targetlist Information (identified by plan id)     
 --------------------------------------------------------
   1 --Streaming (type: GATHER)
         Output: ((a + b))
         Node/s: All datanodes (node_group, bucket:16384)
   2 --Seq Scan on public.t1
         Output: (a + b)

入参含NULL优化

入参含NULL返回值也肯定为NULL的函数，要显示定义STRICT属性。这样当入参含NULL时，可以减少函数体的调用执行。

函数类型选择

为提升性能与稳定性，建议按以下顺序选择函数类型：SQL函数 > C函数 > plpgsql。

函数内设置GUC变量优化

尽量避免在函数内部设置环境变量，造成不必要的通信开销。

1
2
3
4
5
6
7
8

CREATE OR REPLACE FUNCTION func_increment_plsql(i integer) RETURNS integer
SET autoanalyze=off --这里执行，不会跨CN进行设置
AS $$
BEGIN
SET autoanalyze=off; --这里执行，会跨CN进行设置，有通信开销
RETURN i + 1;
END;
$$ LANGUAGE plpgsql;

VOLATILE的SQL函数优化

VOLATILE属性的SQL语言函数会频繁访问GTM，导致性能较差，且给GTM造成较大的压力，避免将SQL函数定义为VOLATILE属性。

其它注意事项

• 数据类型

  函数参数的类型和返回值类型，要严格校验，避免类型转换引发集群异常或结果集错误。例如以下场景：

  • 应返回timestamptz，而返回了text。结果表面显示一致，但无法参与时区转换或时间计算。
  • 应返回timestamptz，而返回了timestamp，丢失失时区信息，导致时间计算偏差。
  • 不兼容类型的强制转换，导致C函数调用报错。
  • 调用内存C函数时，需要传入timestamptz类型，却传入timestamp类型，导致时区转换错误。
• 函数重载

  对于能通过能自动隐式类型转换的类型（如text、timestamp到timestamptz），优先定义一个统一函数，避免冗余实现。

• 函数开发

  推荐使用DWS原生函数定义新函数。因为兼容性函数可能因为版本更新等兼容性问题导致新函数结果集不稳定。

• 函数变更

  函数参数个数和类型，一旦定义，不允许变更，避免用户业务逻辑报错。

  函数行为也禁止前后不兼容的变更，如果必须要变更，需通过数据库GUC参数控制，避免与数据库依赖冲突。

函数开发示例

新函数需添加至dialects目录对应的方言文件中，同时还要在升级脚本中再进行处理。