Greenplum的PL/Perl语言扩展

本章包含了一下信息：

上级主题： Greenplum数据库参考指南

关于Greenplum的PL/Perl

有Greenplum的PL/Perl扩展，用户可以用Perl写用户定义的函数，可以利用Perl先进的字符串处理操作和函数。PL/Perl同时提供了可信和不可信的语言变体。

PL/Perl嵌入在用户的Greenplum数据库发布中。Greenplum数据库的PL/Perl要求系统中每台数据库主机都要事先装好Perl。

参考 PostgreSQL的PL/Perl 文档 获取额外信息。

Greenplum数据库中PL/Perl局限性

Greenplum数据库PL/Perl的局限性包括：

  • Greenplum数据库不支持PL/Perl触发器。
  • PL/Perl函数不能直接相互调用。
  • SPI还没有完全的实现。
  • 如果用户使用spi_exec_query()获取非常大的数据集，用户应该要意识到它们都将进入内存中。用户可以通过使用spi_query()/spi_fetchrow()来防止这个问题的发生。一个相似的问题也可能发生，当集合返回函数通过一个return语句传递一个大量行的数据集到Greenplum数据库中。对每一返回行使用 return_next 能够避免该问题的出现。
  • 当一个会话正常结束而不是由于一个致命错误结束时，PL/Perl会执行用户定义好的任何END块。当前没有其它的动作执行（文件处理不会被自动地刷写并且对象也不会被自动销毁）。

可信/不可信语言

PL/Perl 包含了可信和不可信两种语言的变体。

PL/Perl可信语言被命名为 plperl。可信PL/Perl语言限制文件系统操作，和require、use以及其它可能同操作系统或者数据库服务进程进行交互的语句。有了这些限制，任何Greenplum用户能够创建和执行可信的plperl语言的函数。

PL/Perl不可信语言被命名为plperlu。用户不能限制用户使用plperlu不可信语言创建的函数的操作。只有数据库的超级用户有创建有不可信PL/Perl语言的用户定义函数。同时只有数据库的超级用户以及其他被显式授予特权的用户能够执行不可信PL/Perl的用户定义函数。

在解释器以及运行在单个进程中的多个解释器之间的通信方面，PL/Perl有限制。请参考 PostgreSQL的可信与不可信PL/Perl 文档获取更多的信息。

允许和移除PL/Perl支持

在用户能在数据库中创建以及执行一个PL/Perl的用户定义函数前，必须要在该数据库上注册PL/Perl语言。要实现移除PL/Perl，用户必须显式的从每一个它注册的数据库从移除扩展。用户必须是一个数据库超级用户或者注册的拥有者或者移除Greenplum中可信语言。

注释：只用数据库超级用户可能注册或者移除对不可信 PL/Perl语言plperlu的支持。

在用户在数据库中开启或者移除PL/Perl支持之前，确保：

  • 用户的Greenplum数据库正在运行。
  • 用户已经执行（source）了greenplum_path.sh。
  • 用户已经设置了$MASTER_DATA_DIRECTORY和 $GPHOME环境变量。

开启PL/Perl支持

对于每个用户想开启PL/Perl的数据库，使用SQL命令CREATE LANGUAGE或者Greenplum数据库的createlang功能来注册语言。例如，以gpadmin用户身份执行下面的命令在名为testdb的数据库中注册可信的PL/Perl语言：

  1. $ createlang plperl -d testdb

移除PL/Perl

为了从一个数据库中移除对PL/Perl的支持，执行SQL命令DROP LANGUAGE或者Greenplum的droplang功能。例如，以gpadmin用户身份执行下面命令移除名为testdb的数据库对可信PL/Perl语言的支持：

  1. $ psql -d testdb
  2. psql (8.3.23)
  3. Type "help" for help.
  5. testdb=# DROP LANGUAGE plperl;

默认的DROP LANGUAGE行为是RESTRICT；如果存在对象（例如函数）依赖于该语言则该命令执行失败。如果指定了CASCADE子句，也会移除所有依赖于该语言的对象，包括用户使用PL/Perl创建的函数。

用PL/Perl开发函数

用户可以使用标准的SQLCREATE FUNCTION语法来定义一个PL/Perl函数。一个用户定义的PL/Perl函数的主体是普通的Perl代码。PL/Perl解释器会在一个Perl子程序中包装该代码。

用户也可以使用PL/Perl创建一个匿名块。如果一个匿名代码块被SQL中的DO命令调用，则不接受任何的参数同时任何它可能要返回的值也会被丢弃，否则一个PL/Perl匿名代码块行为仅仅就像一个函数一样。只有数据库的超级用户能够使用不可信的plperlu语言创建一个匿名代码块。

CREATE FUNCTION命令的语法要求函数体被写作一个字符串常量。通常对字符串常量使用美元引用更方便，用户可以选择使用转义字符串语法（E’’），必须双写任何在函数体中使用的单引号 （’）和反斜线（\）。

参数和结果的处理和在任何其他 Perl程序中一样。传递到一个PL/Perl函数中的参数通过@_数组来访问。可以通过一个return返回结果值或者把函数中计算的最后一个表达式作为结果值。由于调用函数在一个常量的环境下，所以不能返回一个非常量类型。可以通过在PL/Perl函数中返回一个引用来实现返回一个非常量类型，例如，数组、记录以及集合等。

PL/Per将null参数看做为“undefined”。添加STRICT关键字到LANGUAGE子语句中来指导Greenplum数据库当任何一个输入参数为null时立刻返回null。当函数被创建为STRICT，函数本身不要执行null检查。

下面的PL/Perl函数利用STRICT关键字返回两个整数中较大的一个，或者当任何输入为null时返回null：

  1. CREATE FUNCTION perl_max (integer, integer) RETURNS integer AS $$
  2.     if ($_[0] > $_[1]) { return $_[0]; }
  3.     return $_[1];
  4. $$ LANGUAGE plperl STRICT;
  6. SELECT perl_max( 1, 3 );
  7.  perl_max
  8. ----------
  9.         3
  10. (1 row)
  12. SELECT perl_max( 1, null );
  13.  perl_max
  14. ----------
  16. (1 row)

是相关数据类型的标准 PostgreSQL外部文本表达。 PL/Perl认为在一个非引用的函数参数中的任何东西都是一个串，是标准Greenplum数据库外部文本表示。一个PL/Perl函数支持的参数值是简单的将输入参数转为为文本形式（正如它们被SELECT语句展示出来的一样）。在函数参数不是普通的数值类型或者文本类型，用户必须转换Greenplum数据库类型为一个Per中更可用的形式。相反，return和return_next语句接受任何字符串，这些字符串是函数声明的返回类型的可接受输入形式。

参考PostgreSQL PL/Perl 函数与参数文档获取更多信息，包括组合类型以及结果集合操作。

内建 PL/Perl函数

PL/Perl包括了访问数据库的内建函数，包括准备和执行查询以及操作查询结果。语言也包括了对错误日志以及字符串操作的功能函数。

下面的示例创建一个包括一个整型列和文本（text）列的简单表。创建一个用户定义的PL/Perl函数，该函数接受一个输入的字符串然后调用spi_exec_query()内建函数来选择表中的所有列和行。在查询结果中，函数返回所有v列包含函数输入字符串的行。

  1. CREATE TABLE test (
  2.     i int,
  3.     v varchar
  4. );
  5. INSERT INTO test (i, v) VALUES (1, 'first line');
  6. INSERT INTO test (i, v) VALUES (2, 'line2');
  7. INSERT INTO test (i, v) VALUES (3, '3rd line');
  8. INSERT INTO test (i, v) VALUES (4, 'different');
  10. CREATE OR REPLACE FUNCTION return_match(varchar) RETURNS SETOF test AS $$
  11.     # 存储输入参数
  12.     $ss = $_[0];
  14.     # 执行查询
  15.     my $rv = spi_exec_query('select i, v from test;');
  17.     # 获取查询状态
  18.     my $status = $rv->{status};
  20.     # 获取查询返回的行数
  21.     my $nrows = $rv->{processed};
  23.     # 循环处理所有行，比较列v和输入参数的值
  24.     foreach my $rn (0 .. $nrows - 1) {
  25.         my $row = $rv->{rows}[$rn];
  26.         my $textstr = $row->{v};
  27.         if( index($textstr, $ss) != -1 ) {
  28.             # match!  return the row.
  29.             return_next($row);
  30.         }
  31.     }
  32.     return undef;
  33. $$ LANGUAGE plperl;
  35. SELECT return_match( 'iff' );
  36.  return_match
  37. ---------------
  38.  (4,different)
  39. (1 row)

参考 PostgreSQL PL/Perl 内建函数文档获取更多可用函数的细节讨论信息。

PL/Perl中的全局值

可以在函数调用之间或者当前会话的生命期中用全局哈希%_SHARED来存储数据，包括代码引用。

下面的示例使用%_SHARED在用户定义的set_var()和get_var()函数之间共享数据：

  1. CREATE OR REPLACE FUNCTION set_var(name text, val text) RETURNS text AS $$
  2.     if ($_SHARED{$_[0]} = $_[1]) {
  3.         return 'ok';
  4.     } else {
  5.         return "cannot set shared variable $_[0] to $_[1]";
  6.     }
  7. $$ LANGUAGE plperl;
  9. CREATE OR REPLACE FUNCTION get_var(name text) RETURNS text AS $$
  10.     return $_SHARED{$_[0]};
  11. $$ LANGUAGE plperl;
  13. SELECT set_var('key1', 'value1');
  14.  set_var
  15. ---------
  16.  ok
  17. (1 row)
  19. SELECT get_var('key1');
  20.  get_var
  21. ---------
  22.  value1
  23. (1 row)

出于安全原因，PL/Perl 一个 SQL 角色独立的 Perl 解释器中执行该角色调用 的任何一个函数。因此，在使用单个会话执行多个 SQL 角色 的代码（通过SECURITY DEFINER函数、使用 SET ROLE等）的应用中，需要采取显式的步骤以保证 PL/Perl 函数能够通过%_SHARED共享数据。要这样做，需要 确保要通信的函数都属于同一个用户，并且把它们标记为 SECURITY DEFINER。当然，要小心这样的函数被滥用。 出于安全的原因，PL/Perl为每个角色创建一个独立的Perl解释器。这可以避免一个用户无意或者恶意地干涉另一个用户的 PL/Perl 函数的行为。每一个这样的解释器都具有其自身的%_SHARED变量值和其他全局状态。只有且仅只有两个 PL/Perl 函数是由同一个 SQL 角色执行时，它们才能共享同一个 %_SHARED值。

有一些场景需要用户必须采取显式的步骤确保PL/Perl函数能够在%_SHARED中共享数据。例如，使用单个会话执行多个SQL角色的代码（通过SECURITY DEFINER函数、使用SET ROLE等）的应用，需要确保要通信的函数都属于同一个用户，并且把它们标记为SECURITY DEFINER。

注解

在开发PL/Perl函数其它要考虑的：

  • PL/Perl 内部采用的是UTF-8 编码。它将转换任何以其它编码形式提供的参数为UTF-8，同时将返回值从UTF-8转换为原始的编码。
  • 嵌套命名的PL/Perl子例程保留着与Perl中相同的危险。
  • 只有不可信的PL/Perl语言变体支持模块导入。使用plperlu需要当心。
  • 任何用户在plperlu函数中使用的模块必须在所有Greenplum数据库主机（host）的相同位置是可访问的。