乐趣区

关于函数:你的数仓函数结果不稳定可能是属性指定错了

jiezi

摘要： 本文简略介绍 GaussDB(DWS) 函数下推属性的相干常识，并提供几个函数属性相干的典型案例供大家参考。

本文分享自华为云社区《GaussDB(DWS) 函数下推属性介绍》，原文作者：Arrow0lf。

用户在应用 GaussDB(DWS) 时，应该正确指定函数属性，谬误指定函数属性不仅会导致查问语句执行效率低，而且可能会导致后果集不稳固的状况。本文简略介绍 GaussDB(DWS) 函数下推属性的相干常识，并提供几个函数属性相干的典型案例供大家参考。

1、函数下推属性介绍

GaussDB(DWS) 创立函数时，能够指定许多函数属性，其中，与函数下推相干的属性为易失性级别 和下推属性，其中：

易失性：

  • IMMUTABLE：该属性的函数不会批改数据库，并且保障在任何状况下同样的输出参数永远返回同样的后果；
  • STABLE：该属性的函数不会批改数据库，并且保障在同一个查问中，对于同样的输出参数，函数返回的后果雷同；
  • VOLATILE：该属性的函数对于同样的输出参数，函数的返回后果可能不通，典型的如 timeofday，创立函数时如果未明确指定，则默认为 VOLATILE；

下推属性：

  • SHIPPABLE：函数能够下推到 DN 执行
  • NOT SHIPPABLE：函数不能下推到 DN 执行，创立函数时如果未明确指定，则默认为 NOT SHIPPABLE。

在 GaussDB(DWS) 中，IMMUTABLE 属性的函数时肯定可能下推到 DN 执行的，不论下推属性是否为 SHIPPABLE，对于 STABLE 和 VOLATILE 属性的函数，函数是否能下推要看指定的 SHIPPABLE 属性。因而，在创立函数时如果同时指定了 IMMUTABLE 和 NOT SHIPPABLE 的属性，函数创立胜利时会有以下提醒：

NOTICE:  Immutable function will be shippable anyway.

2. 函数下推属性典型案例

案例一：未指定函数易失性级别导致函数不下推

函数定义如下：

create function try_cast_int(p_in text, p_default int default 0) returns int
as $$
begin
    begin
        return $1::int;
    exception
    when others then
        return p_default;
    end;
end;
$$
language plpgsql;

因为创立函数时未明确指定函数易失性级别和函数属性，函数默认为 VOLATILE NOT SHIPPABLE，应用该函数时执行打算如下：

postgres=# explain verbose select try_cast_int(b) from test order by a;
                                      QUERY PLAN                                      
--------------------------------------------------------------------------------------
 Sort  (cost=13.91..14.04 rows=50 width=36)
   Output: (try_cast_int(test.b, 0)), test.a
   Sort Key: test.a
   ->  Data Node Scan on "__REMOTE_SORT_QUERY__"  (cost=0.00..12.50 rows=50 width=36)
         Output: try_cast_int(test.b, 0), test.a
         Node/s: All datanodes
         Remote query: SELECT a, b FROM ONLY public.test WHERE true ORDER BY 1
(7 rows)

能够看出该 sql 执行打算不下推，执行效率较低，剖析该函数发现该函数能够指定为 IMMUTABLE 属性，让该函数能够下推，因而，能够通过以下形式优化：

ALTER FUNCTION try_cast_int(text,int) IMMUTABLE;

案例二：谬误指定了函数下推属性导致后果集不稳固

下推函数可能下推到 DN 执行，与不下推函数相比有着更高的执行效率，有时开发者为了放慢函数执行效率，所有自定义函数创立时都会指定为 SHIPPABLE，某函数定义如下：

create function get_count() returns int
SHIPPABLE
as $$
declare
    result int;
begin
    result = (select count(*) from test);  --test 表是 hash 表
    return result;
end;
$$
language plpgsql;

调用该函数发现以下景象：

postgres=# select get_count();
 get_count 
-----------
      2106
(1 row)

postgres=# select get_count() from t_src;
 get_count 
-----------
      1032
(1 row)

发现加上 from 表之后函数的返回值后果产生了变动！为什么会呈现这种状况呢？这是因为因为这个函数指定了 SHIPPABLE 的函数属性，因而生成打算时该函数会下推到 DN 上执行，该函数下推到 DN 后，因为函数定义中的 test 表是 hash 表，因而每个 DN 上只有该表的一部分数据，所以 select count(*) from test 返回的后果不是 test 表全量数据的后果，而是每个 DN 上局部数据的后果，因而导致加上 from 表后函数返回预期发生变化，优化办法：

（1）将函数改为不下推：alter function get_count() not shippable;

（2）将函数中用到的表改为复制表，这样每个 DN 上都是一份该表的全量数据，即便下推到 DN 执行，也能保障后果集合乎预期。

3. 总结

创立自定义函数时，要正确指定函数的属性，确保函数属性合乎预期，避免因函数属性设置不正确导致的性能降落或后果集不稳固。

点击关注，第一工夫理解华为云陈腐技术~

退出移动版