GaussDB(DWS)通用兼容性插件 - sql_dialect

sql_dialect插件是DWS用于支持各种SQL方言的插件包。

优先级：方言插件中函数优先级高于数据库内核中的函数，便于解决与内核的冲突。
绑定方言：每一个数据库可以独立绑定方言插件，与CREATE DATABASE时选择的DBCOMPATIBILITY没有关联关系，但建议保持一致。
修改方言：方言插件一旦绑定，不支持修改，除非先卸载插件再重新绑定。

插件基础能力

1. 支持简单SQL实现的函数
2. 支持plpgsql实现的函数
3. 支持C实现的函数
4. 支持独立的升降级，与内核升降级无关
5. 可以通过管控面平台，快速发布升级

插件包结构

├── lib
│   └── postgresql
│       └── libsql_dialect.so --插件动态库文件
└── share
    └── postgresql
        └── extension
            ├── sql_dialect.control --插件控制文件
            ├── sql_dialect_mysql.sql --mysql方言支持的对象文件
            ├── sql_dialect--1.0.0.sql
            ├── sql_dialect--1.0.0--1.0.1.sql
            └── sql_dialect--1.0.1--1.0.0.sql

插件使用方法

首先确保sql_dialect插件已经正确安装部署。

绑定方言插件

--为当前数据库绑定方言插件
--1. 自动安装了sql_dialect插件
--2. 自动执行了插件中的sql_dialect_mysql.sql文件，并在__mysql__的schema下有了插件自带函数
--3. 当前会话已可直接使用这些插件函数
alter database xxx set sql_dialect='mysql';

--发送信号，通知其它在线会话优先使用__mysql__的schema下的函数，优先级高于pg_catalog
select pg_reload_conf();

--从pg_extension中可以查询到自动安装的sql_dialect插件
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)

--从pg_namespace中可以查到自动创建的schema
select * from pg_namespace where nspname='__dialect_mysql__';
      nspname      | nspowner | nsptimeline | nspacl | permspace | usedspace | nsptype 
-------------------+----------+-------------+--------+-----------+-----------+---------
 __dialect_mysql__ |       10 |           0 |        |        -1 |         0 | i
(1 row)

--从pg_proc中可以查到方言插件自带的函数，它们的选取优先级会高于内核，且不必用户指定schema使用
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 sql_dialect;

插件升降级

-- 确认当前数据库支持的扩展版本
SELECT * FROM pg_extension;
 
-- 升级扩展到最新版本
ALTER EXTENSION extension_name UPDATE;

-- 升级扩展到指定版本
ALTER EXTENSION extension_name UPDATE TO 'x.y.z';

函数开发指导

函数开发的细节请查询产品文档，这里对特别需要注意的地方重点进行解释。

函数属性介绍

易变性

◾VOLATILE（默认）： 表示函数返回值在一次表扫描内随时可以改变，例如：random, currval, timeofday等。这也导致这些函数不会被做任何优化。

◾STABLE： 表示对相同函数入参，在同一次表扫描里，该函数的返回值不会变，但是在同一会话的不同SQL多次调用时可能会变化。

◾IMMUTABLE： 表示该函数在相同入参时总是返回同样的结果。

也就是说该函数不会通过查询数据库，GUC参数，随机数，当前时间等可能不确定性的因素来生成函数返回值。如果入参是常量，这类函数可以直接被优化器替换为函数值，减少函数调用。

注意：IMMUTABLE函数会被自动下推到DN执行，如果用户错误定义了函数的IMMUTABLE属性，那么可能会导致结果集错误。因此，用户在指定函数的属性为IMMUTABLE的时候，要特别慎重。

不可定义IMMUTABLE的几种情况：

1.函数中引用了表，视图等对象，因为当表的数据发生变化的时候，函数的返回值可能发生变化。
2.函数中引用了STABLE/VOALATILE类型的函数，那么该函数不能定义为IMMUTABLE。
3.函数中有不下推的因素，因为IMMUTABLE意味着要下推到DN执行，与函数体内部的不下推因素相互冲突。典型场景例如，包含不下推的函数、语法等。
4.函数中含有聚合运算，但聚合运算的运算需要生成STREAM计划才能完成计算的（部分结果在DN计算，部分结果在CN计算，例如listagg函数等）。

为了防止错误定义IMMUTABLE可能导致严重问题，数据库内部可以通过设置behavior\_compat\_options=‘check\_function\_conflicts’来开启对函数定义冲突的检查，目前可以识别出上述1和2场景。

下推属性

◾SHIPPABLE，表示该函数可以下推到DN上执行。
◾NOT SHIPPABLE（默认），表示该函数不可以下推到DN上执行。

注意：

    对于IMMUTABLE函数，不管SHIPPABLE如何设置，函数始终可以下推到DN上执行。

    对于STABLE/VOLATILE函数，当且仅当函数属性是SHIPPABLE的时候，函数才可以下推到DN执行。

    定义SHIPPABLE属性时需特别慎重，SHIPPABLE意味着整个函数会下推到DN上执行，如果设置不当，会导致结果错误等严重问题。

与定义IMMUTABLE属性一样，SHIPPABLE属性的定义也有诸多约束，简单来说就是函数体内不能有不可下推的因素，函数下推到单DN执行后，函数内部的计算逻辑仅依赖当前DN的数据集合。举例如下：

1.如果函数内部引用了表，并且表为HASH分布，那么该函数通常不能定义为SHIPPABLE。
2.函数内部有不可下推的因素，函数，语法等，那么该函数不能定义为SHIPPABLE，可参考语句下推调优。
3.函数内部的计算过程可能需要跨DN数据，这种情况该函数通常不能定义为SHIPPABLE，例如一些聚合运算等。

入参含NULL处理

◾CALLED ON NULL INPUT（默认）
表明该函数的某些参数是NULL的时候可以按照正常的方式调用该函数，函数开发者需自己做判空处理。

◾RETURNS NULL ON NULL INPUT或STRICT
如果声明了这个参数，当有NULL值参数时该函数不会被执行；而只是自动返回一个NULL结果。

RETURNS NULL ON NULL INPUT和STRICT的功能相同。
入参含NULL返回值也为NULL的，尽量显示定义STRICT。

保护模式

声明用户定义的C函数是否在保护模式下执行。

◾FENCED模式（默认），则函数在新fork的进程执行，这样函数的异常不会影响CN或者DN进程。
◾NOT FENCED模式，函数的执行在CN或者DN进程中进行；

如何选择：

正在开发或者调试的Function使用FENCED模式。开发测试完成，使用NOT FENCED模式执行，减少fork进程以及通信的开销。
复杂的操作系统操作，例：打开文件，信号处理，线程处理等操作，使用FENCED模式。否则可能影响GaussDB(DWS)数据库的执行。
C函数必须是FENCED模式

执行角色

◾SECURITY INVOKER或AUTHID CURRENT_USER（默认）
表明该函数将带着调用它的用户的权限执行。该参数可以省略。

◾SECURITY DEFINER或AUTHID DEFINER

声明该函数将以创建它的用户的权限执行。

注意：一般默认情况下函数属性均为SECURITY INVOKER。只有特殊场景下才会使用SECURITY DEFINER，比如函数体内有不需要函数调用者关注的敏感数据。

如何写出性能最优的函数

inline优化

类似C++中的inline能力，如果是简短的计算或转换函数，正确的书写为满足inline特征的函数，则优化器可将函数调用优化成表达式执行，明显提升执行性能。

例如：

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

可被优化器自动优化为 ```$1 + $2``` 的表达式。
下面执行计划输出的Output已不再是函数调用，而是优化为(a+b)表达式。

postgres=# 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)

inline函数要求：

1. 函数类型，LANGUAGE必须是SQL类型。
2. 函数易变性，不能是VOLATILE，且易变性不高于函数体内的语句。例如：函数体内调用的函数其易变性为STABLE，则该函数只能是STABLE，不能是IMMUTABLE。从高到底按照IMMUTABLE STABLE VOLATILE排序。
3. 函数strict属性，必须与函数体内包含的函数strict一致。
4. 函数体，函数中必须为简单的单条select语句，不含group by等复杂逻辑。
5. 函数返回值，函数内语句返回类型必须与该函数返回类型一致，且不能是set和record等复杂类型。

入参含NULL优化

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

函数类型选择

从性能方面考虑，尽量按照 **SQL函数** > **C函数** > **plpgsql函数** 顺序选择函数类型。

SQL函数

简单计算优先使用SQL类型，尽量满足inline特征。

C函数

非单条语句的函数建议定义为C函数

申请的内存要用完及放
保护模式必须显示定义为DENCED模式
入参和返回值类型严格注意，避免类型不一致或强制转换导致core或结果集问题。

plpgsql函数

逻辑复杂C函数实现较为困难的可以选择plpgsql类型

函数内设置GUC变量优化

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

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造成比较大的压力。
因此，LANGUAGE为SQL的函数，尽量不要定义为VOLATILE。

其它注意事项

数据类型

函数参数的类型和返回值类型，要严格注意。避免类型转换导致的core或结果集错误。

一些常见错误：

应该返回timestamptz，而返回了text。结果看似一样，实际无法使用。
应该返回timestamptz，而返回了timestamp，丢失了时区，未注意到。
不兼容类型的强制转换，导致C函数调用core
调用内存C函数时，需要传入timestamptz类型，却给了timestamp类型，导致时区转换错误。

函数重载

能自动隐式类型转换的类型，尽量只写一个函数。例如：text和timestamp都可以向timestamptz自动转换，不必每个类型都实现一个相似函数。

函数开发

尽量使用pg原生函数定义新函数。因为兼容性函数可能因为各种兼容性导致后期修改，从而级联导致新函数结果集不稳定。

函数变更

函数参数个数和类型，一旦定义，不允许变更。防止用户业务报错。
函数行为也禁止前后不兼容的变更，如果必须要变，需要在内核加GUC参数进行控制。这样也造成了与内核的依赖。

函数开发样例

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

SQL函数

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

PLSql函数

CREATE OR REPLACE FUNCTION func_increment_plsql(i integer) RETURNS integer 
AS $$  
BEGIN
    RETURN i + 1;
END;
$$ LANGUAGE plpgsql;

PL/pgSQL函数的开发，要遵守产品手册中的《PL/pgSQL使用》。

C函数

通过独立C函数（少量使用内核基础函数），都可以放入插件。
在插件中现有或新增的cpp中编写函数，如下：

PG_FUNCTION_INFO_V1(rand_seed);
extern "C" Datum rand_seed(PG_FUNCTION_ARGS);
Datum rand_seed(PG_FUNCTION_ARGS)
{
    int128 n = PG_ARGISNULL(0) ? 0 : PG_GETARG_INT64(0);

    int elevel = ERROR;

    if (unlikely(n > PG_UINT64_MAX)) {
        elog(elevel, "Truncated incorrect DECIMAL value");
        n = PG_UINT64_MAX;
    } else if (unlikely(n < PG_INT64_MIN)) {
        elog(elevel, "Truncated incorrect DECIMAL value");
        n = PG_INT64_MIN;
    }
    gs_srandom((unsigned int)n);
    float8 result;
    /* result [0.0 - 1.0) */
    result = (double)gs_random() / ((double)MAX_RANDOM_VALUE + 1);

    PG_RETURN_FLOAT8(result);
}

在插件的sql文件中注册C函数，如下：

CREATE OR REPLACE FUNCTION __dialect_mysql__.rand_seed(int) 
returns double precision 
LANGUAGE C 
volatile NOT FENCED
as 
'$libdir/libsql_dialect', 'rand_seed';

别忘了在升降级脚本中也要进行处理。