PostgreSQL troubleshooting系列之五-编写适当的存储过程
前言
本文的内容来源于电子书《Troubleshooting PostgreSQL》。那本书虽老，但是里边的内容依然很有参考价值。俺这里尝试对其简译，对某些地方进行修正和补充，希望对学习和深入了解使用PG有帮助。
本文来自原书第6章。
6、编写恰当的存储过程
在理解了事务和锁定之后，是时候专注于编写适当的存储过程，以避免普遍存在的问题和已知问题。我们的目标是涵盖困扰全球许多人的问题。本章将介绍一些在为PostgreSQL编写过程时最常见的陷阱。
选择正确的过程语言
管理存储过程和事务
为索引优化存储过程
避免安全性问题
控制内存
这些议题有助于确保你的存储过程能在低风险的情况下仍然得到高性能。
6.1 选择合适的语言
PostgreSQL的一个很酷的特性就是你可以写几乎任何语言的存储过程。一些语言，如SQL、PL/pgSQL、PL/Perl、PL/Python和PL/Tcl随系统内核一起发布。您还可以向PostgreSQL添加其他语言，如PL/sh、PL/v8等，以确保当您想要编写过程时，手边总是有您选择的语言。
您要是有兴趣，翻一下：https://wiki.postgresql.org/wiki/PL_Matrix ，那里列出了几乎所有的可用的语言。
要支持某种过程语言，您需要调用create language。
test=# \h CREATE LANGUAGE
Command: CREATE LANGUAGE
Description: define a new procedural language
Syntax:
CREATE [ OR REPLACE ] [ PROCEDURAL ] LANGUAGE name
CREATE [ OR REPLACE ] [ TRUSTED ] [ PROCEDURAL ] LANGUAGE name
HANDLER call_handler [ INLINE inline_handler ] [ VALIDATOR
valfunction ]
要使用PL/Perl，只需要调用：
CREATE LANGUAGE plperl;
如果PL/Perl安装成功，你就可以开始使用Perl来编写存储过程了。一旦您决定添加一种语言，检查该语言的文档，并确保该语言安装就绪和正确工作是很有必要的。
6.1.1 Trusted和Untrusted
当确定使用某过程语言时，您必须记住：您不得不决定是事使用信任和不被信任的语言。这是何意？举个栗子，如果您使用不信任模式加载Perl （plperlu), 那么外边的语言是允许做任何事情的。这就暴露了安全威胁，因为用户可以写一个存储过程，可以删除文件、发邮件或者做任何其他破坏性的事情。当然，在安全的环境下边，这一点都不可取。所以，最好是限制Perl到所谓的信任模式，它不提供任何系统调用。它仅仅提供语言的基础部分，并且禁止与外界的任何交互。对于嵌到PG里头的过程语言而言，这是正确的方式。不受信任的Perl只能用于极少的特例情况下。
并不是所有的编程语言为您提供这种选择。比如PL/sh这类语言只提供Untrusted方式，这对于那些没有超级用户权限的普通用户而言，不是很适用。
6.2 管理存储过程和事务
PG的函数重载在过程语言里头大量使用。这意味着不同的函数可以使用相同的名字，只是接受不同种类的输入参数而已。
一个函数可能叫做func(int, int)， 而另一个函数可能叫func(text, text)。完整的函数签名才算是表达一个完整的函数：
test=# CREATE FUNCTION mysum(int, int)
RETURNS int AS ' SELECT $1 + $2 '
LANGUAGE 'sql';
CREATE FUNCTION
test=# CREATE FUNCTION mysum(text, text)
RETURNS text AS ' SELECT $1 || $2 '
LANGUAGE 'sql';
CREATE FUNCTION
这个例子里，定义了两个函数。第一个函数的参数是两个整数，第二个函数的参数是两个字符串。依赖于调用时实际的参数的类型，会调用不同的函数。
test=# SELECT mysum(10, 20);
mysum
-------
30
(1 row)
如果输入参数类型不同，是字符串：
test=# SELECT mysum('10', '20');
mysum
-------
1020
(1 row)
6.2.1 使用事务避免问题
我们看看一个更常见的大多数人都可能遇到的问题。实际情况表明，很多人都过早的执行commit操作。看看下边的步骤：
某人调用了func(int, int)
在某一时刻，开发人员发现实际上func(int8, int8) 需要被调用
新函数碰巧与老函数一起被创建
没人注意到有两个函数，直到发现，为时已晚
这也不是理论上的边界案例。这种事在全世界每时每刻都在发生。这样的错误会让人付出高昂的代价。
减少此类错误发生几率的一种方法是使用事务。请记住，在PostgreSQL中，事务甚至可以与ddl (CREATE FUNCTION, ALTER TABLE等)一起使用。通过(简单地)在处理代码时不提交更改，您可以防止副作用，例如遗留问题，这有助于避免在开发期间和开发后清理东西的问题。编写数据库代码与用编程语言编写代码不同。如果从代码中删除一个函数，它就消失了。在数据库中，从某个文本文件中删除一个函数并不一定从数据库系统中删除该函数。BEGIN、COMMIT或ROLLBACK可以帮助减少痛苦。
基于此，我个人的经验是：
[hs@jacqueline deploy]$ cat START.sql
BEGIN;
\i module_1.sql
\i module_2.sql
在这儿，module_1.sql和module_2.sql包含了所有的代码。注意，这里没有commit语句。只有在所有的模块全部就绪后才commit. \i function用于加载函数脚本，并放到一个事务里，非常方便。这种使用方式，非常有帮助。
6.2.2 理解事务和过程
到目前为止，您已经了解了事务和函数创建是如何工作的，但是这里还有其他方面。重要的一点是，在函数内部，程序员不能开始或结束事务; 这是有道理的。考虑下面的命令:
SELECT * FROM func();
我们假定func() 返回了1百万行。没有办法在返回200000行以后去开启一个事务。这对于commit也适用。你无法在一个操作里头的某个位置进行commit操作。
黄金法则是函数始终是一个事务的一部分。它永远不会去开启或终止一个事务。函数总是在运行它的语句的范围内执行操作。
然而，有一个功能您可以去利用：PL/pgSQL函数可以使用所谓的异常。实际上，异常可以帮您达到与savepoint相同的效果。请看下例：
CREATE FUNCTION failure(int) RETURNS int AS $$
DECLARE
v_one ALIAS FOR $1;
BEGIN
RETURN v_one / 0;
EXCEPTION WHEN division_by_zero THEN
RETURN 0;
END;
$$ LANGUAGE 'plpgsql';
这个过程肯定会出错。出错最后会导致一个失败的事务。但是，异常可以帮我们捕获错误，并确保事务最后能被恢复。
6.3 过程和索引
在这一节里，我们可以学一下索引和过程基本的缺陷。最常见的问题就是人们忘了定义他们的过程。在pg里，有四种类型的过程：
VOLATILE
STABLE
IMMUTABLE
[NOT] LEAKPROOF
一个volatile的存储过程对于相同的参数，在相同的事务里头，会产生不同的输出结果：
test=# SELECT random(), random();
random | random
-------------------+-------------------
0.906597905792296 | 0.368819046299905
(1 row)
random()函数不同时刻，会得到不同的输出结果。这也是随机数生成器的主要目标。逻辑上，这对于索引的使用也是有意义的：
SELECT * FROM tab WHERE field = random();
PG在这里能用上索引吗？答案是NO，因为引擎会在b树中查找什么?根据定义，每一行的值都应该改变。因此，这里禁止使用索引。列上的所有现有索引都将被忽略。
VOLATILE是性能问题的主要来源，因为人们不知道为什么不使用索引。
STABLE告诉pg这个函数会提供确定性输出，相对于VOLATILE而言：
test=# BEGIN;
BEGIN
test=# SELECT now();
now
-------------------------------
2014-10-15 11:04:51.555648+02
(1 row)
test=# SELECT now();
now
-------------------------------
2014-10-15 11:04:51.555648+02
(1 row)
test=# COMMIT;
COMMIT
在相同的事务里，函数在相同的输入的情况下，永远返回相同的输出。now()这里就是一个典型的例子。
提示：clock_timestamp()函数就是now()函数的volatile版本。这下子明白了哈:-)
IMMUTABLE是一个函数可以达到的最稳定的级别。它指的是不管事务啥样，它总是返回相同的值。
test=# SELECT cos(10);
cos
--------------------
-0.839071529076452
(1 row)
Cos(10)它永远返回-0.839，不管事务的内容，也不管开发人员的年纪或者数据库使用的啥系统。它相当于是一种定律。
STABLE以及IMMUTABLE函数是可以被索引的。如：
SELECT * FROM tab WHERE field = now()
因为now()在一个事务里头它的值是固定的，因此PG确切的知道在索引里头要找什么值，并且针对它进行优化。类似的情况适用于pi(), sin(), cos()等函数。
总是要恰当的定义一个函数，避免性能相关问题：
test=# CREATE FUNCTION dummy(int) RETURNS int AS
$$
SELECT $1;
$$ LANGUAGE 'sql' IMMUTABLE;
CREATE FUNCTION
6.3.1 LEAKPROOF和非LEAKPROOF
LEAKPROOF，不赶巧，它比其他三种稍微复杂点儿。要理解这种类型，我们看看下例：
SELECT * FROM tab WHERE a = 10 AND b = 20;
系统先过滤a还是b? 嗯，具体情况具体分析。如果b有可能更大幅度地减少返回的数据量，那么最好先对b进行过滤，以确保在对a应用第二个过滤器时不会留下太多数据。
换句话说，PG允许对那些限制条件进行重排，以期以最佳方式来执行那些条件。
CREATE FUNCTION slow_func(int4) RETURNS int4 AS $$
BEGIN
EXECUTE 'SELECT pg_sleep(1)';
RAISE NOTICE 'slow_func: %', $1;
RETURN $1;
END;
$$ LANGUAGE 'plpgsql' IMMUTABLE
COST 10;
CREATE FUNCTION fast_func(int4) RETURNS int4 AS $$
BEGIN
RAISE NOTICE 'fast_func: %', $1;
RETURN $1;
END;
$$ LANGUAGE 'plpgsql' IMMUTABLE
COST 100;
我们看看这两个函数，一个快，一个慢。对于快函数，我还给了惩罚点100 。下边是测试数据：
CREATE TABLE t_test (id int4);
INSERT INTO t_test SELECT * FROM generate_series(1, 20);
我们再创建一个视图。大概就是返回偶数，并调用那个fast函数，看看是否返回0 。
CREATE VIEW v AS
SELECT *
FROM t_test
WHERE id % 2 = 0
AND fast_func(id) = 0;
下一步，使用这个视图，并带上一个filter。
SELECT * FROM v WHERE slow_func(id) = 0;
现在pg遇上一系列选择：哪个函数先调用，fast那个，还是slow那个？然后会使用哪个顺序进行过滤？
我们看看最后发生的情况：
\timing
SELECT * FROM v WHERE slow_func(id) = 0;
NOTICE: slow_func: 2
NOTICE: slow_func: 4
*snip*
NOTICE: slow_func: 20
id ----
(0 rows)
Time: 10012.580 ms
PostgreSQL将首先选择慢函数，因为它承诺更快，因为成本更低。
现在的问题如下:假设您不允许看到t_test的完整内容。出于安全考虑，您只能看到偶数。PostgreSQL允许对这些过滤器重新排序。谁说先执行id%2和fast_func ?如果PostgreSQL决定先调用slow_函数呢?一个简单的RAISE NOTICE函数将显示您实际上不允许看到的数据。在我们的例子中，PostgreSQL计算出%2应该是第一个操作，但是如果它不在那里，就会有一个主要的安全问题。
这正是LEAKPROOF发挥作用的时候。它实际上告诉规划器一个功能是否可以通过所谓的安全屏障。
下一个清单显示了如何创建安全屏障:
CREATE VIEW v WITH (security_barrier) AS
SELECT *
FROM t_test
WHERE id % 2 = 0
AND fast_func(id) = 0;
只要在视图的定义中添加WITH (security_barrier)， PostgreSQL就不允许再向视图推送NOT LEAKPROOF函数来重新排序。防泄漏功能可以自由重新排序。
如果您正在处理对安全性至关重要的数据或许多不同的用户和过程，那么考虑LEAKPROOF和NOT LEAKPROOF是绝对重要的，以避免令人讨厌的意外。
6.4 理解内存
编写过程时，考虑下大致的内存消耗是有意义的。通常需要考虑下边三点：
过程和游标
处理返回函数的集合
将内存参数赋给函数
6.4.1 过程与游标
首先要讨论的是在过程中处理大型结果集的方式。在常规编程中，一般规则是在数据量很大的情况下，需要游标。游标背后的思想是每次只获取和处理一小部分数据。
PL/pgSQL等语言提供了打开、关闭和获取的功能来自游标的数据。然而，在大多数情况下，您实际上并不需要依赖此功能。过程语言的美妙之处在于，在内部，一切都是游标! 下面是一个例子:
CREATE FUNCTION process() RETURNS int AS
$$
DECLARE
v_rec RECORD;
BEGIN
FOR v_rec IN SELECT * FROM large_table
LOOP
-- do something
END LOOP;
RETURN 0;
END;
$$ LANGUAGE 'plpgsql';
在本例中，过程循环遍历一个大型结果集。PL/pgSQL将自动使用隐式游标。您完全不必担心内存问题，因为在内部，数据可以平滑地处理，而不必完全具体化到内存中。
简而言之，没有必要在游标处理和显式的手动游标管理上浪费时间。
6.4.2 处理自返回的函数
虽然系统可以很好地处理循环，但有些函数确实需要注意。我说的是所谓的集合返回函数。这些函数实际上返回的不仅仅是一行。书中使用最广泛的set返回函数是generate_series:
SELECT * FROM generate_series(1, 10) AS x;
这里的主要问题是PostgreSQL必须计算整个函数，将它的输出存储在某个地方，然后从那里继续。这意味着，如果您使用大范围调用generate_series(或类似的函数)，那么只要需要，所有内容都必须保存在内存中。在这里处理2000行左右绝对不是问题，因为现代硬件很容易处理这个问题。但是，如果希望返回数亿行，则需要切换到另一种技术。您不应该返回如此大的结果集返回集合的函数。
大多数情况下，返回游标会有帮助。
6.4.3 为函数添加内存参数
调优PostgreSQL还包括修改PostgreSQL中的内存参数。在许多情况下，全局设置就足够了。目前在该领域看到的大多数应用程序都完全可以使用全局设置。然而，在某些情况下，将配置参数直接传递给函数是有意义的。
看看下边这个例子：
CREATE FUNCTION func() RETURNS int AS
$$
DECLARE
BEGIN
-- some code
RETURN 0;
END;
$$ LANGUAGE 'plpgsql' SET work_mem = '1 GB';
SELECT func();
最重要的问题是CREATE FUNCTION语句末尾的SET语句。它告诉系统在这个函数中，应该有1gb的work_mem整数可用。在函数之外，可以使用其他值。
您可以在函数定义中指定任何运行时可更改的设置。一个例子是时区。也许，您希望根据UTC时间运行函数。没问题!只需告诉函数这样做。
当然，如果需要更改，您可以稍后使用ALTER FUNCTION更改这些设置。
小结：
在本章中，您了解了过程。我们的目标是在一个简短的、紧凑的章节中涵盖过程中最常见的陷阱。讨论了函数易变性、可信和不可信语言以及内存参数等问题。
参考：
《Troubleshooting PostgreSQL》第6章
