PostgreSQL 9.1 中的新功能
本文档展示了与上一个主要版本 PostgreSQL 9.0 相比，PostgreSQL 9.1 中的许多最新变更。此版本中有许多改进，因此本维基页面详细介绍了许多比较重要的变更。更改的完整列表会在 发行说明 中逐项列举。
新增的主要功能
同步复制及其他复制功能
9.1 中新增了许复制相关的许多新功能
- 在 9.0 中，用于复制的用户必须是超级用户。现在已不再需要，有一个新的“复制”特权。
CREATE ROLE replication_role REPLICATION LOGIN PASSWORD 'pwd_replication'
此角色随后可以添加到 pg_hba.conf 中以用于流复制。从安全角度来看，比使用超级用户角色执行此项任务更好。
我们现在有了集群并创建了复制用户，我们可以设置数据库以进行流复制。这是一个向pg_hba.conf中添加连接到虚拟复制数据库的权限的问题，用于设置wal_level，归档 (archive_mode, archive_command) 和 max_wal_senders，并在 9.0文档中进行了介绍。
当我们的数据库集群准备好进行流操作时，我们可以演示第二个新功能。
- pg_basebackup。
这个新工具可用于创建数据库的克隆或备份，只使用流复制功能。无需调用 pg_start_backup()，然后手动复制数据库并调用 pg_stop_backup()。pg_basebackup 在一个命令中完成所有操作。我们将克隆正在运行的数据库到 /tmp/newcluster
> pg_basebackup -D /tmp/newcluster -U replication -v Password: NOTICE: pg_stop_backup complete, all required WAL segments have been archived pg_basebackup: base backup completed
新数据库准备就绪：只需添加带 restore_command 的 recovery.conf 文件以检索存档的 WAL 文件并启动新集群。pg_basebackup 也可创建 tar 备份，或包含所有必需的 xlog 文件（以获取独立备份）。
由于我们准备演示带同步提交的流复制，我们将设置一个 recovery.conf 以连接到主数据库并流式传输更改。
我们将创建一个 recovery.conf，包含如下内容
restore_command = 'cp /tmp/%f %p' # e.g. 'cp /mnt/server/archivedir/%f %p' standby_mode = on primary_conninfo = 'host=localhost port=59121 user=replication password=replication application_name=newcluster' # e.g. 'host=localhost port=5432' trigger_file = '/tmp/trig_f_newcluster'
然后我们将启动新集群
pg_ctl -D /tmp/newcluster start LOG: database system was interrupted; last known up at 2011-05-22 17:15:45 CEST LOG: entering standby mode LOG: restored log file "00000001000000010000002F" from archive LOG: redo starts at 1/2F000020 LOG: consistent recovery state reached at 1/30000000 LOG: database system is ready to accept read only connections cp: cannot stat « /tmp/000000010000000100000030 »: No such file or directory LOG: streaming replication successfully connected to primary
OK，现在我们有了一个从主数据库流传输的从数据库，但我们仍为异步。请注意，我们在 recovery.conf 中将 application_name 设置在连接字符串中。
- 同步复制
为了获取同步，只需在主数据库的 postgresql.conf 中更改
synchronous_standby_names = 'newcluster'
这是从数据库中的 primary_conninfo 中的 application_name。只需执行一个 pg_ctl reload，此新参数将设置完毕。现在主数据库上的任何提交仅在从数据库在其自己的日志上写入提交内容并向主数据库确认后才会报告为主数据库上的提交内容。
警告：当事务应用到从数据库的日志中时，将被视为提交，而不是当在从数据库中可见时视为提交。这意味着当事务在主数据库上提交与在从数据库上可见之间仍然会存在延迟。这仍然是同步复制，因为如果主数据库崩溃，不会丢失任何数据。
同步复制的一个非常棒的功能是可按会话控制。如果某个会话不需要这种同步保证，则可以关闭参数 synchronous_commit（默认情况下为打开）。如果您在事务中不需要它，只需执行
SET synchronous_commit TO off
而且您将无需支付罚金。
PostgreSQL 9.1 中还有其他新的复制功能
- 从数据库现在可以要求主数据库不清理仍然需要的记录。
这是 9.0 中一个主要的设置问题：清理可能会破坏仍然需要用于在从数据库上运行查询的记录，从而触发复制冲突。然后从数据库必须做出选择：取消正在运行的查询，或同意延迟修改的应用并滞后。可以通过将 vacuum_defer_cleanup_age 设置为非零值来解决此问题，但很难为其获取一个正确的值。在备用数据库中，此新功能通过参数 hot_standey_feedback 启用。当然，从数据库上有极长时间运行的查询时，这意味着备库可以阻止 VACUUM 对主数据库执行正确的维护操作。
- pg_stat_replication 是一个新系统视图。
在 master 上显示所有 slave 的状态：收到多少 WAL，是否已连接，是否同步，回放了什么
=# SELECT * from pg_stat_replication ;
procpid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | state | sent_location | write_location | flush_location | replay_location | sync_priority | sync_state
---------+----------+-------------+------------------+-------------+-----------------+-------------+------------------------------+-----------+---------------+----------------+----------------+-----------------+---------------+------------
17135 | 16671 | replication | newcluster | 127.0.0.1 | | 43745 | 2011-05-22 18:13:04.19283+02 | streaming | 1/30008750 | 1/30008750 | 1/30008750 | 1/30008750 | 1 | sync
不必再查询 slave，就能知道它们相对于 master 的状态。
- pg_stat_database_conflicts 是另一个新的系统视图。
这在备用数据库中，会显示取消了多少个查询，以及取消的原因。
=# SELECT * from pg_stat_database_conflicts ;
datid | datname | confl_tablespace | confl_lock | confl_snapshot | confl_bufferpin | confl_deadlock
-------+-----------+------------------+------------+----------------+-----------------+----------------
1 | template1 | 0 | 0 | 0 | 0 | 0
11979 | template0 | 0 | 0 | 0 | 0 | 0
11987 | postgres | 0 | 0 | 0 | 0 | 0
16384 | marc | 0 | 0 | 1 | 0 | 0
- 现在可以在 slave 上轻松暂停复制。
只需调用 i>pg_xlog_replay_pause() 暂停，调用 pg_xlog_replay_resume() 恢复。这会冻结数据库，使其成为一个非常好的工具，可用于执行一致备份。
可以用 pg_is_xlog_replay_paused() 了解当前状态。
现在还可以在数据库恢复结束时暂停日志回放，而无需将数据库投入生产，这样可以让管理员有机会查询数据库。管理员可以在结束恢复前先检查达到的恢复点是否正确。此新参数是 recovery.conf 中的 pause_at_recovery_target。
- 现在可以创建还原点。
它们只是事务日志中的命名地址。
然后，可以通过指定 recovery_target_name 而不是 recovery.conf 文件中的 recovery_target_time 或 recovery_target_xid 来使用它们。
它们是通过调用 pg_create_restore_point() 创建的。
逐列整理规则
整理顺序在数据库中不再唯一。
比如，你想使用一个 UTF8 编码和 de_DE.utf8 整理规则（字母顺序）来使用一个 9.0 数据库，因为你的用户大多说德语。但是，如果你还想存储法语数据并对其进行排序，某些法语用户可能会失望
SELECT * from (values ('élève'),('élevé'),('élever'),('Élève')) as tmp order by column1;
column1
---------
élevé
élève
Élève
élever
不那么糟糕，但这并不是法语整理规则：在第一遍中，带重音符号（变音符号）的字符将被视为等于不带重音符号的字符。然后，在第二遍中，它们将被视为在不带重音符号的字符之后。只不过，在第二遍中，字母是从单词末尾考虑至单词开头。这有些奇怪，但这就是法语整理规则……
9.1 具有两项新功能
- 可以在查询时指定整理规则
SELECT * FROM (VALUES ('élève'),('élevé'),('élever'),('Élève')) AS tmp ORDER BY column1 COLLATE "fr_FR.utf8";
column1
---------
élève
Élève
élevé
élever
- 可以在表定义时指定整理规则
CREATE TABLE french_messages (message TEXT COLLATE "fr_FR.utf8");
INSERT INTO french_messages VALUES ('élève'),('élevé'),('élever'),('Élève');
SELECT * FROM french_messages ORDER BY message;
message
---------
élève
Élève
élevé
élever
当然，你可以在 message 列上创建一个索引，该索引可用于快速的法语排序。例如，使用一个带有更多数据且未定义整理规则的表
CREATE TABLE french_messages2 (message TEXT); -- no collation here
INSERT INTO french_messages2 SELECT * FROM french_messages, generate_series(1,100000); -- 400k rows
CREATE INDEX idx_french_ctype ON french_messages2 (message COLLATE "fr_FR.utf8");
EXPLAIN SELECT * FROM french_messages2 ORDER BY message;
QUERY PLAN
-------------------------------------------------------------------------------
Sort (cost=62134.28..63134.28 rows=400000 width=32)
Sort Key: message
-> Seq Scan on french_messages2 (cost=0.00..5770.00 rows=400000 width=32)
EXPLAIN SELECT * FROM french_messages2 ORDER BY message COLLATE "fr_FR.utf8";
QUERY PLAN
--------------------------------------------------------------------------------------------------
Index Scan using idx_french_ctype on french_messages2 (cost=0.00..17139.15 rows=400000 width=8)
未记录表
它们可用于临时数据。往未记录表中写入数据的速度要快得多，但它不会在崩溃中幸存下来（如果发生崩溃，它会在数据库重启时被截断）。
它们没有 WAL 维护开销，因此往其中写入数据的速度要快得多。
这是一个（不切实际的）示例
# CREATE TABLE test (a int); CREATE TABLE # CREATE UNLOGGED table testu (a int); CREATE TABLE # CREATE INDEX idx_test on test (a); CREATE INDEX # CREATE INDEX idx_testu on testu (a ); CREATE INDEX =# \timing Timing is on. =# INSERT INTO test SELECT generate_series(1,1000000); INSERT 0 1000000 Time: 17601,201 ms =# INSERT INTO testu SELECT generate_series(1,1000000); INSERT 0 1000000 Time: 3439,982 ms
这些表非常适合缓存数据，或适合在发生崩溃时可以重建的任何内容。
扩展
此项和以下一项是另外一个需要同时介绍多个功能的机会。我们需要安装 pg_trgm，现在它是一个扩展。
让我们首先安装 pg_trgm。在 9.0 之前，我们必须手动运行一个脚本，命令看起来像这样
\i /usr/local/pgsql/share/contrib/pg_trgm.sql
这是一个真正的维护问题：创建的函数默认为 public 模式，在 pg_dump 文件中“按原样”转储，通常无法正确还原，因为它们依赖于外部二进制对象，或者在各版本之间可能更改定义。
使用 9.1，可以使用 CREATE EXTENSION 命令
CREATE EXTENSION [ IF NOT EXISTS ] extension_name
[ WITH ] [ SCHEMA schema ]
[ VERSION version ]
[ FROM old_version ]
最重要的选项当然就是 extension_name 和 schema：扩展可以存储在模式中。
因此，让我们为下一个示例安装 pg_trgm
=# CREATE schema extensions; CREATE SCHEMA
=# CREATE EXTENSION pg_trgm WITH SCHEMA extensions; CREATE EXTENSION
现在，pg_trgm 已安装在“extensions”模式中。它将正确的包含在数据库转储中，并使用 CREATE EXTENSION 语法。因此，如果扩展中发生任何更改，则此扩展将使用新定义进行还原。
人们可以在 psql 下获取扩展列表
\dx
List of installed extensions
Name | Version | Schema | Description
----------+---------+------------+-------------------------------------------------------------------
pg_trgm | 1.0 | extensions | text similarity measurement and index searching based on trigrams
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows)
K 近邻索引
如果“距离”有意义，并且可以为数据类型进行定义，则现在可以使用 GIST 索引来返回排序的行。目前，此项工作已针对 point 数据类型、pg_trgm contrib 和许多 btree_gist 数据类型完成。此功能可供所有数据类型使用，因此在不久的将来可能会更多。
目前，下面是 pg_trgm 的一个示例。pg_trgm 使用三元组比较字符串。以下是“hello”字符串的三元组
SELECT show_trgm('hello');
show_trgm
---------------------------------
{" h"," he",ell,hel,llo,"lo "}
三元组用于计算字符串间的相似度（介于 0 和 1 之间）。因此，存在一个距离的概念，距离定义为“1-相似度”。
下面是一个使用 pg_trgm 的示例。该表包含 500 万条文本记录，大小为 750MB。
CREATE TABLE test_trgm ( text_data text);
CREATE INDEX test_trgm_idx on test_trgm using gist (text_data extensions.gist_trgm_ops);
在 9.0 之前，如果我们想从表中获取最接近 hello 的 2 个 text_data，则查询如下
SELECT text_data, similarity(text_data, 'hello') FROM test_trgm WHERE text_data % 'hello' ORDER BY similarity(text_data, 'hello') LIMIT 2;
在测试数据库上，完成该操作大约需要 2 秒。
使用 9.1 和 KNN，可以编写
SELECT text_data, text_data <-> 'hello' FROM test_trgm ORDER BY text_data <-> 'hello' LIMIT 2;
<-> 运算符是距离运算符。它使用索引直接检索 2 条最佳记录，运行时间为 20ms。
当我们讨论 pg_trgm 时，另一个新特性是 LIKE 和 ILIKE 运算符现在可以自动使用 trgm 索引。仍然使用同一张表
SELECT text_data FROM test_trgm WHERE text_data like '%hello%';
使用 test_trgm_idx 索引（而不是扫描整个表）。
可序列化快照隔离
如果需要所有事务的行为与按顺序运行一样，同时不会牺牲太多吞吐量，那么此特性非常有用，因为目前其他“可序列化”隔离实现并非如此（这通常通过锁定访问的每条记录来完成）。
因为它很难正确演示，因此这里有一个此项特性的完整说明链接：https://wiki.postgresql.ac.cn/wiki/SSI
TODO：SSI 文档总是以提交结束。这可能会让读者感到困惑。
可写公用表表达式
这扩展了 8.4 中引入的 WITH 语法。现在，可以在查询的 WITH 部分中放置数据修改查询，并在后面使用返回的数据。
假设我们想归档与 test_trgm 表中的 %hello% 相匹配的所有记录
CREATE TABLE old_text_data (text_data text); WITH deleted AS (DELETE FROM test_trgm WHERE text_data like '%hello%' RETURNING text_data) INSERT INTO old_text_data SELECT * FROM deleted;
全部在一个查询中。
作为一个更加有挑战性的示例，下面的查询更新了一个 pgbench 数据库，删除了一大批错误的事务，并在一个语句中更新了所有相关的出纳员、分支机构和账户总额
WITH deleted_xtns AS (
DELETE FROM pgbench_history
WHERE bid = 4 and tid = 9
RETURNING *
),
deleted_per_account as (
SELECT aid, sum(delta) as baldiff
FROM deleted_xtns
GROUP BY 1
),
accounts_rebalanced as (
UPDATE pgbench_accounts
SET abalance = abalance - baldiff
FROM deleted_per_account
WHERE deleted_per_account.aid = pgbench_accounts.aid
RETURNING deleted_per_account.aid, pgbench_accounts.bid,
baldiff
),
branch_adjustment as (
SELECT bid, SUM(baldiff) as branchdiff
FROM accounts_rebalanced
GROUP BY bid
)
UPDATE pgbench_branches
SET bbalance = bbalance - branchdiff
FROM branch_adjustment
WHERE branch_adjustment.bid = pgbench_branches.bid
RETURNING branch_adjustment.bid,branchdiff,bbalance;
SE-Postgres
PostgreSQL is the only open source database which integrates with SE-Linux.
PGXN
PGXN 是 PostgreSQL 扩展网络，是一个集中分布 system 用于开源 PostgreSQL 扩展库。扩展作者可以 提交他们的工作 连同 描述它们的元数据：包及其文档被 编入索引 并分布在多个服务器上。借助 简单 API，可以通过 Web 界面或使用命令行客户端来使用此 system。
正在开发一个全面的 PGXN 客户端。可以使用以下命令进行安装
$ easy_install pgxnclient Searching for pgxnclient ... Best match: pgxnclient 0.2.1 Processing pgxnclient-0.2.1-py2.6.egg ... Installed pgxnclient-0.2.1-py2.6.egg
除了其他命令之外，它还允许在网站上搜索扩展
$ pgxn search pair
pair 0.1.3
... Usage There are two ways to construct key/value *pairs*: Via the
*pair*() function: % SELECT *pair*('foo', 'bar'); *pair* ------------
(foo,bar) Or by using the ~> operator: % SELECT 'foo' ~> 'bar';
*pair*...
semver 0.2.2
*pair* │ 0.1.0 │ Key/value *pair* data type Note that "0.35.0b1" is less
than "0.35.0", as required by the specification. Use ORDER BY to get
more of a feel for semantic version ordering rules: SELECT...
在 system 中构建并安装它们
$ pgxn install pair INFO: best version: pair 0.1.3 INFO: saving /tmp/tmpezwyEO/pair-0.1.3.zip INFO: unpacking: /tmp/tmpezwyEO/pair-0.1.3.zip INFO: building extension ... INFO: installing extension [sudo] password for piro: /bin/mkdir -p '/usr/local/pg91b1/share/postgresql/extension' ...
并将它们加载为数据库扩展
$ pgxn load -d mydb pair INFO: best version: pair 0.1.3 CREATE EXTENSION
SQL/MED
SQL/MED（外部数据管理）支持是从 8.4 开始的。但是，现在 PostgreSQL 可以定义外键表，这是 SQL/MED 的主要目标：访问外部数据。
请参阅 现有外部数据包装器扩展列表，其中包括 Oracle、MySQL、CouchDB、Redis、Twitter 等。
请参阅使用 file_fdw 扩展的一个示例。
我们将一个 CSV 文件映射到一个表。
CREATE EXTENSION file_fdw WITH SCHEMA extensions;
\dx+ file_fdw
Objects in extension "file_fdw"
Object Description
----------------------------------------------------
foreign-data wrapper file_fdw
function extensions.file_fdw_handler()
function extensions.file_fdw_validator(text[],oid)
这下一步是可选的。它只是为了显示“CREATE FOREIGN DATA WRAPPER”语法
=# CREATE FOREIGN DATA WRAPPER file_data_wrapper HANDLER extensions.file_fdw_handler; CREATE FOREIGN DATA WRAPPER
该扩展已经创建了一个名为 file_fdw 的外部数据包装器。从此处起我们将使用它。
我们需要创建一个“服务器”。当我们仅从文件中检索数据时，它似乎有些过剩，但是 SQL/MED 也能够处理远程数据库。
CREATE SERVER file FOREIGN DATA WRAPPER file_fdw ; CREATE SERVER
现在，我们来将一个 statistical_data.csv 文件链接到一个 statistical_data 表
CREATE FOREIGN TABLE statistical_data (field1 numeric, field2 numeric) server file options (filename '/tmp/statistical_data.csv', format 'csv', delimiter ';') ;
CREATE FOREIGN TABLE
marc=# SELECT * from statistical_data ;
field1 | field2
--------+--------
0.1 | 0.2
0.2 | 0.4
0.3 | 0.9
0.4 | 1.6
现在，外部表仅为 SELECT。
TODO：这是否也适用于 dblink ？
向后兼容性问题
在迁移到 9.1 时，需要检查以下项目。
- standard_conforming_strings 的默认值已更改为 on
传统上，PostgreSQL 并不像 SQL 标准指定的那样对待普通字符串字面值（“..”）：反斜杠（'\'）被视为一个转义符，因此，紧跟在其后的内容将被解释。例如，\n 是一个新行字符，\\ 是一个反斜杠字符。它更类似于 C。
9.1 中，standard_conforming_strings 现在默认为 on，这意味着将对普通字符串文本按 SQL 标准进行处理。因此，必须用第二个单引号（而不是反斜杠）对单引号进行保护，且反斜杠不再作为转义字符使用。
因此，以前是“I can\'t”，现在应当是“I can''t”。
有几件事需要了解
- 仍旧可以使用旧语法。仅需在起始引号前加入一个 E：E'I can\'t'
- standard_conforming_strings 仍然可以设置为 off
- 只要要求它们为您对字符串进行转义，许多编程语言早已执行正确的操作。例如，libpq 的 PQescapeLiteral 会自动检测 standard_conforming_strings 的值。
当然，请仔细检查您的程序是否已为此做好准备。
- 复合类型的函数形式和属性形式数据类型转换是不可接受的
8.4 以来，可以将几乎任何内容转换为文本格式。让我们尝试使用前面的外部表
=# SELECT cast(statistical_data as text) from statistical_data ; statistical_data ------------------ (0.1,0.2) (0.2,0.4) (0.3,0.9) (0.4,1.6) (4 rows)
问题在于 8.4 和 9.0 为我们提供了 4 种执行此操作的语法
- SELECT cast(statistical_data as text) from statistical_data ;
- SELECT statistical_data::text from statistical_data;
- SELECT statistical_data.text from statistical_data;
- SELECT text(statistical_data) from statistical_data;
对于复合类型（例如表记录），两种后语法不再允许使用：它们太容易被意外使用。
- 已对基于数组的域转换进行检查收紧
现在，当您更新对数组施加的约束元素时，PostgreSQL 会进行二次检查。
以下是 9.0 中的处理方式
=#CREATE DOMAIN test_dom as int[] check (value[1] > 0);
CREATE DOMAIN
=#SELECT '{-1,0,0,0,0}'::test_dom;
ERROR: value for domain test_dom violates check constraint "test_dom_check"
好的，这是正常的
=#CREATE TABLE test_dom_table (test test_dom);
CREATE TABLE
=# INSERT INTO test_dom_table values ('{1,0,0,0,0}');
INSERT 0 1
=# UPDATE test_dom_table SET test[1]=-1;
UPDATE 1
这不是正常的…未通过检查约束允许。9.1 中已不再允许，将正确执行检查。
- 现在，string_to_array() 使用长度为 0 的字符串返回空数组。之前返回的是 NULL。
=# SELECT string_to_array(,'whatever');
string_to_array
-----------------
{}
- 现在，如果分隔符为 NULL，string_to_array() 会将字符串拆分为字符。之前返回的是 NULL。
=# SELECT string_to_array('foo',NULL);
string_to_array
-----------------
{f,o,o}
- PL/pgSQL 的无参数 RAISE 已更改
这是罕见用例，但对于习惯 Oracle 方式的人来说很重要。
这是一个示例
CREATE OR REPLACE FUNCTION raise_demo () returns void language plpgsql as $$
BEGIN
RAISE NOTICE 'Main body';
BEGIN
RAISE NOTICE 'Sub-block';
RAISE EXCEPTION serialization_failure; -- Simulate a problem
EXCEPTION WHEN serialization_failure THEN
BEGIN
-- Maybe we had a serialization error
-- Won't happen here of course
RAISE DEBUG 'There was probably a serialization failure. It could be because of...';
-- ..
-- If I get there let's pretend I couldn't find a solution to the error
RAISE; -- Let's forward the error
EXCEPTION WHEN OTHERS THEN
-- This should capture everything
RAISE EXCEPTION 'Couldn t figure what to do with the error';
END;
END;
END;
$$
;
CREATE FUNCTION
在 9.0 中，会得到这个结果（client_min_messages 设置为 debug）
=# SELECT raise_demo(); NOTICE: Main body NOTICE: Sub-block DEBUG: There was probably a serialization failure. It could be because of... ERROR: serialization_failure
在 9.1 中
=# SELECT raise_demo(); NOTICE: Main body NOTICE: Sub-block DEBUG: There was probably a serialization failure. It could be because of... ERROR: Couldn t figure what to do with the error
区别在于，在 9.0 中，无参数 RAISE 会将代码流放回到 EXCEPTION 发生的位置。而在 9.1 中，RAISE 会继续在发生的块中执行，因此触发 RAISE 时不会离开内部 BEGIN 块。会执行其异常块。
性能提升
- 已优化同步写入以减小文件系统压力。
这样很难演示。但针对写入密集型负载，性能和响应能力（延迟）已得到极大提升。
- 现在，查询中的继承表可以返回内容有意义的排序结果，允许针对继承优化 MIN/MAX
如果您使用大量的继承，可能是在分区上下文中，您会喜欢这些优化。
查询规划器在以下情况下变得更智能。
让我们创建一个模拟模式
=# CREATE TABLE parent (a int); CREATE TABLE =# CREATE TABLE children_1 ( check (a between 1 and 10000000)) inherits (parent); CREATE TABLE =# CREATE TABLE children_2 ( check (a between 10000001 and 20000000)) inherits (parent); CREATE TABLE =# INSERT INTO children_1 select generate_series(1,10000000); INSERT 0 10000000 =# INSERT INTO children_2 select generate_series(10000001,20000000); INSERT 0 10000000 =# CREATE INDEX test_1 ON children_1 (a); CREATE INDEX; =# CREATE INDEX test_2 ON children_2 (a); CREATE INDEX;
我们来查找 a 的 50 个最大值。
SELECT * from parent order by a desc limit 50;
在这台小型测试机上，使用 9.0 数据库花费 13 秒，使用 9.1 数据库花费 0.8 毫秒。
9.0 计划是
Limit (cost=952993.36..952993.48 rows=50 width=4)
-> Sort (cost=952993.36..1002999.24 rows=20002354 width=4)
Sort Key: public.parent.a
-> Result (cost=0.00..288529.54 rows=20002354 width=4)
-> Append (cost=0.00..288529.54 rows=20002354 width=4)
-> Seq Scan on parent (cost=0.00..34.00 rows=2400 width=4)
-> Seq Scan on children_1 parent (cost=0.00..144247.77 rows=9999977 width=4)
-> Seq Scan on children_2 parent (cost=0.00..144247.77 rows=9999977 width=4)
9.1 计划是
Limit (cost=113.75..116.19 rows=50 width=4)
-> Result (cost=113.75..975036.98 rows=20002400 width=4)
-> Merge Append (cost=113.75..975036.98 rows=20002400 width=4)
Sort Key: public.parent.a
-> Sort (cost=113.73..119.73 rows=2400 width=4)
Sort Key: public.parent.a
-> Seq Scan on parent (cost=0.00..34.00 rows=2400 width=4)
-> Index Scan Backward using test_1 on children_1 parent (cost=0.00..303940.35 rows=10000000 width=4)
-> Index Scan Backward using test_2 on children_2 parent (cost=0.00..303940.35 rows=10000000 width=4)
9.0 计划意味着：我将获取每个表中的每条记录，对它们进行排序，然后返回 50 个最大值。
9.1 计划意味着：我将获取每个表中的记录（根据可用索引进行排序），在它们出现时合并它们，并返回前 50 个。
这是一个非常常见的陷阱，当有人对他们的数据进行分区时，这类查询会显著变慢。并且使用查询重写绕过此问题有难度。
- 散列算法现在可以用于全外部联接和数组。
可以使用一个非常简单的示例对此进行演示（用于全外部联接）
CREATE TABLE test1 (a int); CREATE TABLE test2 (a int); INSERT INTO test1 SELECT generate_series(1,100000); INSERT INTO test2 SELECT generate_series(100,1000);
因此我们有一个较大的 test1 表和一个较小的 test2 表。
使用 9.0，此查询使用此计划执行
EXPLAIN ANALYZE SELECT * FROM test1 FULL OUTER JOIN test2 USING (a);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Merge Full Join (cost=11285.07..11821.07 rows=100000 width=8) (actual time=330.092..651.618 rows=100000 loops=1)
Merge Cond: (test1.a = test2.a)
-> Sort (cost=11116.32..11366.32 rows=100000 width=4) (actual time=327.926..446.814 rows=100000 loops=1)
Sort Key: test1.a
Sort Method: external sort Disk: 1368kB
-> Seq Scan on test1 (cost=0.00..1443.00 rows=100000 width=4) (actual time=0.011..119.246 rows=100000 loops=1)
-> Sort (cost=168.75..174.75 rows=2400 width=4) (actual time=2.156..3.208 rows=901 loops=1)
Sort Key: test2.a
Sort Method: quicksort Memory: 67kB
-> Seq Scan on test2 (cost=0.00..34.00 rows=2400 width=4) (actual time=0.009..1.066 rows=901 loops=1
Total runtime: 733.368 ms
使用 9.1，此计划是新计划
--------------------------------------------------------------------------------------------------------------------
Hash Full Join (cost=24.27..1851.28 rows=100000 width=8) (actual time=2.536..331.547 rows=100000 loops=1)
Hash Cond: (test1.a = test2.a)
-> Seq Scan on test1 (cost=0.00..1443.00 rows=100000 width=4) (actual time=0.014..119.884 rows=100000 loops=1)
-> Hash (cost=13.01..13.01 rows=901 width=4) (actual time=2.505..2.505 rows=901 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 32kB
-> Seq Scan on test2 (cost=0.00..13.01 rows=901 width=4) (actual time=0.017..1.186 rows=901 loops=1)
Total runtime: 412.735 ms
9.0 计划执行 2 次排序。9.1 只需在较小的表上创建散列。
在这里，运行时将减少近 2 倍。另一个非常有趣的属性是，新计划具有更小的启动开销：在使用旧计划返回第一个开销需花费 330 毫秒的情况下，在 2 毫秒后返回第一个开销。
SELECT * from test1 full outer join test2 using (a) LIMIT 10
使用 9.0 需花费 330 毫秒，使用 9.1 需花费 3 毫秒。
管理
- wal_buffers 的自动调优。
在设置为 -1（新默认值）时，现在自动调优 wal_buffers 设置。它的自动设置为 shared_buffers 的 1/32，最大为 16MB。要处理的参数减少了一个…
- 在数据库和后台编写程序级统计视图中记录最近重置。
现在您可以知道统计信息最近何时重置。例如，对于数据库
SELECT datname, stats_reset FROM pg_stat_database; datname | stats_reset -----------+------------------------------- template1 | template0 | postgres | 2011-05-11 19:22:05.946641+02 marc | 2011-05-11 19:22:09.133483+02
- 在 pg_stat_*_tables 视图中显示 vacuum 和 analyze 操作数的列。
现在更易于了解哪个表获得了大量 autovacuum 关注
SELECT relname, last_vacuum, vacuum_count, last_autovacuum, autovacuum_count, last_analyze, analyze_count, last_autoanalyze, autoanalyze_count
FROM pg_stat_user_tables
WHERE relname in ('test1','test2');
relname | last_vacuum | vacuum_count | last_autovacuum | autovacuum_count | last_analyze | analyze_count | last_autoanalyze | autoanalyze_count
---------+-------------+--------------+-----------------+------------------+--------------+---------------+-------------------------------+-------------------
test1 | | 0 | | 0 | | 0 | 2011-05-22 15:51:50.48562+02 | 1
test2 | | 0 | | 0 | | 0 | 2011-05-22 15:52:50.325494+02 | 2
SQL 和 PL/PgSQL 功能
- GroupBy 可以猜测一些丢失的列
CREATE TABLE entities (entity_name text primary key, entity_address text);
CREATE TABLE employees (employee_name text primary key, entity_name text references entities (entity_name));
INSERT INTO entities VALUES ('HR', 'address1');
INSERT INTO entities VALUES ('SALES', 'address2');
INSERT INTO employees VALUES ('Smith', 'HR');
INSERT INTO employees VALUES ('Jones', 'HR');
INSERT INTO employees VALUES ('Taylor', 'SALES');
INSERT INTO employees VALUES ('Brown', 'SALES');
现在可以编写
SELECT count(*), entity_name, address
FROM entities JOIN employees using (entity_name)
GROUP BY entity_name;
count | entity_name | address
-------+-------------+----------
2 | HR | address1
2 | SALES | address2
在 9.0 中，也需要对 address 进行分组。由于 entity_name 是实体的主键，因此 address 在功能上依赖于 entity_name，因此 PostgreSQL 显然也必须对其进行分组。
- 可以通过 ALTER TYPE 向现有枚举类型添加新值。
=# CREATE TYPE package_status AS ENUM ('RECEIVED', 'DELIVERED');
CREATE TYPE
=# ALTER TYPE package_status ADD VALUE 'READY FOR DELIVERY' AFTER 'RECEIVED';
ALTER TYPE
在 9.0 之前，必须删除类型并创建一个新类型。这意味着删除使用该类型的所有列。这是阻碍 ENUM 类型采用的一个主要原因。
- 复合类型可以通过 ALTER TYPE ... ADD/DROP/ALTER/RENAME ATTRIBUTE 修改。
让我们创建一个简单的复合数据类型
=#CREATE TYPE package AS (destination text);
让我们使用此数据类型创建一个虚拟函数
=#CREATE FUNCTION package_exists (pack package) RETURNS boolean LANGUAGE plpgsql AS $$ BEGIN RETURN true; END $$ ;
测试此函数
=#SELECT package_exists(row('test'));
package_exists
----------------
t
它起作用了。
现在我们可以更改“package”类型
=#ALTER TYPE package ADD ATTRIBUTE received boolean;
类型已更改
=#SELECT package_exists(row('test'));
ERROR: cannot cast type record to package
LINE 1: SELECT package_exists(row('test'));
^
DETAIL: Input has too few columns.
=# SELECT package_exists(row('test',true));
package_exists
----------------
t
- ALTER TABLE ... ADD UNIQUE/PRIMARY KEY USING INDEX
这可能主要用于在不锁定表太久的情况下创建主键或唯一键
=# CREATE UNIQUE INDEX CONCURRENTLY idx_pk ON test_pk (a); CREATE INDEX =# ALTER TABLE test_pk ADD primary key using index idx_pk; ALTER TABLE
我们仅在 ALTER TABLE 期间获取 test_pk 的写锁。其余工作将在不中断用户工作的情况下完成。
这还可用于在整个重建期间不锁定表的情况下重建主键索引
=# CREATE UNIQUE INDEX CONCURRENTLY idx_pk2 ON test_pk (a); =# BEGIN ; =# ALTER TABLE test_pk DROP CONSTRAINT idx_pk; =# ALTER TABLE test_pk ADD primary key using index idx_pk2; =# COMMIT ;
- ALTER TABLE ... SET DATA TYPE 在合适的情况下可以避免重新编写表。
例如，将 varchar 列转换为文本不再需要重新编写表。
然而，增加 varchar 列上的长度约束仍然需要重新编写表（摘自更新日志）。
这是不言自明的。仍然有一些情况下需要涵盖，但这正在进行中。
- 新 CREATE TABLE IF NOT EXISTS 语法。
如果表已存在，您不会收到错误，只会收到 NOTICE。
请注意，它不会检查您的新定义是否已经就绪。
- COPY TO/FROM 的新增 ENCODING 选项。这允许单独于客户端编码指定 COPY 文件的编码。
COPY test1 TO stdout ENCODING 'latin9'
现在将直接转换编码。不再需要在 COPY 之前设置 client_encoding。
- 视图上的 INSTEAD OF 触发器。
此功能可用于实现完全可更新视图。这里有一个示例。
让我们继续 employees/entities 示例。
=#CREATE VIEW emp_entity AS SELECT employee_name, entity_name, address FROM entities JOIN employees USING (entity_name);
为了在 9.0 中让此视图可更新，需要编写规则。这可能会迅速变成一场噩梦，因为规则很复杂，甚至更难调试。这是完成此操作的方法：规则更新
现在我们可以用触发器做到这一点。这里有一个示例（这里只有 INSERT 部分）
=#CREATE OR REPLACE FUNCTION dml_emp_entity () RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE
vrecord RECORD;
BEGIN
IF TG_OP = 'INSERT' THEN
-- Does the record exist in entity ?
SELECT entity_name,address INTO vrecord FROM entities WHERE entity_name=NEW.entity_name;
IF NOT FOUND THEN
INSERT INTO entities (entity_name,address) VALUES (NEW.entity_name, NEW.address);
ELSE
IF vrecord.address != NEW.address THEN
RAISE EXCEPTION 'There already is a record for % in entities. Its address is %. It conflics with your address %',
NEW.entity_name, vrecord.address, NEW.address USING ERRCODE = 'unique_violation';
END IF;
END IF; -- Nothing more to do, the entity already exists and is OK
-- We now try to insert the employee data. Let's directly try an INSERT
BEGIN
INSERT INTO employees (employee_name, entity_name) VALUES (NEW.employee_name, NEW.entity_name);
EXCEPTION WHEN unique_violation THEN
RAISE EXCEPTION 'There is already an employee with this name %', NEW.employee_name USING ERRCODE = 'unique_violation';
END;
RETURN NEW; -- The trigger succeeded
END IF;
END
$$
;
我们现在只需要声明触发器
=#CREATE TRIGGER trig_dml_emp_entity INSTEAD OF INSERT OR UPDATE OR DELETE ON emp_entity FOR EACH ROW EXECUTE PROCEDURE dml_emp_entity ();
还有其他优点：规则只改写查询。有了触发器，我们添加了一些逻辑，可以发送更有用的错误消息。它让理解出错原因变得容易得多。我们还可以捕获异常。触发器相对于规则拥有所有优势。
- PL/PgSQL FOREACH IN ARRAY。
在 PL/PgSQL 中循环数组变得容易得多。迄今为止，FOR 结构只能用于在记录集中循环（查询结果）。
现在可以用来循环数组。
在 9.1 之前，可以这样编写
=# CREATE OR REPLACE FUNCTION test_array (parray int[]) RETURNS int LANGUAGE plpgsql AS $$
DECLARE
vcounter int :=0;
velement int;
BEGIN
FOR velement IN SELECT unnest (parray)
LOOP
vcounter:=vcounter+velement;
END LOOP;
RETURN vcounter;
END
$$
;
现在
=# CREATE OR REPLACE FUNCTION test_array (parray int[]) RETURNS int LANGUAGE plpgsql AS $$
DECLARE
vcounter int :=0;
velement int;
BEGIN
FOREACH velement IN ARRAY parray
LOOP
vcounter:=vcounter+velement;
END LOOP;
RETURN vcounter;
END
$$
;
它更易于阅读，而且运行速度更快。
另一个优点：当数组是多维数组的时候，我们可以对它进行切片。以下是直接来自文档的一个示例
=#CREATE FUNCTION scan_rows(int[]) RETURNS void AS $$
DECLARE
x int[];
BEGIN
FOREACH x SLICE 1 IN ARRAY $1
LOOP
RAISE NOTICE 'row = %', x;
END LOOP;
END;
$$ LANGUAGE plpgsql;
=#SELECT scan_rows(ARRAY[[1,2,3],[4,5,6],[7,8,9],[10,11,12]]);
NOTICE: row = {1,2,3}
NOTICE: row = {4,5,6}
NOTICE: row = {7,8,9}
NOTICE: row = {10,11,12}
