标准插件

adminpack

adminpack 提供了一些支持函数，pgAdmin 和其他管理工具会用这些函数来提供额外的功能，例如服务器日志文件的远程管理。所有这些函数默认只允许由超级用户使用，但是可以通过使用 GRANT 命令允许其他用户使用。

表 F.1中展示的函数提供了向运行 着服务器的机器上的文件进行写入的途径。对于这些函数，只有位于数据库集簇目录中的文件才能被访问（除非用户是一个超级用户或者被指定了 pg_read_server_files 或者 pg_write_server_files 角色之一），不过允许使用相对路径和绝对路径。

表 F.1. adminpack 函数

名称	返回类型	描述
pg_catalog.pg_file_write(filename text, data text, append boolean)	bigint	向一个文本文件写入或者追加
pg_catalog.pg_file_rename(oldname text, newname text [, archivename text])	boolean	重命名一个文件
pg_catalog.pg_file_unlink(filename text)	boolean	移除一个文件
pg_catalog.pg_logdir_ls()	setof record	列出在 log_directory 目录中的日志文件

pg_file_write 把指定的 data 写入到由 filename 命名的文件中。如果append为假，文件不能已经存在。如果 append 为真，该文件 可能已经存在，并且如果存在就会被追加。这个函数返回写入的字节数。

pg_file_rename重命名一个文件。如果 archivename被省略或者为 NULL，它简单地把 oldname重命名为newname（不能 已经存在）。如果提供了archivename，该函数首先把 newname重命名为archivename（不 能已经存在），然后把*oldname重命名为 newname。当第二次重命名失败时，这个函数会在报告 错误之前尝试把archivename*重命名成 newname。成功时函数返回真，如果源文件不存在或者 不可写则返回假，其他情况下会抛出错误。

pg_file_unlink移除指定的文件。成功时函数返回真， 如果指定的文件不存在或者unlink()调用失败则返回 假，其他情况下会抛出错误。

pg_logdir_ls返回log_directory 目录中所有日志文件的开始时间戳以及路径名。要使用这个函数，

amcheck

amcheck 模块提供的函数让用户能验证关系结构的逻辑一致性。如果结构有效，则不会发生错误。

这些函数验证特定关系的结构表达中的各种不变条件。索引扫描以及其他重要操作背后的访问方法的正确性都要依仗这些不变条件的成立。例如，在这些函数中，有一些负责验证所有B树页面中的项都按照“逻辑”顺序（比如，对于text上的B树索引，索引元组应该按照词典顺序排列）摆放。如果特定的不变条件由于某种原因无法成立，则我们可以预料受影响页面上的二分搜索将无法正确地引导索引扫描，最终导致SQL查询得到错误的答案。

验证过程采用索引扫描自身使用的同种过程来执行，这些过程可能是用户定义的操作符类代码。例如，B树索引验证依赖于由一个或者多个B树支持函数1例程构成的比较。

函数

bt_index_check(index regclass, heapallindexed boolean) returns void

bt_index_check测试一个B树索引，检查各种不变条件。用法实例：

test=# SELECT bt_index_check(index => c.oid, heapallindexed => i.indisunique),
               c.relname,
               c.relpages
FROM pg_index i
JOIN pg_opclass op ON i.indclass[0] = op.oid
JOIN pg_am am ON op.opcmethod = am.oid
JOIN pg_class c ON i.indexrelid = c.oid
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE am.amname = 'btree' AND n.nspname = 'pg_catalog'
-- Don't check temp tables, which may be from another session:
AND c.relpersistence != 't'
-- Function may throw an error when this is omitted:
AND c.relkind = 'i' AND i.indisready AND i.indisvalid
ORDER BY c.relpages DESC LIMIT 10;
 bt_index_check |             relname             | relpages 
----------------+---------------------------------+----------
                | pg_depend_reference_index       |       43
                | pg_depend_depender_index        |       40
                | pg_proc_proname_args_nsp_index  |       31
                | pg_description_o_c_o_index      |       21
                | pg_attribute_relid_attnam_index |       14
                | pg_proc_oid_index               |       10
                | pg_attribute_relid_attnum_index |        9
                | pg_amproc_fam_proc_index        |        5
                | pg_amop_opr_fam_index           |        5
                | pg_amop_fam_strat_index         |        5
(10 rows)

这个例子中的会话执行对数据库“test”中10个最大目录索引的验证。对于唯一索引会要求验证堆元组是否有对应的索引元组存在。由于没有错误报出，所有的被测索引都处于逻辑一致的状态。自然地，很容易将这个查询改为对支持验证的数据库中的每一个索引调用bt_index_check。

bt_index_check要求目标索引及其所属的堆关系上的AccessShareLock。这种锁模式与简单SELECT语句在关系上所要求的锁模式相同。bt_index_check不验证跨越父子关系的不变条件，但是在*heapallindexed*为true时将验证所有堆元组是否作为索引中的索引元组存在。当在生产环境中要求一个使用bt_index_check的例程进行轻量化损坏测试时，它常常需要在验证彻底性和减小对应用性能及可用性的影响之间做出权衡。

bt_index_parent_check(index regclass, heapallindexed boolean) returns void

bt_index_parent_check测试一个B树索引，检查多种不变条件。可选地，当*heapallindexed*参数为true时，该函数验证所有应该在索引中找到的堆元组的存在，同时验证在索引结构中没有缺失的下链。bt_index_parent_check能够执行的检查是bt_index_check能执行的检查的超集。bt_index_parent_check可以被想成是bt_index_check的一种更全面的变体：和bt_index_check不同，bt_index_parent_check还检查跨越父/子关系的不变条件。如果找到逻辑不一致或者其他问题，bt_index_parent_check遵循通常的报错习惯。

bt_index_parent_check要求目标索引上的一个ShareLock（还要求对关系上的一个ShareLock）。这些锁阻止来自INSERT、UPDATE以及DELETE命令的并发数据修改。这些锁同时防止底层关系被并发的VACUUM以及其他工具命令处理。注意该函数只在其运行期间而不是整个事务期间持有锁。

bt_index_parent_check的额外验证更有可能检测到多种病态的情况。这些情况可能涉及到被查索引使用的一种不正确实现的B-树操作符类，或者说不定是底层B-树索引访问方法代码中未被发现的缺陷。注意与bt_index_check不同，当热备模式被启用时（即在只读的物理复制机上）不能使用bt_index_parent_check。

可选的*heapallindexed*验证

当验证函数的*heapallindexed参数为true时，会针对与目标索引关系关联的表执行一个额外的验证过程。这种验证由一个“假的”CREATE INDEX操作组成，它针对一个临时的、内存中的汇总结构（根据需要在基础的第一阶段验证过程中建立）检查所有假想的新索引元组的存在。这个汇总结构对目标索引中的每一个元组“采集指纹”。heapallindexed*验证背后的高层原则是：等效于现有目标索引的新索引必须仅拥有能在现有结构中找得到的项。

额外的*heapallindexed阶段会增加明显的开销：验证的时间通常将会延长几倍。不过，在执行heapallindexed*验证时，所要求的关系级锁没有变化。

这一汇总结构的尺寸以maintenance_work_mem为界。为了确保对于每个堆元组应该存在于索引中这一检测有不超过2%的失效概率能检测到不一致，每个元组需要大约2个字节的内存。因为每个元组可用的内存变少，错失一处不一致的概率就会慢慢增加。这种方法显著地限制了验证的开销，但仅仅略微降低了检测到问题的概率，对于将验证当作例行维护任务的安装来说更是如此。对于每一次新的验证尝试，任何单一的缺失或者畸形元组都有新的机会被检测到。

有效地使用amcheck

amcheck对于检测多种数据页面校验和无法捕捉到的失效模式非常有效。包括：

• 由不正确的操作符类实现导致的结构性不一致。

  这包括操作系统排序规则的比较规则变化导致的问题。text之类的可排序类型数据的比较必须是不变的（正如用于B-树索引扫描的所有比较必须不变一样），这意味着操作系统排序规则必须保持不变。但是在很少的情况下，操作系统排序规则的更新会导致这些问题。更常见的，主服务器和后备服务器之间排序顺序的不一致会相互牵连，这可能是因为使用的主操作系统版本不一致。这类不一致通常仅出现在后备服务器上，因此通常也仅能在后备服务器上检测到。

  如果这类问题出现，则它可能不会影响使用受影响排序规则排序的每一个索引，其原因是被索引值可能正好具有与行为不一致无关的相同的绝对顺序。

• 索引和被索引的对关系之间的结构不一致（在执行*heapallindexed*验证时）。

  在普通操作时没有将索引针对其对关系进行交叉检查。堆损坏的症状可能是很微妙的。

• 由于底层UDB-TX访问方法代码、排序代码或者事务管理代码中（假想的）未发现的缺陷导致的损坏。

  在测试可能引入逻辑不一致的UDB-TX新特性或者被提议的特性时，索引的结构完整性自动验证扮演了重要角色。表结构、相关的可见性和事务状态信息的验证扮演了类似的角色。一种显而易见的测试策略是在运行标准回归测试时持续地调用amcheck函数。

• 正巧没有开启校验和的文件系统或者存储子系统故障。

  注意，如果在访问块时仅有一次共享缓存命中，验证时amcheck会在检查表示在某个共享内存缓冲区中的页面。因此，amcheck没有必要在验证时检查从文件系统读出的数据。注意当校验和被启用时，如果一个损坏的块被读取到缓冲区中，amcheck可能会由于校验和失效而产生错误。

• 有缺陷的RAM或者内存子系统导致的损坏。

  UDB-TX无法提供针对可更正内存错误的保护并且它假定用户使用的是具有工业标准纠错码（ECC）或更好保护技术的RAM。不过，ECC内存通常只能免疫单个位错误，并且不应该假定它能提供对导致内存损坏失效的绝对保护。

  在执行*heapallindexed*验证时，通常有大幅增加的机会可以检测单个位错误，因为会测试严格的二元等值并且会在堆中测试被索引属性。

通常，amcheck仅能证明损坏的存在，但它无法证明损坏不存在。

auth_delay

auth_delay

auth_delay使得服务器在报告鉴定失败之前短暂地停顿一会儿，这使得对数据库口令的蛮力攻击更加困难。注意它无助于组织拒绝服务攻击，甚至可能会加剧它们，因为在报告鉴定失败之前等待的进程仍然要消耗连接。

要使之工作，该模块必须通过unvdbsvr.conf中的shared_preload_libraries载入。

配置参数

• auth_delay.milliseconds (int)

  在报告鉴定失败之前等待的毫秒数。默认值为0。

这些参数必须在unvdbsvr.conf中设置。典型的使用是：

# unvdbsvr.conf
shared_preload_libraries = 'auth_delay'

auth_delay.milliseconds = '500'

auto_explain

auto_explain模块提供了一种方式来自动记录慢速语句的执行计划，而不需 要手工运行EXPLAIN。这在大型应用中追踪未被优化的查询时有用。

该模块没有提供 SQL 可访问的函数。要使用它，简单地将它载入服务器。你可以把它载入到一个单独的 会话：

LOAD 'auto_explain';

（你必须作为超级用户来这样做）。更典型的用法是通过在unvdbsvr.conf的session_preload_libraries或shared_preload_libraries参数中包括auto_explain将它预先 载入到某些或者所有会话中。然后你就可以追踪那些出乎意料地慢的查询，而不管它们何时发生。当然为 此会付出一些额外的负荷作为代价。

配置参数

有几个配置参数用来控制auto_explain的行为。注意默认行为是什么也不做，因此如果你想要任何结果就必须至少设置auto_explain.log_min_duration。

• auto_explain.log_min_duration (integer)

  auto_explain.log_min_duration是最小语句执行时间（以毫秒计），这将导致语句的计划被记录。设置这个参数为零将记录所有计划。负一（默认值）禁用记录计划。例如，如果你将它设置为250ms，则所有运行时间等于或超过 250ms 的语句将被记录。只有超级用户能够改变这个设置。

• auto_explain.log_analyze (boolean)

  当一个执行计划被记录时，auto_explain.log_analyze导致EXPLAIN ANALYZE输出（而不仅仅是EXPLAIN输出）被打印。默认情况下这个参数是关闭的。只有超级用户能够改变这个设置。注意当这个参数为打开时，对所有被执行的语句将引起对每个计划节点的计时，不管它们是否运行得足够长以至于被记录。这可能对性能有极度负面的影响。

• auto_explain.log_buffers (boolean)

  当一个执行计划被记录时，auto_explain.log_buffers控制是否打印 缓冲区使用统计信息；它等效于EXPLAIN的BUFFERS选项。除非 auto_explain.log_analyze参数被设置，否则这个参数没有效果。这个参数默 认情况下是关闭的。只有超级用户能够改变这个设置。

• auto_explain.log_timing (boolean)

  当一个执行计划被记录时，auto_explain.log_timing控制是否打印每 个结点上的计时信息；它等效于EXPLAIN的TIMING选项。重复读取 系统锁的开销在某些系统上可能会显著地拖慢查询，因此当只需要实际行计数而非确切时间时，关闭 这个参数将会很有帮助。只有当auto_explain.log_analyze也被启用 时这个参数才有效。这个参数默认情况下是打开的。只有超级用户能够改变这个设置。

• auto_explain.log_triggers (boolean)

  当一个执行计划被记录时，auto_explain.log_triggers会导致触发 器执行统计信息被包括在内。只有当auto_explain.log_analyze也被 启用时这个参数才有效。这个参数默认情况下是关闭的。只有超级用户能够改变这个设置。

• auto_explain.log_verbose (boolean)

  当一个执行计划被记录时，auto_explain.log_verbose控制是否打印很长的详细信息；它等效于EXPLAIN的VERBOSE选项。这个参数默认情况下是关闭的。只有超级用户能够改变这个设置。

• auto_explain.log_format (enum)

  auto_explain.log_format选择要使用的EXPLAIN输出格式。允许的值是text、xml、json和yaml。默认是文本形式。只有超级用户能够改变这个设置。

• auto_explain.log_nested_statements (boolean)

  auto_explain.log_nested_statements导致嵌套语句（在一个函数内执行的语句）会被考虑在记录范围之内。当它被关闭时，只有顶层查询计划被记录。这个参数默认情况下是关闭的。只有超级用户能够改变这个设置。

• auto_explain.sample_rate (real)

  auto_explain.sample_rate会让 auto_explain 只解释每个会话中的一部分语句。默认值为 1，表示解释所有的查询。在嵌套 语句的情况下，要么所有语句都被解释，要么一个也不被解释。只有超级用户 能够更改这个设置。

在普通用法中，这些参数都在unvdbsvrsql.conf中设置，不过超级用户可以在他们自己的会话中随时修改这些参数。典型的用法可能是：

# unvdbsvr.conf
session_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = '3s'

例子

unvdbsvr=# LOAD 'auto_explain';
unvdbsvr=# SET auto_explain.log_min_duration = 0;
unvdbsvr=# SET auto_explain.log_analyze = true;
unvdbsvr=# SELECT count(*)
           FROM pg_class, pg_index
           WHERE oid = indrelid AND indisunique;

这可能会产生这样的日志输出：

LOG:  duration: 3.651 ms  plan: Query Text: SELECT count(*)
              FROM pg_class, pg_index
              WHERE oid = indrelid AND indisunique;
  Aggregate  (cost=16.79..16.80 rows=1 width=0) (actual time=3.626..3.627 rows=1 loops=1)
    ->  Hash Join  (cost=4.17..16.55 rows=92 width=0) (actual time=3.349..3.594 rows=92 loops=1)
          Hash Cond: (pg_class.oid = pg_index.indrelid)
          ->  Seq Scan on pg_class  (cost=0.00..9.55 rows=255 width=4) (actual time=0.016..0.140 rows=255 loops=1)
          ->  Hash  (cost=3.02..3.02 rows=92 width=4) (actual time=3.238..3.238 rows=92 loops=1)
                Buckets: 1024  Batches: 1  Memory Usage: 4kB
                ->  Seq Scan on pg_index  (cost=0.00..3.02 rows=92 width=4) (actual time=0.008..3.187 rows=92 loops=1)
                      Filter: indisunique

bloom

bloom提供了一种基于布鲁姆过滤器的索引访问方法。

布鲁姆过滤器是一种空间高效的数据结构，它被用来测试一个元素是否为一个集合的成员。在索引访问方法的情况下，它可以通过尺寸在索引创建时决定的签名来快速地排除不匹配的元组。

签名是被索引属性的一种有损表达，并且因此容易报告伪肯定，也就是说对于一个不在集合中的元素有可能报告该元素在集合中。因此索引搜索结果必须使用来自堆项的实际属性值进行再次检查。较大的签名可以降低伪肯定的几率并且减少无用的堆访问的次数，但是这显然会让索引更大且扫描起来更慢。

当表具有很多属性并且查询可能会测试其中任意组合时，这种类型的索引最有用。传统的 btree 索引比布鲁姆索引更快，但是需要很多 btree 索引来支持所有可能的查询，而对于布鲁姆索引来说只需要一个即可。不过要注意 bloom 索引只支持等值查询，而 btree 索引还能执行不等和范围搜索。

参数

bloom索引在其WITH子句中接受下列参数：

• length

  每个签名（索引项）的长度位数，它会被圆整成为最近的16的倍数。默认是80位，最长是4096位。

• col1 — col32

  从每一个索引列产生的位数。每个参数的名字表示它所控制的索引列的编号。默认是2位，最大是4095位。没有实际使用的索引列的参数会被忽略。

例子

这是一个创建布鲁姆索引的例子：

例子

这是一个创建布鲁姆索引的例子：

CREATE INDEX bloomidx ON tbloom USING bloom (i1,i2,i3)  WITH (length=80, col1=2, col2=2, col3=4);

该索引是用长度为 80 位的签名所创建，其中属性 i1 和 i2 被映射为 2 位，属性 i3 被映射为 4 位。我们可以省略length、col1和col2说明，因为它们都有默认值。

这里是布鲁姆索引定义和使用的更完整的例子，其中还与等效的 btree 做了对比。布鲁姆索引比 btree 索引更小，并且效率更高。

=# CREATE TABLE tbloom AS
   SELECT
     (random() * 1000000)::int as i1,
     (random() * 1000000)::int as i2,
     (random() * 1000000)::int as i3,
     (random() * 1000000)::int as i4,
     (random() * 1000000)::int as i5,
     (random() * 1000000)::int as i6
   FROM
  generate_series(1,10000000);
SELECT 10000000
=# CREATE INDEX bloomidx ON tbloom USING bloom (i1, i2, i3, i4, i5, i6);
CREATE INDEX
=# SELECT pg_size_pretty(pg_relation_size('bloomidx'));
 pg_size_pretty
----------------
 153 MB
(1 row)
=# CREATE index btreeidx ON tbloom (i1, i2, i3, i4, i5, i6);
CREATE INDEX
=# SELECT pg_size_pretty(pg_relation_size('btreeidx'));
 pg_size_pretty
----------------
 387 MB
(1 row)

在这个大表上的顺序扫描需要很长时间：

=# EXPLAIN ANALYZE SELECT * FROM tbloom WHERE i2 = 898732 AND i5 = 123451;
                                                 QUERY PLAN
------------------------------------------------------------------------------------------------------------
 Seq Scan on tbloom  (cost=0.00..213694.08 rows=1 width=24) (actual time=1445.438..1445.438 rows=0 loops=1)
   Filter: ((i2 = 898732) AND (i5 = 123451))
   Rows Removed by Filter: 10000000
 Planning time: 0.177 ms
 Execution time: 1445.473 ms
(5 rows)

因此规划器通常将尽可能选择索引扫描。使用 btree 索引，我们可以得到这样的结果：

=# EXPLAIN ANALYZE SELECT * FROM tbloom WHERE i2 = 898732 AND i5 = 123451;
                                                           QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using btreeidx on tbloom  (cost=0.56..298311.96 rows=1 width=24) (actual time=445.709..445.709 rows=0 loops=1)
   Index Cond: ((i2 = 898732) AND (i5 = 123451))
   Heap Fetches: 0
 Planning time: 0.193 ms
 Execution time: 445.770 ms
(5 rows)

在处理这类搜索时，bloom 比 btree 表现得更好：

=# CREATE INDEX bloomidx ON tbloom USING bloom (i1, i2, i3, i4, i5, i6);
CREATE INDEX
=# EXPLAIN ANALYZE SELECT * FROM tbloom WHERE i2 = 898732 AND i5 = 123451;
                                                        QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on tbloom  (cost=178435.39..178439.41 rows=1 width=24) (actual time=76.698..76.698 rows=0 loops=1)
   Recheck Cond: ((i2 = 898732) AND (i5 = 123451))
   Rows Removed by Index Recheck: 2439
   Heap Blocks: exact=2408
   ->  Bitmap Index Scan on bloomidx  (cost=0.00..178435.39 rows=1 width=0) (actual time=72.455..72.455 rows=2439 loops=1)
         Index Cond: ((i2 = 898732) AND (i5 = 123451))
 Planning time: 0.475 ms
 Execution time: 76.778 ms
(8 rows)

注意其中相对较大的伪肯定数：有 2439 行被选中进行堆访问但实际却不匹配查询。我们可以通过指定更大的签名长度来减少这种情况。在这个例子中，用length=200创建索引可以把伪肯定数减小到 55，但是这同时会使索引尺寸翻倍（306MB）并且最终使这个查询变慢（总体 125 ms）。

现在，btree 搜索的主要问题是，当搜索条件不约束前几个索引列时，btree 的效率不好。对于 btree 更好的策略是在每一列上创建一个独立的索引。那么规划器将选择这样的计划：

=# EXPLAIN ANALYZE SELECT * FROM tbloom WHERE i2 = 898732 AND i5 = 123451;
                                                          QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on tbloom  (cost=9.29..13.30 rows=1 width=24) (actual time=0.148..0.148 rows=0 loops=1)
   Recheck Cond: ((i5 = 123451) AND (i2 = 898732))
   ->  BitmapAnd  (cost=9.29..9.29 rows=1 width=0) (actual time=0.145..0.145 rows=0 loops=1)
         ->  Bitmap Index Scan on tbloom_i5_idx  (cost=0.00..4.52 rows=11 width=0) (actual time=0.089..0.089 rows=10 loops=1)
               Index Cond: (i5 = 123451)
         ->  Bitmap Index Scan on tbloom_i2_idx  (cost=0.00..4.52 rows=11 width=0) (actual time=0.048..0.048 rows=8 loops=1)
               Index Cond: (i2 = 898732)
 Planning time: 2.049 ms
 Execution time: 0.280 ms
(9 rows)

尽管这个查询运行起来比在其中任一单个索引上都快，但是我们在索引尺寸上付出了很大的代价。每一个单列 btree 索引占用 214 MB，因此总的空间会超过 1.2GB，这是布鲁姆索引所使用的空间的 8 倍。

操作符类接口

用于布鲁姆索引的操作符类只要一个用于被索引数据类型的哈希函数以及一个用于搜索的等值操作符。这个例子展示了用于text数据类型的操作符类定义：

CREATE OPERATOR CLASS text_ops
DEFAULT FOR TYPE text USING bloom AS
    OPERATOR    1   =(text, text),
    FUNCTION    1   hashtext(text);

限制

• 在模块中只包括了用于int4以及text的操作符类。

• 搜索只支持=操作符。但是未来可以为带合并和交集操作的数组增加支持。

• bloom访问方法不支持UNIQUE索引。

• bloom访问方法不支持对NULL值的搜索。

btree_gin

btree_gin

btree_gin提供了一个为数据类型 int2、int4、int8、float4、 float8、timestamp with time zone、 timestamp without time zone、time with time zone、 time without time zone、date、interval、 oid、money、"char"、 varchar、text、bytea、bit、 varbit、macaddr、macaddr8、inet、 cidr、uuid、name、bool、 bpchar和所有enum类型实现B树等价行为的GIN操作符类例子。

通常，这些操作符类不会比等效的标准B树索引方法更好，并且它们缺少标准B树代码的一个主要特性：强制一致性的能力。但是，它们有助于GIN测试并且有助于作为开发其他GIN操作符类的基础。另外，对于测试一个GIN可索引的列和一个B树可索引的列的查询，创建一个使用这些操作符类之一的多列GIN索引要比创建必须通过位图AND组合在一起的两个独立索引要更有效。

用法示例

CREATE TABLE test (a int4);
-- create index
CREATE INDEX testidx ON test USING GIN (a);
-- query
SELECT * FROM test WHERE a < 10;

btree_gist

btree_gist

btree_gist为数据类型 int2、int4、int8、float4、 float8、numeric、timestamp with time zone、 timestamp without time zone、time with time zone、 time without time zone、date、interval、 oid、money、char、 varchar、text、bytea、bit、 varbit、macaddr、macaddr8、 inet、cidr、uuid和所有enum 类型提供了实现 B 树等效行为的 GiST 索引操作符类。

通常，这些操作符类不会比等效的标准 B 树索引方法更好，并且它们缺少标准 B 树代码的一个主要特性：强制一致性的能力。但是，如下文所述，它们提供了在一个 B 树索引中没有的一些其他特性。另外，当需要一个多列 GiST 索引，并且其某些列的数据类型只在 GiST 中是可索引的而其他列是简单数据类型时，这些操作符类就有用了。最后，这些操作符可以用于 GiST 测试以及作为开发其他 GiST 操作符类的基础。

除了典型的 B 树搜索操作符之外，btree_gist也为<>（“不等于”）提供了索引支持。这可能与下文描述的排他约束组合在一起产生作用。

另外，对于那些具有自然距离度量的数据类型，btree_gist定义了一个距离操作符<->，并且为使用这个操作符的最近邻搜索提供了 GiST 索引支持。距离操作符还提供给了：int2、int4、 int8、float4、 float8、timestamp with time zone、 timestamp without time zone、 time without time zone、date、interval、 oid和money。

用法示例

使用btree_gist代替btree的简单例子：

CREATE TABLE test (a int4);
-- create index
CREATE INDEX testidx ON test USING GIST (a);
-- query
SELECT * FROM test WHERE a < 10;
-- nearest-neighbor search: find the ten entries closest to "42"
SELECT *, a <-> 42 AS dist FROM test ORDER BY a <-> 42 LIMIT 10;

使用一个排他约束来强制规则：一个动物园里的一个笼子只能装一种动物：

=> CREATE TABLE zoo (
  cage   INTEGER,
  animal TEXT,
  EXCLUDE USING GIST (cage WITH =, animal WITH <>)
);
=> INSERT INTO zoo VALUES(123, 'zebra');
INSERT 0 1
=> INSERT INTO zoo VALUES(123, 'zebra');
INSERT 0 1
=> INSERT INTO zoo VALUES(123, 'lion');
ERROR:  conflicting key value violates exclusion constraint "zoo_cage_animal_excl"
DETAIL:  Key (cage, animal)=(123, lion) conflicts with existing key (cage, animal)=(123, zebra).
=> INSERT INTO zoo VALUES(124, 'lion');
INSERT 0 1

citext

citext

citext模块提供了一种大小写不敏感的字符串类型：citext。特别地，它在比较值时内部调用的是lower。除此之外，它的行为几乎与text完全相同。

基本原理

在UDB-TX中做大小写不敏感匹配的标准方法曾经是在比较值时使用lower函数，例如：

SELECT * FROM tab WHERE lower(col) = LOWER(?);

这工作得比较好，但是有一些缺点：

• 它让你的 SQL 语句冗长，并且你必须总是要记住在列和查询值上使用lower。

• 它不会使用一个索引，除非你使用lower创建一个函数索引。

• 如果你声明一个列为UNIQUE或PRIMARY KEY，隐式生成的索引是大小写敏感的。因此，它对于大小写不敏感的搜索是没有用处的，并且它不会强制大小写不敏感的唯一性。

citext数据类型允许你在 SQL 查询中消除对lower的调用，并且允许一个主键是大小写无关的。就和text一样，citext是区域相关的，这意味着大写和小写字符的匹配依赖于数据库LC_CTYPE设置的规则。此外，这种行为和在查询中使用lower是一样的。但是因为它是由数据类型以透明的方式完成的，你不需要记住在你的查询中做任何特殊的事情。

如何使用它

这里是一个简单的用法示例：

CREATE TABLE users (
    nick CITEXT PRIMARY KEY,
    pass TEXT   NOT NULL
);
INSERT INTO users VALUES ( 'larry',  sha256(random()::text::bytea) );
INSERT INTO users VALUES ( 'Tom',    sha256(random()::text::bytea) );
INSERT INTO users VALUES ( 'Damian', sha256(random()::text::bytea) );
INSERT INTO users VALUES ( 'NEAL',   sha256(random()::text::bytea) );
INSERT INTO users VALUES ( 'Bjørn',  sha256(random()::text::bytea) );
SELECT * FROM users WHERE nick = 'Larry';

即使nick列被设置为larry而查询是Larry，SELECT语句也将只返回一个元组。

cube

ube

这个模块实现了一种数据类型cube来表示多维立方体。

语法

表 F.2展示了cube类型有效的外部表示。x、*y*等表示浮点数。

立方体外部表示

外部语法	含义
*x*	一个一维点（或者长度为零的一维区间）
(*x*)	同上
*x1*,*x2*,...,*xn*	n-维空间中的一个点，内部表示为一个零容积立方体
(*x1*,*x2*,...,*xn*)	同上
(*x*),(*y*)	开始于x并且结束于y的一个一维区间，反之亦然。顺序并不重要
[(*x*),(*y*)]	同上
(*x1*,...,*xn*),(*y1*,...,*yn*)	一个 n-维立方体，用它的对角顶点对表示
[(*x1*,...,*xn*),(*y1*,...,*yn*)]	同上

一个立方体的对角录入的顺序无关紧要。如果需要创建一种统一的“左下 — 右上”的内部表示，cube函数会自动地交换值。当角重合时，cube只存储一个角和一个“is point”标志，这样避免浪费空间。

输入中的空白空间会被忽略，因此[(*x*),(*y*)]与[ ( *x* ), ( *y* ) ]相同。

精度

值在内部被存储为 64 位浮点数。这意味着超过 16 位有效位的数字将被截断。

用法

表 F.3展示了为类型cube提供的操作符。

表 F.3. 立方体操作符

操作符	结果	描述
a = b	boolean	立方体 a 和 b 相同。
a && b	boolean	立方体 a 和 b 重叠。
a @> b	boolean	立方体 a 包含 立方体 b。
a <@ b	boolean	立方体 a 被包含在立方体 b 中。
a < b	boolean	立方体 a 小于立方体 b。
a <= b	boolean	立方体 a 小于或者等于立方体 b。
a > b	boolean	立方体 a 大于立方体 b。
a >= b	boolean	立方体 a 大于或者等于立方体 b。
a <> b	boolean	立方体 a 不等于立方体 b。
a -> n	float8	得到立方体的第n个坐标（从 1 开始数）。
a ~> n	float8	以下列方式获取多维数据集的第n个坐标： n = 2 * k - 1表示第k维度的下限， n = 2 * k表示第k维度的上限。 负的n表示相应正坐标的倒数值。此运算符专为KNN-GiST支持而设计。
a <-> b	float8	a 和 b 之间的欧氏距离。
a <#> b	float8	a 和 b 之间的直线距离（taxicab 距离，L1 度量）。
a <=> b	float8	a 和 b 之间的切比雪夫（L-inf 度量）距离。

标量排序操作符（<、>=等）除了用来排序之外没有什么实际用途。这些操作符首先比较第一个坐标，如果它们相等再比较第二个坐标等等。它们主要为支持cube的 b-树索引操作符类而存在，这类操作符对支持cube列上的 UNIQUE 约束等很有用。

cube模块也为cube值提供了一个 GiST 索引操作符类。cube GiST 索引可以被用于在WHERE子句中通过=、&&、@>以及<@操作符来搜索值。

此外，cube GiST 索引可以被用在ORDER BY子句中通过度量操作符<->、<#>和<=>来查找最近邻。例如， 3-D 点(0.5, 0.5, 0.5)的最近邻可以用下面的查询很快地找到：

SELECT c FROM test ORDER BY c <-> cube(array[0.5,0.5,0.5]) LIMIT 1;

也可以用这种方式使用~>操作符来高效地检索通过选定坐标排序后的前几个值。例如，可以用下面的查询得到通过第一个坐标（左下角）升序排列后的前几个立方体：

SELECT c FROM test ORDER BY c ~> 1 LIMIT 5;

以及得到通过右上角第一个坐标降序排列后的 2-D 立方体：

SELECT c FROM test ORDER BY c ~> 3 DESC LIMIT 5;

表 F.4展示了可用的函数。

表 F.4. 立方体函数

函数	结果	描述	例子
cube(float8)	cube	制造一个一维立方体，坐标都是相同的。	cube(1) == '(1)'
cube(float8, float8)	cube	制造一个一维立方体。	cube(1,2) == '(1),(2)'
cube(float8[])	cube	使用数组定义的坐标制造一个零容积的立方体。	cube(ARRAY[1,2]) == '(1,2)'
cube(float8[], float8[])	cube	用由两个数组定义的右上和左下坐标制造一个立方体，两个数组必须等长。	cube(ARRAY[1,2], ARRAY[3,4]) == '(1,2),(3,4)'
cube(cube, float8)	cube	在一个现有的立方体上增加一维来制造一个新立方体，对新坐标的各个端点都采用相同的值。这可以用于从计算得到的值逐渐地构建立方体。	cube('(1,2),(3,4)'::cube, 5) == '(1,2,5),(3,4,5)'
cube(cube, float8, float8)	cube	在一个现有的立方体上增加一维来制造一个新立方体。这可以用于从计算得到的值逐渐地构建立方体。	cube('(1,2),(3,4)'::cube, 5, 6) == '(1,2,5),(3,4,6)'
cube_dim(cube)	integer	返回该立方体的维数	cube_dim('(1,2),(3,4)') == '2'
cube_ll_coord(cube, integer)	float8	返回一个立方体的左下角的第 n个坐标值	cube_ll_coord('(1,2),(3,4)', 2) == '2'
cube_ur_coord(cube, integer)	float8	返回一个立方体的右上角的第n个坐标值	cube_ur_coord('(1,2),(3,4)', 2) == '4'
cube_is_point(cube)	boolean	如果一个立方体是一个点则返回真，也就是两个定义点相同。
cube_distance(cube, cube)	float8	返回两个立方体之间的距离。如果两个都是点，这就是普通距离函数。
cube_subset(cube, integer[])	cube	从一个现有的立方体制造一个新立方体，使用来自于一个数组的维索引列表。它可以被用来抽取一个单一维度的端点，或者它可以被用来去除维度，或者按照需要对它们重新排序。	cube_subset(cube('(1,3,5),(6,7,8)'), ARRAY[2]) == '(3),(7)' cube_subset(cube('(1,3,5),(6,7,8)'), ARRAY[3,2,1,1]) == '(5,3,1,1),(8,7,6,6)'
cube_union(cube, cube)	cube	产生两个立方体的并
cube_inter(cube, cube)	cube	产生两个立方体的交
cube_enlarge(c cube, r double, n integer)	cube	用一个指定的半径r在至少n个维度上增加立方体的尺寸。如果该半径是负值，则该立方体会收缩。这有助于围绕一个点创建一个外包盒来搜索附近点。所有已定义的维度都会按照半径r被改变。左下坐标按照r被减小并且右上坐标按照r被增加。如果一个左下坐标被增加得超过对应的右上坐标（这只会发生在r< 0 时），则两个坐标会被设置为它们的均值。如果*n大于已定义的维度数并且该立方体被增加（*r >= 0）， 则额外的维度会被加入以让维度数达到n，对于额外的坐标将使用 0 作为初始值。这个函数可用来创建围绕一个点的外包盒以搜索临近点。	cube_enlarge('(1,2),(3,4)', 0.5, 3) == '(0.5,1.5,-0.5),(3.5,4.5,0.5)'

dblink

dblink

• dblink_connect — 打开一个到远程数据库的持久连接

• dblink_connect_u — 不安全地打开一个到远程数据库的持久连接

• dblink_disconnect— 关闭一个到远程数据库的持久连接

• dblink — 在一个远程数据库中执行一个查询

• dblink_exec— 在一个远程数据库中执行一个命令

• dblink_open— 在一个远程数据库中打开一个游标

• dblink_fetch— 从一个远程数据库中的打开的游标返回行

• dblink_close— 关闭一个远程数据库中的游标

• dblink_get_connections— 返回所有打开的命名 dblink 连接的名称

• dblink_error_message— 得到在命名连接上的最后一个错误消息

• dblink_send_query— 发送一个异步查询到远程数据库

• dblink_is_busy— 检查连接是否正在忙于一个异步查询

• dblink_get_notify— 在一个连接上检索异步通知

• dblink_get_result— 得到一个异步查询结果

• dblink_cancel_query — 在命名连接上取消任何活动查询

• dblink_get_pkey— 返回一个关系的主键域的位置和域名称

• dblink_build_sql_insert— 使用一个本地元组构建一个 INSERT 语句，将主键域值替换为提供的值

• dblink_build_sql_delete— 使用所提供的主键域值构建一个 DELETE 语句

• dblink_build_sql_update — 使用一个本地元组构建一个 UPDATE 语句，将主键域值替换为提供的值

dblink是一个支持在一个数据库会话中连接到其他UDB-TX数据库的模块。

还可以看看udb_fdw，它以一种更现代和更加兼容标准的架构提供了相同的功能。

dict_int

dict_int

dict_int是一个附加全文搜索词典模板的例子。这个例子词典的动机是控制整数（有符号和无符号）的索引，允许在阻止唯一词数量的过度增长（会严重影响搜索性能）时也能索引这些数字。

配置

该词典接受两个选项：

• maxlen参数指定在一个整数词中允许的最大位数。默认值为 6。

• rejectlong参数指定一个超长整数是否应该被截断或忽略。如果rejectlong为false（默认），该词典返回该整数的第一个数字。如果rejectlong为true，该词典将一个超长整数作为一个停用词对待，因此它将不会被索引。注意这也意味着这样一个整数不能被搜索。

用法

安装dict_int扩展会使用默认参数创建一个文本搜索模板intdict_template和一个基于它的词典intdict。你可以修改参数，例如

mydb# ALTER TEXT SEARCH DICTIONARY intdict (MAXLEN = 4, REJECTLONG = true);
ALTER TEXT SEARCH DICTIONARY

或者创建基于该模板的新词典。

要测试该词典，可以尝试

mydb# select ts_lexize('intdict', '12345678');
ts_lexize
-----------
 {123456}

dict_xsyn

dict_xsyn（扩展同义词字典）是一个附加全文搜索字典模板的例子。这种字典类型将词替换为它们的同义词分组，并且让使用其任一同义词进行搜索变得可能。

配置

一个dict_xsyn词典接受以下选项：

• matchorig控制该词典是否接受原生词。默认为true。

• matchsynonyms控制该词典是否接受同义词。默认为false。

• keeporig控制原生词是否被包括在词典的输出中。默认为true。

• keepsynonyms控制同义词是否被包括在词典的输出中。默认为true。

• rules是包含同义词列表的文件的基本名。这个文件必须被存储在$SHAREDIR/tsearch_data/（其中$SHAREDIR表示UDB-TX安装的共享数据目录）中。它的名称必须以.rules结束（这不包括在rules参数中）。

规则文件具有下面的格式：

• 每一行表示一个单一词的同义词分组，它在该行中首先被给出。同义词被空白分隔，这样：

  word syn1 syn2 syn3
  

• 井号（#）是注释定界符。它可以出现在一行中的任何位置。该行的剩余部分将被跳过。

例如，可以看看安装在$SHAREDIR/tsearch_data/中的xsyn_sample.rules。

用法

安装dict_xsyn扩展会用默认参数创建一个文本搜索模板xsyn_template以及一个基于它的词典xsyn。你可以修改参数，例如

mydb# ALTER TEXT SEARCH DICTIONARY xsyn (RULES='my_rules', KEEPORIG=false);
ALTER TEXT SEARCH DICTIONARY

或者基于该模板创建新的词典。

要测试该词典，你可以尝试

mydb=# SELECT ts_lexize('xsyn', 'word');
      ts_lexize
-----------------------
 {syn1,syn2,syn3}
mydb# ALTER TEXT SEARCH DICTIONARY xsyn (RULES='my_rules', KEEPORIG=true);
ALTER TEXT SEARCH DICTIONARY
mydb=# SELECT ts_lexize('xsyn', 'word');
      ts_lexize
-----------------------
 {word,syn1,syn2,syn3}
mydb# ALTER TEXT SEARCH DICTIONARY xsyn (RULES='my_rules', KEEPORIG=false, MATCHSYNONYMS=true);
ALTER TEXT SEARCH DICTIONARY
mydb=# SELECT ts_lexize('xsyn', 'syn1');
      ts_lexize
-----------------------
 {syn1,syn2,syn3}
mydb# ALTER TEXT SEARCH DICTIONARY xsyn (RULES='my_rules', KEEPORIG=true, MATCHORIG=false, KEEPSYNONYMS=false);
ALTER TEXT SEARCH DICTIONARY
mydb=# SELECT ts_lexize('xsyn', 'syn1');
      ts_lexize
-----------------------
 {word}

earthdistance

earthdistance模块提供两种不同的方法来计算地球表面的大圆距离。第一种要介绍的依赖于cube模块（必须在earthdistance之前安装）。第二种基于内建的point数据类型，为座标使用精度和纬度。

在这个模块中，地球被假定为完美的球型。

基于立方体的地球距离

数据被存储在立方体中，立方体的点（所有的角都一样）使用 3 个座标表示到地球中心的 x、y 和 z 距离。提供了一个cube之上的域earth，这包括检查值符合这些限制并且合理地接近于地球的真实表面的约束。

地球的半径获得自earth()函数。其单位是米。但是通过改变这一个函数你能够把该模块改为使用某些其他单位，或者使用一种你认为更合适的不同半径值。

这个包也有在天文数据库中的应用。天文学家可能想要改变earth()来返回一个180/pi()的半径，这样距离就会是度数。

函数也被提供来支持经纬度输入（以度数）、经纬度输出、计算两点间的大圆距离以及容易地指定一个可用于索引搜索的边界框。

所提供的函数在表 F.5中描述。

表 F.5. 基于立方体的地球距离函数

函数	返回	描述
earth()	float8	返回地球的假定半径。
sec_to_gc(float8)	float8	将地球表面两点间的普通直线（切线）距离转换为它们之间的大圆距离。
gc_to_sec(float8)	float8	将地球表面两点间的大圆距离转换为它们之间的普通直线（切线）距离。
ll_to_earth(float8, float8)	earth	给定一个地球表面点的维度（参数 1）和精度（参数 2）度数，返回它的位置。
latitude(earth)	float8	返回一个地球表面点的以度数表示的维度。
longitude(earth)	float8	返回一个地球表面点的以度数表示的经度。
earth_distance(earth, earth)	float8	返回地球表面上两点间的大圆距离。
earth_box(earth, float8)	cube	为一个位置的给定大圆距离范围内的点使用立方体@>操作符返回一个适合于索引搜索的框。这个框中的某些点到该位置的大圆距离会超过指定的大圆距离，因此使用earth_distance的第二次检查应该被包括在查询中。

基于点的地球距离

这个模块的第二部分依赖于将地球位置表示为类型point的值，其中第一部分被用来表示经度数，第二部分被用来表示纬度数。点被取做 (longitude, latitude) 并且不能反过来，因为经度更接近直观上的 x 轴，而纬度则接近 y 轴。

如表 F.6所示，这一部分只提供了一个单一操作符。

表 F.6. 基于点的地球距离操作符

操作符	返回	描述
point <@> point	float8	给定地球表面两点之间的法定英里距离。

注意和这个模块的基于cube的部分不同，这里的单位是被硬编码的：改变earth()函数将不会影响这个操作符的结果。

经度/纬度表示的一个缺点是你需要小心靠近两极和靠近经度正负 180 度处的边界情况。基于cube的表示可以避免这些不连续性。

file_fdw

file_fdw

file_fdw模块提供外部数据包装器file_fdw， 它能被用来访问服务器的文件系统中的数据文件，或者在服务器上执行程序并读取它们的输出。 数据文件或程序输出必须是能够被COPY FROM读取的格式， 详见COPY。当前只能读取数据文件。

用这个包装器创建的一个外部表可以有下列选项：

• filename

  指定要被读取的文件。必须是一个绝对路径名。 必须指定filename或program， 但不能同时指定两个。

• program

  指定要执行的命令。该命令的标准输出将被读取， 就像使用COPY FROM PROGRAM一样。必须指定program 或filename，但不能同时指定两个。

• format

  指定数据的格式，和COPY的FORMAT选项相同。

• header

  指定数据是否具有一个头部行，和COPY的HEADER选项相同。

• delimiter

  指定数据的定界符字符，和COPY的DELIMITER选项相同。

• quote

  指定数据的引用字符，和COPY的QUOTE选项相同。

• escape

  指定数据的转义字符，和COPY的ESCAPE选项相同。

• null

  指定数据的空字符串，和COPY的NULL选项相同。

• encoding

  指定数据的编码，和COPY的ENCODING选项相同。

注意虽然COPY允许诸如HEADER的选项不用一个相应的值指定， 但是外部表选项语法要求在所有情况下都出现一个值。要激活通常写入没有值的 COPY选项，你可以传递值 TRUE，因为所有这些选项都是布尔值。

使用这个包装器创建的表的一列可以具有下列选项：

• force_not_null

  这是一个布尔选项。如果为真，它指定该列的值不应该与空字符串匹配（也就是表级别的null选项）。这和把该列放在COPY的FORCE_NOT_NULL选项中具有相同的效果。

• force_null

  这是一个布尔选项。如果为真，它指定匹配空值字符串的列值会被返回为NULL， 即使该值被引号引用。如果没有这个选项，只有匹配空值字符串的未被引用的值会被返回为 NULL。这和在COPY的FORCE_NULL 选项中列出该列有同样的效果。

COPY的OIDS和FORCE_QUOTE选项当前不被file_fdw支持。

这些选项只能为一个外部表及其列指定，而不能在file_fdw外部数据包装器的选项中指定，也不能在使用该包装器的服务器或者用户映射的选项中指定。

出于安全原因，改变表级别的选项要求超级用户特权或 具有默认角色pg_read_server_files（使用文件名）或 默认角色pg_execute_server_program（使用程序）的权限： 只有特定用户能够控制读取哪个文件或者运行哪个程序。 原则上普通用户可以被允许改变其它选项，但是当前还不支持这样做。

当指定program选项时，请记住，选项字符串是通过shell执行的。 如果想传递任何参数到来自不受信任的源的命令， 必须小心去掉或转义任何对shell来说可能有特殊含义的字符。安全起见，最好使用固定的命令字符串， 或者至少避免传递任何用户输入。

对于一个使用file_fdw的外部表，EXPLAIN显示要读取的文件名或要运行的程序。对于文件来说，除非指定COSTS OFF，否则文件尺寸（以字节计）也会被显示。

例 F.1. 为Unvdbsvr CSV 日志创建一个外部表

一种file_fdw的用法是把可用的 Unvdbsvr 活动日志变成一个表用于查询。要这样做，首先你必须正在将日志记录到一个 CSV 文件，这里我们称其为pglog.csv。首先，将file_fdw安装为一个扩展：

CREATE EXTENSION file_fdw;

然后创建一个外部服务器：

CREATE SERVER pglog FOREIGN DATA WRAPPER file_fdw;

现在你已经准备好创建外部数据表。使用CREATE FOREIGN TABLE命令，你将需要为该表定义列、CSV 文件名以及格式：

CREATE FOREIGN TABLE pglog (
 log_time timestamp(3) with time zone,
  user_name text,
  database_name text,
  process_id integer,
  connection_from text,
  session_id text,
  session_line_num bigint,
  command_tag text,
  session_start_time timestamp with time zone,
  virtual_transaction_id text,
  transaction_id bigint,
  error_severity text,
  sql_state_code text,
  message text,
  detail text,
  hint text,
  internal_query text,
  internal_query_pos integer,
  context text,
  query text,
  query_pos integer,
  location text,
  application_name text
) SERVER pglog
OPTIONS ( filename '/home/josh/data/log/pglog.csv', format 'csv' );

就是这样了 — 现在你能够直接查询你的日志了。当然，在生产中你会需要定义一些方法来处理日志轮转。

fuzzystrmatch

fuzzystrmatch模块提供多个函数来判断字符串之间的相似性和距离。

Soundex

语音表示法系统是一种将相似发音的名字转换成相同的代码来匹配它们的方法。这最初由美国国家统计局在 1880 年、1900 年和 1910 年使用。注意语音表示法对于非英语名称不是很有用。

fuzzystrmatch模块提供了两个函数用于语音表示法代码：

soundex(text) 返回 text
difference(text, text) 返回 int

soundex函数将一个字符串转换成它的语音表示法代码。difference函数将两个字符串转换成它们的语音表示法代码并且接着报告能匹配代码位置的数量。由于语音表示法代码具有四个字符，结果可以从零到四，零表示没有匹配而四表示完全匹配（因此这个函数的命名并不适当 — similarity才是更合适的名称）。

这里有一些例子：

SELECT soundex('hello world!');
SELECT soundex('Anne'), soundex('Ann'), difference('Anne', 'Ann');
SELECT soundex('Anne'), soundex('Andrew'), difference('Anne', 'Andrew');
SELECT soundex('Anne'), soundex('Margaret'), difference('Anne', 'Margaret');
CREATE TABLE s (nm text);
INSERT INTO s VALUES ('john');
INSERT INTO s VALUES ('joan');
INSERT INTO s VALUES ('wobbly');
INSERT INTO s VALUES ('jack');
SELECT * FROM s WHERE soundex(nm) = soundex('john');
SELECT * FROM s WHERE difference(s.nm, 'john') > 2;

Levenshtein

这个函数计算两个字符串之间的编辑距离。

levenshtein(text source, text target, int ins_cost, int del_cost, int sub_cost) 返回 int
levenshtein(text source, text target) 返回 int
levenshtein_less_equal(text source, text target, int ins_cost, int del_cost, int sub_cost, int max_d) 返回 int
levenshtein_less_equal(text source, text target, int max_d) 返回 int

source以及target都可以是任何非空字符串， 最长为 255 个字符。代价参数分别指定一个字符插入、删除或替换的开销。 你可以像这个函数的第二种版本那样忽略代价参数，那样它们都会默认为 1。

levenshtein_less_equal是 Levenshtein 函数的速度更快 的版本，它被用于只对小距离感兴趣的情况。如果实际距离小于等于max_d， 那么levenshtein_less_equal返回正确的距离。否则它返回某个 大于max_d的值。如果max_d是负值，那么其行为等同于 levenshtein。

例子：

test=# SELECT levenshtein('GUMBO', 'GAMBOL');
 levenshtein
-------------
           2
(1 row)
test=# SELECT levenshtein('GUMBO', 'GAMBOL', 2,1,1);
 levenshtein
-------------
           3
(1 row)
test=# SELECT levenshtein_less_equal('extensive', 'exhaustive',2);
 levenshtein_less_equal
------------------------
                      3
(1 row)
test=# SELECT levenshtein_less_equal('extensive', 'exhaustive',4);
 levenshtein_less_equal
------------------------
                      4
(1 row)

Metaphone

和 Soundex 相似，Metaphone 的思想是构建一个输入字符串的一种代码。如果两个字符串具有相同的代码则认为它们相似。

这个函数计算一个输入字符串的变音位代码：

metaphone(text source, int max_output_length) 返回 text

source必须是一个非空字符串，最大长度为 255 个字符。max_output_length设置输出的变音位代码的最大长度，如果超长，输出会被截断到这个长度。

例子：

test=# SELECT metaphone('GUMBO', 4);
 metaphone
-----------
 KM
(1 row)

双 Metaphone

双变音位系统为一个给定输入字符串计算两个“听起来像的”字符串 — 一个“主要”代码和一个“次要”代码。在大部分情况下它们是相同的，但是对于非英语名称它们可能有一点不同，这取决于发音。这些函数计算主要和次要代码：

dmetaphone(text source) 返回 text
dmetaphone_alt(text source) 返回 text

对输入字符串没有长度限制。

例子：

test=# SELECT dmetaphone('gumbo');
 dmetaphone
------------
 KMP
(1 row)

hstore

这个模块实现了hstore数据类型用来在一个单一UDB-TX值中存储键值对。这在很多情景下都有用，例如带有很多很少被检查的属性的行或者半结构化数据。键和值都是简单的文本字符串。

hstore 外部表示

一个hstore的文本表示用于输入和输出，包括零个或者多个由逗号分隔的*key* => *value*对。一些例子：

k => v
foo => bar, baz => whatever
"1-a" => "anything at all"

键值对的顺序没有意义（并且在输出时也不会重现）。键值对之间或者=>号周围的空白会被忽略。双引号内的键和值可以包括空白、逗号、=或>。要在一个键或值中包括一个双引号或一个反斜线，用一个反斜线对它转义。

一个hstore中的每一个键是唯一的。如果你声明了一个有重复键的hstore，只有一个会被存储在hstore中并且无法保证哪一个将被保留：

SELECT 'a=>1,a=>2'::hstore;
 hstore
 ----------
 "a"=>"1"

一个值（但不是一个键）能够是一个 SQL NULL。例如：

key => NULL

NULL关键词是大小写不敏感的。将NULL放在双引号中可以将它当作一个普通的字符串“NULL”。

注意

记住当hstore文本格式当被用于输入时，它应用在任何必须的引用或转义之前。如果你通过一个参数传递一个hstore文字，那么不需要额外的处理。但是如果你将它作为一个引用的文字常数，那么任何单引号字符以及（取决于standard_conforming_strings配置参数的设置）反斜线字符需要被正确地转义。

在输出时，双引号总是围绕着键和值，即使这样做不是绝对必要。

hstore 操作符和函数

hstore模块所提供的操作符显示在表 F.7中，函数在表 F.8中。

表 F.7. hstore 操作符

操作符	描述	例子	结果
hstore -> text	为键得到值（不存在则是NULL）	'a=>x, b=>y'::hstore -> 'a'	x
hstore -> text[]	为多个键得到值（不存在则是NULL）	'a=>x, b=>y, c=>z'::hstore -> ARRAY['c','a']	{"z","x"}
hstore | | hstore	串接hstore
hstore ? text	hstore是否包含键？	'a=>1'::hstore ? 'a'	t
hstore ?& text[]	hstore是否包含所有指定的键？	'a=>1,b=>2'::hstore ?& ARRAY['a','b']	t
hstore ? | text[]	hstore是否包含任何指定的键？	`’a=>1,b=>2’::hstore ?
hstore @> hstore	左操作数是否包含右操作数？	'a=>b, b=>1, c=>NULL'::hstore @> 'b=>1'	t
hstore <@ hstore	左操作数是否被包含在右操作数中？	'a=>c'::hstore <@ 'a=>b, b=>1, c=>NULL'	f
hstore - text	从左操作数中删除键	'a=>1, b=>2, c=>3'::hstore - 'b'::text	"a"=>"1", "c"=>"3"
hstore - text[]	从左操作数中删除多个键	'a=>1, b=>2, c=>3'::hstore - ARRAY['a','b']	"c"=>"3"
hstore - hstore	从左操作数中删除匹配的对	'a=>1, b=>2, c=>3'::hstore - 'a=>4, b=>2'::hstore	"a"=>"1", "c"=>"3"
record #= hstore	用来自hstore的匹配值替换record中的域	见示例小节
%% hstore	把hstore转换成键和值交替出现的数组	%% 'a=>foo, b=>bar'::hstore	{a,foo,b,bar}
%# hstore	把hstore转换成二维的键值数组	%# 'a=>foo, b=>bar'::hstore	{{a,foo},{b,bar}}

表 F.8. hstore 函数

函数	返回类型	描述	例子	结果
hstore(record)	hstore	从一个记录或行构造一个hstore	hstore(ROW(1,2))	f1=>1,f2=>2
hstore(text[])	hstore	从一个数组构造一个hstore，数组可以是一个键值数组或者一个二维数组	`hstore(ARRAY[‘a’,’1’,’b’,’2’])
hstore(text[], text[])	hstore	从独立的键和值数组构建一个hstore	hstore(ARRAY['a','b'], ARRAY['1','2'])	"a"=>"1","b"=>"2"
hstore(text, text)	hstore	构造单一项的hstore	hstore('a', 'b')	"a"=>"b"
akeys(hstore)	text[]	取得hstore的键作为一个数组	akeys('a=>1,b=>2')	{a,b}
skeys(hstore)	setof text	取得hstore的键作为一个集合	skeys('a=>1,b=>2')	a b
avals(hstore)	text[]	取得hstore的值作为一个数组	avals('a=>1,b=>2')	{1,2}
svals(hstore)	setof text	取得hstore的值作为一个集合	svals('a=>1,b=>2')	1 2
hstore_to_array(hstore)	text[]	取得hstore的键和值作为一个键和值交替出现的数组	hstore_to_array('a=>1,b=>2')	{a,1,b,2}
hstore_to_matrix(hstore)	text[]	取得hstore的键和值作为一个二维的数组	hstore_to_matrix('a=>1,b=>2')	{{a,1},{b,2}}
hstore_to_json(hstore)	json	取得hstore作为一个json值，把所有非空值转换为 JSON 字符串	hstore_to_json('"a key"=>1, b=>t, c=>null, d=>12345, e=>012345, f=>1.234, g=>2.345e+4')	{"a key": "1", "b": "t", "c": null, "d": "12345", "e": "012345", "f": "1.234", "g": "2.345e+4"}
hstore_to_jsonb(hstore)	jsonb	取得hstore作为一个jsonb值，把把所有非空值转换为 JSON 字符串	hstore_to_jsonb('"a key"=>1, b=>t, c=>null, d=>12345, e=>012345, f=>1.234, g=>2.345e+4')	{"a key": "1", "b": "t", "c": null, "d": "12345", "e": "012345", "f": "1.234", "g": "2.345e+4"}
hstore_to_json_loose(hstore)	json	取得hstore作为一个json值，但是尝试区分数字值和布尔值这样它们在 JSON 中无需引用	hstore_to_json_loose('"a key"=>1, b=>t, c=>null, d=>12345, e=>012345, f=>1.234, g=>2.345e+4')	{"a key": 1, "b": true, "c": null, "d": 12345, "e": "012345", "f": 1.234, "g": 2.345e+4}
hstore_to_jsonb_loose(hstore)	jsonb	取得hstore作为一个jsonb值，但是尝试区分数字值和布尔值这样它们在 JSON 中无需引用	hstore_to_jsonb_loose('"a key"=>1, b=>t, c=>null, d=>12345, e=>012345, f=>1.234, g=>2.345e+4')	{"a key": 1, "b": true, "c": null, "d": 12345, "e": "012345", "f": 1.234, "g": 2.345e+4}
slice(hstore, text[])	hstore	从一个hstore抽取一个子集	slice('a=>1,b=>2,c=>3'::hstore, ARRAY['b','c','x'])	"b"=>"2", "c"=>"3"
each(hstore)	setof(key text, value text)	取得hstore的键和值作为一个集合	select * from each('a=>1,b=>2')	`key
exist(hstore,text)	boolean	hstore是否包含键？	exist('a=>1','a')	t
defined(hstore,text)	boolean	hstore是否为键包含非NULL值？	defined('a=>NULL','a')	f
delete(hstore,text)	hstore	删除匹配键的对	delete('a=>1,b=>2','b')	"a"=>"1"
delete(hstore,text[])	hstore	删除匹配多个键的多个对	delete('a=>1,b=>2,c=>3',ARRAY['a','b'])	"c"=>"3"
delete(hstore,hstore)	hstore	删除匹配第二个参数的对	delete('a=>1,b=>2','a=>4,b=>2'::hstore)	"a"=>"1"
populate_record(record,hstore)	record	用来自hstore的匹配值替换record中的域	见示例小节

注意

当一个hstore值被造型成json时，将使用函数hstore_to_json。同样地，当一个hstore值被造型成jsonb时，将使用函数hstore_to_jsonb。

索引

hstore有对@>、?、?&和?|操作符的 GiST 和 GIN 索引支持。例如：

CREATE INDEX hidx ON testhstore USING GIST (h);
CREATE INDEX hidx ON testhstore USING GIN (h);

hstore也为=操作符支持btree或hash索引。这允许hstore列被声明为UNIQUE或者被使用在GROUP BY、ORDER BY或DISTINCT表达式中。hstore值的排序顺序不是特别有用，但是这些索引可能对等值查找有用。为=比较创建以下索引：

CREATE INDEX hidx ON testhstore USING BTREE (h);
CREATE INDEX hidx ON testhstore USING HASH (h);

例子

增加一个键，或者用一个新值更新一个现有的键：

UPDATE tab SET h = h || hstore('c', '3');

删除一个键：

UPDATE tab SET h = delete(h, 'k1');

将一个record转换成一个hstore：

CREATE TABLE test (col1 integer, col2 text, col3 text);
INSERT INTO test VALUES (123, 'foo', 'bar');
SELECT hstore(t) FROM test AS t;
                   hstore                    
---------------------------------------------
 "col1"=>"123", "col2"=>"foo", "col3"=>"bar"
(1 row)

将一个hstore转换成一个预定义的record类型：

CREATE TABLE test (col1 integer, col2 text, col3 text);
SELECT * FROM populate_record(null::test1,
                              '"col1"=>"456", "col2"=>"zzz"');
 col1 | col2 | col3 
------+------+------
  456 | zzz  | 
(1 row)

用来自于一个hstore的值修改一个现有的记录：

CREATE TABLE test (col1 integer, col2 text, col3 text);
INSERT INTO test VALUES (123, 'foo', 'bar');
SELECT (r).* FROM (SELECT t #= '"col3"=>"baz"' AS r FROM test t) s;
 col1 | col2 | col3 
------+------+------
  123 | foo  | baz
(1 row)

统计

由于hstore类型本质的宽大性，它能够包含一些不同的键。检查合法键是应用的任务。下列例子验证了用于检查键以及获得统计的一些技术。

简单例子：

SELECT * FROM each('aaa=>bq, b=>NULL, ""=>1');

使用一个表：

SELECT (each(h)).key, (each(h)).value INTO stat FROM testhstore;

在线统计：

SELECT key, count(*) FROM
  (SELECT (each(h)).key FROM testhstore) AS stat
  GROUP BY key
  ORDER BY count DESC, key;
    key    | count
-----------+-------
 line      |   883
 query     |   207
 pos       |   203
 node      |   202
 space     |   197
 status    |   195
 public    |   194
 title     |   190
 org       |   189
...................

intagg

intagg模块提供了一个整数聚集器以及一个枚举器。intagg现在已被弃用，因为有内建的函数能提供其功能的超集。不过，该模块仍然作为内建函数的一个兼容性包装器被提供。

函数

聚集器是一个聚集函数int_array_aggregate(integer)，它产生一个完全包含输入整数的整数数组。这是一个array_agg的包装器，它对任何数组类型做同样的事情。

枚举器是一个函数int_array_enum(integer[])，它返回setof integer。它本质上是聚集器的一个逆操作：给定一个整数数组，将它展开成行的集合。这是unnest的一个包装器，它对任何数组类型做相同的事情。

使用示例

很多数据库系统都有一对多表的概念这样一个表通常位于两个被索引表之间，例如：

CREATE TABLE left (id INT PRIMARY KEY, ...);
CREATE TABLE right (id INT PRIMARY KEY, ...);
CREATE TABLE one_to_many(left INT REFERENCES left, right INT REFERENCES right);

通常这样用它：

SELECT right.* from right JOIN one_to_many ON (right.id = one_to_many.right)
WHERE one_to_many.left = item;

这将为左手表中的一个项返回右手表中的所有项。这在 SQL 中是很常见的结构。

现在，这种方法对于具有非常多项的one_to_many表会有麻烦。通常，这样的一次连接将为一个特定的左手项导致一次索引扫描以及为每一个右手项导致一次取出。如果你有一个非常动态的系统，你没什么可做的。但是，如果你的数据相对比较静态，你可以使用聚集器创建一个汇总表。

CREATE TABLE summary AS
SELECT left, int_array_aggregate(right) AS right
  FROM one_to_many
  GROUP BY left;

这将创建一个表，其中对每一个左项都有一行，并且有一个右项的数组。现在如果没有某种方法来使用该数组，这样做是没有任何用处的。这时数组枚举器就派上用场了，你可以

SELECT left, int_array_enum(right) FROM summary WHERE left = item;

上述使用int_array_enum的查询产生与以下语句相同的结果

SELECT left, right FROM one_to_many WHERE left = item;

区别是针对汇总表的查询必须只从表中得到一行，而针对one_to_many的直接查询必须索引扫描并且为每项都获取一行。

在一个系统上，一个EXPLAIN显示一个查询的代价从 8488 降低到了 329。原始查询是一个涉及到one_to_many表的连接，它被替换为：

SELECT right, count(right) FROM
  ( SELECT left, int_array_enum(right) AS right
    FROM summary JOIN (SELECT left FROM left_table WHERE left = item) AS lefts
         ON (summary.left = lefts.left)
  ) AS list
  GROUP BY right
  ORDER BY count DESC;

intarray

intarray模块提供了一些有用的函数和操作符来操纵不含空值的整数数组。也提供了对使用某些操作符的索引搜索的支持。

如果一个提供的数组中包含任何 NULL 元素，所有这些操作都将抛出一个错误。

很多这些操作只对一维数组有意义。尽管它们将接受更多维数的数组输入，数据将被当作一个按照存储顺序排列的线性数组对待。

intarray 函数和操作符

intarray模块提供的函数被列在表 F.9中，操作符被列在表 F.10中。

表 F.9. intarray 函数

函数	返回类型	描述	例子	结果
icount(int[])	int	数组中元素的数量	icount('{1,2,3}'::int[])	3
sort(int[], text dir)	int[]	排序数组 — dir必须是asc或desc	sort('{1,2,3}'::int[], 'desc')	{3,2,1}
sort(int[])	int[]	以升序排序	sort(array[11,77,44])	{11,44,77}
sort_asc(int[])	int[]	以升序排序	|
sort_desc(int[])	int[]	以降序排序	|
uniq(int[])	int[]	移除临近的重复	uniq(sort('{1,2,3,2,1}'::int[]))	{1,2,3}
idx(int[], int item)	int	匹配item的第一个元素的索引（如果没有为 0）	idx(array[11,22,33,22,11], 22)	2
subarray(int[], int start, int len)	int[]	从位置start开始的由len个元素组成的元组部分	subarray('{1,2,3,2,1}'::int[], 2, 3)	{2,3,2}
subarray(int[], int start)	int[]	从位置start开始的元组部分	subarray('{1,2,3,2,1}'::int[], 2)	{2,3,2,1}
intset(int)	int[]	创建单一元素数组	intset(42)	{42}

表 F.10. intarray 操作符

操作符	返回	描述
int[] && int[]	boolean	重叠 — 如果数组有至少一个公共元素，则为true
int[] @> int[]	boolean	包含 — 如果左数组包含右数组，则为true
int[] <@ int[]	boolean	被包含 — 如果左数组被右数组包含，则为true
# int[]	int	数组中元素的数目
int[] # int	int	索引（与idx函数相同）
int[] + int	int[]	把元素推到数组中（增加到数组末尾）
int[] + int[]	int[]	数组串接（把右数组增加到左数组的末尾）
int[] - int	int[]	从数组中移除匹配右参数的项
int[] - int[]	int[]	从左数组中移除右数组的元素
int[] | int	int[]
int[] | int[]	int[]
int[] & int[]	int[]	数组的交
int[] @@ query_int	boolean	如果数组满足查询（见下文），则为true
query_int ~~ int[]	boolean	如果数组满足查询（@@交换子），则为true

操作符&&、@>和<@等效于UDB-TX的内建同名操作符，不过它们只能在不含空值的整数数组上工作，而内建的操作符可以对任何数组类型工作。这种限制使它们在很多情况下比内建操作符更快。

@@和~~操作符测试一个数组是否满足一个query，它被表示成一种特殊数据类型query_int的一个值。一个由整数值组成的查询会被针对数组的元素检查，可能会组合使用操作符&（AND）、|（OR）以及!（NOT）。根据需要可以使用圆括号。例如，查询1&(2|3)匹配包含 1 并且还包括 2 或 3 的数组。

索引支持

intarray提供对于&&、@>、<@和@@操作符以及常规数组相等的索引支持。

提供了两种 GiST 索引操作符类：gist__int_ops（被默认使用）适合于中小尺寸的数据集，而gist__intbig_ops使用一种更大的签名并且更适合于索引大型数据集（即，包含大量可区分数组值的列）。该实现使用了一种带有内建有损压缩的 RD 树结构。

也有一种非默认的 GIN 操作符类gin__int_ops支持相同的操作符。

在 GiST 和 GIN 索引之间的选择取决于 GiST 和 GIN 的相对性能特点， 这将在其他地方讨论。

例子

-- 一个消息可以在一个或多个“小节”中
CREATE TABLE message (mid INT PRIMARY KEY, sections INT[], ...);
-- 创建专门的索引
CREATE INDEX message_rdtree_idx ON message USING GIST (sections gist__int_ops);
-- 选择小节 1 或 2 中的消息 - OVERLAP 操作符
SELECT message.mid FROM message WHERE message.sections && '{1,2}';
-- 选择小节 1 和 2 中的消息 - CONTAINS 操作符
SELECT message.mid FROM message WHERE message.sections @> '{1,2}';
-- 相同，使用 QUERY 操作符
SELECT message.mid FROM message WHERE message.sections @@ '1&2'::query_int;

测试基准

源代码目录contrib/intarray/bench包含有一个基准测试套件，可以针对 一个安装好的UDB-TX服务器运行这个套件（还要求安装 DBD::Pg）。要运行测试基准：

cd .../contrib/intarray/bench
createdb TEST
ud_sql -c "CREATE EXTENSION intarray" TEST
./create_test.pl | ud_sql TEST
./bench.pl

bench.pl脚本有多个选项，当它不使用任何参数运行时会显示这些选项。

isn

isn模块为下列国际产品编号标准提供数据类型：EAN13、UPC、ISBN（图书）、ISMN（音乐）以及 ISSN（期刊）。在输入时会按照一个硬编码的前缀列表对输入进行验证，这个前缀的列表也被用来在输出时连接号码。因为新的前缀总是不时地出现，这个前缀列表可能会过时。这个模块的一个未来版本有希望得到一个来自于一个或多个表的前缀列表，这样用户可以根据需要来方便地更新前缀列表。不过，在当前该列表只能通过修改源代码并且重新编译来更新。另外一种方案是，在这个模块的未来版本中可能会直接移除掉前缀验证和连接支持。

数据类型

表 F.11展示了isn模块提供的数据类型。

表 F.11. isn 数据类型

数据类型	描述
EAN13	欧洲文章号，总是以 EAN13 格式显示
ISBN13	国际标准图书号，以新的 EAN13 格式显示
ISMN13	国际标准音乐号，以新的 EAN13 格式显示
ISSN13	国际标准期刊号，以新的 EAN13 格式显示
ISBN	国际标准图书号，以旧的短格式显示
ISMN	国际标准音乐号，以旧的短格式显示
ISSN	国际标准期刊号，以旧的短格式显示
UPC	通用产品代码

一些注记：

1. ISBN13、ISMN13、ISSN13 号码都是 EAN13 号码。

2. EAN13 号码不总是 ISBN13、ISMN13 或 ISSN13（有些是）。

3. 一些 ISBN13 号码能够作为 ISBN 显示。

4. 一些 ISMN13 号码能够作为 ISMN 显示。

5. 一些 ISSN13 号码能够作为 ISSN 显示。

6. UPC 号码是 EAN13 号码的一个子集（它们基本上是去掉了第一个0位的 EAN13）。

7. 所有 UPC、ISBN、ISMN 以及 ISSN 号码可以被表示为 EAN13 号码。

在内部，所有这些类型使用同一种表达（一个 64 位整数）并且所有内部表达是可以互换的。多种类型被提供来控制显示格式化并且对假定为表示一种特定类型号码的输入进行更严格的合法性检查。

在可能时，ISBN、ISMN和ISSN类型将显示号码的短版本（ISxN 10），并且在无法适应短版本时显示号码的 ISxN 13 格式。EAN13、ISBN13、ISMN13和ISSN13类型总是显示长版本的 ISxN（EAN13）。

造型

isn模块提供了下列类型之间的造型：

• ISBN13 <=> EAN13

• ISMN13 <=> EAN13

• ISSN13 <=> EAN13

• ISBN <=> EAN13

• ISMN <=> EAN13

• ISSN <=> EAN13

• UPC <=> EAN13

• ISBN <=> ISBN13

• ISMN <=> ISMN13

• ISSN <=> ISSN13

当从EAN13造型为另一种类型时， 会有对该值是否在另一种类型的域中的运行时检查，如果不在则抛出一个错误。其他的造型则是简单地重新贴个标签，因而总是会成功。

函数和操作符

isn模块提供了标准的比较操作符，外加对所有这些数据类型的 B 树和哈希索引支持。此外还有一些特殊的函数，它们展示在表 F.12中。在这个表中，isn意味着该模块的数据类型中的任何一种。

表 F.12. isn 函数

函数	返回	描述
isn_weak(boolean)	boolean	设置弱输入模式（返回新设置）
isn_weak()	boolean	得到弱模式的当前状态
make_valid(isn)	isn	验证一个非法号码（清除非法标志）
is_valid(isn)	boolean	检查非法标志的存在

弱模式被用来允许插入非法数据到一个表中。非法意味着校验位错误，而不是有丢失号码。

为什么你会想要使用弱模式？你可能有一个巨大的 ISBN 号码集合并且出于某种奇怪的原因其中具有错误的校验位（可能这些号码是从印刷稿中扫描并且 OCR 而来，也可能是手工输入的……谁知道呢）。不管怎样，重点是你可能希望清理这些混乱，但是你仍然想要能够把这些号码放在你的数据库中并且可能会使用一个外部工具在数据库中定位非法号码，这样你能够更容易地验证信息。因此你可能会想要在表中选择所有非法的号码。

当你使用弱模式在一个表中插入非法号码时，被插入的号码将会被加上修正过的校验位，但是它的最后将会有一个感叹号（!），例如0-11-000322-5!。这种非法标志符可以用is_valid函数检查并且可以用make_valid函数清除。

即使不在弱模式中，你也能通过在号码某位追加!字符来强制非法号码的插入。

另一个特殊特性是在输入过程中，你可以写一个?代替校验位，然后正确的校验位将被自动插入。

例子

--直接使用类型：
SELECT isbn('978-0-393-04002-9');
SELECT isbn13('0901690546');
SELECT issn('1436-4522');
--转换类型：
-- 注意只有在号码处于另一种类型的合法值之中时，才能从 EAN13 转换成另一种类型
-- 因此下面的用法将不会工作： select isbn(ean13('0220356483481'));
-- 但是下面的可以：
SELECT upc(ean13('0220356483481'));
SELECT ean13(upc('220356483481'));
--创建一个表，它有一个单一列来保存 ISBN 号码：
CREATE TABLE test (id isbn);
INSERT INTO test VALUES('9780393040029');
--自动计算校验位（观察 '?'）：
INSERT INTO test VALUES('220500896?');
INSERT INTO test VALUES('978055215372?');
SELECT issn('3251231?');
SELECT ismn('979047213542?');
--使用弱模式：
SELECT isn_weak(true);
INSERT INTO test VALUES('978-0-11-000533-4');
INSERT INTO test VALUES('9780141219307');
INSERT INTO test VALUES('2-205-00876-X');
SELECT isn_weak(false);
SELECT id FROM test WHERE NOT is_valid(id);
UPDATE test SET id = make_valid(id) WHERE id = '2-205-00876-X!';
SELECT * FROM test;
SELECT isbn13(id) FROM test;

lo

lo模块提供管理大对象（也被称为 LO 或 BLOB）的支持。这包括一种数据类型lo以及一个触发器lo_manage。

原理

JDBC 驱动的问题之一（并且这也影响 ODBC 驱动）是其说明书假定对 BLOB（二进制大对象）的引用被存储在一个表中，并且如果该项被改变相关的 BLOB 会被从数据库删除。

但对于UDB-TX来说这并不会发生。大对象被当做自主的对象，一个表项可以通过 OID 引用一个大对象，但是可以有多个表项引用同一个大对象 OID，因此系统不会因为你改变或者删除这种项而删除大对象。

现在这对UDB-TX-相关的应用挺好的，但是使用 JDBC 或 ODBC 的标准代码不会删除那些对象，从而导致孤立对象 — 不被任何东西引用的对象，而且会占据磁盘空间。

lo允许通过附加一个触发器到包含 LO 引用列的表来修复这种问题。该触发器本质上只是在你删除或修改一个引用大对象的值时做lo_unlink。当你使用这个触发器时，你必须假定在一个触发器控制的列中只有一个对任意大对象的数据库引用！

这个模块也提供了一种数据类型lo，它实际上只是oid类型的一个域。这有助于区分保存大对象引用的数据库列和保存其他东西 OID 的列。你并不是必须使用lo类型来使用该触发器，但是用它来追踪数据库中哪些列表示你要用触发器管理的大对象非常方便。也有传言说如果你不为 BLOB 列使用lo，ODBC 驱动会感到困惑。

如何使用它

这里是一个简单的用法示例：

CREATE TABLE image (title text, raster lo);

CREATE TRIGGER t_raster BEFORE UPDATE OR DELETE ON image
    FOR EACH ROW EXECUTE FUNCTION lo_manage(raster);

对每一个将包含到大对象唯一引用的列，创建一个BEFORE UPDATE OR DELETE触发器，并且将该列名作为唯一的触发器参数。你也可以用BEFORE UPDATE OF *column_name*来限制该触发器只对该列上的更新事件执行。如果你需要在同一个表中有多个lo列，为每一个创建一个独立的触发器，记住为同一个表上的每个触发器指定一个不同的名称。

限制

• 删除一个表仍将让它包含的任何对象变成孤立的，因为触发器在这种情况下不会被执行。你可以在DROP TABLE之前放上DELETE FROM *table*来避免这种问题。

  TRUNCATE有同样的危害。

  如果你已经有或者怀疑有孤立的大对象，参考vacuumlo模块可以帮助你清理它们。偶尔运行vacuumlo作为lo_manage触发器的后备是个好主意。

• 有些前端可能会创建它们自己的表，并且将不会创建相关的触发器。另外，用户可能不会记得（或知道）要创建触发器。

ltree

ltree

这个模块实现了一种数据类型ltree用于表示存储在一个层次树状结构中的数据的标签。还提供了在标签树中搜索的扩展功能。

定义

一个标签是一个字母数字字符和下划线的序列（例如，在 C 区域中允许字符A-Za-z0-9_）。标签长度必须少于 256 字节。

例子： 42, Personal_Services

一个标签路径是由点号分隔的零个或者更多个标签的序列，例如L1.L2.L3，它表示一个从层次树的根到一个特定节点的路径。一个标签路径的长度必须小于 65kB，但是最好将它保持在 2kB 以下。

例子：Top.Countries.Europe.Russia

ltree模块提供多种数据类型：

• ltree存储一个标签路径。

• lquery表示一个用于匹配ltree值的类正则表达式的模式。一个简单词匹配一个路径中的那个标签。一个星号（*）匹配零个或更多个标签。例如：

foo         正好匹配标签路径foo
*.foo.*     匹配任何包含标签foo的标签路径
*.foo       匹配任何最后一个标签是foo的标签路径

星号也可以被限定来限制它能匹配多少标签：

*{n}        匹配正好n个标签
*{n,}       匹配至少n个标签
*{n,m}      匹配至少n个但是最多m个标签
*{,m}       匹配最多m个标签 — 与*{0,m}相同 

在lquery中，有多种修饰符可以被放在一个非星号标签的末尾来让它不仅仅能准确匹配：

@           不区分大小写匹配，例如a@匹配A
*           匹配带此前缀的任何标签，例如foo*匹配foobar
%           匹配开头以下划线分隔的词

%的行为有点复杂。它尝试匹配词而不是整个标签。例如，foo_bar%匹配foo_bar_baz但是不匹配foo_barbaz。如果和*组合，前缀匹配可以单独应用于每一个词，例如foo_bar%*匹配foo1_bar2_baz但不匹配foo1_br2_baz。

此外，你可以写多个带有|（OR）的可能改过的标签来匹配那些标签中的任何一个（或几个），并且你可以在最前面放上!（NOT）来匹配任何不匹配那些分支的标签。

这里是一个lquery的例子：

Top.*{0,2}.sport*@.!football|tennis.Russ*|Spain
a.  b.     c.      d.               e.

• 这个查询将匹配任何这样的标签路径：

  1. 开始于标签Top

  2. 并且接着具有 0 到 2 个标签

  3. 之后是一个开始于大小写无关的前缀sport的标签

  4. 再后是一个不匹配football和tennis的标签

  5. 并且结尾是一个开始于Russ的标签，或者完全匹配Spain的标签。

• ltxtquery表示一种用于匹配ltree值的类全文搜索的模式。一个ltxtquery值包含词，也可能在末尾带有修饰符@、*、%，修饰符具有和lquery中相同的含义。词可以用&（AND）、|（OR）、!（NOT）以及圆括号组合。lquery和ltxtquery的关键区别是前者匹配词时不考虑它们在标签路径中的位置。

  这是一个ltxtquery的例子：

  Europe & Russia*@ & !Transportation
  

  这将匹配包含标签Europe以及任何以Russia开始（大小写不敏感）的标签的路径，但是不匹配包含标签Transportation的路径。这些词在路径中的位置并不重要。还有，当使用%时，该次可以与一个标签中任何下划线分隔的词匹配，而不管它们的位置如何。

注意：ltxtquery允许符号之间的空白，但是ltree和lquery不允许。

操作符和函数

类型ltree有普通比较操作符 =、<>、 <、>、<=、>=。 比较会按照树遍历的顺序排序，一个节点的子女按照标签文本排序。另外，还有表F.13中显示的特殊操作符。

表 F.13. ltree 操作符

操作符	返回值	描述
ltree @> ltree	boolean	左参数是不是右参数的一个祖先（或者相等）？
ltree <@ ltree	boolean	左参数是不是右参数的一个后代（或者相等）？
ltree ~ lquery	boolean	ltree匹配lquery吗？
lquery ~ ltree	boolean	ltree匹配lquery吗？
ltree ? lquery[]	boolean	ltree匹配数组中的任意lquery吗？
lquery[] ? ltree	boolean	ltree匹配数组中的任意lquery吗？
ltree @ ltxtquery	boolean	ltree匹配ltxtquery吗？
ltxtquery @ ltree	boolean	ltree匹配ltxtquery吗？
ltree | | ltree
ltree | | text
text | | ltree
ltree[] @> ltree	boolean	数组是否包含ltree的一个祖先？
ltree <@ ltree[]	boolean	数组是否包含ltree的一个祖先？
ltree[] <@ ltree	boolean	数组是否包含ltree的一个后代？
ltree @> ltree[]	boolean	数组是否包含ltree的一个后代？
ltree[] ~ lquery	boolean	数组是否包含匹配lquery的路径？
lquery ~ ltree[]	boolean	数组是否包含匹配lquery的路径？
ltree[] ? lquery[]	boolean	ltree数组是否包含匹配任意lquery的路径？
lquery[] ? ltree[]	boolean	ltree数组是否包含匹配任意lquery的路径？
ltree[] @ ltxtquery	boolean	数组是否包含匹配ltxtquery的路径？
ltxtquery @ ltree[]	boolean	数组是否包含匹配ltxtquery的路径？
ltree[] ?@> ltree	ltree	是ltree祖先的第一个数组项；如果没有则是 NULL
ltree[] ?<@ ltree	ltree	是ltree祖先的第一个数组项；如果没有则是 NULL
ltree[] ?~ lquery	ltree	匹配lquery的第一个数组项；如果没有则是 NULL
ltree[] ?@ ltxtquery	ltree	匹配lquery的第一个数组项；如果没有则是 NULL

操作符<@、@>、 @以及~有类似的、 ^<@、^@>、^@、 ^~，只是它们不适用索引。它们只对测试目的有用。

可用的函数在表 F.14中。

表 F.14. ltree 函数

函数	返回类型	描述	例子	结果
subltree(ltree, int start, int end)	ltree	ltree的从位置start到位置end-1（从 0 开始计）的子路径	subltree('Top.Child1.Child2',1,2)	Child1
subpath(ltree, int offset, int len)	ltree	ltree从位置offset开始长度为len的子路径。如果offset为负，则子路径开始于距离路径尾部那么远的位置。如果len为负，则从路径的尾部开始丢掉那么多个标签。	subpath('Top.Child1.Child2',0,2)	Top.Child1
subpath(ltree, int offset)	ltree	ltree从位置offset开始一直延伸到路径末尾的子路径。如果offset为负，则子路径开始于距离路径尾部那么远的位置。	subpath('Top.Child1.Child2',1)	Child1.Child2
nlevel(ltree)	integer	路径中标签的数量	nlevel('Top.Child1.Child2')	3
index(ltree a, ltree b)	integer	a中第一次出现b的位置，如果没有找到则为 -1	index('0.1.2.3.5.4.5.6.8.5.6.8','5.6')	6
index(ltree a, ltree b, int offset)	integer	a中第一次出现b的位置，搜索从offset开始。负的offset表示从距路径尾部-offset个标签的位置开始	index('0.1.2.3.5.4.5.6.8.5.6.8','5.6',-4)	9
text2ltree(text)	ltree	把text转换成ltree	|
ltree2text(ltree)	text	把ltree转换成text	|
lca(ltree, ltree, ...)	ltree	路径的最长公共祖先（最多支持 8 个参数）	lca('1.2.3','1.2.3.4.5.6')	1.2
lca(ltree[])	ltree	数组中路径的最长公共祖先	lca(array['1.2.3'::ltree,'1.2.3.4'])	1.2

索引

ltree支持一些能加速上述操作符的索引类型：

• ltree上的 B-树索引： <、<=、=、 >=、>

• ltree上的 GiST 索引： <、<=、=、 >=、>、 @>、<@、 @、~、?

  创建这样一个索引的例子：

  CREATE INDEX path_gist_idx ON test USING GIST (path);
  

• ltree[]上的 GiST 索引： ltree[] <@ ltree、ltree @> ltree[]、 @、~、?

  创建这样一个索引的例子：

  CREATE INDEX path_gist_idx ON test USING GIST (array_path);
  

  注意：这种索引类型是有损的。

例子

这个例子使用下列数据（在源代码发布的contrib/ltree/ltreetest.sql文件中也有）：

CREATE TABLE test (path ltree);
INSERT INTO test VALUES ('Top');
INSERT INTO test VALUES ('Top.Science');
INSERT INTO test VALUES ('Top.Science.Astronomy');
INSERT INTO test VALUES ('Top.Science.Astronomy.Astrophysics');
INSERT INTO test VALUES ('Top.Science.Astronomy.Cosmology');
INSERT INTO test VALUES ('Top.Hobbies');
INSERT INTO test VALUES ('Top.Hobbies.Amateurs_Astronomy');
INSERT INTO test VALUES ('Top.Collections');
INSERT INTO test VALUES ('Top.Collections.Pictures');
INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy');
INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy.Stars');
INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy.Galaxies');
INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy.Astronauts');
CREATE INDEX path_gist_idx ON test USING GIST (path);
CREATE INDEX path_idx ON test USING BTREE (path);

现在，我们有一个表test，它被填充了描述下列层次的数据：

                        Top
                     /   |  \
             Science Hobbies Collections
                 /       |              \
        Astronomy   Amateurs_Astronomy Pictures
           /  \                            |
Astrophysics  Cosmology                Astronomy
                                        /  |    \
                                 Galaxies Stars Astronauts

我们可以做继承：

ltreetest=> SELECT path FROM test WHERE path <@ 'Top.Science';
                path
------------------------------------
 Top.Science
 Top.Science.Astronomy
 Top.Science.Astronomy.Astrophysics
 Top.Science.Astronomy.Cosmology
(4 rows)

这里是一些路径匹配的例子：

ltreetest=> SELECT path FROM test WHERE path ~ '*.Astronomy.*';
                     path
-----------------------------------------------
 Top.Science.Astronomy
 Top.Science.Astronomy.Astrophysics
 Top.Science.Astronomy.Cosmology
 Top.Collections.Pictures.Astronomy
 Top.Collections.Pictures.Astronomy.Stars
 Top.Collections.Pictures.Astronomy.Galaxies
 Top.Collections.Pictures.Astronomy.Astronauts
(7 rows)
ltreetest=> SELECT path FROM test WHERE path ~ '*.!pictures@.*.Astronomy.*';
                path
------------------------------------
 Top.Science.Astronomy
 Top.Science.Astronomy.Astrophysics
 Top.Science.Astronomy.Cosmology
(3 rows)

这里是一些全文搜索的例子：

ltreetest=> SELECT path FROM test WHERE path @ 'Astro*% & !pictures@';
                path
------------------------------------
 Top.Science.Astronomy
 Top.Science.Astronomy.Astrophysics
 Top.Science.Astronomy.Cosmology
 Top.Hobbies.Amateurs_Astronomy
(4 rows)
ltreetest=> SELECT path FROM test WHERE path @ 'Astro* & !pictures@';
                path
------------------------------------
 Top.Science.Astronomy
 Top.Science.Astronomy.Astrophysics
 Top.Science.Astronomy.Cosmology
(3 rows)

使用函数的路径构建：

ltreetest=> SELECT subpath(path,0,2)||'Space'||subpath(path,2) FROM test WHERE path <@ 'Top.Science.Astronomy';
                 ?column?
------------------------------------------
 Top.Science.Space.Astronomy
 Top.Science.Space.Astronomy.Astrophysics
 Top.Science.Space.Astronomy.Cosmology
(3 rows)

我们可以通过常见一个在路径中指定位置插入标签的 SQL 函数来简化：

CREATE FUNCTION ins_label(ltree, int, text) RETURNS ltree
    AS 'select subpath($1,0,$2) || $3 || subpath($1,$2);'
    LANGUAGE SQL IMMUTABLE;
ltreetest=> SELECT ins_label(path,2,'Space') FROM test WHERE path <@ 'Top.Science.Astronomy';
                ins_label
------------------------------------------
 Top.Science.Space.Astronomy
 Top.Science.Space.Astronomy.Astrophysics
 Top.Science.Space.Astronomy.Cosmology
(3 rows)

pageinspect

pageinspect模块提供函数让你从低层次观察数据库页面的内容，这对于调试目的很有用。所有这些函数只能被超级用户使用。

get_raw_page(relname text, fork text, blkno int) 返回 bytea

get_raw_page读取提及的关系中的指定块并且以一个bytea值的形式返回一个拷贝。这允许得到该块的一个单一的时间一致的拷贝。对于主数据分叉，*fork*应该是'main'，对于空闲空间映射应该是'fsm'，对于可见性映射应该是'vm'，对于初始化分叉应该是'init'。

get_raw_page(relname text, blkno int) 返回 bytea

一个简写版的get_raw_page，用于读取主分叉。等效于get_raw_page(relname, 'main', blkno)

page_header(page bytea) 返回 record

page_header显示所有UDB-TX堆和索引页面的公共域。

用get_raw_page获得的一个页面映像应该作为参数传递。例如：

test=# SELECT * FROM page_header(get_raw_page('pg_class', 0));
    lsn    | checksum | flags  | lower | upper | special | pagesize | version | prune_xid
-----------+----------+--------+-------+-------+---------+----------+---------+-----------
 0/24A1B50 |        0 |      1 |   232 |   368 |    8192 |     8192 |       4 |         0

返回的列对应于PageHeaderData结构中的域。详见src/include/storage/bufpage.h。

checksum域是存放在页面中的校验和，如果页面被损坏它可能是不正确的。如果对这个实例没有启用数据校验和，则存储的这个值没有意义。

page_checksum(page bytea, blkno int4) returns smallint

page_checksum为页面计算校验和，就像它被放置在给定块上一样。

应该将get_raw_page得到的页面映像作为参数传入。例如：

test=# SELECT page_checksum(get_raw_page('pg_class', 0), 0);
 page_checksum
---------------
         13443

注意校验和取决于块号，因此应该将匹配的块号传入（除非在做调试）。

用这个函数计算的校验和可以拿来和函数page_header的结果域checksum进行比较。如果为这个实例启用了数据校验和，则两个值应该相等。

heap_page_items(page bytea) 返回 setof record

heap_page_items显示一个堆页面上所有的行指针。 对那些使用中的行指针，元组头部和元组原始数据也会被显示。 不管元组对于拷贝原始页面时的 MVCC 快照是否可见，它们都会被显示。

用get_raw_page获得的一个堆页面映像应该作为参数传递。例如：

test=# SELECT * FROM heap_page_items(get_raw_page('pg_class', 0));

返回的域的解释可见src/include/storage/itemid.h和src/include/access/htup_details.h。

tuple_data_split(rel_oid, t_data bytea, t_infomask integer, t_infomask2 integer, t_bits text [, do_detoast bool]) returns bytea[]

tuple_data_split以后端内部的相同方式将元组数据拆解成属性。

test=# SELECT tuple_data_split('pg_class'::regclass, t_data, t_infomask, t_infomask2, t_bits) FROM heap_page_items(get_raw_page('pg_class', 0));

应该用与heap_page_items的返回属性相同的参数来调用这个函数。

如果*do_detoast*是true，则根据需要将把属性解除TOAST。默认值为false。

heap_page_item_attrs(rel_oid, t_data bytea, [, do_detoast bool]) returns bytea[]

heap_page_item_attrs等效于 heap_page_items，不过它会把元组原始数据 返回为属性的数组，如果*do_detoast*为真（ 默认为false），这些属性会被反 TOAST。

应该把用get_raw_page得到的一个堆页面映像 作为参数传入。例如：

test=# SELECT * FROM heap_page_item_attrs(get_raw_page('pg_class', 0), 'pg_class'::regclass);
fsm_page_contents(page bytea) returns text

fsm_page_contents展示一个FSM页面的内部节点结构。输出是一个多行字符串，每一行对应于页面中二叉树的每一个节点。只有非零节点才会被打印。所谓的“next”指针（指向页面中下一个要返回的槽）也会被打印。

更多有关FSM页面结构的信息请见src/backend/storage/freespace/README。

passwordcheck

只要通过CREATE ROLE或ALTER ROLE设置用户， passwordcheck模块会检查用户的口令。如果一个口令被认为太弱，它将被拒绝并且该命令将带着一个错误终止。

要启用这个模块，把'$libdir/passwordcheck'加入到unvdbsvr.conf中的shared_preload_libraries，然后重启服务器。

你可以通过修改源代码来按你的需要修改这个模块。例如，你可以使用CrackLib来检查口令 — 这只需要在Makefile中取消两行的注释并且重新编译该模块（由于授权原因，我们不能默认包括CrackLib）。如果没有CrackLib，该模块会对口令强度强制一些简单的规则，你可以自行修改和扩充。

#vim /home/t/data/unvdbsvr.conf
添加shared_preload_libraries = 'passwordcheck'
#需要重新启动
ud_ctl restart -D /home/t/data/

pg_buffercache

pg_buffercache模块提供了一种方法实时检查共享缓冲区。

该模块提供了一个 C 函数pg_buffercache_pages，它返回一个记录的集合，外加一个包装了该函数以便于使用的视图pg_buffercache。

默认情况下，使用仅限于超级用户和pg_read_all_stats 角色的成员。可以使用GRANT给其他人授予访问权限。

pg_buffercache视图

视图显示的列的定义如表 F.15所示。

表 F.15. pg_buffercache 列

名称	类型	引用	描述
bufferid	integer		ID，在范围 1..shared_buffers中
relfilenode	oid	pg_class.relfilenode	关系的文件结点编号
reltablespace	oid	pg_tablespace.oid	关系的表空间 OID
reldatabase	oid	pg_database.oid	关系的数据库 OID
relforknumber	smallint		关系内的分叉数，见include/common/relpath.h
relblocknumber	bigint		关系内的页面数
isdirty	boolean		页面是否为脏？
usagecount	smallint		Clock-sweep 访问计数
pinning_backends	integer		对这个缓冲区加 pin 的后端数量

共享缓存中的每一个缓冲区都有一行。没有使用的缓冲区的行中只有bufferid为非空。共享的系统目录被显示为属于数据库零。

因为缓冲是所有数据库共享的，通常会有不属于当前数据库的关系的页面。这意味着对于一些行在pg_class中可能不会有匹配的连接行，或者甚至有错误的连接。如果你试图与pg_class连接，将连接限制于reldatabase等于当前数据库 OID 或零的行是一个好主意。

当访问pg_buffercache视图时，内部缓冲区管理器会被锁住足够长时间来拷贝视图将显示的所有缓冲区状态数据。这确保了该视图会产生一个一致的结果集合，而不会不必要地长时间阻塞普通的缓冲区活动。尽管如此，如果经常读取这个视图还是会对数据库性能产生一些影响。

样例输出

regression=# SELECT c.relname, count(*) AS buffers
             FROM pg_buffercache b INNER JOIN pg_class c
             ON b.relfilenode = pg_relation_filenode(c.oid) AND
                b.reldatabase IN (0, (SELECT oid FROM pg_database
                                      WHERE datname = current_database()))
             GROUP BY c.relname
             ORDER BY 2 DESC
             LIMIT 10;
             relname             | buffers
---------------------------------+---------
 tenk2                           |     345
 tenk1                           |     141
 pg_proc                         |      46
 pg_class                        |      45
 pg_attribute                    |      43
 pg_class_relname_nsp_index      |      30
 pg_proc_proname_args_nsp_index  |      28
 pg_attribute_relid_attnam_index |      26
 pg_depend                       |      22
 pg_depend_reference_index       |      20
(10 rows)

pgcrypto模块为UDB-TX提供了密码函数。

普通哈希函数

digest()
digest(data text, type text) returns bytea
digest(data bytea, type text) returns bytea

计算一个给定*data的一个二进制哈希值。type*是要使用的算法。标准算法是md5、sha1、sha224、sha256、sha384和sha512。如果使用 OpenSSL 编译了pgcrypto，如表 F.19中所述，有更多算法可用。

如果你想摘要成为一个十六进制字符串，可以在结果上使用encode()。例如：

CREATE OR REPLACE FUNCTION sha1(bytea) returns text AS $$
    SELECT encode(digest($1, 'sha1'), 'hex')
$$ LANGUAGE SQL STRICT IMMUTABLE;
hmac()
hmac(data text, key text, type text) returns bytea
hmac(data bytea, key bytea, type text) returns bytea

为带有密钥*key的data计算哈希过的 MAC。type*与digest()中相同。

这与digest()相似，但是该哈希只能在知晓密钥的情况下被重新计算出来。这阻止了某人修改数据且还想更改哈希以匹配之的企图。

如果该密钥大于哈希块的尺寸，它将先被哈希然后把结果用作密钥。

口令哈希函数

函数crypt()和gen_salt()是特别设计用来做口令哈希的。crypt()完成哈希，而gen_salt()负责为前者准备算法参数。

crypt()中的算法在以下方面不同于通常的 MD5 或 SHA1 哈希算法：

1. 它们很慢。由于数据量很小，这是增加蛮力口令破解难度的唯一方法。

2. 它们使用一个随机值（称为salt），这样具有相同口令的用户将得到不同的密文口令。这也是针对逆转算法的一种额外保护。

3. 它们会在结果中包括算法类型，这样用不同算法哈希的口令能共存。

4. 其中一些是自适应的 — 这意味着当计算机变得更快时，你可以调整该算法变得更慢，而不会产生与现有口令的不兼容。

表 F.16列出了crypt()函数所支持的算法。

表 F.16. crypt()支持的算法

算法	最大口令长度	自适应？	Salt 位数	输出长度	描述
bf	72	yes	128	60	基于 Blowfish，变体 2a
md5	unlimited	no	48	34	基于 MD5 的加密
xdes	8	yes	24	20	扩展的 DES
des	8	no	12	13	原生 UNIX 加密

crypt()

crypt(password text, salt text) 返回 text

计算*password的一个 crypt(3) 风格的哈希。在存储一个新口令时，你需要使用gen_salt()产生一个新的salt值。要检查一个口令，把存储的哈希值作为salt*，并且测试结果是否匹配存储的值。

设置一个新口令的例子：

UPDATE ... SET pswhash = crypt('new password', gen_salt('md5'));

认证的例子：

SELECT (pswhash = crypt('entered password', pswhash)) AS pswmatch FROM ... ;

如果输入的口令正确，这会返回true。

gen_salt()

gen_salt(type text [, iter_count integer ]) 返回 text

产生一个在crypt()中使用的新随机 salt 字符串。该 salt 字符串也告诉了crypt()要使用哪种算法。

*type*参数指定哈希算法。可接受的类型是：des、xdes、md5以及bf。

*iter_count参数让用户可以为使用迭代计数的算法指定迭代计数。计数越高，哈希口令花的时间更长并且因而需要更多时间去攻破它。不过使用太高的计数会导致计算一个哈希的时间高达数年 — 这并不使用。如果iter_count参数被忽略，将使用默认的迭代计数。允许的iter_count*值与算法相关，如表 F.17所示。

表 F.17. crypt()的迭代计数

算法	默认值	最小值	最大值
xdes	725	1	16777215
bf	6	4	31

对xdes算法还有额外的限制：迭代计数必须是一个奇数。

要选取一个合适的迭代计数，考虑最初的 DES 加密被设计成在当时的硬件上每秒钟完成 4 次哈希。低于每秒 4 次哈希的速度很可能会损害可用性。而超过每秒 100 次哈希又可能太快了。

表 F.18给出了不同哈希算法的相对慢度的综述。该表展示了在假设口令只含有小写字母或者大小写字母及数字的情况下，在一个 8 字符口令中尝试所有字符组合所需要的时间。在crypt-bf项中，在一个斜线之后的数字是gen_salt的*iter_count*参数

表 F.18. 哈希算法速度

算法	次哈希/秒	对于[a-z]	对于[A-Za-z0-9]	相对于md5 hash的持续时间
crypt-bf/8	1792	4 年	3927 年	100k
crypt-bf/7	3648	2 年	1929 年	50k
crypt-bf/6	7168	1 年	982 年	25k
crypt-bf/5	13504	188 天	521 年	12.5k
crypt-md5	171584	15 天	41 年	1k
crypt-des	23221568	157.5 分	108 天	7
sha1	37774272	90 分	68 天	4
md5(hash)	150085504	22.5 分	17 天	1

注意：

• 使用的机器是一台 Intel Mobile Core i3。

• crypt-des和crypt-md5算法的数字是取自 John the Ripper v1.6.38 -test输出。

• md5 hash的数字来自于 mdcrack 1.2。

• sha1的数字来自于 lcrack-20031130-beta.

• crypt-bf的数字是采用一个在 1000 个 8 字符口令上循环的简单程序采集到的。用那种方法我能展示不同迭代次数的速度。供参考：john-test对于crypt-bf/5显示 13506 次循环/秒（结果中的微小差异符合pgcrypto中的crypt-bf实现与 John the Ripper 中的一致这一情况）。

注意“尝试所有组合”并非是现实中会采用的方式。通常口令破解都是在词典的帮助下完成的，词典中会包含常用词以及它们的多种变化。因此，甚至有些像词的口令被破解的时间可能会大大小于上面建议的数字，而一个 6 字符的不像词的口令可能会逃过破解，也可能不能逃脱。

pg_freespacemao

pg_freespacemao

pg_freespacemap模块提供了一种方法来检查空闲空间映射（FSM）。它提供了一个称为pg_freespace的函数，或者准确地说是两个重载的函数。这些函数显示空闲空间映射中为一个给定页面所记录的值，或者显示关系中所有页面的记录值。

默认情况下，使用仅限于超级用户和pg_stat_scan_tables 角色的成员。可以使用GRANT给其他人授予访问权限。

函数

• pg_freespace(rel regclass IN, blkno bigint IN) 返回 int2

  根据 FSM，返回由blkno指定的关系页面上的空闲空间总量。

• pg_freespace(rel regclass IN, blkno OUT bigint, avail OUT int2)

  根据 FSM，显示关系的每个页面上的空闲空间总量。一个(blkno bigint, avail int2)元组的集合会被返回，每一个元组对应关系中的一个页面。

存储在空闲空间映射中的值不准确。它们被圆整到BLCKSZ的 1/256（对于默认的BLCKSZ是 32 字节），并且在元组被插入和更新时它们不会被实时更新。

对于索引，被跟踪的是整个没有使用的页面，而不是页面中的空闲空间。因此，这些值可能没有意义，只是表示一个页面是满的还是空的。

样例输出

unvdbsvr=# SELECT * FROM pg_freespace('foo');
 blkno | avail 
-------+-------
     0 |     0
     1 |     0
     2 |     0
     3 |    32
     4 |   704
     5 |   704
     6 |   704
     7 |  1216
     8 |   704
     9 |   704
    10 |   704
    11 |   704
    12 |   704
    13 |   704
    14 |   704
    15 |   704
    16 |   704
    17 |   704
    18 |   704
    19 |  3648
(20 rows)
unvdbsvr=# SELECT * FROM pg_freespace('foo', 7);
 pg_freespace 
--------------
         1216
(1 row)

pg_prewarm

pg_prewarm模块提供一种方便的方法把关系 数据载入到操作系统缓冲区或者 UDB-TX缓冲区。可以使用pg_prewarm函数手工执行预热，或者通过在shared_preload_libraries中包括pg_prewarm来自动执行预热。在后一种情况中，系统将运行一个后台工作者，它会周期性地把共享内存中的内容记录在一个名为autoprewarm.blocks的文件中，并且在重新启动后用两个后台工作者重新载入那些块。

函数

pg_prewarm(regclass, mode text default 'buffer', fork text default 'main',
           first_block int8 default null,
           last_block int8 default null) RETURNS int8

第一个参数是要预热的关系。第二个参数是要使用的预热方法，下文将会 进一步讨论。第三个参数是要被预热的关系分叉，通常是main。 第四个参数是要预热的第一个块号（NULL也被接受，它等同于 零）。第五个参数是要预热的最后一个块号（NULL表示一直 预热到关系的最后一个块）。返回值是被预热的块数。

有三种可用的预热方法。prefetch会向操作系统发出异步 预取请求（如果支持异步预取），不支持异步预取则抛出一个错误。 read会读取要求范围的块。与prefetch 不同，它是同步的并且在所有平台上都被支持，但是可能较慢。 buffer会把要求范围的块读入道数据库的缓冲区。

注意使用任意一种方法尝试预热比能缓存的数量更多的块 — 使用 prefetch或者read（由 OS）或者使用 buffer（由 UDB-TX） — 将很可能导致高编号块被读入时把低编号的块从缓冲区中逐出的情况。 被预热的数据也不享受对缓冲区替换的特别保护，因此其他系统活动可能会在刚刚 被预热的块被读入后很快就将它们逐出。反过来，预热也可能把其他数据逐出缓存。 由于这些原因，预热通常在启动时最有用，那时缓冲大部分都为空。

autoprewarm_start_worker() RETURNS void

启动主要的autoprewarm工作者。这通常将会自动发生，但是如果没有在服务器启动时配置自动预热并且用户希望在稍晚的时候启动该工作者，这个函数就能发挥作用。

autoprewarm_dump_now() RETURNS int8

立即更新autoprewarm.blocks。如果autoprewarm工作者没有运行但用户希望它在下一次重启后运行，则这个函数会很有用。返回值是写入到autoprewarm.blocks中的记录数。

配置参数

• pg_prewarm.autoprewarm (boolean)

  控制服务器是否应该运行autoprewarm工作者。默认这个参数为on。这个参数只能在服务器启动时设置。

• pg_prewarm.autoprewarm_interval (int)

  这是更新autoprewarm.blocks的间隔。默认是300秒。如果被设置为0，该文件将不会以常规的间隔方式转储，而是只在服务器关闭时转储。

pgrowlocks

pgrowlocks模块提供了一个函数来显示一个指定表的行锁定信息。

默认情况下，使用仅限于超级用户、pg_stat_scan_tables 角色的成员和在该表上拥有SELECT权限的用户。

概述

pgrowlocks(text) 返回 setof record

参数是一个表的名称。结果是一个记录集合，其中每一行对应表中一个被锁定的行。输出列如表 F.20所示。

表 F.20. pgrowlocks 输出列

名称	类型	描述
locked_row	tid	被锁定行的元组 ID（TID）
locker	xid	持锁者的事务 ID，如果是多事务则为多事务 ID
multi	boolean	如果持锁者是一个多事务，则为真
xids	xid[]	持锁者的事务 ID（如果是多事务则多于一个）
modes	text[]	持锁者的锁模式（如果是多事务则多于一个），是一个Key Share、Share、For No Key Update、No Key Update、For Update、Update组成的数组。
pids	integer[]	锁定后端的进程 ID（如果是多事务则多于一个）

pgrowlocks会为目标表加AccessShareLock并且一个一个读取每一行来收集行的锁定信息。这对于一个大表不是很快。注意：

1. 如果表被其他人整体加上了排他锁，pgrowlocks将被阻塞。

2. pgrowlocks不保证能产生一个自我一致的快照。在它执行期间，有可能加上一个新行锁，也有可能有旧行锁被释放。

pgrowlocks不显示被锁定行的内容。如果你想同时查看行内容，你可以这样做：

SELECT * FROM accounts AS a, pgrowlocks('accounts') AS p  WHERE p.locked_row = a.ctid;

不过要注意，这样一个查询将非常低效。

样例输出

=# SELECT * FROM pgrowlocks('t1');
 locked_row | locker | multi | xids  |     modes      |  pids
------------+--------+-------+-------+----------------+--------
 (0,1)      |    609 | f     | {609} | {"For Share"}  | {3161}
 (0,2)      |    609 | f     | {609} | {"For Share"}  | {3161}
 (0,3)      |    607 | f     | {607} | {"For Update"} | {3107}
 (0,4)      |    607 | f     | {607} | {"For Update"} | {3107}
(4 rows)

pg_stat_statements

pg_stat_statements模块提供一种方法追踪一个服务器所执行的所有 SQL 语句的执行统计信息。

该模块必须通过在unvdbsvr.conf的shared_preload_libraries中增加pg_stat_statements来载入，因为它需要额外的共享内存。这意味着增加或移除该模块需要一次服务器重启。

当pg_stat_statements被载入时，它会跟踪该服务器 的所有数据库的统计信息。该模块提供了一个视图 pg_stat_statements以及函数pg_stat_statements_reset 和pg_stat_statements用于访问和操纵这些统计信息。这些视图 和函数不是全局可用的，但是可以用CREATE EXTENSION pg_stat_statements 为特定数据库启用它们。

pg_stat_statements视图

由该模块收集的统计信息可以通过一个名为 pg_stat_statements的视图使用。这个视图为每 一个可区分的数据库 ID、用户 ID 和查询 ID（最多到该模块可以追 踪的可区分语句的数量）的组合都包含一行。该视图的列如 表 F.21中所示。

表 F.21. pg_stat_statements列

名称	类型	引用	描述
userid	oid	pg_authid.oid	执行该语句的用户的 OID
dbid	oid	pg_database.oid	在其中执行该语句的数据库的 OID
queryid	bigint		内部哈希码，从语句的解析树计算得来
query	text		语句的文本形式
calls	bigint		被执行的次数
total_time	double precision		在该语句中花费的总时间，以毫秒计
min_time	double precision		在该语句中花费的最小时间，以毫秒计
max_time	double precision		在该语句中花费的最大时间，以毫秒计
mean_time	double precision		在该语句中花费的平均时间，以毫秒计
stddev_time	double precision		在该语句中花费时间的总体标准偏差，以毫秒计
rows	bigint		该语句检索或影响的行总数
shared_blks_hit	bigint		该语句造成的共享块缓冲命中总数
shared_blks_read	bigint		该语句读取的共享块的总数
shared_blks_dirtied	bigint		该语句弄脏的共享块的总数
shared_blks_written	bigint		该语句写入的共享块的总数
local_blks_hit	bigint		该语句造成的本地块缓冲命中总数
local_blks_read	bigint		该语句读取的本地块的总数
local_blks_dirtied	bigint		该语句弄脏的本地块的总数
local_blks_written	bigint		该语句写入的本地块的总数
temp_blks_read	bigint		该语句读取的临时块的总数
temp_blks_written	bigint		该语句写入的临时块的总数
blk_read_time	double precision		该语句花在读取块上的总时间，以毫秒计
blk_write_time	double precision		该语句花在写入块上的总时间，以毫秒计

由于安全性原因，只有超级用户和pg_read_all_stats 角色的成员被允许看到其他用户执行的查询 的 SQL 文本或者queryid。 不过，如果该视图被安装在其他用户的数据库中，那么他们就能够看见统 计信息。

只要可规划的查询（即SELECT、INSERT、UPDATE以及DELETE）根据一种内部哈希计算具有相同的查询结构，它们就会被组合到一个单一的pg_stat_statements项。通常，对于这里的目的，如果两个查询除了查询中的文本常量值之外在语义上等效，它们将会被认为是相同的。不过，功能性命令（即所有其他命令）会严格地以它们的文本查询字符串为基础进行比较。

当为了把一个查询与其他查询匹配，常数值会被忽略， 在pg_stat_statements显示中它会被一个参数符号， 比如$1所替换。查询文本的剩余部分就是具有与该pg_stat_statements项相关的特定queryid哈希值的第一个查询的文本。

在某些情况中，具有明显不同文本的查询可能会被融合到一个单一的pg_stat_statements项。通常这只会发生在语义等价的查询身上，但是也有很小的机会因为哈希碰撞的原因导致无关的查询被融合到一个项中（不过，对于属于不同用户或数据库的查询来说不会发生这种情况）。

由于queryid哈希值是根据查询被解析和分析后的表达计算的，对立的情况也可能存在：如果具有相同文本的查询由于参数（如不同的search_path设置）的原因而具有不同的含义，它们就可能作为不同的项存在。

pg_stat_statements的使用者可能希望使用 queryid（也许会与dbid和userid组合）作为一个项比查询文本更稳定和可靠的标识符。但是，有一点很重要的是，对于queryid哈希值稳定性只有有限的保障。因为该标识符是从解析分析后的树得来的，它的值是以这种形式出现的内部对象标识符的函数。这有一些违背直觉的含义。例如，如果有两个查询引用了同一个表，但是该表在两次查询之间被删除并且重建，显然这两个查询是完全一致的，但是pg_stat_statements将把它们认为是不同的。哈希处理也对机器架构以及平台的其他方面的差别很敏感。更进一步，认为UDB-TX的不同主版本之间queryid将会保持稳定是不安全的。

根据经验，只有在底层服务器版本以及目录元数据细节保持完全相同时，queryid值才能被假定为稳定并且可比。两台参与到基于物理 WAL 重放的复制中的服务器会对相同的查询给出一样的queryid值。但是，逻辑复制模式并不保证在所有相关细节上都保持完全一样的复制，因此在逻辑复制机之间计算代价时，queryid并非是一个有用的标识符。如果有疑问，推荐直接进行测试。

代表性查询文本中用于替换常量的参数符号从原始查询文本中最高的 $*n*参数之后的下一个数字开始， 如果没有则为$1。值得注意的是，在某些情况下， 可能存在影响编号的隐藏参数符号。例如，PL/pgSQL 使用隐藏参数符号将函数局部变量的值插入到查询中，以便像 SELECT i + 1 INTO j的PL/pgSQL 语句将具有像SELECT i + $2这样的代表性文本。

有代表性的查询文本被保存在一个外部磁盘文件中，并且不会消耗共享内存。 因此，即便是很长的查询文本也能被成功的存储下来。不过，如果累积了很多 长的查询文本，该外部文件也会增长到很大。作为一种恢复方法，如果这样的 情况发生，pg_stat_statements可能会选择丢弃这些查询文本， 于是pg_stat_statements视图中的所有现有项将会显示空的 query域，不过与每个queryid相关联的 统计信息会被保留下来。如果发生这种情况，可以考虑减小 pg_stat_statements.max来防止复发。

函数

• pg_stat_statements_reset() 返回 void

  pg_stat_statements_reset抛弃目前由pg_stat_statements收集的所有统计信息。默认情况下，这个函数只能被超级用户执行。

• pg_stat_statements(showtext boolean) returns setof record

  pg_stat_statements视图按照一个也叫 pg_stat_statements的函数来定义。客户端可以直接调用 pg_stat_statements函数，并且通过指定 showtext := false来忽略查询文本（即，对应于视图的 query列的OUT参数将返回空值）。 这个特性是为了支持不想重复接收长度不定的查询文本的外部工具而设计的。 这类工具可以转而自行缓存第一个观察到的查询文本，因为这就是 pg_stat_statements自己所做的全部工作，并且只在需要的 时候检索查询文本。因为服务器会把查询文本存储在一个文件中，这种方法可 以降低重复检查pg_stat_statements数据的 物理 I/O。

配置参数

• pg_stat_statements.max (integer)

  pg_stat_statements.max是由该模块跟踪的语句的最大数目（即pg_stat_statements视图中行的最大数量）。如果观测到的可区分的语句超过这个数量，最少被执行的语句的信息将会被丢弃。默认值为 5000。这个参数只能在服务器启动时设置。

• pg_stat_statements.track (enum)

  pg_stat_statements.track控制哪些语句会被该模块计数。指定top可以跟踪顶层语句（那些直接由客户端发出的语句），指定all还可以跟踪嵌套的语句（例如在函数中调用的语句），指定none可以禁用语句统计信息收集。默认值是top。 只有超级用户能够改变这个设置。

• pg_stat_statements.track_utility (boolean)

  pg_stat_statements.track_utility控制该模块是否会跟踪工具命令。工具命令是除了SELECT、INSERT、 UPDATE和DELETE之外所有的其他命令。默认值是on。 只有超级用户能够改变这个设置。

• pg_stat_statements.save (boolean)

  pg_stat_statements.save指定是否在服务器关闭之后还保存语句统计信息。如果被设置为off，那么关闭后不保存统计信息并且在服务器启动时也不会重新载入统计信息。默认值为on。这个参数只能在unvdbsvr.conf文件中或者在服务器命令行上设置。

该模块要求与pg_stat_statements.max成比例的额外共享内存。注意只要该模块被载入就会消耗这么多的内存，即便pg_stat_statements.track被设置为none。

这些参数必须在unvdbsvr.conf中设置。典型的用法可能是：

# unvdbsvr.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all

示例输出

bench=# SELECT pg_stat_statements_reset();
$ pgbench -i bench
$ pgbench -c10 -t300 bench
bench=# \x
bench=# SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit /
               nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
          FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;
-[ RECORD 1 ]---------------------------------------------------------------------
query       | UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2;
calls       | 3000
total_time  | 9609.00100000002
rows        | 2836
hit_percent | 99.9778970000200936
-[ RECORD 2 ]---------------------------------------------------------------------
query       | UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2;
calls       | 3000
total_time  | 8015.156
rows        | 2990
hit_percent | 99.9731126579631345
-[ RECORD 3 ]---------------------------------------------------------------------
query       | copy pgbench_accounts from stdin
calls       | 1
total_time  | 310.624
rows        | 100000
hit_percent | 0.30395136778115501520
-[ RECORD 4 ]---------------------------------------------------------------------
query       | UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2;
calls       | 3000
total_time  | 271.741999999997
rows        | 3000
hit_percent | 93.7968855088209426
-[ RECORD 5 ]---------------------------------------------------------------------
query       | alter table pgbench_accounts add primary key (aid)
calls       | 1
total_time  | 81.42
rows        | 0
hit_percent | 34.4947735191637631

pgstattuple

pgstattuple模块提供多种函数来获得元组层的统计信息。

由于这些函数返回详细的页面级信息，因此默认访问权限是受限制的。 默认情况下，只有角色pg_stat_scan_tables具有EXECUTE特权。 超级用户当然可以绕过这个限制。扩展程序安装后,用户可以发送GRANT命令来更改函数的权限以允许其他用户来执行它们。但是，最好将这些用户添加到pg_stat_scan_tables角色。

函数

• pgstattuple(regclass) 返回 record

  pgstattuple返回一个关系的物理长度、“死亡”元组的百分比以及其他信息。这可以帮助用户决定是否需要清理。参数是目标关系的名称（可以有选择地用模式限定）或者 OID。例如：

test=> SELECT * FROM pgstattuple('pg_catalog.pg_proc');
-[ RECORD 1 ]------+-------
table_len          | 458752
tuple_count        | 1470
tuple_len          | 438896
tuple_percent      | 95.67
dead_tuple_count   | 11
dead_tuple_len     | 3157
dead_tuple_percent | 0.69
free_space         | 8932
free_percent       | 1.95

表 F.22中描述了输出列。

表 F.22. pgstattuple 输出列

列	类型	描述
table_len	bigint	物理关系长度，以字节计
tuple_count	bigint	存活元组的数量
tuple_len	bigint	存活元组的总长度，以字节计
tuple_percent	float8	存活元组的百分比
dead_tuple_count	bigint	死亡元组的数量
dead_tuple_len	bigint	死亡元组的总长度，以字节计
dead_tuple_percent	float8	死亡元组的百分比
free_space	bigint	空闲空间总量，以字节计
free_percent	float8	空闲空间的百分比

pgstattuple只要求在关系上的一个读锁。因此结果不能反映一个即时快照，并发更新将影响结果。

如果HeapTupleSatisfiesDirty返回假，pgstattuple就判定一个元组是“死亡的”。

pgstattuple(text) 返回 record

与pgstattuple(regclass)相同，只不过通过 TEXT 指定目标关系。这个函数只是为了向后兼容而保留，在未来的发布中将会被废除。

pgstatindex(regclass) 返回 record

pgstatindex返回一个记录显示有关一个 B-树索引的信息。例如：

test=> SELECT * FROM pgstatindex('pg_cast_oid_index');
-[ RECORD 1 ]------+------
version            | 2
tree_level         | 0
index_size         | 16384
root_block_no      | 1
internal_pages     | 0
leaf_pages         | 1
empty_pages        | 0
deleted_pages      | 0
avg_leaf_density   | 54.27
leaf_fragmentation | 0

输出列是：

列	类型	描述
version	integer	B-树 版本号
tree_level	integer	根页的树层次
index_size	bigint	以字节计的索引总尺寸
root_block_no	bigint	根页的位置（如果没有则为零）
internal_pages	bigint	“内部”（上层）页面的数量
leaf_pages	bigint	叶子页的数量
empty_pages	bigint	空页的数量
deleted_pages	bigint	删除页的数量
avg_leaf_density	float8	叶子页的平均密度
leaf_fragmentation	float8	叶子页碎片

报告的index_size通常对应于internal_pages + leaf_pages + empty_pages + deleted_pages加一，因为它还包括索引的元页。

对于pgstattuple，结果是一页一页累计的并且不要期望结果会表示整个索引的一个即时快照。

pgstatindex(text) returns record

与pgstatindex(regclass)相同，只不过通过 TEXT 指定目标索引。这个函数只是为了向后兼容而保留，在未来的某个发布中将会被废除。

pgstatginindex(regclass) 返回 record

pgstatginindex返回一个记录显示有关一个 GIN 索引的信息。例如：

test=> SELECT * FROM pgstatginindex('test_gin_index');
-[ RECORD 1 ]--+--
version        | 1
pending_pages  | 0
pending_tuples | 0

输出列是：

列	类型	描述
version	integer	GIN 版本号
pending_pages	integer	待处理列表中的页面数
pending_tuples	bigint	待处理列表中的元组数

pgstathashindex(regclass) returns record

pgstathashindex返回一个显示HASH索引信息的记录。例如：

test=> select * from pgstathashindex('con_hash_index');
-[ RECORD 1 ]--+-----------------
version        | 4
bucket_pages   | 33081
overflow_pages | 0
bitmap_pages   | 1
unused_pages   | 32455
live_items     | 10204006
dead_items     | 0
free_percent   | 61.8005949100872

输出字段是：

字段	类型	描述
version	integer	HASH版本号
bucket_pages	bigint	存储桶页面的数量
overflow_pages	bigint	溢出页面的数量
bitmap_pages	bigint	位图页数
unused_pages	bigint	未使用页面的数量
live_items	bigint	活元组的数量
dead_tuples	bigint	死元组的数量
free_percent	float	自由空间的百分比

pg_relpages(regclass) 返回 bigint

pg_relpages返回关系中的页面数。

pg_relpages(text) returns bigint

与pg_relpages(regclass)相同，只不过用 TEXT 来 指定目标关系。这个函数只是为了向后兼容而保留，在未来的某个发布中将会被废除。

pgstattuple_approx(regclass) returns record

pgstattuple_approx是pgstattuple的一个更加快速的替代品，它返回近似的结果。参数是目标关系的 OID 或者名称。例如：

test=> SELECT * FROM pgstattuple_approx('pg_catalog.pg_proc'::regclass);
-[ RECORD 1 ]--------+-------
table_len            | 573440
scanned_percent      | 2
approx_tuple_count   | 2740
approx_tuple_len     | 561210
approx_tuple_percent | 97.87
dead_tuple_count     | 0
dead_tuple_len       | 0
dead_tuple_percent   | 0
approx_free_space    | 11996
approx_free_percent  | 2.09

输出列在表 F.23中描述。

鉴于pgstattuple总是执行全表扫描并且返回存活和死亡元组的准确计数、尺寸和空闲空间，pgstattuple_approx尝试避免全表扫描并且返回死亡元组的准确统计信息，以及存活元组和空闲空间的近似数量及尺寸。

这个函数通过根据可见性映射跳过只包含可见元组的页面来实现这一目的（如果一个页面对应的 VM 位被设置，那么就说明它不含有死亡元组）。对于这样的额页面，它会从空闲空间映射中得到空闲空间值，并且假定该页面上的剩余空间由存活元组占据。

对于不能被跳过的页面，它会扫描每个元组，在合适的计数器中记录它的存在以及尺寸，并且统计该页面上的空闲空间。最后，它会基于已扫描的页面和元组数量来估计存活元组的总数（采用与 VACUUM 估计 pg_class.reltuples 时相同的方法）。

表 F.23. pgstattuple_approx输出列

列	类型	描述
table_len	bigint	以字节计的物理关系长度（准确）
scanned_percent	float8	已扫描表的百分比
approx_tuple_count	bigint	存活元组的数量（估计）
approx_tuple_len	bigint	以字节计的存活元组总长度（估计）
approx_tuple_percent	float8	存活元组的百分比
dead_tuple_count	bigint	死亡元组的数量（准确）
dead_tuple_len	bigint	以字节计的死亡元组总长度（准确）
dead_tuple_percent	float8	死亡元组的百分比
approx_free_space	bigint	以字节计的总空闲空间（估计）
approx_free_percent	float8	空闲空间的百分比

在上述的输出中，空闲空间数字可能不完全匹配pgstattuple的输出，这是因为空闲空间映射会给出一个准确的数字，但是这个数字不能保证是一个准确的字节数。

pg_trgm

pg_trgm模块提供用于决定基于 trigram 匹配的字母数字文本相似度的函数和操作符，以及支持快速搜索相似字符串的索引操作符类。

Trigram（或者 Trigraph）概念

一个 trigram 是从一个字符串中取出的由三个连续字符组成的组。我们可以通过对两个字符串之间共享的 trigram 计数来度量它们的相似度。这种简单的思想已经成为在很多自然语言中度量词相似度的有效方法。

函数和操作符

pg_trgm模块所提供的函数如表 F.24中所示，操作符则显示在表 F.25中。

表 F.24. pg_trgm函数

函数	返回值	描述
similarity(text, text)	real	返回一个数字指示两个参数有多相似。该结果的范围是 0（指示两个字符串完全不相似）到 1（指示两个字符串完全一样）。
show_trgm(text)	text[]	返回一个给定字符串中所有的 trigram 组成的一个数组（实际上除了调试很少有用）。
word_similarity(text, text)	real	返回一个数字表示第一个字符串中的trigram集合与第二个字符串中trigram的有序集中任何连续部分的最大相似度。详情请见下文的解释。
strict_word_similarity(text, text)	real	与word_similarity(text, text)相同，但是强制连续部分的边界与词边界相匹配。由于我们没有跨词的trigram，这个函数实际上返回第一个字符串和第二个字符串任意连续部分的相似度。
show_limit()	real	返回%操作符使用的当前相似度阈值。例如，这设定两个词被认为足够相似时，它们之间应满足的最小相似度（已废弃）。
set_limit(real)	real	设定%操作符使用的当前相似度阈值。该阈值必须介于 0 和 1 之间（默认为 0.3）。返回传递进来的同一个值（已废弃）。

考虑下面的例子：

# SELECT word_similarity('word', 'two words');
 word_similarity
-----------------
             0.8
(1 row)

在第一个字符串中，trigram集合是{" w"," wo","wor","ord","rd "}。在第二个字符串中，trigram的有序集是{" t"," tw","two","wo "," w"," wo","wor","ord","rds","ds "}。在第二个字符串中最相似的trigram有序集的部分是{" w"," wo","wor","ord"}，并且相似度是0.8。

这个函数返回的值可以大概地理解为第一个字符串和第二个字符串任意子串的最大相似度。不过，这个函数不会对该部分的边界加入填充。因此，除了失配的词边界之外，第二个字符串中存在的额外字符的数目没有被考虑。

同时，strict_word_similarity(text, text)在第二个字符串中选择一个由词构成的部分。在上面的例子中，strict_word_similarity(text, text)会选择单个词'words'形成的部分，其trigram集合为{" w"," wo","wor","ord","rds","ds "}。

# SELECT strict_word_similarity('word', 'two words'), similarity('word', 'words');
 strict_word_similarity | similarity
------------------------+------------
               0.571429 |   0.571429
(1 row)

因此，strict_word_similarity(text, text)函数对于计算整个词的相似度有用，而word_similarity(text, text)更适合于计算词的部分相似度。

表 F.25. pg_trgm操作符

操作符	返回值	描述
text % text	boolean	如果参数具有超过pg_trgm.similarity_threshold设置的当前相似度阈值的相似度，则返回true。
text <% text	boolean	如果第一个参数中的trigram集合与第二个参数中有序trigram集合的一个连续部分之间的相似度超过pg_trgm.word_similarity_threshold参数设置的当前词相似度阈值，则返回true。
text %> text	boolean	<%操作符的交换子。
text <<% text	boolean	如果第二个参数有有序trigram集合的一个连续部分匹配词边界，并且其与第一个参数的trigram集合的相似度超过pg_trgm.strict_word_similarity_threshold参数设置的当前严格词相似度阈值，则返回true。
text %>> text	boolean	<<%操作符的交换子。
text <-> text	real	返回参数之间的“距离”，即 1 减去similarity()值。
text <<-> text	real	返回参数之间的“距离”，它是 1 减去word_similarity()的值。
text <->> text	real	<<->操作符的交换子。
text <<<-> text	real	返回参数之间的“距离”，也就是1减去strict_word_similarity()的值。
text <->>> text	real	<<<->操作符的交换子。

GUC 参数

• pg_trgm.similarity_threshold (real)

  设置%操作符使用的当前相似度阈值。该阈值必须位于 0 和 1 之间（默认是 0.3）。

• pg_trgm.word_similarity_threshold (real)

  设置<%和%>操作符使用的当前词相似度阈值。该阈值必须位于 0 和 1 之间（默认是 0.6）。

索引支持

pg_trgm模块提供了 GiST 和 GIN 索引操作符类，这允许你在一个文本列上创建索引用于快速相似度搜索的目的。这些索引类型支持上述的相似度操作符，并且额外支持基于 trigram 的索引搜索用于LIKE、ILIKE、~和~*查询（这些索引不支持等值或简单比较操作符，因此你可能还需要一个常规的 B-树索引）。

例子：

CREATE TABLE test_trgm (t text);
CREATE INDEX trgm_idx ON test_trgm USING GIST (t gist_trgm_ops);
或

CREATE INDEX trgm_idx ON test_trgm USING GIN (t gin_trgm_ops);

此时，你将有一个t列上的索引，你可以用它进行相似度搜索。一个典型的查询是

SELECT t, similarity(t, 'word') AS sml
  FROM test_trgm
  WHERE t % 'word'
  ORDER BY sml DESC, t;

这将返回在文本列中与*word*足够相似的所有值，按最佳匹配到最差匹配的方式排序。索引将被用来让这种搜索变快，即使在一个非常大的数据集上。

上述查询的一种变体是

SELECT t, t <-> 'word' AS dist
  FROM test_trgm
  ORDER BY dist LIMIT 10;

这能够用 GiST 索引有效地实现，但是用 GIN 索引无法做到。当只想要少数最接近的匹配时，这通常会比第一种形式更好。

也可以把一个t列上的索引用于词相似度或者严格词相似度。典型的查询是：

SELECT t, word_similarity('word', t) AS sml
  FROM test_trgm
  WHERE 'word' <% t
  ORDER BY sml DESC, t;
  和

SELECT t, strict_word_similarity('word', t) AS sml
  FROM test_trgm
  WHERE 'word' <<% t
  ORDER BY sml DESC, t;

这将返回文本列中符合条件的所有值：这些值在其对应的有序trigram集中有一个连续部分与*word*的trigram集合足够相似，这些值会按照最好匹配到最差匹配的顺序排列。即便在非常大的数据集上，索引也将使得这一操作的速度够快。

上述查询可能的变体有：

SELECT t, 'word' <<-> t AS dist
  FROM test_trgm
  ORDER BY dist LIMIT 10;
和

SELECT t, 'word' <<<-> t AS dist
  FROM test_trgm
  ORDER BY dist LIMIT 10;

这可以用 GiST 索引很高效地实现，但是用 GIN 索引不行。

从UDB-TX9.1 中开始，这些索引类型也支持用于LIKE和ILIKE的索引搜索，例如

SELECT * FROM test_trgm WHERE t LIKE '%foo%bar';

该索引搜索通过从搜索字符串中抽取 trigram 并且在索引中查找它们来工作。搜索字符串中有更多 trigram，索引搜索的效率更高。不像基于 B-树的搜索，搜索字符串不需要是左锚定的。

从UDB-TX 9.3 中开始，这些索引类型也支持用于正则表达式匹配（~和~*操作符）的索引搜索，例如

SELECT * FROM test_trgm WHERE t ~ '(foo|bar)';

该索引搜索通过从正则表达式中抽取 trigram 并且在索引中查找它们来工作。正则表达式中能抽取出更多 trigram，索引搜索的效率更高。不像基于 B-树的搜索，搜索字符串不需要是左锚定的。

对于LIKE和正则表达式搜索，记住没有可抽取 trigram 的模式将退化成一个全索引扫描。

GiST 和 GIN 索引之间的选择依赖于 GiST 和 GIN 的相对性能特性，这在其他地方讨论。

文本搜索集成

在与一个全文索引联合使用时，trigram 匹配是一种非常有用的工具。特别是它能有助于识别拼写错误的输入词，这些词直接用全文搜索机制是不会被匹配的。

第一步是生成一个包含文档中所有唯一词的辅助表：

CREATE TABLE words AS SELECT word FROM
        ts_stat('SELECT to_tsvector(''simple'', bodytext) FROM documents');

其中documents是一个具有我们希望搜索的文本域bodytext的表。对to_tsvector函数使用simple配置而不是使用语言相关的配置的原因是，我们想要一个原始（没有去掉词根的）词的列表。

接下来，在词列上创建一个 trigram 索引：

CREATE INDEX words_idx ON words USING GIN(word gin_trgm_ops);

现在，类似于前面例子的一个SELECT查询可以被用来为用户搜索术语中的拼写不当的词建议拼写。要求被选择的词也与拼写不当的词具有相似的长度是一种有用的额外测试。

pg_visibility

pg_visibility模块提供了一种方式来检查一个表的可见性映射（VM）以及页级别的可见性信息。它还提供了函数来检查可见性映射的完整性以及强制重建可见性映射。

有三个不同的位被用来存储有关页级别可见性的信息。 可见性映射中的“全部可见”位表示一个关系的对应页面上的所有元组对每一个当前和未来事务都可见。 可见性映射中的“全部冻结”位表示该页上的每一个元组都被冻结， 也就是说直到在那个页面上对一个元组进行插入、更新、 删除或者锁定之前都不需要用 vacuum 对该页面进行修改。 页面头部的PD_ALL_VISIBLE位具有和可见性映射中“全部可见”位相同的含义， 但是它存储在数据页面本身中而不是存储在单独的数据结构中。这两个位通常是相互一致的， 但是有时在崩溃恢复后会出现页面的“全部可见”位被设置而可见性映射位被清除的情况， 由于在pg_visibility检查了可见性映射之后， 且在它检查数据页面之前发生了修改，报告的值也会不一致。 任何导致数据损坏的事件也可能导致这些位不一致。

显示有关PD_ALL_VISIBLE的信息的函数代价比那些查看可见性映射的函数要高很多，因为它们必须读取关系的数据块而不是只读取（小很多的）可见性映射。类似地，检查关系的数据块的函数也很昂贵。

函数

• pg_visibility_map(relation regclass, blkno bigint, all_visible OUT boolean, all_frozen OUT boolean) returns record

  为给定关系的给定块返回其在可见性映射中的“全部可见”和“全部冻结”位。

• pg_visibility(relation regclass, blkno bigint, all_visible OUT boolean, all_frozen OUT boolean, pd_all_visible OUT boolean) returns record

  为给定关系的给定块返回其在可见性映射中的“全部可见”和“全部冻结”位，外加块的PD_ALL_VISIBLE位。

• pg_visibility_map(relation regclass, blkno OUT bigint, all_visible OUT boolean, all_frozen OUT boolean) returns setof record

  为给定关系的每一块返回其在可见性映射中的“全部可见”和“全部冻结”位。

• pg_visibility(relation regclass, blkno OUT bigint, all_visible OUT boolean, all_frozen OUT boolean, pd_all_visible OUT boolean) returns setof record

  为给定关系的每一块返回其在可见性映射中的“全部可见”和“全部冻结”位，外加每一块的PD_ALL_VISIBLE位。

• pg_visibility_map_summary(relation regclass, all_visible OUT bigint, all_frozen OUT bigint) returns record

  根据可见性映射返回关系中“全部可见”页面和“全部冻结”页面的数量。

• pg_check_frozen(relation regclass, t_ctid OUT tid) returns setof tid

  返回存储在可见性映射中被标为“全部冻结”的页面中的非冻结元组的 TID。 如果这个函数返回一个非空的 TID 集合，则可见性映射已经损坏。

• pg_check_visible(relation regclass, t_ctid OUT tid) returns setof tid

  返回存储在可见性映射中标记为全部可见的页面中的非全部可见元组的TID。 如果此函数返回非空的一组TID，则可见性映射已损坏。

• pg_truncate_visibility_map(relation regclass) returns void

  为给定关系截断可见性映射。如果您认为该关系的可见性映射已损坏并希望强制重建它， 则该函数非常有用。在这个函数被执行后，在给定关系上进行的第一次VACUUM 将会扫描关系中的每一个页面并且重建可见性映射。（在完成之前， 查询会将可见性映射看做包含的全是零。）

默认情况下，这些函数只有超级用户和pg_stat_scan_tables角色的成员可以执行， 除了pg_truncate_visibility_map(relation regclass)只能由超级用户执行之外。

udb_fdw

udb_fdw

udb_fdw模块提供了外部数据包装器udb_fdw，它可以被用来访问存储在外部UDB-TX服务器中的数据。

这个模块提供的功能大体上覆盖了较老的dblink模块的功能。但是udb_fdw提供了更透明且更兼容标准的语法来访问远程表，并且可以在很多情况下给出更好的性能。

要使用udb_fdw来为远程访问做准备：

1. 使用CREATE EXTENSION来安装udb_fdw扩展。

2. 使用CREATE SERVER创建一个外部服务器对象，它用来表示你想连接的每一个远程数据库。指定除了user和password之外的连接信息作为该服务器对象的选项。

3. 使用CREATE USER MAPPING创建一个用户映射，每一个用户映射都代表你想允许一个数据库用户访问一个外部服务器。指定远程用户名和口令作为用户映射的user和password选项。

4. 为每一个你想访问的远程表使用CREATE FOREIGN TABLE或者IMPORT FOREIGN SCHEMA创建一个外部表。外部表的列必须匹配被引用的远程表。但是，如果你在外部表对象的选项中指定了正确的远程名称，你可以使用不同于远程表的表名和/或列名。

现在你只需要从一个外部表SELECT来访问存储在它的底层的远程表中的数据。你也可以使用INSERT、UPDATE或DELETE修改远程表（当然，你在你的用户映射中已经指定的远程用户必须具有做这些事情的权限）。

注意当前udb_fdw缺少对于带ON CONFLICT DO UPDATE子句的INSERT语句的支持。不过，它支持ON CONFLICT DO NOTHING子句，已提供的唯一索引推断说明会被省略。

我们通常推荐一个外部表的列被声明为与被引用的远程表列完全相同的数据类型和排序规则（如果可用）。尽管udb_fdw目前已经能够容忍在需要时执行数据类型转换，但是当类型或排序规则不匹配时可能会发生奇怪的语义异常，因为远程服务器解释WHERE子句时可能会与本地服务器有所不同。

注意一个外部表可以被声明比底层的远程表较少的列，或者使用一种不同的列序。与远程表的列匹配是通过名字而不是位置进行的。

udb_fdw 的 FDW 选项

** 连接选项**

一个使用udb_fdw外部数据包装器的外部服务器可以使用和libpq在连接字符串中能接受的选项

• user和password（应该在用户映射中指定这些）

• client_encoding（这是自动从本地服务器编码设置）

• fallback_application_name（总是设置为udb_fdw）

只有超级用户可以在不经过口令认证的情况下连接到外部服务器，因此应总是为属于非超级用户的用户映射指定password选项。

对象名称选项

这些选项可以被用来控制使用在被发送到远程UDB-TX服务器的 SQL 语句中使用的名称。当一个外部表被使用不同于底层远程表的名称创建时，就需要这些选项。

• schema_name

  这个选项给出用在远程服务器之上的外部表的模式名称，它可以为一个外部表指定。如果这个选项被忽略，该外部表的模式名称将被使用。

• table_name

  这个选项给出用在远程服务器上的外部表给出表名，它可以为一个外部表指定。如果这个选项被忽略，该外部表的名字将被使用。

• column_name

  这个选项给出用在远程服务器上列的列名，它可以为一个外部表的一个列指定。如果这个选项被忽略，该列的名字将被使用。

代价估计选项

udb_fdw通过在远程服务器上执行查询来检索远程数据，因此理想的扫描一个外部表的估计代价应该是在远程服务器上完成它的花销，外加一些通信开销。得到这样一个估计的最可靠的方法是询问远程服务器并加上一些通信开销 — 但是对于简单查询，不值得为获得一个代价估计而额外使用一次远程查询。因此udb_fdw提供了下列选项来控制如何完成代价估计：

• use_remote_estimate

  这个选项控制udb_fdw是否发出EXPLAIN命令来获得代价估计，它可以为一个外部表或一个外部服务器指定。一个外部表的设置会覆盖它的服务器的任何设置，但是只用于这个表。默认值是false。

• fdw_startup_cost

  这个选项是一个要被加到那个服务器上所有外部表扫描的估计启动代价的数字值。这表示建立一个连接、在远端解析和规查询的额外负荷等。默认值是100。

• fdw_tuple_cost

  这个选项是一个数字值，它被用作那个服务器上外部表扫描的每元组额外代价，它可以为一个外部服务器指定。这表示在服务器之间数据传输的额外负荷。你可以增加或减少这个数来反映到远程服务器更高或更低的网络延迟。默认值是0.01。

当use_remote_estimate为真时，udb_fdw从远程服务器获得行计数和代价估计，然后在代价估计上加上fdw_startup_cost和fdw_tuple_cost。当use_remote_estimate为假时，udb_fdw执行本地行计数和代价估计，并且接着在代价估计上加上fdw_startup_cost 和fdw_tuple_cost。这种本地估计不会很准确，除非有远程表统计数据的本地拷贝可用。在外部表上运行ANALYZE是更新本地统计数据的方法，这将执行远程表的一次扫描并接着计算和存储统计数据，就好像表在本地一样。保留本地统计数据可能是一种有用的方法来减少一个远程表的预查询规划负荷 — 但是如果远程表被频繁更新，本地统计数据将很快就被废弃。

远程执行选项

默认情况下，只有使用了内建操作符和函数的WHERE子句才会被考虑在远程服务器上执行。涉及非内建函数的子句将会在取完行后在本地进行检查。如果这类函数在远程服务器上可用并且可以用来产生和本地执行时一样的结果，则可以通过将这种WHERE子句发送到远程执行来提高性能。可以用下面的选项控制这种行为：

• extensions

  这个选项是一个用逗号分隔的已安装的UDB-TX扩展名称列表，这些扩展在本地和远程服务器上具有兼容的版本。属于一个该列表中扩展的 immutable 函数和操作符将被考虑转移到远程服务器上执行。这个选项只能为外部服务器指定，无法逐个表指定。在使用extensions选项时，用户应该负责确保列出的扩展在本地和远程服务器上都存在且保持一致。否则，远程查询可能失败或者行为异常。

• fetch_size

  这个选项指定在每次获取行的操作中udb_fdw应该得到的行数。可以为一个外部表或者外部服务器指定这个选项。在表上指定的选项将会覆盖在服务器级别上指定的选项。默认值为100。

可更新性选项

默认情况下，所有使用udb_fdw的外部表都被假定是可更新的。这可以使用下列选项覆盖：

• updatable

  这个选项控制udb_fdw是否允许外部表被使用INSERT、UPDATE和DELETE命令更新。它可以为一个外部表或一个外部服务器指定。一个表级选项会覆盖一个服务器级选项。默认值是true。当然，如果远程表实际上并非可更新的，将产生一个错误。这个选项的使用主要是允许在不查询远程服务器的情况下在本地抛出错误。但是要注意information_schema视图会根据这个选项的设置报告一个udb_fdw外部表是可更新的（或者不可更新），而不需要远程服务器的任何检查。

导入选项

udb_fdw能使用IMPORT FOREIGN SCHEMA导入外部表定义。这个命令会在本地服务器上创建外部表定义，这个定义能匹配存在于远程服务器上的表或者视图。如果要被导入的远程表有用户自定义数据类型的列，本地服务器上也必须具有相同名称的兼容类型。

导入行为可以用下列选项自定义（在IMPORT FOREIGN SCHEMA命令中给出）：

• import_collate

  这个选项控制是否在从外部服务器导入的外部表定义中包括列的COLLATE选项。默认是true。如果远程服务器具有和本地服务器不同的排序规则名集合，可能需要关闭这个选项，在不同的操作系统上运行时很可能就是这样。

• import_default

  这个选项控制是否在从外部服务器导入的外部表定义中包括列的DEFAULT表达式。默认是false。如果启用这个选项，要当心在远程服务器和本地服务器上计算表达式的方式不同，nextval()常会导致这类问题。如果导入的默认值表达式使用了一个本地不存在的函数或者操作符，IMPORT将整个失败。

• import_not_null

  这个选项控制是否在从外部服务器导入的外部表定义中包括列的NOT NULL约束。默认是true。

注意除NOT NULL之外的约束将不会从远程表中导入。虽然UDB-TX确实支持外部表上的CHECK约束，但不会自动导入它们，因为存在本地和远程服务器计算约束表达式方式不同的风险。CHECK约束中的任何这类不一致都可能导致查询优化中很难检测的错误。因此，如果你希望导入CHECK约束，你必须手工来做，并且你应该仔细地验证每一个这种约束的语义。

自动排除作为其他表的分区的表或外部表。分区表被导入，除非它们是其他表的分区。 由于所有数据都可以通过作为分区层次根的分区表来访问， 所以这种方法应该允许访问所有数据而不创建额外的对象。

连接管理

udb_fdw在第一个使用关联到外部服务器的外部表的查询期间建立一个到外部服务器的连接。这个连接会被保持，并被重用于同一个会话中的后续查询。但是，如果使用了多个用户实体（用户映射）来访问外部服务器，会为每一个用户映射建立一个连接。

事务管理

在一个引用外部服务器上任何远程表的查询期间，如果还没有根据当前的本地事务打开一个远程事务，udb_fdw将在远程服务器上打开一个事务。当本地事务提交或中止时，远程事务也被提交或中止。保存点也相似地采用创建相应的远程保存点来管理。

当本地事务为SERIALIZABLE隔离级别时，远程事务使用SERIALIZABLE隔离级别；否则它使用REPEATABLE READ隔离级别。如果一个查询在远程服务器上执行多个表查询，这种选择保证它将为所有扫描得到快照一致的结果。一种后果是在单一事务中的后继查询将会看到来自远程服务器的相同数据，即便由于其他活动在远程服务器上发生了其他并发更新。如果本地事务使用SERIALIZABLE或REPEATABLE READ隔离级别，这种行为也是可以预期的，但是对于一个READ COMMITTED本地事务它是奇怪的。一个未来的UDB-TX发布可能会修改这些规则。

远程查询优化

udb_fdw尝试优化远程查询来减少从外部服务器传来的数据量。这可以通过把查询的WHERE子句发送给远程服务器执行来完成，并且还可以不检索当前查询不需要的表列。为了降低查询被误执行的风险，除非WHERE子句使用的数据类型、操作符和函数都是内建的或者属于列在该外部服务器的extensions选项中的一个扩展，将不会把WHERE子句发送到远程服务器。这些子句中的操作符合函数也必须是IMMUTABLE。对于UPDATE或者DELETE查询， 如果没有不能发送给远程服务器的WHERE子句、 没有查询的本地连接、目标表上没有本地的行级BEFORE或AFTER触发器， 并且没有来自父视图的CHECK OPTION约束，udb_fdw会尝试通过将整个查询发送给远程服务器来优化查询的执行。在UPDATE中，赋值给目标列的表达式只能使用内建数据类型、IMMUTABLE操作符或者IMMUTABLE操作符，这样能降低查询被误执行的风险。

当udb_fdw碰到同一个外部服务器上的外部表之间的连接时，它会把整个连接发送给外部服务器，除非由于某些原因它认为逐个从每一个表取得行的效率更高或者涉及的表引用属于不同的用户映射。在发送JOIN子句时，它也会采取和上述WHERE子句相同的预防措施。

实际被发送到远程服务器执行的查询可以使用EXPLAIN VERBOSE来检查。

例子

这里是一个用udb_fdw创建外部表的例子。首先安装该扩展：

CREATE EXTENSION udb_fdw;

然后使用CREATE SERVER创建一个外部服务器。在这个例子中我们希望连接到一个位于主机192.83.123.89上并且监听5432端口的UDB-TX服务器。在该远程服务器上要连接的数据库名为foreign_db：

CREATE SERVER foreign_server
        FOREIGN DATA WRAPPER udb_fdw
        OPTIONS (host '192.83.123.89', port '5432', dbname 'foreign_db');

需要用CREATE USER MAPPING定义一个用户映射来标识在远程服务器上使用哪个角色：

CREATE USER MAPPING FOR local_user
        SERVER foreign_server
        OPTIONS (user 'foreign_user', password 'password');

现在就可以使用CREATE FOREIGN TABLE创建外部表了。在这个例子中我们希望访问远程服务器上名为some_schema.some_table的表。它的本地名称是foreign_table：

CREATE FOREIGN TABLE foreign_table (
        id integer NOT NULL,
        data text
)
        SERVER foreign_server
        OPTIONS (schema_name 'some_schema', table_name 'some_table');

CREATE FOREIGN TABLE中声明的列数据类型和其他性质必须要匹配实际的远程表。列名也必须匹配，不过也可以为个别列附上column_name选项以表示它们在远程服务器上对应哪个列。在很多情况中，要手工构造外部表定义，使用IMPORT FOREIGN SCHEMA会更好。

seg

seg

这个模块为表示线段或者浮点区间实现了一种数据类型seg。 seg可以表示区间端点中的不确定性，这使得它对表达实验室测量特别有用。

原理

度量的几何结构通常比一个数字连续区中的一个点更复杂。 一个度量通常是具有一些模糊限制的连续区的一个分段。 由于不确定性和随机性，也因为被度量的值可能天然地就是 一个指示某种情况的区间（例如一种蛋白质的稳态的温度范围）， 度量呈现为区间的形式。

只用常识，我们就知道将这类数据存储为区间比存储为数字对更加方便。 实际上，这样做在大部分应用中也更有效。

还是根据常识，限度的模糊性意味着使用传统的数字数据类型会导致信息丢失。 试想：你的仪器读到 6.50，并且你把这个读数输入到数据库。在你取出它时会 得到什么？看看：

test=> select 6.50 :: float8 as "pH";
 pH
---
6.5
(1 row)

在度量世界里，6.50 和 6.5 并不相同。有时候它们可能很不同。实验者们通常会 写下（并且发表）他们信任的数字。6.50 实际上是一个模糊的区间，它被包含于 一个更大的而且更模糊的区间 6.5 中，它们的中心点（可能）是它们唯一共享的 公共特征。我们绝对不希望这类不同的数据项表现得相同。

结论？一种能够记录具有任意可变精度的区间的限度的特殊数据类型是很好的。 这种意义下，每一个数据元素都记录其自身的精度。

来看看这个：

test=> select '6.25 .. 6.50'::seg as "pH";
       pH
------------
6.25 .. 6.50
(1 row)

语法

一个区间的外部表达由通过范围操作符（..或者...）连接的一个或者两个浮点数构成。 或者，它也可以被指定为一个中心点加上或者减去一个偏差值。 也能够存储可选的确定性指示符（<、 >或者~）。不过，所有内建操作符会忽略确定性指示符。 表 F.26给出了所有允许的表达形式，表 F.27展示了一些例子。

在表 F.26中，x、*y和delta*表示浮点数。 *x和y可以前置一个确定性指示符，但是delta*不行。

表 F.26. seg外部表达

*x*	单一值（零长度区间）
*x* .. *y*	从x到y的区间
*x* (+-) *delta*	从x - delta到 x + delta的区间
*x* ..	下界为x的开区间
.. *x*	上界为x的开区间

表 F.27. 合法seg输入的例子

5.0	创建一个零长度的段（一个点）
~5.0	创建一个零长度的段并且在数据中记录~。 ~会被seg操作忽略，但是会被保留为一个注释。
<5.0	在 5.0 创建一个点。<会被忽略，但是被保留为一个注释。
>5.0	在 5.0 创建一个点。>会被忽略，但是被保留为一个注释。
5(+-)0.3	创建一个区间4.7 .. 5.3。注意(+-)标记不会被保留。
50 ..	大于或等于 50 的所有东西
.. 0	小于或等于 0 的所有东西
1.5e-2 .. 2E-2	创建一个区间0.015 .. 0.02
1 ... 2	与1...2、1 .. 2或者1..2相同 （范围操作符周围的空格会被忽略）

由于...被广泛地用在数据源中，它被允许作为..的一种替代。 不幸地是，这会带来解析歧义：分不清0...23的上界是23或者0.23。 通过要求seg输入中所有数字的小数点前至少有一位可以解决这个问题。

作为一种完整性检查，seg会拒绝下界大于上界的区间，例如5 .. 2。

精度

seg值在内部被存储为一对 32 位浮点数。这意味着具有超过 7 个有效位的数字会被截断。

具有 7 个或者更少有效位的数字会保留它们的原有精度。即，如果你的查询返回 0.00， 你可以确信拖尾的零不是人工造成的，它们反映了原始数据的精度。前导零的数量不影响精度： 值 0.0067 被认为只有 2 个有效位。

用法

seg模块包括了用于seg值的一个 GiST 索引操作符类。 该 GiST 操作符类所支持的操作符在表 F.28中展示。

表 F.28. Seg GiST 操作符

操作符	描述
[a, b] << [c, d]	[a, b] 完全位于 [c, d] 左边。即如果b < c 则 [a, b] << [c, d] 为真，否则为假。
[a, b] >> [c, d]	[a, b] 完全位于 [c, d] 右边。即如果b > c 则 [a, b] >> [c, d] 为真，否则为假。
[a, b] &< [c, d]	重叠或者是在左边 — 这最好读作 “不超过右边”。当 b <= d 时为真。
[a, b] &> [c, d]	重叠或者是在右边 — 这最好读作 “不超过左边”。当 a >= c 时为真。
[a, b] = [c, d]	相等 — 段 [a, b] 和 [c, d] 是一样的，也就是 a = c 并且 b = d。
[a, b] && [c, d]	段 [a, b] 和 [c, d] 重叠。
[a, b] @> [c, d]	段 [a, b] 包含段 [c, d]，也就是 a <= c 并且 b >= d。
[a, b] <@ [c, d]	段 [a, b] 被包含在 [c, d] 中，也就是 >= c 并且 b <= d。

也提供了标准的 B-树操作符，例如

操作符	描述
[a, b] < [c, d]	小于
[a, b] > [c, d]	大于

这些操作符对除了排序之外的任何实际目的都没有什么意义。 这些操作符首先比较 a 和 c，并且如果它们相等则比较 b 和 d。 在大部分情况下这会得到相当好的排序，如果你想对这种类型使用 ORDER BY， 这会很有用。

注解

使用的例子，请见回归测试sql/seg.sql。

将(+-)转换成常规范围的机制在确定边界的有效位数时并不完全准确。 例如，如果结果区间包括一个 10 的幂时，它会加上一个额外的位：

postgres=> select '10(+-)1'::seg as seg;
      seg
---------
9.0 .. 11             -- should be: 9 .. 11

sepgsql

sepgsql是一个基于SELinux安全策略的 支持基于标签的强制访问控制（MAC）模块。

概述

这个模块和SELinux集成在一起在 数据库提供的安全检查之上提供了一个 额外的安全检查层。从SELinux的角度来看，这个模块允许 数据库作为一个用户空间对象管理器。 对每一次由 DML 查询发起的表或者函数访问将根据系统安全策略进行检查。这种 检查是在数据库执行的常规 SQL 权限 检查之外进行的。

SELinux访问控制决定是通过使用安全标签 来做出的，安全标签使用system_u:object_r:sepgsql_table_t:s0 这样的字符串表示。每个访问控制决定涉及两个标签：尝试执行该动作的主体的 标签以及要在其上执行该动作的客体的标签。由于这些标签可以被应用于任何种 类的对象，对于存储在数据库中的对象的（用这个模块做出的）访问控制决定服 从于用于任意其他类型对象（例如文件）的同一种一般准则。这种设计是为了允 许一种中央安全策略来保护信息资产，而不依赖于这些资产是如何存储的。

SECURITY LABEL语句允许为一个数据库对象分配安全标签。

安装

sepgsql只能在启用了 SELinux的 Linux 2.6.28 或者更高版本上使用。在任何 其他平台上都无法使用这个模块。你将还需要 libselinux 2.1.10 或者更高版本以及 selinux-policy 3.9.13 或者更高版本（尽管某些发行中可能 把必要的规则逆向移植到较老的策略版本中）。

你可以使用sestatus命令检查 SELinux的状态。一种典型的显示是：

$ sestatus
SELinux status:                 enabled
SELinuxfs mount:                /selinux
Current mode:                   enforcing
Mode from config file:          enforcing
Policy version:                 24
Policy from config file:        targeted

如果没有安装或者启用SELinux，你就必须在安装这个模块 之前先安装或者启用它。

要编译这个模块，应该在你的 UDB-TX configure 命令中包括 选项--with-selinux。还要确定编译时安装了 libselinux-devel RPM 包。

要使用这个模块，你必须在unvdbsvr.conf文件中的 shared_preload_libraries参数里包括 sepgsql。如果以其他任何方式载入该模块，它将无法正确地工作。 一旦该模块被载入，你应该在每一个数据库中执行 sepgsql.sql。这将会安装安全标签管理所需的函数 并且分配初始的安全标签。

这里有一个展示如何用sepgsql函数和安全标签初始化一个新 数据库集簇的例子（根据你的安装调整其中的路径）：

$ export UDDATA=/path/to/data/directory
$ initdb
$ vi $UDDATA/unvdbsvr.conf
  change
    #shared_preload_libraries = ''                # (change requires restart)
  to
    shared_preload_libraries = 'sepgsql'          # (change requires restart)
$ for DBNAME in template0 template1 postgres; do
    postgres --single -F -c exit_on_error=true $DBNAME \
      </usr/local/pgsql/share/contrib/sepgsql.sql >/dev/null
  done

请注意，如果你具有特定版本的libselinux和 selinux-policy，你可能会看到下列提示中的一些或者全部：

/etc/selinux/targeted/contexts/sepgsql_contexts:  line 33 has invalid object type db_blobs
/etc/selinux/targeted/contexts/sepgsql_contexts:  line 36 has invalid object type db_language
/etc/selinux/targeted/contexts/sepgsql_contexts:  line 37 has invalid object type db_language
/etc/selinux/targeted/contexts/sepgsql_contexts:  line 38 has invalid object type db_language
/etc/selinux/targeted/contexts/sepgsql_contexts:  line 39 has invalid object type db_language
/etc/selinux/targeted/contexts/sepgsql_contexts:  line 40 has invalid object type db_language

这些消息是无害的并且应该被忽略。

如果该安装过程完成时没有出现错误，就可以正常启动服务器了。

回归测试

由于SELinux的本质，为sepgsql 运行回归测试要求一些额外的配置步骤，某些步骤还需要由 root 来完成。该回归测试 无法通过普通的make check或者make installcheck命令运行， 你必须建立配置并且接着手工调用测试脚本。这些测试必须在一个已配置 数据库 编译树的contrib/sepgsql目录中运行。尽管它们要求一个编译树，但是 这些测试被设计成在一个已安装服务器上执行，也就是说它们可以比得上 make installcheck（而不是make check）。

首先，根据第 F.35.2 节中的指导在一个工作数据库中设置 sepgsql。注意当前操作系统用户必须能够不使用口令认证作 为超级用户连接到该数据库。

第二，为该回归测试编译和安装策略包。sepgsql-regtest策略是一个 特殊的策略包，它提供一组在回归测试浅见要被允许的规则。它应该从策略源文件 sepgsql-regtest.te编译，这需要通过使用 make和一个 SELinux 提供的 Makefile 完成。你将需要 在你自己的系统上找到合适的 Makefile，下面展示的路径只是一个例子。一旦编译好， 使用semodule命令安装这个策略包，它会把所提供的策略包载入到 内核中。如果该包被正确地安装，semodule -l应该把 sepgsql-regtest列成一个可用的策略包：

$ cd .../contrib/sepgsql
$ make -f /usr/share/selinux/devel/Makefile
$ sudo semodule -u sepgsql-regtest.pp
$ sudo semodule -l | grep sepgsql
sepgsql-regtest 1.07

第三，打开sepgsql_regression_test_mode。由于安全性的原因， sepgsql-regtest中的规则默认没有被启用。 the sepgsql_regression_test_mode参数会启用启动该回归 测试所需的规则。它可以使用setsebool命令来启用：

$ sudo setsebool sepgsql_regression_test_mode on
$ getsebool sepgsql_regression_test_mode
sepgsql_regression_test_mode --> on

第四，验证你的 shell 在unconfined_t域中操作：

$ id -Z
unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023

如果有必要，可以参考第 F.35.8 节来调整你的工作域。

最后，运行该回归测试脚本：

$ ./test_sepgsql

这个脚本将尝试验证你已经正确地完成了所有的配置步骤，接下来它将运行 sepgsql模块的回归测试。

完成测试后，推荐你禁用 sepgsql_regression_test_mode参数：

$ sudo setsebool sepgsql_regression_test_mode off

你可能想要完全移除sepgsql-regtest策略：

$ sudo semodule -r sepgsql-regtest

GUC 参数

• sepgsql.permissive (boolean)

  不管系统设置如何，这个参数让sepgsql在自由模式中运行。 默认值为关闭。这个参数只能在unvdbsvr.conf文件中或者 服务器命令行上被设置。当这个参数为打开时，sepgsql在自由模式中运行，即便 SELinux 运行在强制模式中也是如此。这个参数主要用于测试目的。

• sepgsql.debug_audit (boolean)

  不管系统策略设置如何，这个参数启用打印审计消息。默认值为关闭，表示将 根据系统设置打印消息。SELinux的安全性策略也具有控制是否记录特定访问的 规则。默认情况下，违法访问将会被记录，但是被允许的访问则不会被记录。这个参数强制打开所有可能的记录而不管该系统策略。

特性

控制对象类

SELinux的安全模型把所有访问控制规则描述为一个 主体（典型的是一个数据库客户端）和一个客体（例如一个数据库对象）之 间的关系， 每一个这样的关系被一个安全标签标识。如果尝试访问一个未加 标签的客体，会认为该客体被分配了标签unlabeled_t。

当前，sepgsql允许把安全标签分配给模式、表、列、 序列、视图和函数。在使用sepgsql时，安全标签会 在所支持的数据库对象创建时自动分配给它们。这种标签被称为默认安全标签 并且根据系统安全性策略决定，默认安全标签被用来输入创建者标签、分配给 新对象父对象的标签以及所构造对象的可选名称。

一个新数据库对象基本上会继承父对象的安全标签，不过当安全策略具有特殊的 类型转换规则时，将会应用一个不同的标签。对于模式，其父对象是当前数据库。 对于表、序列、视图和函数，父对象是包含它的模式。对于列，其父对象是包含 它的表。

DML 权限

对于表，根据语句的种类会对所有被引用的目标表检查 db_table:select、db_table:insert、 db_table:update或者db_table:delete。此外，对于 所有其列被WHERE或RETURNING子句引用、作为 UPDATE的数据源（以及其他情况）的表， 都要检查db_table:select。

对每一个被引用的列也将检查列级权限。不仅在使用SELECT读取列 时会检查db_column:select，在其他 DML 语句中引用列时也要检查。 对于被UPDATE或者INSERT修改的列也将检查 db_column:update或者db_column:insert。

例如，考虑：

UPDATE t1 SET x = 2, y = func1(y) WHERE z = 100;

这里，将对t1.x检查db_column:update，因为它 被更新。对t1.y将检查db_column:{select update}， 因为它既被更新也被引用。并且会对t1.z检查 db_column:select，因为它只被更新。还将在表层面上检查 db_table:{select update}。

对于序列，当我们使用SELECT引用一个序列对象时会检查 db_sequence:get_value。不过，我们当前不会检查执行相应 函数（例如lastval()）的权限。

对于视图，将检查db_view:expand，然后对从视图展开来的任何 对象都会分别检查所需的权限。

对于函数，当用户尝试在一个查询中或者使用快路径调用执行一个函数时会检查 db_procedure:{execute}。如果该函数是一个可信过程，也会检查 db_procedure:{entrypoint}权限来看看它能否作为一个可信程序 的入口点来执行。

为了访问任何模式对象，在其所在的模式上需要db_schema:search 权限。当不用模式限定引用一个对象时，其上没有该权限的模式不会被搜索（就好 像该用户在该模式上没有USAGE特权）。如果出现一个显式的模式 限定，当该用户在提及的模式上没有要求的权限时将会发生一个错误。

客户端必须被允许访问所有引用到的表和列，即便它们是由视图扩展得来的。这样 我们可以应用一致的访问控制规则而不管表内容被引用的方式。

默认的数据库特权系统允许数据库超级用户使用 DML 命令修改系统目录并且引用 或者修改 TOAST 表。当sepgsql被启用时，这些操作会被禁止。

DDL 权限

SELinux为每一种对象类型定义了数个权限来控制 常用操作，例如创建、修改、删除以及重新标记安全标签。此外，数种 对象类型具有特殊的权限来控制它们的特性化操作，例如在一个特定模式 中增加或者删除名字项。

创建一个新的数据库对象要求create权限。 SELinux将基于客户端的安全标签来授予或者否决 这个权限并且为新对象提出安全标签。在某些情况下，还需要额外的特权：

• CREATE DATABASE额外要求源数据库或者模板数 据库的getattr权限。

• 创建一个模式对象额外地要求父模式上的add_name权限。

• 创建一个表额外要求创建单个表列的权限，就好像每一个表列都是一个 单独的顶层对象。

• 创建一个被标记为LEAKPROOF的函数额外要求 install权限（当为一个现有函数设置 LEAKPROOF时也要检查这个权限）。

当执行DROP命令时，在要移除的对象上会检查drop。 对于通过CASCADE间接被删除的对象也会检查权限。删除包含在 一个特定模式内的对象（表、视图、序列以及过程）额外地要求该模式上的 remove_name。

在执行ALTER命令时，会在被修改的对象上为每一种对象类型检查 setattr。附属对象（例如一个表的索引或者触发器）除外， 这种 情况下权限是在父对象上检查的。在某些情况下，还需要额外的权限：

• 将一个对象移动到一个新的模式要求旧模式上的remove_name 权限以及新模式上的add_name权限。

• 设置一个函数上的LEAKPROOF属性要求install权限。

• 在一个对象上使用SECURITY LABEL会额外对该对象要求 relabelfrom权限连同它的旧安全标签以及relabelto 权限连同它的新安全标签（在安装了多个标签提供者并且用户尝试设置一个不由 SELinux管理的安全标签的情况中，这里只应该检查 setattr。当前由于实现限制没有这样做。）。

可信过程

可信过程类似于 SECURITY DEFINER 函数或者 setuid 命令。 SELinux提供了一个特性来允许可信代码使用一个不同 于客户端的安全标签运行，通常这是为了提供对敏感数据的高度控制的访问（ 例如行可能会被忽略或者存储值的精度可能会被降低）。一个函数是否可以 作为可信过程受到其安全标签和操作系统安全性策略的控制。例如：

unvdbsvr=# CREATE TABLE customer (
               cid     int primary key,
               cname   text,
               credit  text
           );
CREATE TABLE
unvdbsvr=# SECURITY LABEL ON COLUMN customer.credit
               IS 'system_u:object_r:sepgsql_secret_table_t:s0';
SECURITY LABEL
unvdbsvr=# CREATE FUNCTION show_credit(int) RETURNS text
             AS 'SELECT regexp_replace(credit, ''-[0-9]+$'', ''-xxxx'', ''g'')
                        FROM customer WHERE cid = $1'
           LANGUAGE sql;
CREATE FUNCTION
unvdbsvr=# SECURITY LABEL ON FUNCTION show_credit(int)
               IS 'system_u:object_r:sepgsql_trusted_proc_exec_t:s0';
SECURITY LABEL

上述的操作应该由一个管理员用户执行。

unvdbsvr=# SELECT * FROM customer;
ERROR:  SELinux: security policy violation
unvdbsvr=# SELECT cid, cname, show_credit(cid) FROM customer;
 cid | cname  |     show_credit
-----+--------+---------------------
   1 | taro   | 1111-2222-3333-xxxx
   2 | hanako | 5555-6666-7777-xxxx
(2 rows)

在这种情况下，一个常规用户无法直接引用customer.credit， 但是一个可信过程show_credit允许用户在打印客户的信用卡号时 把一些数字掩盖掉。

动态域转换

如果安全性策略允许，可以使用 SELinux 的动态域转换特性来切换客户端 进程（客户端域）的安全性标签到一个新的上下文。该客户端域需要 setcurrent权限还有从旧的域到新的域的 dyntransition权限。

动态域转换需要被仔细考虑，因为在用户看来，它们允许用户切换其标签， 并且因而切换特权，而不是（像可信过程的情况那样）受系统的强制性管理。 因此，只有当被用来切换到一个比原来的域具有更少特权的域时， dyntransition才被认为是安全的。例如：

regression=# select sepgsql_getcon();
                    sepgsql_getcon
-------------------------------------------------------
 unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023
(1 row)
regression=# SELECT sepgsql_setcon('unconfined_u:unconfined_r:unconfined_t:s0-s0:c1.c4');
 sepgsql_setcon 
----------------
 t
(1 row)
regression=# SELECT sepgsql_setcon('unconfined_u:unconfined_r:unconfined_t:s0-s0:c1.c1023');
ERROR:  SELinux: security policy violation

在上面的这个例子中，我们被允许从较大范围的c1.c1023切换到 较小范围的c1.c4，但却禁止切换回去。

动态域转换和可信过程的组合开启了一种有趣的使用案例，它适合典型的 连接池软件的处理生命周期。即便你的连接池软件不被允许运行大部分的 SQL 命令，你可以从一个可信过程中使用 sepgsql_setcon()函数允许它切换该客户端的安全标签， 这个过程应该采用一些证据来授权该请求切换该客户端标签。之后，这个 会话将会具有目标用户而不是连接池的特权。该连接池之后可以用 NULL参数再次调用sepgsql_setcon() 逆转这次安全标签改变，当然再次的调用也要在一个可信过程中配合适当 的权限检查进行。这里的要点是只有可信过程实际具有权限来更改有效的安全 标签，并且只有在得到适当的证据后才这样做。当然，对于安全操作，必须 保护证据存储（表、过程定义或者其他什么）不会受到未经授权的访问。

Sepgsql 函数

表 F.29展示了可用的函数。

表 F.29. Sepgsql 函数

sepgsql_getcon() returns text	返回该客户端域，也就是该客户端当前的安全标签。
sepgsql_setcon(text) returns bool	如果安全性策略允许，把当前会话的客户端域切换到一个新的域。它也接受 NULL输入，并且把它当做是切换到该客户端原始域的请求。
sepgsql_mcstrans_in(text) returns text	如果 mcstrans 守护进程在运行中，把给定的限定 MLS/MCS 范围 翻译成原始格式。
sepgsql_mcstrans_out(text) returns text	如果 mcstrans 守护进程在运行中，把给定的原始 MLS/MCS 范围 翻译成限定格式。
sepgsql_restorecon(text) returns bool	在当前数据库中为所有对象设置初始安全标签。参数可能是 NULL，或者是 一个被用作系统默认 specfile 替代品的 specfile 名称。

sslinfo

在连接到unvdbsvr时，sslinfo模块提供当前客户端提供的 SSL 证书的有关信息。如果当前连接不使用 SSL，这个模块就没有用处（大部分函数将返回 NULL）。

除非安装时用--with-openssl进行了配置，这个扩展压根就不会被编译。

提供的函数

• ssl_is_used() 返回 boolean

  如果当前到服务器的连接使用 SSL 则返回 true，否则返回 false。

• ssl_version() 返回 text

  返回 SSL 连接使用的协议名称（如 TLSv1.0、TLSv1.1 或者 TLSv1.2）。

• ssl_cipher() 返回 text

  返回 SSL 连接所用的加密方法名称（如 DHE-RSA-AES256-SHA）。

• ssl_client_cert_present() 返回 boolean

  如果当前客户端已经向服务器出示了一个合法的 SSL 客户端证书则返回 true，否则返回 false（服务器可能被配置要求一个客户端配置，也可能没有被配置成这样）。

• ssl_client_serial() 返回 numeric

  返回当前客户端证书的序列号。证书序列号和证书发行人的组合被确保可以 唯一标识一个证书（但是不能唯一标识其拥有者 — 拥有者应该定期地更换其密钥， 并且从发行人那里得到新的证书）。因此，如果你运行自己的 CA 并且只允许服务器接收来自于这个 CA 的证书，序列号就是最可靠的（虽然并非很好记忆）标识一个用户的方法。

• ssl_client_dn() 返回 text

  返回当前客户端证书的完整主题，并且将字符数据转换成当前数据库的编码。我们假定如果你在证书名中使用非 ASCII 字符，你的数据库也有能力展示这些字符。如果你的数据库使用 SQL_ASCII 编码，名称中的非 ASCII 字符将被表示为 UTF-8 序列。结果看起来像/CN=某人 /C=某个国家 /O=某个组织。

• ssl_issuer_dn() 返回 text

  返回当前客户端证书的完整的发行人名称，并把字符数据转换成当前数据库的编码。编码转换以与ssl_client_dn相同的方式处理。这个函数的返回值与证书序列号的组合唯一地标识证书。如果在服务器的证书授权中心文件中有多于一个的可信 CA 证书，或者如果 CAI 已经发行了某些中间认证授权证书，这个函数就真的很有用。

• ssl_client_dn_field(fieldname text) 返回 text

  这个函数返回证书主题中指定域的值，如果域不存在则返回 NULL。域的名称是字符串常量，它们被使用 OpenSSL 对象数据库转换成 ASN1 对象标识符。下列值是可接受的：commonName (alias CN) surname (alias SN) name givenName (alias GN) countryName (alias C) localityName (alias L) stateOrProvinceName (alias ST) organizationName (alias O) organizationUnitName (alias OU) title description initials postalCode streetAddress generationQualifier description dnQualifier x500UniqueIdentifier pseudonym role emailAddress这些域中除了commonName都是可选的。它们之中哪些会被包括或者不会被包括完全取决于你的 CA 策略。不过，这些域的含义由 X.500 和 X.509 标准严格地定义，因此你不能只是为它们分配任意含义。

• ssl_issuer_field(fieldname text) 返回 text

  和ssl_client_dn_field一样，但是用于证书发行人而不是证书主题。

• ssl_extension_info() 返回 setof record

  提供有关客户端证书扩展的信息：扩展名、扩展值以及是否为 决定性的扩展。

spi

spi模块提供了多个可工作的使用服务器编程接口(SPI) 和触发器的例子。尽管这些例子的价值只对它们自己合适，它们甚至更有助于作为例子来修改达到你自己的目的。这些函数足够普通，可以与任何表一起使用，但是在创建一个触发器时你必须指定表名和域名（如下所述） 。

下面描述的函数组中的每一个都作为一个独立可安装的扩展被提供。

refint — 用于实现参照完整性的函数

check_primary_key()和check_foreign_key()被用来检查外键约束（当然，这个功能很早以前被内建的外建机制取代了，但是该模块还是可以用作一个例子）。

check_primary_key()检查引用表。用法是使用这个函数在一个引用其他表的表上创建一个BEFORE INSERT OR UPDATE触发器。指定该触发器的参数为：引用表中构成外键的列名、被引用表名称以及在被引用表中构成主键/唯一键的列名。要处理多个外键，请为每一个引用创建一个触发器。

check_foreign_key()检查被引用表。用法是使用这个函数在一个被其他表引用的表上创建一个BEFORE DELETE OR UPDATE触发器。指定该触发器的参数为：该函数必须对其执行检查的引用表数量、找到一个引用键后的动作（cascade — 删除引用行，restrict — 如果引用键存在则中断事务，setnull — 设置引用键域为空）、触发器所在表中构成主键/唯一键的列名、引用表名称和列名称（第一个参数指定多少个引用表就重复多少次）。注意主键/唯一键列应该被标记为 NOT NULL 并且应该有一个唯一索引。

refint.example中有一些例子。

timetravel — 实现时间旅行的函数

很久以前，UDB-TX有一个内建的时间旅行特性，它为每一个元组保留插入和删除时间。这能够用这些函数模拟。要使用这些函数，你必须为一个表增加两个abstime类型的列来存储元组被插入的日期（start_date）以及被改变/删除的日期（stop_date）：

CREATE TABLE mytab (
        ...             ...
        start_date      abstime,
        stop_date       abstime
        ...             ...
);

这些列可以以你喜欢的方式命名，但是在这次讨论中我们称它们为 start_date 和 stop_date。

当一个新行被插入时，start_date 通常应该被设置为当前时间，并且 stop_date 应当被设置为infinity。如果被插入的数据在这些列上包含空，触发器将自动替换这些值。通常，在这些列中插入显式非空数据的情况只有重新载入已转储数据。

等于infinity的元组“现在是合法的”，并且能够被修改。具有有限 stop_date 的元组不能再被修改 — 触发器将阻止修改（如果你需要这样做，你可以按照下面展示的关闭时间旅行）。

对于一个可修改的行，更新时只有被更新元组的 stop_date 将被改变（为当前时间）并且将会插入一个带有被修改数据的新元组。在这个新元组中的 start_date 将被设置为当前时间而 stop_date 被设置为infinity。

一次删除并非真正地移除元组，而只是将它的 stop_date 设置为当前时间。

要查询“当前有效”的元组，在查询的 WHERE 条件中包括stop_date = 'infinity'（你可能希望在一个视图中使用它）。类似地，你可以用 start_date 和 stop_date 上合适的条件来查询在任何过去时刻有效的元组。

timetravel()是支持这种行为的通用触发器函数。使用这个函数在每一个需要时间旅行的表上创建一个BEFORE INSERT OR UPDATE OR DELETE触发器。指定两个触发器参数：start_date 和 stop_date 列的真实名称。可选地，你可以指定 1-3 个更多的参数，它们必须表示text类型的列。该触发器将会在 INSERT、UPDATE、DELETE 期间将当前用户名分别存储到第 1、2、3 列中。

set_timetravel()允许你在一个表上打开或关闭时间旅行。set_timetravel('mytab', 1)将为表mytab打开时间旅行。set_timetravel('mytab', 0)将为表mytab关闭时间旅行。在两种情况中都会报告旧的状态。当时间旅行被关闭时，你可以自由地修改 start_date 和 stop_date 列。注意开/关状态是对于当前数据库会话局部可见的 — 新会话开始时所有表上的时间旅行总是被打开的。

get_timetravel()返回一个表的时间旅行状态，但不会改变它。

在timetravel.example中有一个例子。

autoinc — 用于自增域的函数

autoinc()是一个将序列的下一个值存储到一个整数域的触发器。这和内建的“序数列”特性有些重叠，但是它并不完全一样：autoinc()在插入时会覆盖掉给出的不同域值，并且它可被选择用来在更新时增加域。

用法是使用这个函数创建一个BEFORE INSERT（或者BEFORE INSERT OR UPDATE）触发器。指定两个触发器参数：要被修改的整数列名和将提供值的序列对象名（事实上，如果你想要更新多于一个自增列，你可以指定任意数量的这种名称对）。

在autoinc.example中有一个例子。

insert_username — 用于跟踪谁修改了一个表的函数

insert_username()是存储当前用户名到一个文本域的触发器。这有助于跟踪是谁最后在一个表中修改了一个特定行。

用法是使用这个函数创建一个BEFORE INSERT以及/或者UPDATE触发器。指定一个触发器参数：要被修改的文本列名。

在insert_username.example中有一个例子。

moddatetime — 用于跟踪上一次修改时间的函数

moddatetime()是一个存储当前时间到一个timestamp域的触发器。它有助于跟踪一个表中特定行最后一次的修改时间。

用法是使用这个函数创建一个BEFORE UPDATE触发器。指定一个触发器参数：要被修改的列名。该列必须是类型timestamp或者timestamp with time zone。

在moddatetime.example中有一个例子。

tablefunc

tablefunc tablefunc模块包括多个返回表（也就是多行）的函数。这些函数都很有用，并且也可以作为如何编写返回多行的 C 函数的例子。

所提供的函数

表 F.30显示了tablefunc模块提供的函数。

表 F.30. tablefunc函数

函数	返回	描述
normal_rand(int numvals, float8 mean, float8 stddev)	setof float8	产生一个正态分布的随机值集合
crosstab(text sql)	setof record	产生一个包含行名称外加N个值列的“数据透视表”，其中N由调用查询中指定的行类型决定
crosstab*N*(text sql)	setof table_crosstab_*N*	产生一个包含行名称外加N个值列的“数据透视表”。crosstab2、crosstab3和crosstab4是被预定义的，但你可以按照下文所述创建额外的crosstab*N*函数
crosstab(text source_sql, text category_sql)	setof record	产生一个“数据透视表”，其值列由第二个查询指定
crosstab(text sql, int N)	setof record	crosstab(text)的废弃版本。参数N现在被忽略，因为值列的数量总是由调用查询所决定
connectby(text relname, text keyid_fld, text parent_keyid_fld [, text orderby_fld ], text start_with, int max_depth [, text branch_delim ])	setof record	产生一个层次树结构的表达

normal_rand

normal_rand(int numvals, float8 mean, float8 stddev) returns setof float8

normal_rand产生一个正态分布随机值（高斯分布）的集合。

*numvals是从该函数返回的值的数量。mean是值的正态分布的均值而stddev*是值的正态分布的标准偏差。

例如，这个调用请求 1000 个值，它们具有均值 5 和标准偏差 3：

test=# SELECT * FROM normal_rand(1000, 5, 3);
     normal_rand
----------------------
     1.56556322244898
     9.10040991424657
     5.36957140345079
   -0.369151492880995
    0.283600703686639
       .
       .
       .
     4.82992125404908
     9.71308014517282
     2.49639286969028
(1000 rows)

crosstab(text)

crosstab(text sql)
crosstab(text sql, int N)

crosstab函数被用来产生“pivot”显示，在其中数据被横布在页面上而不是直接向下列举。例如，我们可能有这样的数据

row1    val11
row1    val12
row1    val13
...
row2    val21
row2    val22
row2    val23
...
而我们希望显示成这样

row1    val11   val12   val13   ...
row2    val21   val22   val23   ...
...

crosstab函数会采用一个文本参数，该文本是一个 SQL 查询，它产生按照第一种方式格式化的原始数据，并且产生以第二种方式格式化的一个表。

*sql参数是一个产生数据的源集合的 SQL 语句。这个语句必须返回一个row_name列、一个category列和一个value列。N*是一个废弃参数，即使提供也会被忽略（之前这必须匹配输出值列的数目，但是现在这由调用查询决定了）。

例如，所提供的查询可能会产生这样的一个集合：

 row_name    cat    value
----------+-------+-------
  row1      cat1    val1
  row1      cat2    val2
  row1      cat3    val3
  row1      cat4    val4
  row2      cat1    val5
  row2      cat2    val6
  row2      cat3    val7
  row2      cat4    val8

crosstab函数被声明为返回setof record， 因此输出列的实际名称和类型必须定义在调用的SELECT语句的FROM子句中，例如：

SELECT * FROM crosstab('...') AS ct(row_name text, category_1 text, category_2 text);

这个例子产生这样一个集合：

           <== value  columns  ==>
 row_name   category_1   category_2
----------+------------+------------
  row1        val1         val2
  row2        val5         val6

FROM子句必须把输出定义为一个row_name列 （具有 SQL 查询的第一个结果列的相同数据类型），其后跟随着 N 个value列 （都具有 SQL 查询的第三个结果列的相同数据类型）。你可以按照你的意愿设置任意多的输出值列。 而输出列的名称取决于你。

crosstab函数为具有相同row_name值的 输入行的每一个连续分组产生一个输出行。它使用来自这些行的值域 从左至右填充输出的值列。如果一个分组中的行比输出值列少， 多余的输出列将被用空值填充。如果行更多，则多余的输入行会被跳过。

事实上，SQL 查询应该总是指定ORDER BY 1,2来保证输入行被正确地排序， 也就是说具有相同row_name的值会被放在一起并且在行内 被正确地排序。注意crosstab本身并不关注查询结果的第二列，它放在那里 只是为了被排序，以便控制出现在页面上的第三列值的顺序。

这是一个完整的例子：

CREATE TABLE ct(id SERIAL, rowid TEXT, attribute TEXT, value TEXT);
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att1','val1');
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att2','val2');
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att3','val3');
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att4','val4');
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att1','val5');
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att2','val6');
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att3','val7');
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att4','val8');
SELECT *
FROM crosstab(
  'select rowid, attribute, value
   from ct
   where attribute = ''att2'' or attribute = ''att3''
   order by 1,2')
AS ct(row_name text, category_1 text, category_2 text, category_3 text);
 row_name | category_1 | category_2 | category_3
----------+------------+------------+------------
 test1    | val2       | val3       |
 test2    | val6       | val7       |
(2 rows)

你可以避免总是要写出一个FROM子句来定义输出列， 方法是设置一个在其定义中硬编码所期望的输出行类型的自定义 crosstab 函数。 这会在下一节中描述。另一种可能性是在一个视图定义中嵌入所需的FROM子句。

crosstabN(text)

crosstabN(text sql)

crosstab*N*系列函数是如何为普通crosstab 函数设置自定义包装器的例子，这样你不需要在调用的SELECT查询中 写出列名和类型。tablefunc模块包括crosstab2、 crosstab3以及crosstab4，它们的输入行类型被定义为：

CREATE TYPE tablefunc_crosstab_N AS (
    row_name TEXT,
    category_1 TEXT,
    category_2 TEXT,
        .
        .
        .
    category_N TEXT
);

因此，当输入查询产生类型为text的列row_name和value 并且想要 2、3 或 4 个输出值列时，这些函数可以被直接使用。在所有其他方法中，它们的行为都和上面的 一般crosstab函数完全相同。

例如，前一节给出的例子也可以这样来做

SELECT *
FROM crosstab3(
  'select rowid, attribute, value
   from ct
   where attribute = ''att2'' or attribute = ''att3''
   order by 1,2');

这些函数主要是出于举例的目的而提供。你可以基于底层的crosstab()函数 创建你自己的返回类型和函数。有两种方法来做：

• 与contrib/tablefunc/tablefunc--1.0.sql中相似，创建一个组合类型来描述所期望的输出列。 然后定义一个唯一的函数名，它接受一个text参数并且返回setof your_type_name，但是链接到同样的 底层crosstab C 函数。例如，如果你的源数据产生为text类型的行名称，并且值是float8， 并且你想要 5 个值列：

CREATE TYPE my_crosstab_float8_5_cols AS (
    my_row_name text,
    my_category_1 float8,
    my_category_2 float8,
    my_category_3 float8,
    my_category_4 float8,
    my_category_5 float8
);
CREATE OR REPLACE FUNCTION crosstab_float8_5_cols(text)
    RETURNS setof my_crosstab_float8_5_cols
    AS '$libdir/tablefunc','crosstab' LANGUAGE C STABLE STRICT;

使用OUT参数来隐式定义返回类型。同样的例子也可以这样来做：

CREATE OR REPLACE FUNCTION crosstab_float8_5_cols(
    IN text,
    OUT my_row_name text,
    OUT my_category_1 float8,
    OUT my_category_2 float8,
    OUT my_category_3 float8,
    OUT my_category_4 float8,
    OUT my_category_5 float8)
  RETURNS setof record
  AS '$libdir/tablefunc','crosstab' LANGUAGE C STABLE STRICT;

crosstab(text, text)

crosstab(text source_sql, text category_sql)

crosstab的单一参数形式的主要限制是它把一个组中的所有值都视作相似， 并且把每一个值插入到第一个可用的列中。如果你想要值列对应于特定的数据分类，并且 某些分组可能没有关于某些分类的数据，这样的形式就无法工作。crosstab的双参数形式 通过提供一个对应于输出列的显式分类列表来处理这种情况。

*source_sql*是一个产生源数据集的 SQL 语句。这个语句必须返回一个 row_name列、一个category列以及一个value列。 也可以有一个或者多个“extra”列。row_name列必须是第一个。 category和value列必须是按照这个顺序的最后两个列。 row_name和category之间的任何列都被视作“extra”。 对于具有相同row_name值的所有行，其“extra”列都应该相同。

例如，*source_sql*可能产生一组这样的东西：

SELECT row_name, extra_col, cat, value FROM foo ORDER BY 1;
 row_name    extra_col   cat    value
----------+------------+-----+---------
  row1         extra1    cat1    val1
  row1         extra1    cat2    val2
  row1         extra1    cat4    val4
  row2         extra2    cat1    val5
  row2         extra2    cat2    val6
  row2         extra2    cat3    val7
  row2         extra2    cat4    val8

*category_sql是一个产生分类集合的 SQL 语句。这个语句必须只返回一列。 它必须产生至少一行，否则会生成一个错误。还有，它不能产生重复值，否则会生成一个错误。category_sql*可能是这样的：

SELECT DISTINCT cat FROM foo ORDER BY 1;
    cat
  -------
    cat1
    cat2
    cat3
    cat4

crosstab函数被声明为返回setof record，这样输出列的实际名称和类型 就必须在调用的SELECT语句的FROM子句中被定义，例如：

SELECT * FROM crosstab('...', '...')
    AS ct(row_name text, extra text, cat1 text, cat2 text, cat3 text, cat4 text);

这将产生这样的结果：

            <==  value  columns   ==>
row_name   extra   cat1   cat2   cat3   cat4
---------+-------+------+------+------+------
  row1     extra1  val1   val2          val4
  row2     extra2  val5   val6   val7   val8

FROM子句必须定义正确数量的输出列以及正确的数据类型。如果在*source_sql* 查询的结果中有*N列，其中的前N-2 列必须匹配前N-2 个输出列。剩余的输出列必须具有source_sql查询结果的最后一列的类型，并且并且它们的数量 必须正好和source_sql*查询结果中的行数相同。

crosstab函数为具有相同row_name值的输入行形成的每一个连续分组 产生一个输出行。输出的row_name列外加任意一个“extra”列都是从分组的 第一行复制而来。输出的value列被使用具有匹配的category值的行中的 value域填充。如果一个行的category不匹配*category_sql* 查询的任何输出，它的value会被忽略。匹配的分类不出现于分组中任何输出行中的的 输出列会被用空值填充。

事实上，*source_sql查询应该总是指定ORDER BY 1来保证 具有相同row_name的值会被放在一起。但是，一个分组内分类的顺序并不重要。 还有，确保category_sql*查询的输出的顺序与指定的输出列顺序匹配是非常重要的。

这里有两个完整的例子：

create table sales(year int, month int, qty int);
insert into sales values(2007, 1, 1000);
insert into sales values(2007, 2, 1500);
insert into sales values(2007, 7, 500);
insert into sales values(2007, 11, 1500);
insert into sales values(2007, 12, 2000);
insert into sales values(2008, 1, 1000);
select * from crosstab(
  'select year, month, qty from sales order by 1',
  'select m from generate_series(1,12) m'
) as (
  year int,
  "Jan" int,
  "Feb" int,
  "Mar" int,
  "Apr" int,
  "May" int,
  "Jun" int,
  "Jul" int,
  "Aug" int,
  "Sep" int,
  "Oct" int,
  "Nov" int,
  "Dec" int
);
 year | Jan  | Feb  | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov  | Dec
------+------+------+-----+-----+-----+-----+-----+-----+-----+-----+------+------
 2007 | 1000 | 1500 |     |     |     |     | 500 |     |     |     | 1500 | 2000
 2008 | 1000 |      |     |     |     |     |     |     |     |     |      |
(2 rows)
CREATE TABLE cth(rowid text, rowdt timestamp, attribute text, val text);
INSERT INTO cth VALUES('test1','01 March 2003','temperature','42');
INSERT INTO cth VALUES('test1','01 March 2003','test_result','PASS');
INSERT INTO cth VALUES('test1','01 March 2003','volts','2.6987');
INSERT INTO cth VALUES('test2','02 March 2003','temperature','53');
INSERT INTO cth VALUES('test2','02 March 2003','test_result','FAIL');
INSERT INTO cth VALUES('test2','02 March 2003','test_startdate','01 March 2003');
INSERT INTO cth VALUES('test2','02 March 2003','volts','3.1234');
SELECT * FROM crosstab
(
  'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
  'SELECT DISTINCT attribute FROM cth ORDER BY 1'
)
AS
(
       rowid text,
       rowdt timestamp,
       temperature int4,
       test_result text,
       test_startdate timestamp,
       volts float8
);
 rowid |          rowdt           | temperature | test_result |      test_startdate      | volts
-------+--------------------------+-------------+-------------+--------------------------+--------
 test1 | Sat Mar 01 00:00:00 2003 |          42 | PASS        |                          | 2.6987
 test2 | Sun Mar 02 00:00:00 2003 |          53 | FAIL        | Sat Mar 01 00:00:00 2003 | 3.1234
(2 rows)

你可以创建预定义的函数来避免在每个查询中都必须写出结果列的名称和类型。请参考前一节中的例子。 用于这种形式的crosstab的底层 C 函数被命名为crosstab_hash。

connectby

connectby(text relname, text keyid_fld, text parent_keyid_fld
          [, text orderby_fld ], text start_with, int max_depth
          [, text branch_delim ])

connectby函数产生存储在一个表中的层次数据的显示。该表必须具有一个用以 唯一标识行的键域，以及一个父亲键域用来引用其父亲（如果有）。connectby能 显示从任意行开始向下的子树。

表 F.31)解释了参数。

表 F.31. connectby 参数

参数	描述
relname	源关系的名称
keyid_fld	键域的名称
parent_keyid_fld	父亲键域的名称
orderby_fld	用于排序兄弟的域的名称（可选）
start_with	起始行的键值
max_depth	要向下的最大深度，零表示无限深度
branch_delim	在分支输出中用于分隔键值的字符串（可选）

键域和父亲键域可以是任意数据类型，但是它们必须是同一类型。 注意*start_with*值必须作为一个文本串被输入，而不管键域的类型如何。

connectby函数被声明为返回setof record，因此输出列的实际名称和类型 就必须在调用的SELECT语句的FROM子句中被定义，例如：

SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0, '~')
    AS t(keyid text, parent_keyid text, level int, branch text, pos int);

前两个输出列被用于当前行的键和其父亲行的键，它们必须匹配该表的键域的类型。第三个输出行是该树中的深度， 并且必须是类型integer。如果给定了一个*branch_delim参数，下一个输出列 就是分支显示并且必须是类型text。最后，如果给出了一个orderby_fld*参数， 最后一个输出列是一个序号，并且必须是类型integer。

“branch”输出列显示了用于到达当前行的由键构成的路径。其中的键用指定的*branch_delim* 字符串分隔开。如果不需要分支显示，可以在输出列列表中忽略*branch_delim*参数和分支列。

如果同一父亲的子女之间的顺序很重要，可以包括*orderby_fld参数以指定用哪个域对兄弟排序。 这个域可以是任何可排序数据类型。当且仅当orderby_fld*被指定时，输出列列表必须包括一个 最终的整数序号列。

表示表和列名的参数会被原样复制到connectby内部生成的 SQL 查询中。 因此，如果名称是大小写混合或者包含特殊字符，应包括双引号。你也可能需要用模式限定表名。

在大型的表中，除非在父亲键域上有索引，否则性能会很差。

branch_delim字符串不出现在任何键值中是很重要的，否则connectby可能会错误地 报告一个无限递归错误。注意如果没有提供branch_delim，将用一个默认值~来进行递归检测。

这里是一个例子：

CREATE TABLE connectby_tree(keyid text, parent_keyid text, pos int);
INSERT INTO connectby_tree VALUES('row1',NULL, 0);
INSERT INTO connectby_tree VALUES('row2','row1', 0);
INSERT INTO connectby_tree VALUES('row3','row1', 0);
INSERT INTO connectby_tree VALUES('row4','row2', 1);
INSERT INTO connectby_tree VALUES('row5','row2', 0);
INSERT INTO connectby_tree VALUES('row6','row4', 0);
INSERT INTO connectby_tree VALUES('row7','row3', 0);
INSERT INTO connectby_tree VALUES('row8','row6', 0);
INSERT INTO connectby_tree VALUES('row9','row5', 0);
-- 带有分支，但没有 orderby_fld （不保证结果的顺序）
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0, '~')
 AS t(keyid text, parent_keyid text, level int, branch text);
 keyid | parent_keyid | level |       branch
-------+--------------+-------+---------------------
 row2  |              |     0 | row2
 row4  | row2         |     1 | row2~row4
 row6  | row4         |     2 | row2~row4~row6
 row8  | row6         |     3 | row2~row4~row6~row8
 row5  | row2         |     1 | row2~row5
 row9  | row5         |     2 | row2~row5~row9
(6 rows)
-- 没有分支，也没有 orderby_fld （不保证结果的顺序）
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0)
 AS t(keyid text, parent_keyid text, level int);
 keyid | parent_keyid | level
-------+--------------+-------
 row2  |              |     0
 row4  | row2         |     1
 row6  | row4         |     2
 row8  | row6         |     3
 row5  | row2         |     1
 row9  | row5         |     2
(6 rows)
-- 有分支，有 orderby_fld （注意 row5 在 row4 前面）
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0, '~')
 AS t(keyid text, parent_keyid text, level int, branch text, pos int);
 keyid | parent_keyid | level |       branch        | pos
-------+--------------+-------+---------------------+-----
 row2  |              |     0 | row2                |   1
 row5  | row2         |     1 | row2~row5           |   2
 row9  | row5         |     2 | row2~row5~row9      |   3
 row4  | row2         |     1 | row2~row4           |   4
 row6  | row4         |     2 | row2~row4~row6      |   5
 row8  | row6         |     3 | row2~row4~row6~row8 |   6
(6 rows)
-- 没有分支，有 orderby_fld （注意 row5 在 row4 前面）
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0)
 AS t(keyid text, parent_keyid text, level int, pos int);
 keyid | parent_keyid | level | pos
-------+--------------+-------+-----
 row2  |              |     0 |   1
 row5  | row2         |     1 |   2
 row9  | row5         |     2 |   3
 row4  | row2         |     1 |   4
 row6  | row4         |     2 |   5
 row8  | row6         |     3 |   6
(6 rows)

tcn

tcn模块提供一个触发器函数，它通知监听者有关它所附着的任意表上的改变。它必须被用作一个行级``AFTER触发器。

在一个CREATE TRIGGER语句中只可以为该函数提供一个参数，并且是可选的。如果提供该参数，它将被作为用于通知的频道名。如果忽略它，频道名将使用tcn。

通知的负载由表名、一个指示所执行操作类型的字母以及用于主键列的列名/值对构成。每一部分都用逗号与下一部分隔开。为了便于解析对正则表达式的使用，表和列名总是被包裹在双引号内，并且数据值总是被包裹在单引号内。嵌入的引号都被双写。

下面是使用该扩展的简单例子。

test=# create table tcndata
test-#   (
test(#     a int not null,
test(#     b date not null,
test(#     c text,
test(#     primary key (a, b)
test(#   );
CREATE TABLE
test=# create trigger tcndata_tcn_trigger
test-#   after insert or update or delete on tcndata
test-#   for each row execute function triggered_change_notification();
CREATE TRIGGER
test=# listen tcn;
LISTEN
test=# insert into tcndata values (1, date '2012-12-22', 'one'),
test-#                            (1, date '2012-12-23', 'another'),
test-#                            (2, date '2012-12-23', 'two');
INSERT 0 3
Asynchronous notification "tcn" with payload ""tcndata",I,"a"='1',"b"='2012-12-22'" received from server process with PID 22770.
Asynchronous notification "tcn" with payload ""tcndata",I,"a"='1',"b"='2012-12-23'" received from server process with PID 22770.
Asynchronous notification "tcn" with payload ""tcndata",I,"a"='2',"b"='2012-12-23'" received from server process with PID 22770.
test=# update tcndata set c = 'uno' where a = 1;
UPDATE 2
Asynchronous notification "tcn" with payload ""tcndata",U,"a"='1',"b"='2012-12-22'" received from server process with PID 22770.
Asynchronous notification "tcn" with payload ""tcndata",U,"a"='1',"b"='2012-12-23'" received from server process with PID 22770.
test=# delete from tcndata where a = 1 and b = date '2012-12-22';
DELETE 1
Asynchronous notification "tcn" with payload ""tcndata",D,"a"='1',"b"='2012-12-22'" received from server process with PID 22770.

test_decoding

test_decoding

test_decoding是一个逻辑解码输出插件的例子。它其实不做 任何特别有用的事情，但是可以作为开发你自己的输出插件的起点。

test_decoding通过逻辑解码机制接收 WAL 并且把它解码成 被执行的操作的文本表达形式。

这个插件的典型输出（在 SQL 逻辑解码接口上使用）可能是：

unvdbsvr=# SELECT * FROM pg_logical_slot_get_changes('test_slot', NULL, NULL, 'include-xids', '0');
   lsn     | xid |                       data
-----------+-----+--------------------------------------------------
 0/16D30F8 | 691 | BEGIN
 0/16D32A0 | 691 | table public.data: INSERT: id[int4]:2 data[text]:'arg'
 0/16D32A0 | 691 | table public.data: INSERT: id[int4]:3 data[text]:'demo'
 0/16D32A0 | 691 | COMMIT
 0/16D32D8 | 692 | BEGIN
 0/16D3398 | 692 | table public.data: DELETE: id[int4]:2
 0/16D3398 | 692 | table public.data: DELETE: id[int4]:3
 0/16D3398 | 692 | COMMIT
(8 rows)

tsm_system_rows

tsm_system_rows模块提供了表采样方法 SYSTEM_ROWS，它可以用在SELECT 命令的TABLESAMPLE子句中。

这种表采样方法接受一个整数参数，它是要读取的最大行数。得到的采样将总是包 含正好这么多行，除非该表中没有足够的行，在那种情况下整个表都会被选择出来。

和内建的SYSTEM采样方法一样， SYSTEM_ROWS执行块级别的采样，所以采样不是完全随机的， 而是服从于聚簇效果，特别是只要求少量行时。

SYSTEM_ROWS不支持 REPEATABLE子句。

示例

这里是一个用SYSTEM_ROWS选择一个表采样的例子。 首先安装扩展：

CREATE EXTENSION tsm_system_rows;

然后就可以在SELECT命令中使用它，例如：

SELECT * FROM my_table TABLESAMPLE SYSTEM_ROWS(100);

这个命令从表my_table中返回一个 100 行的采样（除非 该表没有 100 个可见行，那时将会返回其中所有的行）。

tsm_system_time

tsm_system_time模块提供了表采样方法 SYSTEM_TIME，它可以用在SELECT 命令的TABLESAMPLE子句中。

这种表采样方法接受一个浮点类型的参数，它是花费在读表上的最大毫秒数。 这可以让你直接控制查询进行多久，但付出的代价是很难预测采样的尺寸。 得到的采样将包含在指定时间内能读入的那么多行，除非首先已经读入了整个表。

和内建的SYSTEM采样方法一样， SYSTEM_TIME执行块级别的采样，所以采样不是完全随机的， 而是服从于聚簇效果，特别是只选择少量行时。

SYSTEM_TIME不支持 REPEATABLE子句。

示例

这里是一个用SYSTEM_TIME选择一个表采样的例子。 首先安装扩展：

CREATE EXTENSION tsm_system_time;

然后就可以在SELECT命令中使用它，例如：

SELECT * FROM my_table TABLESAMPLE SYSTEM_TIME(1000);

这个命令将返回在 1 秒（1000 毫秒）能读到的my_table 采样。当然，如果 1 秒内就能读完整个表，所有的行都将被返回。

unaccent

unaccent unaccent是一个文本搜索字典，它能从词位中移除重音（附加符号）。它是一个过滤词典，这表示它的输出总是会被传递给下一个字典（如果有），这和字典的通常行为不同。这允许为全文搜索做与重音无关的处理。

unaccent的当前实现不能被用作thesaurus字典的正规化字典。

配置

unaccent字典接受下列选项：

• RULES是包含翻译规则列表的文件的基本名。这个文件必须被存储在$SHAREDIR/tsearch_data/（这里$SHAREDIR表示UDB-TX安装的共享数据目录）中。它的名称必须以.rules（不包含在RULES参数中）结束。

规则文件具有下面的格式：

• 每一行表示一个由带有重音的字符和不带重音的字符构成的对。第一个字符将被翻译成第二个。例如：

À        A
Á        A
        A
à       A
Ä        A
Å        A
Æ        AE

• 两个字符必须由空格分隔，并且一行上的任何前导或尾随空白都将被忽略。

• 或者，如果一行只给出一个字符，则删除该字符的实例; 这在用单独的字符表示重音的语言中是有用的。

• 实际上，每个“字符”可以是不包含空格的任何字符串，因此， 除了去除变音符之外，unaccent字典也可以用于其他类型的字符串替换。

• 与其他文本搜索配置文件一样， 规则文件必须以UTF-8编码方式存储。加载时，数据将自动转换为当前数据库的编码。 任何含有不可翻译字符的行都将被忽略，因此规则文件可以包含当前编码中不适用的规则。

在unaccent.rules中可以找到一个更完整的例子，它可以直接用于大部分欧洲语言，当unaccent模块被安装时，它被安装在$SHAREDIR/tsearch_data/中。

用法

安装unaccent扩展会创建一个文本搜索模板unaccent和一个基于前者的字典unaccent。unaccent字典有默认的参数设置RULES='unaccent'，这会让该字典使用标准的unaccent.rules文件。如果希望修改该参数，可以

mydb=# ALTER TEXT SEARCH DICTIONARY unaccent (RULES='my_rules');

或者基于该模板创建新的字典。

要测试该字典，你可以尝试：

mydb=# select ts_lexize('unaccent','Hôtel');
 ts_lexize
-----------
 {Hotel}
(1 row)

这里是一个展示把unaccent字典插入到一个文本搜索配置的例子：

mydb=# CREATE TEXT SEARCH CONFIGURATION fr ( COPY = french );
mydb=# ALTER TEXT SEARCH CONFIGURATION fr
        ALTER MAPPING FOR hword, hword_part, word
        WITH unaccent, french_stem;
mydb=# select to_tsvector('fr','Hôtels de la Mer');
    to_tsvector
-------------------
 'hotel':1 'mer':4
(1 row)
mydb=# select to_tsvector('fr','Hôtel de la Mer') @@ to_tsquery('fr','Hotels');
 ?column?
----------
 t
(1 row)
mydb=# select ts_headline('fr','Hôtel de la Mer',to_tsquery('fr','Hotels'));
      ts_headline
------------------------
 <b>Hôtel</b> de la Mer
(1 row)

函数

unaccent()函数从一个给定的字符串中移除重音（附加符号）。基本上，它是unaccent字典的一个包装器，但是它能在普通的文本搜索环境之外使用。

unaccent([dictionary regdictionary, ] string text) returns text

如果省略*dictionary*参数，则使用名为unaccent并且与unaccent()函数有相同模式的文本搜索词典。

例如：

SELECT unaccent('unaccent', 'Hôtel');
SELECT unaccent('Hôtel');

uuid-ossp

uuid-ossp模块提供函数使用几种标准算法之一产生通用唯一标识符（UUID）。还提供产生某些特殊 UUID 常量的函数。

uuid-ossp 函数

表 F.32展示了可用来产生 UUID 的函数。相关标准 ITU-T Rec. X.667、ISO/IEC 9834-8:2005 以及 RFC 4122 指定了四种用于产生 UUID 的算法，分别用版本号 1、3、4、5 标识（没有版本 2 的算法）。这些算法中的每一个都适合于不同的应用集合。

用于 UUID 产生的函数

函数	描述
uuid_generate_v1()	这个函数产生一个版本 1 的 UUID。这涉及到计算机的 MAC 地址和一个时间戳。注意这种 UUID 会泄露产生该标识符的计算机标识以及产生的时间，因此它不适合某些对安全性很敏感的应用。
uuid_generate_v1mc()	这个函数产生一个版本 1 的 UUID，但是使用一个随机广播 MAC 地址而不是该计算机真实的 MAC 地址。
uuid_generate_v3(namespace uuid, name text)	这个函数使用指定的输入名称在给定的名字空间中产生一个版本 3 的 UUID。该名字空间应该是由uuid_ns_*()函数（如表 F.33所示）产生的特殊常量之一（理论上它可以是任意 UUID）。名称是选择的名字空间中的一个标识符。
uuid_generate_v4()	这个函数产生一个版本 4 的 UUID，它完全从随机数产生。
uuid_generate_v5(namespace uuid, name text)	这个函数产生一个版本 5 的 UUID，它和版本 3 的 UUID 相似，但是采用的是 SHA-1 作为哈希方法。版本 5 比版本 3 更好，因为 SHA-1 被认为比 MD5 更安全。

返回 UUID 常量的函数**

uuid_nil()	一个“nil” UUID 常量，它不作为一个真正的 UUID 发生。
uuid_ns_dns()	为 UUID 指定 DNS 名字空间的常量。
uuid_ns_url()	为 UUID 指定 URL 名字空间的常量。
uuid_ns_oid()	为 UUID 指定 ISO 对象标识符（OID） 名字空间的常量（这属于 ASN.1 OID，它与UDB-TX使用的 OID 无关）。
uuid_ns_x500()	为 UUID 指定 X.500 可识别名（DN）名字空间的常量。Constant designating the X.500 distinguished name (DN) namespace for UUIDs.

编译uuid-ossp

在历史上这个模块依赖于 OSSP UUID 库，这也是这个模块名称的由来。虽然 现在还能在http://www.ossp.org/pkg/lib/uuid/上 找到 OSSP UUID 库，但是它已经不再被维护并且越来越难以被一直到新的平台。 uuid-ossp现在在一些平台上可以脱离 OSSP 库被编译。在 FreeBSD、NetBSD 和一些其他源自 BSD 的平台上，在核心的libc 库中已经包括了合适的 UUID 创建函数。在 Linux、macOS和一些其他平台上， libuuid库中提供了合适的函数，它最初是来自于 e2fsprogs项目（不过在现代 Linux 上它被认为是 util-linux-ng的一部分）。在调用configure时， 指定--with-uuid=bsd可使用 BSD 的函数，指定 --with-uuid=e2fs会使用e2fsprogs的 libuuid，指定--with-uuid=ossp则会 使用 OSSP UUID 库。在一台特定的机器上可能会存在多种上述的库，因此 configure不会自动选择其中一个。

sr_plan

概述

sr_plan类似Oracle Outline系统。它可以用来锁定执行计划。如果您不信任规划器或能够制定更好的计划，那本插件是必需的。

sr_plan插件可以保存QUERY的执行计划，（支持绑定变量的QUERY），同时允许篡改执行计划，让篡改的执行计划生效。

针对每一条保存的执行计划，允许单独开启或关闭。 sr_plan实际上通过post_parse_analyze_hook获取parser后的text并保存到sr_plan的query字段中，通过planner_hook保存、处理、返回保存的执行计划。

验证安装

testdb=# create extension sr_plan;
CREATE EXTENSION

用法示例

在需要使用sr_plan的数据库中创建extension, 会创建保留执行计划的表

testdb=# create extension sr_plan;
CREATE EXTENSION
testdb=# \d sr_plans
Table "public.sr_plans"
    Column     |       Type        | Collation | Nullable | Default 
---------------+-------------------+-----------+----------+---------
 query_hash    | integer           |           | not null | 
 query_id      | bigint            |           | not null | 
 plan_hash     | integer           |           | not null | 
 enable        | boolean           |           | not null | 
 query         | character varying |           | not null | 
 plan          | text              |           | not null | 
 reloids       | oid[]             |           |          | 
 index_reloids | oid[]             |           |          | 
Indexes:
    "sr_plans_query_hash_idx" btree (query_hash)
    "sr_plans_query_index_oids" gin (index_reloids)
    "sr_plans_query_oids" gin (reloids)

创建测试表，分别插入100万条记录

create table a(id int, info text);  
create table b(id int, info text);  

insert into a select generate_series(1,1000000), 'test'||generate_series(1,1000000);  -- 插入100万数据  
insert into b select * from a;    -- 插入100万数据  
create index idx_a_info on a (info);  
create index idx_b_id on b(id);  

开启sr_plan.write_mode, 允许sr_plan收集SQL和执行计划

testdb=# set sr_plan.write_mode = true;  
SET 

查看QUERY 1的执行计划

testdb=# explain (analyze,verbose,timing,costs,buffers) select * from a left join (select id,count(info) from b group by id) b on (a.id=b.id) where a.info='test1';

QUERY PLAN      
                                                          
--------------------------------------------------------------------------------
----------------------------------------------------------
 Merge Left Join  (cost=8.88..58897.90 rows=1 width=26) (actual time=2.412..2.83
2 rows=1 loops=1)
   Output: a.id, a.info, b.id, (count(b.info))
   Inner Unique: true
   Merge Cond: (a.id = b.id)
   Buffers: shared hit=2 read=6
   ->  Sort  (cost=8.45..8.46 rows=1 width=14) (actual time=1.236..1.376 rows=1 
loops=1)
         Output: a.id, a.info
         Sort Key: a.id
         Sort Method: quicksort  Memory: 25kB
         Buffers: shared hit=1 read=3
         ->  Index Scan using idx_a_info on public.a  (cost=0.42..8.44 rows=1 wi
dth=14) (actual time=1.075..1.148 rows=1 loops=1)
               Output: a.id, a.info
               Index Cond: (a.info = 'test1'::text)
               Buffers: shared hit=1 read=3
   ->  GroupAggregate  (cost=0.42..46389.43 rows=1000000 width=12) (actual time=
0.986..1.091 rows=1 loops=1)
         Output: b.id, count(b.info)
         Group Key: b.id
         Buffers: shared hit=1 read=3
         ->  Index Scan using idx_b_id on public.b  (cost=0.42..31389.42 rows=10
00000 width=14) (actual time=0.695..0.770 rows=2 loops=1)
               Output: b.id, b.info
               Buffers: shared hit=1 read=3
 Planning:
   Buffers: shared hit=98 read=3 dirtied=15 written=6
 Planning Time: 9.499 ms
 Execution Time: 3.330 ms
(25 rows)

查看QUERY 2的执行计划

testdb=# explain (analyze,verbose,timing,costs,buffers) select * from a left join (select id,count(info) from b where exists (select 1 from a where a.id=b.id and a.info='test1')  -- 改写QUERY，得到同样结果，但是B的聚合量减少了  
group by id) b on (a.id=b.id) where a.info='test1';  

QUERY PLAN
                                                                       
--------------------------------------------------------------------------------
-----------------------------------------------------------------------
 Nested Loop Left Join  (cost=17.34..25.40 rows=1 width=26) (actual time=1.337..
1.961 rows=1 loops=1)
   Output: a.id, a.info, b.id, (count(b.info))
   Inner Unique: true
   Join Filter: (a.id = b.id)
   Buffers: shared hit=12
   ->  Index Scan using idx_a_info on public.a  (cost=0.42..8.44 rows=1 width=14
) (actual time=0.075..0.148 rows=1 loops=1)
         Output: a.id, a.info
         Index Cond: (a.info = 'test1'::text)
         Buffers: shared hit=4
   ->  GroupAggregate  (cost=16.92..16.94 rows=1 width=12) (actual time=1.144..1
.522 rows=1 loops=1)
         Output: b.id, count(b.info)
         Group Key: b.id
         Buffers: shared hit=8
         ->  Sort  (cost=16.92..16.92 rows=1 width=14) (actual time=0.988..1.333
 rows=1 loops=1)
               Output: b.id, b.info
               Sort Key: b.id
               Sort Method: quicksort  Memory: 25kB
               Buffers: shared hit=8
               ->  Nested Loop  (cost=8.87..16.91 rows=1 width=14) (actual time=
0.389..1.106 rows=1 loops=1)
                     Output: b.id, b.info
                     Buffers: shared hit=8
                     ->  HashAggregate  (cost=8.45..8.46 rows=1 width=4) (actual
 time=0.220..0.359 rows=1 loops=1)
                           Output: a_1.id
                           Group Key: a_1.id
                           Batches: 1  Memory Usage: 24kB
                           Buffers: shared hit=4
                           ->  Index Scan using idx_a_info on public.a a_1  (cos
t=0.42..8.44 rows=1 width=4) (actual time=0.063..0.135 rows=1 loops=1)
                                 Output: a_1.id, a_1.info
                                 Index Cond: (a_1.info = 'test1'::text)
                                 Buffers: shared hit=4
                     ->  Index Scan using idx_b_id on public.b  (cost=0.42..8.44
 rows=1 width=14) (actual time=0.055..0.424 rows=1 loops=1)
                           Output: b.id, b.info
                           Index Cond: (b.id = a_1.id)
                           Buffers: shared hit=4
 Planning:
   Buffers: shared hit=27 read=2
 Planning Time: 3.250 ms
 Execution Time: 2.244 ms
(38 rows)

执行以下QUERY后，QUERY的执行计划被保存到sr_plans中

testdb=# select * from a left join (select id,count(info) from b group by id) b on (a.id=b.id) where a.info='test1';  

 id | info  | id | count 
----+-------+----+-------
  1 | test1 |  1 |     1
(1 row)

testdb=# select * from a left join (select id,count(info) from b where exists (select 1 from a where a.id=b.id and a.info='test1')  -- 改写QUERY，得到同样结果，但是B的聚合量减少了  
group by id) b on (a.id=b.id) where a.info='test1'; 

 id | info  | id | count 
----+-------+----+-------
  1 | test1 |  1 |     1
(1 row)

禁止sr_plan收集SQL与执行计划

testdb=# set sr_plan.write_mode = false;  
SET

查看保存的执行计划

testdb=# select query_hash,query from sr_plans ;
 query_hash  |                                                                  
                 query                                                          
                          
-------------+------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------
  1208984763 | explain (analyze,verbose,timing,costs,buffers) select * from a le
ft join (select id,count(info) from b group by id) b on (a.id=b.id) where a.info
='test1';
 -2097954535 | explain (analyze,verbose,timing,costs,buffers) select * from a le
ft join (select id,count(info) from b where exists (select 1 from a where a.id=b
.id and a.info='test1')  +
             | group by id) b on (a.id=b.id) where a.info='test1';

替换（篡改）执行计划将query_hash=1208984763的执行计划替换为-2097954535的执行计划达到query rewrite的目的

testdb=# update sr_plans set plan=(select plan from sr_plans where query_hash=-2097954535) where query_hash=1208984763;
UPDATE 1

允许QUERY使用sr_plan保存的执行计划

testdb=# update sr_plans set enable=true where query_hash=1208984763;  
UPDATE 1

验证QUERY是否已使用sr_plan保存的执行计划

testdb=# \set VERBOSITY verbose
testdb=# explain select * from a left join (select id,count(info) from b group by id) b on (a.id=b.id) where a.info='test1';  

                                      QUERY PLAN                               
         
--------------------------------------------------------------------------------
---------
 Merge Left Join  (cost=8.88..58897.90 rows=1 width=26)
   Merge Cond: (a.id = b.id)
   ->  Sort  (cost=8.45..8.46 rows=1 width=14)
         Sort Key: a.id
         ->  Index Scan using idx_a_info on a  (cost=0.42..8.44 rows=1 width=14)
               Index Cond: (info = 'test1'::text)
   ->  GroupAggregate  (cost=0.42..46389.43 rows=1000000 width=12)
         Group Key: b.id
         ->  Index Scan using idx_b_id on b  (cost=0.42..31389.42 rows=1000000 w
idth=14)
(9 rows)

testdb=# select * from a left join (select id,count(info) from b group by id) b on (a.id=b.id) where a.info='test1';

 id | info  | id | count 
----+-------+----+-------
  1 | test1 |  1 |     1
(1 row)

system_stats

概述

system_stats 提供了数据库服务器 CPU、内存、磁盘和网络相关的统计指标，可以用于替代操作系统级别的监控。

验证安装

testdb=# create extension system_stats;
CREATE EXTENSION

函数及用法示例

ud_sys_os_info() 函数

ud_sys_os_info() 函数可以用于获取操作系统相关的信息。例如：

testdb=# \x
Expanded display is on.

testdb=# select * from ud_sys_os_info();
-[ RECORD 1 ]-------+--------------------------
name                | "Ubuntu 20.04.6 LTS"     +
                    | 
version             | Linux 5.15.0-79-generic
host_name           | virtual-machine
domain_name         | (none)
handle_count        | 7520
process_count       | 301
thread_count        | 521
architecture        | x86_64
last_bootup_time    | 
os_up_since_seconds | 4963

ud_sys_cpu_info() 函数

ud_sys_cpu_info() 函数用于获取 CPU 相关的信息。例如：

testdb=# select * from ud_sys_cpu_info();
-[ RECORD 1 ]------+--------------------------------------
vendor             | GenuineIntel
description        | GenuineIntel model 183 family 6
model_name         | 13th Gen Intel(R) Core(TM) i9-13900HX
processor_type     | 
logical_processor  | 0
physical_processor | 4
no_of_cores        | 2
architecture       | x86_64
clock_speed_hz     | 2419107840
cpu_type           | 
cpu_family         | 
byte_order         | 
l1dcache_size      | 48
l1icache_size      | 32
l2cache_size       | 2048
l3cache_size       | 36864

ud_sys_cpu_usage_info() 函数

ud_sys_cpu_usage_info() 函数用于获取 CPU 使用信息，按照 CPU 占用时间百分比显示。例如：

testdb=# select * from ud_sys_cpu_usage_info();
-[ RECORD 1 ]-------------------+-----
usermode_normal_process_percent | 6.67
usermode_niced_process_percent  | 0
kernelmode_process_percent      | 1.67
idle_mode_percent               | 90
io_completion_percent           | 0
servicing_irq_percent           | 0
servicing_softirq_percent       | 1.67
user_time_percent               | 
processor_time_percent          | 
privileged_time_percent         | 
interrupt_time_percent          | 

ud_sys_memory_info() 函数

ud_sys_memory_info() 函数用于获取内存使用信息，单位为字节。例如：

testdb=# select * from ud_sys_memory_info();
-[ RECORD 1 ]----+-----------
total_memory     | 4064788480
used_memory      | 2357620736
free_memory      | 1707167744
swap_total       | 968105984
swap_used        | 0
swap_free        | 968105984
cache_total      | 1122975744
kernel_total     | 
kernel_paged     | 
kernel_non_paged | 
total_page_file  | 
avail_page_file  | 

ud_sys_io_analysis_info() 函数

ud_sys_io_analysis_info() 函数用于获取块设备的 I/O 性能分析。例如：

testdb=# select * from ud_sys_io_analysis_info();
-[ RECORD 1 ]-+----------
device_name   | sdb
total_reads   | 4673
total_writes  | 11215
read_bytes    | 593527808
write_bytes   | 258909184
read_time_ms  | 110966
write_time_ms | 368935
-[ RECORD 2 ]-+-----------
device_name   | sdb1
total_reads   | 4642
total_writes  | 5556
read_bytes    | 591815680
write_bytes   | 258909184
read_time_ms  | 110804
write_time_ms | 314021
-[ RECORD 3 ]-+-----------
device_name   | sda
total_reads   | 18147
total_writes  | 21426
read_bytes    | 1220719104
write_bytes   | 712343040
read_time_ms  | 378269
write_time_ms | 1152711

ud_sys_disk_info() 函数

ud_sys_disk_info() 函数用于获取磁盘信息。例如：

testdb=# select * from ud_sys_disk_info();
LOCATION:  ReadDiskInformation, disk_info.c:146
-[ RECORD 1 ]----+------------
mount_point      | /
file_system      | /dev/sda5
drive_letter     | 
drive_type       | 
file_system_type | ext4
total_space      | 20425850880
used_space       | 12119777280
free_space       | 7242555392
total_inodes     | 1277952
used_inodes      | 247978
free_inodes      | 1029974
-[ RECORD 2 ]----+------------
mount_point      | /boot/efi
file_system      | /dev/sda1
drive_letter     | 
drive_type       | 
file_system_type | vfat
total_space      | 535805952
used_space       | 4096
free_space       | 535801856
total_inodes     | 0
used_inodes      | 0
free_inodes      | 0

ud_sys_load_avg_info() 函数

ud_sys_load_avg_info() 函数用于获取系统在 1、5、10 和 15 分钟内的平均负载。例如：

testdb=# select * from ud_sys_load_avg_info();
-[ RECORD 1 ]------------+-----
load_avg_one_minute      | 0.16
load_avg_five_minutes    | 0.08
load_avg_ten_minutes     | 0.06
load_avg_fifteen_minutes |  

ud_sys_process_info() 函数

ud_sys_process_info() 函数用于获取进程数量信息。例如：

testdb=# select * from ud_sys_process_info();
-[ RECORD 1 ]------+----
total_processes    | 301
running_processes  | 4
sleeping_processes | 216
stopped_processes  | 0
zombie_processes   | 0

ud_sys_network_info() 函数

ud_sys_network_info() 函数用于获取网络接口信息。例如：

testdb=# select * from ud_sys_network_info();
-[ RECORD 1 ]---+----------------
interface_name  | lo
ip_address      | 127.0.0.1
tx_bytes        | 843402
tx_packets      | 4565
tx_errors       | 0
tx_dropped      | 0
rx_bytes        | 843402
rx_packets      | 4565
rx_errors       | 0
rx_dropped      | 0
link_speed_mbps | 0
-[ RECORD 2 ]---+----------------
interface_name  | ens33
ip_address      | 192.168.109.128
tx_bytes        | 51167
tx_packets      | 555
tx_errors       | 0
tx_dropped      | 0
rx_bytes        | 494722
rx_packets      | 1698
rx_errors       | 0
rx_dropped      | 0
link_speed_mbps | 1000

ud_sys_cpu_memory_by_process() 函数

ud_sys_cpu_memory_by_process() 函数用于获取每个进程的 CPU 和内存使用信息，例如：

testdb=# select * from ud_sys_cpu_memory_by_process();
-[ RECORD 1 ]---------+--------------------------------
pid                   | 1
name                  | (systemd)
running_since_seconds | 6011
cpu_usage             | 0
memory_usage          | 0.29
memory_bytes          | 11624448
-[ RECORD 2 ]---------+--------------------------------
pid                   | 2
name                  | (kthreadd)
running_since_seconds | 6011
cpu_usage             | 0
memory_usage          | 0
memory_bytes          | 0
-[ RECORD 3 ]---------+--------------------------------
pid                   | 3
name                  | (rcu_gp)
running_since_seconds | 6011
cpu_usage             | 0
memory_usage          | 0
memory_bytes          | 0
...

ud_hint_plan

概述

UnvDB使用基于代价的优化器，该优化器的代价计算利用的是数据统计信息，而不是静态规则。对于一条SQL语句，优化器会估计所有可能的执行计划的代价，然后最终选择代价最低的执行计划。由于优化器不会考虑列之间的相关性，因此，最终选出来的执行计划可能并不是完美的。

ud_hint_plan允许我们在sql语句中通过特殊格式的提示来调整执行计划，达到优化执行计划的目的。当判断数据库优化器生成的执行计划不是最优的时候，可以在不修改SQL的情况下（例如：等价改写、非等价改写）通过添加Hint，来影响执行计划的生成。

验证安装

testdb=# create extension ud_hint_plan;
CREATE EXTENSION

使用方法

基本使用

ud_hint_plan在目标SQL语句中读取特殊格式的提示短语。提示短语以字符~/+~开始，~/~结尾。提示短语由提示名和参数（用括号包起来，两个参数之间用空格分隔）组成。为了增加可读性，每一个提示短语可另起一行。例如：

testdb=# explain (analyze,verbose,buffers) select /*+NestLoop(t2 t1)*/* from tenk1 t1, tenk2 t2 where t1.id = t2.id and t1.name = '一';

在上面的示例中，NestLoop为新的连接方法

提示表

当查询语句无法编辑时，是很不方便的。面对这种情况，可以将提示放在特殊的表hint_plan.hints中，表结构如下所示：

testdb=# \d hint_plan.hints
                                     Table "hint_plan.hints"
      Column       |  Type   | Collation | Nullable |                   Default 
                  
-------------------+---------+-----------+----------+---------------------------
------------------
 id                | integer |           | not null | nextval('hint_plan.hints_i
d_seq'::regclass)
 norm_query_string | text    |           | not null | 
 application_name  | text    |           | not null | 
 hints             | text    |           | not null | 

列名	描述
id	提示行唯一标识符，该列按顺序自动填充，不需要我们维护
norm_query_string	与要提示的查询相匹配的模式，查询中的常量必须替换为?，注意别忘记空格
application_name	应用的名字，为空字符串的话，表示任何的会话都可以
hints	需要添加的提示短语，其中不包括注释标记
### 提示类型
根据对象类型以及如何影响计划提示短语可分为六类。扫描方法、连接方法、连接顺序、行号校正、并行查询和GUC设置。您可以在提示列表中看到每种类型的提示短语列表。

分类	格式	说明
扫描方法	SeqScan(table)	强制对指定表使用序列扫描。
	TidScan(table)	强制对指定表使用TID扫描。
	IndexScan(table[ index...])	强制对指定表使用索引扫描，可以指定某个索引。
	IndexOnlyScan(table[ index...])	强制对指定表仅使用索引扫描，可以指定某个索引。
	BitmapScan(table[ index...])	强制对指定表使用位图扫描，可以指定某个索引。
	IndexScanRegexp(table[ POSIX Regexp...])	强制对指定表使用索引扫描或仅索引扫描或位图扫描。使用正则匹配。
	IndexOnlyScanRegexp(table[ POSIX Regexp...])
	BitmapScanRegexp(table[ POSIX Regexp...])
	NoSeqScan(table)	对指定表禁止使用序列扫描。
	NoTidScan(table)	对指定表禁止使用TID扫描。
	NoIndexScan(table)	对指定表禁止使用索引扫描（包括仅索引扫描）。
	NoIndexOnlyScan(table)	对指定表禁止使用仅索引扫描。
	NoBitmapScan(table)	对指定表禁止使用位图扫描。
连接方法	NestLoop(table table[ table...])	强制对指定表使用嵌套循环连接。
	HashJoin(table table[ table...])	强制对指定表使用哈希连接。
	MergeJoin(table table[ table...])	强制对指定表使用合并连接。
	NoNestLoop(table table[ table...])	对指定表禁止使用嵌套循环连接。
	NoHashJoin(table table[ table...])	对指定表禁止使用哈希连接。
	NoMergeJoin(table table[ table...])	对指定表禁止使用合并连接
连接顺序	Leading(table table[ table...])	强制连接顺序。
	Leading(<join pair>)	强制连接顺序和方向，
行号校正	Rows(table table[ table...] correction)	纠正由指定表组成的联接结果的行号。可用的校正方法有绝对值（＃<n>），加法（+ <n>），减法（-<n>）和乘法（* <n>）。<n>是函数strtod（）可以读取的字符串。
并行查询配置	Parallel(table <# of workers> [soft|hard])	强制或禁止指定表并行查询。<worker#>是所需的并行工作进程数量，0表示禁止并行查询。第三个参数如果是soft（默认），表示仅更改max_parallel_workers_per_gather，且其他内容由计划器自主选择； 如果是hard，表示所有相关参数都会被强制指定。
GUC	Set(GUC-param value)	计划器运行时，设置GUC参数。

用法示例

执行原查找计划：

testdb=# set enable_hashjoin = on;
SET
testdb=# explain (analyze,verbose,buffers) select * from tenk1 t1, tenk2 t2 where t1.id = t2.id and t1.name = '一';
                                                         QUERY PLAN             
                                            
--------------------------------------------------------------------------------
--------------------------------------------
Hash Join  (cost=374.29..774.70 rows=1 width=32) (actual time=5.083..1379.096 r
ows=1 loops=1)
   Output: t1.id, t1.num, t1.name, t1.sex, t2.id, t2.num, t2.name, t2.sex
   Hash Cond: (t2.id = t1.id)
   Buffers: shared hit=226
   ->  Seq Scan on public.tenk2 t2  (cost=0.00..322.02 rows=20902 width=16) (act
ual time=0.093..678.793 rows=20902 loops=1)
         Output: t2.id, t2.num, t2.name, t2.sex
         Buffers: shared hit=113
   ->  Hash  (cost=374.28..374.28 rows=1 width=16) (actual time=4.340..4.443 row
s=1 loops=1)
         Output: t1.id, t1.num, t1.name, t1.sex
         Buckets: 1024  Batches: 1  Memory Usage: 9kB
         Buffers: shared hit=113
         ->  Seq Scan on public.tenk1 t1  (cost=0.00..374.28 rows=1 width=16) (a
ctual time=0.097..4.204 rows=1 loops=1)
               Output: t1.id, t1.num, t1.name, t1.sex
               Filter: ((t1.name)::text = '一'::text)
               Rows Removed by Filter: 20901
               Buffers: shared hit=113
 Query Identifier: -5240454774119237226
 Planning:
   Buffers: shared hit=19
 Planning Time: 0.775 ms
 Execution Time: 1379.314 ms
(21 rows)

Hint提示后的执行计划:

testdb=# explain (analyze,verbose,buffers) select /*+NestLoop(t2 t1)*/* from tenk1 t1, tenk2 t2 where t1.id = t2.id and t1.name = '一';
                                                         QUERY PLAN             
                                            
--------------------------------------------------------------------------------
--------------------------------------------
Nested Loop  (cost=0.00..957.57 rows=1 width=32) (actual time=0.307..1224.259 r
ows=1 loops=1)
   Output: t1.id, t1.num, t1.name, t1.sex, t2.id, t2.num, t2.name, t2.sex
   Join Filter: (t1.id = t2.id)
   Rows Removed by Join Filter: 20901
   Buffers: shared hit=226
   ->  Seq Scan on public.tenk1 t1  (cost=0.00..374.28 rows=1 width=16) (actual 
time=0.097..4.643 rows=1 loops=1)
         Output: t1.id, t1.num, t1.name, t1.sex
         Filter: ((t1.name)::text = '一'::text)
         Rows Removed by Filter: 20901
         Buffers: shared hit=113
   ->  Seq Scan on public.tenk2 t2  (cost=0.00..322.02 rows=20902 width=16) (act
ual time=0.089..599.928 rows=20902 loops=1)
         Output: t2.id, t2.num, t2.name, t2.sex
         Buffers: shared hit=113
 Query Identifier: -5240454774119237226
 Planning Time: 0.336 ms
 Execution Time: 1224.485 ms
(16 rows)

固定执行计划

由于生产环境中有些应用是不可以修改代码的，加Hint也不可以，ud_hint_plan支持固定执行计划，hint_plan.hints表前文已经介绍过，下面介绍使用方法：

插入数据

testdb=# insert into hint_plan.hints(norm_query_string,application_name,hints)
testdb=# values ('explain (analyze,verbose,buffers) select from tenk1 t1, tenk2 t2 where t1.id = t2.id and t1.name = ?;','','NestLoop(t2 t1)');
INSERT 0 1
testdb=# select * from hint_plan.hints;
 id |                                           norm_query_string               
                            | application_name |      hints      
----+---------------------------------------------------------------------------
----------------------------+------------------+-----------------
  1 | explain (analyze,verbose,buffers) select from tenk1 t1, tenk2 t2 where t1.
id = t2.id and t1.name = ?; |                  | NestLoop(t2 t1)
(1 row)

开启参数ud_hint_plan.enable_hint_table

testdb=# show ud_hint_plan.enable_hint_table;
ud_hint_plan.enable_hint_table 
--------------------------------
 off
(1 row)

set ud_hint_plan.enable_hint_table=on;  --当前会话生效：
SET

testdb=# set ud_hint_plan.enable_hint_table=on; --永久生效，修改完此参数记得重启数据库
SET
testdb=# alter system  set ud_hint_plan.enable_hint_table=on;
ALTER SYSTEM

ud_hint_plan参数介绍

| 参数名 | 描述 |
|———-|———-| |ud_hint_plan.enable_hint |True为启动,默认为on|
|ud_hint_plan.enable_hint_table|True为可以在表上加Hint,默认为off|
|ud_hint_plan.parse_messages|指定提示解析错误的日志级别。有效值为.error ，warning ，notice ，info ，log ，debug|
|ud_hint_plan.debug_print|控制调试打印和详细信息。有效的值是off ，on ，detailed ，verbose,默认为off|
|ud_hint_plan.message_level|指定调试打印的消息级别。有效值为error ，warning ，notice ，info ，log ，debug|

查看计划是否固定

testdb=# explain (analyze,verbose,buffers) select from tenk1 t1, tenk2 t2 where t1.id = t2.id and t1.name = '一';
                                                        QUERY PLAN              
                                           
--------------------------------------------------------------------------------
-------------------------------------------
Nested Loop  (cost=0.00..957.57 rows=1 width=0) (actual time=0.303..1310.722 ro
ws=1 loops=1)
   Join Filter: (t1.id = t2.id)
   Rows Removed by Join Filter: 20901
   Buffers: shared hit=226
   ->  Seq Scan on public.tenk1 t1  (cost=0.00..374.28 rows=1 width=4) (actual t
ime=0.095..4.966 rows=1 loops=1)
         Output: t1.id, t1.num, t1.name, t1.sex
         Filter: ((t1.name)::text = '一'::text)
         Rows Removed by Filter: 20901
         Buffers: shared hit=113
   ->  Seq Scan on public.tenk2 t2  (cost=0.00..322.02 rows=20902 width=4) (actu
al time=0.090..644.165 rows=20902 loops=1)
         Output: t2.id, t2.num, t2.name, t2.sex
         Buffers: shared hit=113
 Query Identifier: -7891239385585250035
 Planning Time: 0.346 ms
 Execution Time: 1310.943 ms
(15 rows)

ud_bulkload

概述

ud_bulkload是一种用于UnvDB的高速数据加载工具，相比copy命令。最大的优势就是速度,我们可以跳过shared buffer,wal buffer。直接写文件。ud_bulkload的direct模式就是这种思路来实现的，它还包含了数据恢复功能，即导入失败的话，需要恢复。 ud_bulkload 旨在将大量数据加载到数据库中。您可以选择是否检查数据库约束以及在加载期间忽略多少错误。例如，当您将数据从另一个数据库复制到UnvDB时，您可以跳过性能完整性检查。另一方面，您可以在加载不干净的数据时启用约束检查。

验证安装

testdb=# create extension ud_bulkload;
CREATE EXTENSION

ud_bulkload 参数

下面介绍ud_bulkload主要的参数选项，主要有一下选项：

$ ud_bulkload --help

ud_bulkload is a bulk data loading tool for UNVDB

Usage:
  Dataload: ud_bulkload [dataload options] control_file_path
  Recovery: ud_bulkload -r [-D DATADIR]

Dataload options:
  -i, --input=INPUT         INPUT path or function
  -O, --output=OUTPUT       OUTPUT path or table
  -l, --logfile=LOGFILE     LOGFILE path
  -P, --parse-badfile=*     PARSE_BADFILE path
  -u, --duplicate-badfile=* DUPLICATE_BADFILE path
  -o, --option="key=val"    additional option

Recovery options:
  -r, --recovery            execute recovery
  -D, --uddata=DATADIR      database directory

Connection options:
  -d, --dbname=DBNAME       database to connect
  -h, --host=HOSTNAME       database server host or socket directory
  -p, --port=PORT           database server port
  -U, --username=USERNAME   user name to connect as
  -w, --no-password         never prompt for password
  -W, --password            force password prompt

Generic options:
  -e, --echo                echo queries
  -E, --elevel=LEVEL        set output message level
  --help                    show this help, then exit
  --version                 output version information, then exit

加载选项:

|选项|功能| |—-|—-| |-i INPUT –input=INPUT|数据源的位置，与控制文件里的”INPUT”类似| |-O OUTPUT –output=OUTPUT|数据的输出，比如数据库的某个表，与控制文件的”OUTPUT”类似| |-l LOGFILE –logfile=LOGFILE| 保存加载结果日志文件的路径，与控制文件的”LOGFILE”类似| |-o, –option=”key=val” |其他可用的选项，比如“TYPE=CSV”,”WRITER=PARALLEL”,可以指定多个选项|

连接选项

|选项|功能| |—-|—-| |-d, –dbname=DBNAME|指定需要连接的数据库，如果没有没有指定，默认使用环境变量，如果没有设定，默认使用用户名作为数据库名| |-h, –host=HOSTNAME|指定需要连接的主机地址| |-p, –port=PORT| 指定端口，默认端口是5678|

上面这三个参数通常都可以从环境变量得到，建议设置环境变量，因为ud_bulkload命令内部使用libpq，libpq需要这些环境变量。

通用选项

|选项|功能| |—-|—-| |–help |显示帮助信息| |–version|显示版本号|

控制文件

除了在命令行上指定参数外，还可以在控制文件中指定参数，下面介绍控制文件里的参数。 |选项|功能| |—-|—-| |TYPE = CSV | BINARY | FIXED | FUNCTION |CSV : 从 CSV 格式的文本文件加载 ， 默认为CSV。 BINARY | FIXED：从固定的二进制文件加载。 FUNCTION ：从函数的结果集中加载。如果使用它，INPUT 必须是调用函数的表达式。|
| WRITER | LOADER = DIRECT | BUFFERED | BINARY | PARALLEL |DIRECT ：将数据直接加载到表中。绕过共享缓冲区并跳过 WAL 日志记录，但需要自己的恢复过程。这是默认的，也是原始旧版本的模式。BUFFERED：通过共享缓冲区将数据加载到表中。使用共享缓冲区，写入WAL，并使用原始 WAL 恢复。BINARY ：将数据转换为二进制文件，该文件可用作要从中加载的输入文件。创建加载输出二进制文件所需的控制文件样本。此示例文件创建在与二进制文件相同的目录中，其名称为 .ctl。PARALLEL：与“WRITER=DIRECT”和“MULTI_PROCESS=YES”相同。如果指定了 PARALLEL，则忽略MULTI_PROCESS。如果为要加载的数据库配置了密码验证，则必须设置密码文件。| |TRUNCATE = YES | NO|如果YES，则使用 TRUNCATE 命令从目标表中删除所有行。如果NO，什么也不做。默认为NO。您不能同时指定“WRITER=BINARY”和 TRUNCATE。| |CHECK_CONSTRAINTS = YES | NO|指定在加载期间是否检查 CHECK 约束。默认为否。您不能同时指定“WRITER=BINARY”和 CHECK_CONSTRAINTS。| |PARSE_ERRORS = n|在解析、编码检查、编码转换、FILTER 函数、CHECK 约束检查、NOT NULL 检查或数据类型转换期间引发错误的 ingored 元组的数量。无效的输入元组不会加载并记录在 PARSE BADFILE 中。默认值为 0。如果解析错误数等于或多于该值，则提交已加载的数据并且不加载剩余的元组。0 表示不允许错误，-1 和 INFINITE 表示忽略所有错误。| |INPUT|INFILE=path|stdin|function_name|数据源，必须指定，类型不同，它的值也不一样：文件：如果是文件，此处就是路径，可以是相对路径，服务器必须有读文件的权限。stdin：ud_bulkload将从标准输入读取数据。SQL FUNCTION：指定SQL函数，用这个函数返回插入数据，可以是内建的函数，也可以是用户自定义的函数| |LOGFILE=path|日志文件的路径 ，执行过程中会记录状态。| |MULTI_PROCESS=YES|NO| 若设置了此值，会开启多线程模式，并行处理数据导入。若没设置，单线程模式，默认模式是单线程模式。| |VERBOSE=YES|NO|若设置了YES，坏的元组将写入服务器日志，默认是NO。| |SKIP|OFFSET=n|跳过的行数，默认是0，不能跟”TYPE=FUNCTION”同时设置。| |LIMIT|LOAD=n|限制加载的行数，默认是INFINITE，即加载所有数据，这个选项可以与”TYPE=FUNCTION”同时设置。 | |ENCODING = encoding|指定输入数据的编码。检查指定的编码是否有效，如果需要，将输入数据转换为数据库编码。默认情况下，输入数据的编码既不验证也不转换。| |FILTER = [ schema_name. ] function_name [ (argtype, … ) ]|指定过滤函数以转换输入文件中的每一行。只要函数名在数据库中是唯一的，就可以省略 argtype 的定义。如果未指定，则直接将输入数据解析为加载目标表。另请参阅如何编写 FILTER 函数以生成 FILTER 函数。不能同时指定“TYPE=FUNCTION”和 FILTER。此外，CSV 选项中的 FORCE_NOT_NULL 不能与 FILTER 选项一起使用。|

使用方法

初始化数据

testdb=# create table tb_asher (id int,name text);
CREATE TABLE
testdb=# \d
         List of relations
 Schema |   Name   | Type  | Owner 
--------+----------+-------+-------
 public | tb_asher | table | unvdb
(1 row)

testdb=# create extension ud_bulkload; #如果连接指定到单个库时，需要创建扩展以生成 udbulkload.ud_bulkload() 函数
CREATE EXTENSION
testdb=# quit

模拟CSV 文件

$ seq 100000| awk '{print $0"|asher"}' > bulk_asher.txt 
$ more bulk_asher.txt
1|asher
2|asher
3|asher
4|asher
5|asher
...

加载到指定表

将bulk_asher.txt里的数据加载到testdb 库下的 tb_asher表中

$ ud_bulkload -i /home/lihaozhan/bulk/bulk_asher.txt -O tb_asher -l  /home/lihaozhan/bulk/tb_asher_output.log -P /home/lihaozhan/bulk/tb_asher_bad.txt -o "TYPE=CSV" -o "DELIMITER=|" -d testdb -U unvdb -h 127.0.0.1

NOTICE: BULK LOAD START
NOTICE: BULK LOAD END
	0 Rows skipped.
	100000 Rows successfully loaded.
	0 Rows not loaded due to parse errors.
	0 Rows not loaded due to duplicate errors.
	0 Rows replaced with new rows.

查看导入日志

$ cat /home/lihaozhan/bulk/tb_asher_output.log

ud_bulkload 3.1.20 on 2023-09-20 17:53:03.296287+08

INPUT = /home/lihaozhan/bulk/bulk_asher.txt
PARSE_BADFILE = /home/lihaozhan/bulk/tb_asher_bad.txt
LOGFILE = /home/lihaozhan/bulk/tb_asher_output.log
LIMIT = INFINITE
PARSE_ERRORS = 0
CHECK_CONSTRAINTS = NO
TYPE = CSV
SKIP = 0
DELIMITER = |
QUOTE = "\""
ESCAPE = "\""
NULL = 
OUTPUT = public.tb_asher
MULTI_PROCESS = NO
VERBOSE = NO
WRITER = DIRECT
DUPLICATE_BADFILE = /home/lihaozhan/data/soft/unvdb-data/ud_bulkload/20230920175303_testdb_public_tb_asher.dup.csv
DUPLICATE_ERRORS = 0
ON_DUPLICATE_KEEP = NEW
TRUNCATE = NO


  0 Rows skipped.
  100000 Rows successfully loaded.
  0 Rows not loaded due to parse errors.
  0 Rows not loaded due to duplicate errors.
  0 Rows replaced with new rows.

Run began on 2023-09-20 17:53:03.296287+08
Run ended on 2023-09-20 17:53:03.394545+08

CPU 0.02s/0.05u sec elapsed 0.10 sec

先清空在加载

增加了 -o “TRUNCATE=YES” 参数

$ ud_bulkload -i /home/lihaozhan/bulk/bulk_asher.txt -O tb_asher -l /home/lihaozhan/bulk/tb_asher_output.log -P /home/lihaozhan/bulk/tb_asher_bad.txt -o "TYPE=CSV" -o "DELIMITER=|" -o "TRUNCATE=YES" -d testdb -U unvdb -h 127.0.0.1

NOTICE: BULK LOAD START
NOTICE: BULK LOAD END
	0 Rows skipped.
	100000 Rows successfully loaded.
	0 Rows not loaded due to parse errors.
	0 Rows not loaded due to duplicate errors.
	0 Rows replaced with new rows.

数据查询

$ ud_sql -h 127.0.0.1 -d testdb -c "select count(1) from tb_asher;"

 count  
--------
 100000
(1 row)

使用控制文件

新建控制文件asher.ctl ，可以根据之前加载时，产生的日志文件tb_asher_output.log来更改，去掉里面没有值的参数 NULL=

vi asher.ctl 

INPUT = /home/lihaozhan/bulk/bulk_asher.txt
PARSE_BADFILE = /home/lihaozhan/bulk/tb_asher_bad.txt
LOGFILE = /home/lihaozhan/bulk/tb_asher_output.log
LIMIT = INFINITE
PARSE_ERRORS = 0
CHECK_CONSTRAINTS = NO
TYPE = CSV
SKIP = 0
DELIMITER = |
QUOTE = "\""
ESCAPE = "\""
OUTPUT = public.tb_asher
MULTI_PROCESS = NO
VERBOSE = NO
WRITER = DIRECT
DUPLICATE_BADFILE = /home/lihaozhan/data/soft/unvdb-data/ud_bulkload/20230921101752_testdb_public_tb_asher.dup.csv
DUPLICATE_ERRORS = 0
ON_DUPLICATE_KEEP = NEW
TRUNCATE = YES

使用控制文件来加载

$ ud_bulkload /home/lihaozhan/bulk/asher.ctl -d testdb -U unvdb -h 127.0.0.1

NOTICE: BULK LOAD START
NOTICE: BULK LOAD END
	0 Rows skipped.
	100000 Rows successfully loaded.
	0 Rows not loaded due to parse errors.
	0 Rows not loaded due to duplicate errors.
	0 Rows replaced with new rows.

数据查询

$ ud_sql -h 127.0.0.1 -d testdb -c "select count(1) from tb_asher;"

 count  
--------
 100000
(1 row)

强制写wal日志

ud_bulkload 默认是跳过buffer直接写文件 ，但时如果有复制 ，或者需要基本wal日志恢复时没有wal日志是不行的，这是我们可以强制让其写wal日志 ，只需要加载 -o “WRITER=BUFFERED” 参数就可以了

$ ud_bulkload -i /home/lihaozhan/bulk/bulk_asher.txt -O tb_asher -l /home/lihaozhan/bulk/tb_asher_output.log -P /home/lihaozhan/bulk/tb_asher_bad.txt -o "TYPE=CSV" -o "DELIMITER=|" -o "TRUNCATE=YES" -o "WRITER=BUFFERED" -d testdb -U unvdb -h 127.0.0.1

NOTICE: BULK LOAD START
NOTICE: BULK LOAD END
	0 Rows skipped.
	100000 Rows successfully loaded.
	0 Rows not loaded due to parse errors.
	0 Rows not loaded due to duplicate errors.
	0 Rows replaced with new rows.

其他

如果您使用直接加载模式（WRITER=DIRECT 或 PARALLEL），您必须注意以下事项：

1. PITR/Replication ：由于绕过了 WAL，PITR 的归档恢复不可用。这并不意味着它可以在没有加载表数据的情况下完成 PITR。 如果您想使用 PITR，请在通过 ud_bulkload 加载后对数据库进行完整备份。如果您使用流式复制，则需要根据 ud_bulkload 之后的备份集重新创建备用数据库。

2. 尽量不要使用 “ kill -9” 终止 ud_bulkload 命令。如果您这样做了，您必须调用 UnvDB 脚本来执行 ud_bulkload恢复并重新启动 UnvDB 以继续。

3. 默认情况下，在数据加载期间仅强制执行唯一约束和非空约束。您可以设置“CHECK_CONSTRAINTS=YES”来检查 CHECK 约束。无法检查外键约束。用户有责任提供有效的数据集。

4. maintenance_work_mem会影响 ud_bulkload的性能。如果将此参数从 64 MB 更改为 1 GB，则持续时间将缩短近 15%。