本文件展示了 PostgreSQL 9.0 与上一个主要版本 PostgreSQL 8.4 相比的许多最新进展。本版本包含 200 多项改进，因此本 wiki 页面详细介绍了其中许多重要变化。完整的更改列表在发行说明中列出。

不容错过的两个特性

热备和流式复制是两个新特性，它们标志着 PostgreSQL 开发史上的里程碑，也是将本版本分配为完整版本号 9.0（而不是 8.5）的动力。这两种特性结合起来，为 PostgreSQL 添加了内置的“二进制复制”。

有关如何使用热备的更多文档，请查看其页面，此外，一个广泛的二进制复制教程正在进行中。

热备

此特性允许用户创建“备用”数据库，即第二个数据库实例（通常位于单独的服务器上），它重放主数据库的二进制日志，同时使备用服务器可用于只读查询。它类似于专有数据库（如 Oracle 的 Active DataGuard）的备用数据库特性。当备用数据库持续重放来自主数据库的二进制修改流时，查询会正常执行。新数据更改的可见性遵循 MVCC 模型，因此新更改不会锁定查询。

启用热备是一个简单的过程。在主数据库服务器上，将以下内容添加到postgresql.conf文件

 wal_level = 'hot_standby' # Adds the required data in the WAL logs

在备用服务器上，将以下内容添加到其postgresql.conf文件

 hot_standby = on

热备与新的流式复制特性配合使用效果很好，尽管它也可以与之前版本中提供的基于文件的日志传送一起使用，也可以用来创建完全不接收更新的独立副本。

在某些情况下，来自主数据库的更改可能会与备用数据库上的查询发生冲突。一个简单的例子是，当 DROP TABLE 在主数据库上执行时，而备用数据库仍在对该表执行查询。备用数据库无法在先取消正在运行的查询的情况下处理 DROP 语句，而且延迟时间越长，备用数据库的复制落后程度就越大。这里有两个选项：暂停重放或取消只读查询并继续前进。

各种参数允许调整使用的冲突解决机制。

 max_standby_archive_delay = 30s # -1= always wait, 0= never wait, else wait for this
 max_standby_streaming_delay = 30s # -1= always wait, 0= never wait, else wait for this

两个 max_standby_{archive,streaming}_delay 设置决定了备用数据库在重放和只读查询之间发生冲突时的行为。在这种情况下，备用数据库最多会等待，直到它落后于主数据库的延迟量，然后才会取消冲突的只读查询。这两个参数允许针对通过常规文件归档传送出现的文件和通过 9.0 的流式复制新特性流式传送的文件，设置不同的延迟时间容忍度级别。

在主数据库上，也可以通过增加此参数来避免冲突

 vacuum_defer_cleanup_age = 10000 # Adjust updwards slowly to reduce conflicts

此特性功能丰富且复杂，因此建议在规划服务器部署之前阅读文档。

流式复制

流式复制是对热备的补充，是 PostgreSQL “巨大飞跃”的另一半。虽然有几种针对 PostgreSQL 的第三方复制解决方案可以满足各种特定需求，但这个新版本提供了一个简单、稳固且集成的版本，可能会在使用 PostgreSQL 的大多数高可用性安装中用作默认版本。

这次，目标是改进归档机制，使其尽可能连续，并且不依赖于日志文件传送。备用服务器现在可以连接到主/主服务器，并根据需要获得它们从预写日志中丢失的内容，不是以完整文件（“wal 段”）的形式，而是以 WAL 中记录的形式（可以将其视为这些文件的片段）。

流式复制是一种异步机制；备用服务器落后于主服务器。但与其他复制方法不同的是，这种延迟非常短，可以短至单个事务，具体取决于网络速度、数据库活动和热备设置。此外，每个从服务器对主服务器的负载很小，允许单个主服务器支持数十个从服务器。

主数据库和备用数据库在二进制级别上是相同的（实际上，几乎相同；但如果您的数据文件没有相同的校验和，请不要担心）。

对于流式复制，wal_level 应该为 “archive” 或 “hot standby”。

postgresql.conf, 主数据库

 max_wal_senders = 5 # Maximum 'wal_senders', processes responsible for managing a connection with a standby server
 wal_keep_segments # How many WAL segments (=files) should be kept on the primary, whatever may happen (you won't have to copy them manually on the standby if the standby gets too far behind)

在备用服务器上

recovery.conf, 备用

 standby_mode = 'on'
 primary_conninfo = 'host=192.168.1.50 port=5432 user=foo password=foopass' # connection string to reach the primary database

postgresql.conf, 从服务器

 wal_level # same value as on the primary (you'll need this after a failover, to build a new standby)
 hot_standby=on/off # Do you want to use Hot Standby at the same time ?

pg_hba.conf 文件

这里必须有一个用于复制连接的条目。假数据库是 “replication”，指定的用户名应该是超级用户。请注意不要为该帐户提供广泛的访问权限：可以从 WAL 记录中提取大量特权数据。

pg_hba.conf, 主数据库

 host    replication     foo             192.168.1.100/32        md5

与热备一样，此特性功能丰富且复杂。建议阅读文档。并在一切就绪后进行故障转移和切换测试。

需要强调的一点是：这两种特性可以一起使用。这意味着您可以拥有一个近乎实时的备用数据库，并在其上运行只读查询，例如报告查询。您也可以独立使用它们；备用数据库可以仅使用文件传送进行热备，而流式复制数据库可以在不接受查询的情况下进行流式传送。

其他新特性

9.0 中包含数百项改进、更新和新特性... 即使没有二进制复制，也足以使其成为一个主要版本。我们将在下面按类别介绍其中的一些，并详细说明如何使用它们。

安全和认证

当然，作为最安全的 SQL 数据库（根据《SQL 黑客手册》），我们总是渴望改进数据安全性。9.0 在这方面添加了几个新特性。

SCHEMA 中的 GRANT/REVOKE

PostgreSQL 中的一个令人讨厌的限制是缺乏全局 GRANT/REVOKE 功能。有了 9.0，现在可以使用以下命令为 schema 中的所有表、序列和函数设置权限，而无需编写脚本或存储过程

 GRANT SELECT ON ALL TABLES IN SCHEMA public TO toto;

并撤消此操作

 REVOKE SELECT ON ALL TABLES IN SCHEMA public FROM toto;

有关更多详细信息，请参阅 GRANT 文档页面。

请注意，以上操作仅适用于现有对象。但是，现在还可以为新对象定义默认权限

ALTER DEFAULT PRIVILEGES

此特性还使权限管理更高效。

 ALTER DEFAULT PRIVILEGES FOR ROLE marc GRANT SELECT ON TABLES TO public;
 CREATE TABLE test_priv (a int);
 \z test_priv
                             Access privileges
 Schema |    Name    | Type  | Access privileges | Column access privileges
--------+------------+-------+-------------------+--------------------------
 public | test_priv  | table | =r/marc          +|
        |            |       | marc=arwdDxt/marc |

此新信息存储在 pg_default_acl 系统表中。

passwordcheck

此 contrib 模块可以检查密码，并防止最糟糕的密码进入。安装并设置好该模块后，如文档中所述，其结果如下

 marc=# ALTER USER marc password 'marc12';
 <marc%marc> ERROR:  password is too short
 <marc%marc> STATEMENT:  ALTER USER marc password 'marc12';
 ERROR:  password is too short
 marc=# ALTER USER marc password 'marc123456';
 <marc%marc> ERROR:  password must not contain user name
 <marc%marc> STATEMENT:  ALTER USER marc password 'marc123456';
 ERROR:  password must not contain user name

此模块存在限制，主要是由于 PostgreSQL 接受已加密的密码声明，这使得正确的验证变得不可能。

此外，其代码有很好的文档，可以轻松地根据特定需求进行调整（例如，可以很容易地激活 cracklib）

SQL 特性

SQL03 具有大量功能，超过了当前任何一个 DBMS 实现的功能。但我们一直在添加 SQL 特性，并以与各种小细节兼容的方式扩展 SQL，从而使编写查询变得更容易，功能也更加强大。

列触发器

列触发器仅在显式更新特定列时触发。它们允许您避免在触发器代码中添加大量条件逻辑和值比较。

示例

 CREATE TRIGGER foo BEFORE UPDATE OF some_column ON table1 FOR EACH ROW EXECUTE PROCEDURE my_trigger();

此触发器仅在 'some_column' 列的 'table1数据表已更新。

如果将列设置为默认值，则不会执行列触发器。

WHEN 触发器

为了完成 PostgreSQL 在触发器中限制 IF ... THEN 代码的努力，条件触发器定义了触发器将执行的简单条件。这可以显著减少触发器执行次数，并降低数据库服务器的 CPU 负载。

例如，此触发器将仅在余额发生变化时检查帐户是否已正确平衡。

 CREATE TRIGGER check_update
 BEFORE UPDATE ON accounts
 FOR EACH ROW
 WHEN (OLD.balance IS DISTINCT FROM NEW.balance)
 EXECUTE PROCEDURE check_account_update();

此触发器仅在行实际发生更改时记录行更新。对于可能尝试保存未更改行的框架或 ORM 应用程序来说，这非常有用。

 CREATE TRIGGER log_update
 AFTER UPDATE ON accounts
 FOR EACH ROW
 WHEN (OLD.* IS DISTINCT FROM NEW.*)
 EXECUTE PROCEDURE log_account_update();

你甚至可以更进一步，如果行没有改变，就完全不保存行。

 CREATE TRIGGER log_update
 BEFORE UPDATE ON accounts
 FOR EACH ROW
 WHEN (OLD.* IS NOT DISTINCT FROM NEW.*)
 EXECUTE PROCEDURE no_op();

可延迟唯一约束

此功能也非常有用。以下是一个使用主键而不是简单唯一键的示例。

 marc=# CREATE TABLE test (a int primary key);
 marc=# INSERT INTO test values (1), (2);
 marc=# UPDATE test set a = a+1;
 ERROR:  duplicate key value violates unique constraint "test_pkey"
 DETAIL:  Key (a)=(2) already exists.

太可惜了：在语句结束时，就该约束而言，我的数据本来是一致的。更糟糕的是，如果表已按降序物理排序，则查询将有效！在 8.4 中，没有简单的方法可以解决这个问题；我们不得不找到一个技巧以正确的顺序更新记录。

现在我们可以这样做

 marc=# CREATE TABLE test (a int primary key deferrable);
 marc=# INSERT INTO test values (1), (2);
 marc=# UPDATE test set a = a+1;
 UPDATE 2

使用可延迟唯一索引，唯一性在语句结束时强制执行，而不是像简单索引一样在每次行更新后强制执行。这有时会稍微慢一些，但如果你需要进行这种更新，它就是一个救星。

也可以在事务结束时而不是在每个语句结束后强制执行唯一性检查。如果你需要执行需要多个 SQL 语句才能完成的“冲突”更新，这很有帮助。例如

 marc=# CREATE TABLE test (a int primary key deferrable, b text);
 marc=# INSERT INTO test values (1, 'x'), (2, 'y');
 marc=# BEGIN;
 marc=# SET CONSTRAINTS ALL DEFERRED;
 marc=# UPDATE test SET a = 2 WHERE b = 'x';
 marc=# UPDATE test SET a = 1 WHERE b = 'y';
 marc=# COMMIT;

如果不想每次都执行 SET CONSTRAINTS，也可以将约束声明为 INITIALLY DEFERRED

 CREATE TABLE test (a int PRIMARY KEY DEFERRABLE INITIALLY DEFERRED);

请记住，必须将要在语句或事务结束时检查的记录列表存储在某处。因此，请小心不要一次对数百万条记录执行此操作。这是唯一索引默认情况下不可延迟的原因之一，即使严格的 SQL 规范解读需要这样做。

窗口函数的新框架选项

如果你还不了解窗口函数，最好学习一下。你可以从这里开始：[waiting-for-84-window-functions](http://www.depesz.com/index.php/2009/01/21/waiting-for-84-window-functions)。它们使编写某些类型的查询变得容易得多。

已为声明窗口函数的框架添加了新选项。让我们使用此表（没有更好的示例…）

 marc=# SELECT * FROM salary ;
 entity     | name      | salary  | start_date
 -----------+-----------+---------+---------------
 R&D        | marc      |  700.00 | 2010-02-15
 Accounting | jack      |  800.00 | 2010-05-01
 R&D        | maria     |  700.00 | 2009-01-01
 R&D        | kevin     |  500.00 | 2009-05-01
 R&D        | john      | 1000.00 | 2008-07-01
 R&D        | tom       | 1100.00 | 2005-01-01
 Accounting | millicent |  850.00 | 2006-01-01

这是一个没有声明框架的窗口函数示例。

 marc=# SELECT entity, name, salary, start_date,
        avg(salary) OVER (PARTITION BY entity ORDER BY start_date)
        FROM salary;
 
 entity     | name      | salary  | start_date    |          avg         
 -----------+-----------+---------+---------------+-----------------------
 Accounting | millicent |  850.00 | 2006-01-01    |  850.0000000000000000
 Accounting | jack      |  800.00 | 2010-05-01    |  825.0000000000000000
 R&D        | tom       | 1100.00 | 2005-01-01    | 1100.0000000000000000
 R&D        | john      | 1000.00 | 2008-07-01    | 1050.0000000000000000
 R&D        | maria     |  700.00 | 2009-01-01    |  933.3333333333333333
 R&D        | kevin     |  500.00 | 2009-05-01    |  825.0000000000000000
 R&D        | marc      |  700.00 | 2010-02-15    |  800.0000000000000000

框架是在其上运行窗口函数的记录组。当然，如果没有显式声明框架，则存在默认框架。

以下是具有显式框架的相同查询。

 marc=# SELECT entity, name, salary, start_date,
        avg(salary) OVER (PARTITION BY entity ORDER BY start_date
                          RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
        FROM salary;
 
 entity     | name      | salary  | start_date    |          avg         
 -----------+-----------+---------+---------------+-----------------------
 Accounting | millicent |  850.00 | 2006-01-01    |  850.0000000000000000
 Accounting | jack      |  800.00 | 2010-05-01    |  825.0000000000000000
 R&D        | tom       | 1100.00 | 2005-01-01    | 1100.0000000000000000
 R&D        | john      | 1000.00 | 2008-07-01    | 1050.0000000000000000
 R&D        | maria     |  700.00 | 2009-01-01    |  933.3333333333333333
 R&D        | kevin     |  500.00 | 2009-05-01    |  825.0000000000000000
 R&D        | marc      |  700.00 | 2010-02-15    |  800.0000000000000000

在此示例中，框架是“范围”框架，介于分区（类似行的组）的开始和当前行（不完全是当前行，但现在让我们先放在一边，如果你想了解更多，请阅读文档）之间。可以看到，平均值（avg）函数是从框架的第一行（分组在一起的记录）和当前行计算的。

第一个新功能：从 9.0 开始，可以将框架声明为介于当前行和分区结束之间。

 marc=# SELECT entity, name, salary, start_date,
        avg(salary) OVER (PARTITION BY entity ORDER BY start_date
                          RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
        FROM salary;
 
 entity     | name      | salary  | start_date    |         avg         
 -----------+-----------+---------+---------------+----------------------
 Accounting | millicent |  850.00 | 2006-01-01    | 825.0000000000000000
 Accounting | jack      |  800.00 | 2010-05-01    | 800.0000000000000000
 R&D        | tom       | 1100.00 | 2005-01-01    | 800.0000000000000000
 R&D        | john      | 1000.00 | 2008-07-01    | 725.0000000000000000
 R&D        | maria     |  700.00 | 2009-01-01    | 633.3333333333333333
 R&D        | kevin     |  500.00 | 2009-05-01    | 600.0000000000000000
 R&D        | marc      |  700.00 | 2010-02-15    | 700.0000000000000000

第二个新功能：框架可以声明为“x 个前一个记录到 y 个下一个记录”。在本示例中没有意义，但无论如何让我们试试：

 marc=# SELECT entity, name, salary, start_date,
        avg(salary) OVER (PARTITION BY entity ORDER BY start_date
                          ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
        FROM salary;
 
 entity     | name      | salary  | start_date    |          avg         
 -----------+-----------+---------+---------------+-----------------------
 Accounting | millicent |  850.00 | 2006-01-01    |  825.0000000000000000
 Accounting | jack      |  800.00 | 2010-05-01    |  825.0000000000000000
 R&D        | tom       | 1100.00 | 2005-01-01    | 1050.0000000000000000
 R&D        | john      | 1000.00 | 2008-07-01    |  933.3333333333333333
 R&D        | maria     |  700.00 | 2009-01-01    |  733.3333333333333333
 R&D        | kevin     |  500.00 | 2009-05-01    |  633.3333333333333333
 R&D        | marc      |  700.00 | 2010-02-15    |  600.0000000000000000

框架仍然限制在分区内（例如，查看 tom 的记录：jack 的记录不用于计算其平均值）。

如果想要使用相同的查询，但要使用 3 行的移动平均值，而不是在每个分区切换时重置（仍然没有实际用途）

 marc=# SELECT entity, name, salary, start_date,
        avg(salary) OVER (ORDER BY entity, start_date
                          ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
        FROM salary;
 
 entity     | name      | salary  | start_date    |         avg         
 -----------+-----------+---------+---------------+----------------------
 Accounting | millicent |  850.00 | 2006-01-01    | 825.0000000000000000
 Accounting | jack      |  800.00 | 2010-05-01    | 916.6666666666666667
 R&D        | tom       | 1100.00 | 2005-01-01    | 966.6666666666666667
 R&D        | john      | 1000.00 | 2008-07-01    | 933.3333333333333333
 R&D        | maria     |  700.00 | 2009-01-01    | 733.3333333333333333
 R&D        | kevin     |  500.00 | 2009-05-01    | 633.3333333333333333
 R&D        | marc      |  700.00 | 2010-02-15    | 600.0000000000000000

简而言之，这是一个需要掌握的强大工具，即使我无法提供好的示例。

聚合中的排序

此功能很细微：聚合函数的结果可能取决于接收数据的顺序。

当然，我们不是在谈论 count、avg，而是在谈论 array_agg、string_agg…

这很好，因为它将展示 string_agg（另一个 9.0 功能），一石二鸟。

让我们再次从薪资表开始。我们想要按实体分组的员工列表，并将其连接成一个值。它将被输入到电子表格中…

 marc=# SELECT entity,string_agg(name,', ') FROM salary GROUP BY entity;
 entity     |          string_agg          
 -----------+-------------------------------
 Accounting | stephanie, etienne
 R&D        | marc, maria, kevin, john, tom

这已经很不错了。但我希望它们按字母顺序排序，因为我不知道如何在电子表格中编写宏来对这些数据进行排序。

 marc=# SELECT entity,string_agg(name,', ' ORDER BY name) FROM salary GROUP BY entity;
 entity     |          string_agg          
 -----------+-------------------------------
 Accounting | etienne, stephanie
 R&D        | john, kevin, marc, maria, tom

要使用此新功能，必须在聚合函数内插入 sort 子句，并且不要使用逗号将其与参数隔开。

数据库管理

DBA 是一项艰巨且经常令人沮丧的工作——尤其是如果你没有这个职位。9.0 包含一些新的改进功能，让这项工作更容易一些。

更好的 VACUUM FULL

到目前为止，VACUUM FULL 非常慢。此语句可以从表中恢复可用空间以减小其大小，主要是在 VACUUM 本身没有被频繁运行时。

它之所以慢是因为其工作方式：记录逐个从源块读取并移动到更靠近表开头的块。清空表尾后，将删除此空部分。

这种策略非常低效：逐个移动记录会产生大量的随机 IO。此外，在此重组过程中，必须维护索引，这会使整个过程更加昂贵，并导致索引碎片。因此，建议在 VACUUM FULL 后立即重新索引表。

从 9.0 版本开始，VACUUM FULL 语句会从当前表创建新表，并将所有记录按顺序复制。复制完所有记录后，将重新创建索引，然后销毁并替换旧表。

这样做的好处是速度快得多。尽管 VACUUM FULL 在运行时仍然需要 AccessExclusiveLock。与旧方法相比，这种方法的唯一缺点是 VACUUM FULL 可以使用磁盘上表大小的两倍，因为它正在创建它的新版本。

现在让我们比较一下两种方法的运行时间。在这两种情况下，我们都按如下方式准备测试数据（适用于 8.4 和 9.0）

 marc=# CREATE TABLE test (a int);
 CREATE TABLE
 marc=# CREATE INDEX idxtsta on test (a);
 CREATE INDEX
 marc=# INSERT INTO test SELECT generate_series(1,1000000);
 INSERT 0 1000000
 marc=# DELETE FROM test where a%3=0; -- making holes everywhere
 DELETE 333333
 marc=# VACUUM test;
 VACUUM

使用 8.4

 marc=# \timing
 Timing is on.
 marc=# VACUUM FULL test;
 VACUUM
 Time: 6306,603 ms
 marc=# REINDEX TABLE test;
 REINDEX
 Time: 1799,998 ms

大约 8 秒。使用 9.0

 marc=# \timing
 Timing is on.
 marc=# VACUUM FULL test;
 VACUUM
 Time: 2563,467 ms

但这并不意味着 VACUUM FULL 在生产环境中是一个好主意。如果你需要它，可能是因为你的 VACUUM 策略不合适。

pg_stat_activity 中的 application_name

在监控会话中

 marc=# SELECT * from pg_stat_activity where procpid= 5991;
 datid | datname | procpid | usesysid | usename | application_name | client_addr | client_port |         backend_start         | xact_start | query_start | waiting | current_query
 ------+---------+---------+----------+---------+------------------+-------------+-------------+-------------------------------+------------+-------------+---------+----------------
 16384 | marc    |    5991 |       10 | marc    | psql             |             |          -1 | 2010-05-16 13:48:10.154113+02 |            |             | f       | <IDLE>
 (1 row)

在“5991”会话中

 marc=# SET application_name TO 'my_app';
 SET

返回监控会话

 >marc=# SELECT * from pg_stat_activity where procpid= 5991;
 datid | datname | procpid | usesysid | usename | application_name | client_addr | client_port |         backend_start         | xact_start |          query_start          | waiting | current_query
 ------+---------+---------+----------+---------+------------------+-------------+-------------+-------------------------------+------------+-------------+---------+-----------------+----------------
 16384 | marc    |    5991 |       10 | marc    | my_app           |             |          -1 | 2010-05-16 13:48:10.154113+02 |            | 2010-05-16 13:49:13.107413+02 | f       | <IDLE>
 (1 row)

你的工作是在你的程序或会话中正确设置它。你的 DBA 会感谢你这样做，因为他们终于可以轻松地知道谁在数据库上运行什么程序了。

按数据库+角色配置

现在可以按特定数据库或特定用户的配置变量进行设置，而不是只能按数据库或按用户进行设置。

 marc=# ALTER ROLE marc IN database marc set log_statement to 'all';
 ALTER ROLE

要了解谁在哪些用户+数据库中设置了哪些变量，有一个新的 psql 命令

 marc=# \drds
         List of settings
 role | database |     settings
 -----+----------+-----------------
 marc | marc     | log_statement=all
 (1 row)

目录已发生更改以存储此信息

 Table "pg_catalog.pg_db_role_setting"
   Column    |  Type  | Modifier
 ------------+--------+----------
 setdatabase | oid    | not null
 setrole     | oid    | not null
 setconfig   | text   |

记录 postgresql.conf 重新加载时所有已更改的参数

以下是一个示例，log_line_prefix 参数已更改

 LOG:  received SIGHUP, reloading configuration files
 <%> LOG:  parameter "log_line_prefix" changed to "<%u%%%d> "

更好的唯一约束错误消息

使用 8.4

 marc=# INSERT INTO test VALUES (1);
 ERROR:  duplicate key value violates unique constraint "test_a_key"

使用 9.0

 marc=# INSERT INTO test VALUES (1);
 ERROR:  duplicate key value violates unique constraint "test_a_key"
 DETAIL:  Key (a)=(1) already exists.

这将使诊断约束违规错误变得容易得多。

vacuumdb --analyze-only

顾名思义，现在可以使用 vacuumdb 仅运行 analyze。例如，这对于 cron 任务可能很有用。

性能

如果没有速度提升，它就不是 PostgreSQL 的新版本，对吧？虽然 9.0 不是“性能版本”，但它确实添加了一些新功能，使某些特定操作的速度提高了 1000%。

适用于 Windows 的 64 位二进制文件

现在可以将 PostgreSQL 编译为适用于 Windows 的 64 位二进制文件，PostgreSQL 项目正在发布 64 位软件包。

这对 Windows 用户有很多好处：在 64 位数字运算（如 BIGINT 和 BIGSERIAL）方面有更好的性能，能够使用超过 2GB 的 work_mem，并增强了与运行在 Linux 上的 64 位 PostgreSQL 版本的兼容性。最后一个优势在热备情况下尤其重要。

但是，请注意，目前还没有证据表明，在 Windows 版本的 32 位版本上看到的 500MB shared_buffers 大小限制（超过此限制性能会下降）在使用此 64 位版本时得到解决。还有另一个限制，即许多第三方开源库在适用于 Windows 的 64 位版本中不可用，因此你可能无法添加所有 PostgreSQL 扩展。欢迎测试报告！

联接去除

此新的优化允许我们从 SQL 执行计划中去除不必要的联接。

当使用自动生成的 SQL（例如来自 ORM（对象关系映射）工具）时，SQL 可能是次优的。在某些情况下，去除不必要的联接可以将查询计划的执行速度提高一个数量级。

这对使用许多联接和嵌套视图的数据库尤其重要。

 marc=# CREATE TABLE t1 (a int);
 CREATE TABLE
 marc=# CREATE TABLE t2 (b int);
 CREATE TABLE
 marc=# CREATE TABLE t3 (c int);
 CREATE TABLE

我们使用 generate_series 添加了一些数据…

 marc=# EXPLAIN SELECT t1.a,t2.b from t1 join t2 on (t1.a=t2.b) left join t3 on (t1.a=t3.c);
 QUERY PLAN                                 
 ------------------------------------------------------------------------------
 Merge Right Join  (cost=506.24..6146.24 rows=345600 width=8)
     Merge Cond: (t3.c = t1.a)
   ->  Sort  (cost=168.75..174.75 rows=2400 width=4)
         Sort Key: t3.c
         ->  Seq Scan on t3  (cost=0.00..34.00 rows=2400 width=4)
   ->  Materialize  (cost=337.49..853.49 rows=28800 width=8)
         ->  Merge Join  (cost=337.49..781.49 rows=28800 width=8)
               Merge Cond: (t1.a = t2.b)
               ->  Sort  (cost=168.75..174.75 rows=2400 width=4)
                     Sort Key: t1.a
                     ->  Seq Scan on t1  (cost=0.00..34.00 rows=2400 width=4)
               ->  Sort  (cost=168.75..174.75 rows=2400 width=4)
                     Sort Key: t2.b
                     ->  Seq Scan on t2  (cost=0.00..34.00 rows=2400 width=4)

目前，一切正常，我们在 8.4 中的行为相同。但让我们假设在 t3 上，'c' 列上有一个 UNIQUE 约束。在这种情况下，从理论上讲，对 t3 的联接没有任何作用：返回的记录数量不会改变，它们的内容也不会改变。这是因为该列是 UNIQUE，联接是 LEFT JOIN，并且没有检索 t3 的任何列。如果该列不是 UNIQUE，则联接可能会带来更多记录。如果它不是 LEFT JOIN，则联接可能会忽略一些记录。

使用 9.0

 marc=# ALTER TABLE t3 ADD UNIQUE (c);
 NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index "t3_c_key" for table "t3"
 ALTER TABLE
 marc=# EXPLAIN SELECT t1.a,t2.b from t1 join t2 on (t1.a=t2.b) left join t3 on (t1.a=t3.c);
                             QUERY PLAN                           
 ------------------------------------------------------------------
 Merge Join  (cost=337.49..781.49 rows=28800 width=8)
   Merge Cond: (t1.a = t2.b)
   ->  Sort  (cost=168.75..174.75 rows=2400 width=4)
         Sort Key: t1.a
         ->  Seq Scan on t1  (cost=0.00..34.00 rows=2400 width=4)
   ->  Sort  (cost=168.75..174.75 rows=2400 width=4)
         Sort Key: t2.b
         ->  Seq Scan on t2  (cost=0.00..34.00 rows=2400 width=4)
 (8 rows)

IS NOT NULL 现在可以使用索引

为了演示，我们将比较 8.4 和 9.0 版本（我创建的表主要包含空值）

使用 8.4

 marc=# EXPLAIN ANALYZE SELECT max(a) from test;
 QUERY PLAN                                                                  
 ------------------------------------------------------------------------------------------------------------------------------------------------
 Result  (cost=0.03..0.04 rows=1 width=0) (actual time=281.320..281.321 rows=1 loops=1)
 InitPlan 1 (returns $0)
   ->  Limit  (cost=0.00..0.03 rows=1 width=4) (actual time=281.311..281.313 rows=1 loops=1)
   ->  Index Scan Backward using idxa on test  (cost=0.00..29447.36 rows=1001000 width=4) (actual time=281.307..281.307 rows=1 loops=1)
 Filter: (a IS NOT NULL)
 Total runtime: 281.360 ms
 (6 rows)

使用 9.0

 marc=# EXPLAIN ANALYZE SELECT max(a) from test;
 QUERY PLAN                                                                
 --------------------------------------------------------------------------------------------------------------------------------------------
 Result  (cost=0.08..0.09 rows=1 width=0) (actual time=0.100..0.102 rows=1 loops=1)
 InitPlan 1 (returns $0)
   ->  Limit  (cost=0.00..0.08 rows=1 width=4) (actual time=0.092..0.093 rows=1 loops=1)
   ->  Index Scan Backward using idxa on test  (cost=0.00..84148.06 rows=1001164 width=4) (actual time=0.089..0.089 rows=1 loops=1)
 Index Cond: (a IS NOT NULL)
 Total runtime: 0.139 ms
 (6 rows)

不同之处在于，9.0 只扫描索引中的非空键。8.4 必须去检查表（当 9.0 使用索引条件时，则执行 Filter 步骤）。在这个特定的用例中，收益非常大。

使用索引来获取更好的动态统计信息

在开始解释这个新功能之前，让我们谈谈直方图：PostgreSQL 与其他一些数据库一样，使用统计优化器。这意味着在规划查询时，它（或应该）对查询的每个步骤将返回多少记录有一个大致正确的概念。为此，它使用统计信息，例如表中记录的大致数量、其大小、最常见的值以及直方图。PostgreSQL 使用这些信息根据此 WHERE 子句中询问的值或范围，对 WHERE 子句在列上返回的记录数量进行估计。

在某些情况下，这些直方图会很快过时，并成为某些 SQL 查询的问题。例如，一个日志表，其中会插入带时间戳的记录，而且我们大部分时间都希望从该表中获取最近 5 分钟的记录。

在这个特定的情况下，在 9.0 之前无法获得正确的统计信息。现在，当 PostgreSQL 在规划时检测到一个查询要求对一个值进行“范围扫描”，而该值大于直方图中的最大值（或小于最小值），即最后一次统计计算期间检测到的最大值，并且该列有索引时，它会使用索引获取该列的最大值（或最小值），然后再真正执行查询，以便获得更真实的统计信息。当然，由于 PostgreSQL 使用索引来执行此操作，所以必须存在索引。

下面是一个例子。测试表中的 a 列已经填充了许多日期，所有日期都在过去。它的统计信息是最新的。

现在是 13:37，我在 13:37 之后还没有插入任何数据。

 marc=# EXPLAIN ANALYZE select * from test where a > '2010-06-03 13:37:00';
 QUERY PLAN                                                 
 --------------------------------------------------------------------------------------------------------------
 Index Scan using idxtsta on test  (cost=0.00..8.30 rows=1 width=8) (actual time=0.007..0.007 rows=0 loops=1)
 Index Cond: (a > '2010-06-03 13:37:00'::timestamp without time zone)
 Total runtime: 0.027 ms
 (3 rows)

一切正常。直方图的上限是 '2010-06-03 13:36:16.830007'（此信息来自 pg_stats）。没有办法猜测有多少记录大于 13:37，在 8.4 中，PostgreSQL 会继续估计为 '1'，直到下一次分析。

 marc=# DO LANGUAGE plpgsql
 $$
 DECLARE
   i int;
 BEGIN
   FOR i IN 1..10000 LOOP
   INSERT INTO test VALUES (clock_timestamp());
   END LOOP;
 END
 $$
 ;

（我必须说我真的很喜欢 'DO'）。我们刚刚插入了 10000 条记录，它们的日期都大于 13:37。

 marc=# EXPLAIN ANALYZE SELECT * FROM test WHERE a > '2010-06-03 13:37:00';
 QUERY PLAN                                                      
 -----------------------------------------------------------------------------------------------------------------------
 Index Scan using idxtsta on test  (cost=0.00..43.98 rows=1125 width=8) (actual time=0.012..13.590 rows=10000 loops=1)
 Index Cond: (a > '2010-06-03 13:37:00'::timestamp without time zone)
 Total runtime: 23.567 ms
 (3 rows)

估计的行数不再是 0 或 1 了。不过，统计信息还没有更新。

 marc=# SELECT last_autoanalyze FROM pg_stat_user_tables WHERE relname = 'test';
 last_autoanalyze       
 -------------------------------
 2010-06-03 13:36:21.553477+02
 (1 row)

我们的评估仍然存在一个数量级的误差（10 倍）。但这不算太糟糕：如果没有这个增强功能，误差将达到四个数量级（10,000）。总之，更小的误差使得我们更有可能从这类查询中获得一个好的计划。

解释缓冲区、哈希统计、xml、json、yaml、新的可选解释语法

这是我们都知道的 EXPLAIN ANALYZE

 marc=# EXPLAIN ANALYZE SELECT a, sum(c) FROM pere JOIN fils ON (pere.a = fils.b) WHERE b BETWEEN 1000 AND 300000 GROUP BY a;
                                                           QUERY PLAN                                                          
 ---------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=905.48..905.86 rows=31 width=8) (actual time=0.444..0.453 rows=6 loops=1)
   ->  Nested Loop  (cost=10.70..905.32 rows=31 width=8) (actual time=0.104..0.423 rows=6 loops=1)
         ->  Bitmap Heap Scan on fils  (cost=10.70..295.78 rows=31 width=8) (actual time=0.040..0.154 rows=30 loops=1)
               Recheck Cond: ((b >= 1000) AND (b <= 300000))
               ->  Bitmap Index Scan on fils_pkey  (cost=0.00..10.69 rows=31 width=0) (actual time=0.023..0.023 rows=30 loops=1)
                     Index Cond: ((b >= 1000) AND (b <= 300000))
         ->  Index Scan using pere_pkey on pere  (cost=0.00..19.65 rows=1 width=4) (actual time=0.005..0.005 rows=0 loops=30)
               Index Cond: (pere.a = fils.b)
 Total runtime: 0.560 ms
 (9 rows)

要访问新的可用信息，请使用新的语法：

 EXPLAIN [ ( { ANALYZE boolean | VERBOSE boolean | COSTS boolean | BUFFERS boolean | FORMAT { TEXT | XML | JSON | YAML } } [, ...] ) ] instruction

例如

 marc=# EXPLAIN (ANALYZE true, VERBOSE true, BUFFERS true) SELECT a, sum(c) FROM pere JOIN fils ON (pere.a = fils.b) WHERE b BETWEEN 1000 AND 300000 GROUP BY a;
                                                             QUERY PLAN
 -------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=905.48..905.86 rows=31 width=8) (actual time=1.326..1.336 rows=6 loops=1)
   Output: pere.a, sum(fils.c)
   Buffers: shared hit=58 read=40
   ->  Nested Loop  (cost=10.70..905.32 rows=31 width=8) (actual time=0.278..1.288 rows=6 loops=1)
         Output: pere.a, fils.c
         Buffers: shared hit=58 read=40
         ->  Bitmap Heap Scan on public.fils  (cost=10.70..295.78 rows=31 width=8) (actual time=0.073..0.737 rows=30 loops=1)
               Output: fils.b, fils.c
               Recheck Cond: ((fils.b >= 1000) AND (fils.b <= 300000))
               Buffers: shared hit=4 read=28
               ->  Bitmap Index Scan on fils_pkey  (cost=0.00..10.69 rows=31 width=0) (actual time=0.030..0.030 rows=30 loops=1)
                     Index Cond: ((fils.b >= 1000) AND (fils.b <= 300000))
                     Buffers: shared hit=3
         ->  Index Scan using pere_pkey on public.pere  (cost=0.00..19.65 rows=1 width=4) (actual time=0.013..0.014 rows=0 loops=30)
               Output: pere.a
               Index Cond: (pere.a = fils.b)
               Buffers: shared hit=54 read=12
 Total runtime: 1.526 ms
 (18 rows)

VERBOSE 显示 'Output' 行（它在 8.4 中已经存在）。

BUFFERS 显示有关缓冲区的信息（查询执行的输入输出操作）：hit 是直接从 shared_buffers 获取的块数，read 是向操作系统请求的块数。这里，shared_buffers 中只有很少的数据。

还可以要求使用除纯文本之外的其他格式。对于用户来说，这没有用。对于在 EXPLAIN 上开发 GUI 的人来说，它简化了开发，因为他们可以摆脱 'explain' 解析器（及其潜在的错误），并使用更标准的解析器，例如 XML。

还可以使用 COSTS false 禁用成本显示。

每个表空间的 seq_page_cost/random_page_cost

 marc=# ALTER TABLESPACE pg_default SET ( random_page_cost = 10, seq_page_cost=5);
 ALTER TABLESPACE

我们刚刚更改了 pg_default 中包含的所有对象的 random_page_cost 和 seq_page_cost。为什么呢？

用例是当不同的表空间具有不同的性能时：例如，你有一些关键数据存储在 SSD 驱动器上，或者一些历史数据存储在旧的磁盘阵列上，速度比你用于活动数据的全新阵列慢。这使得告诉 PostgreSQL 所有表空间在性能方面并不总是表现相同成为可能。当然，这只有在非常大的数据库中才有用。

对列强制使用不同的统计信息

这使得设置列的不同值数量成为可能。不要轻易使用它，只在对该列的 ANALYZE 无法获得好的值时才使用。

以下是如何执行此操作

 marc=# ALTER TABLE test ALTER COLUMN a SET (n_distinct = 2);
 ALTER TABLE

必须再次运行 ANALYZE 才能将此更改考虑在内

 marc=# ANALYZE test;
 ANALYZE

现在让我们试试

 marc=# EXPLAIN SELECT distinct * from test;
 QUERY PLAN                           
 ------------------------------------------------------------------
 HashAggregate  (cost=6263.00..6263.02 rows=2 width=8)
 ->  Seq Scan on test  (cost=0.00..5338.00 rows=370000 width=8)
 (2 rows)

这是一个不应该做的事情的例子：我的表中真的有 370 000 个不同的值。现在我的执行计划可能非常糟糕。

如果 n_distinct 为正数，则表示不同的值的数量。

如果它为负数（介于 0 和 -1 之间），则表示与表中估计的记录数量相关的乘数：例如，-0.2 表示每 5 条表记录有一个不同的值。

0 使行为恢复正常（ANALYZE 自行估计 distinct）。

不要更改此参数，除非你完全确定已经正确诊断了问题。否则，请确保性能会下降。

由 auto_explain 记录的语句

auto_explain contrib 模块现在将打印语句及其计划，这将使其更容易使用。

针对 pg_stat_statements 的缓冲区统计

这个已经非常有用的 contrib 模块现在也提供了有关缓冲区的信息。作为提醒，pg_stat_statements 收集有关在数据库上运行的查询的统计信息。到目前为止，它存储了查询的代码、执行次数、累积运行时间、累积返回的记录。它现在也收集缓冲区操作。

 marc=# SELECT * from pg_stat_statements order by total_time desc limit 2;
 -[ RECORD 1 ]-------+---------------------
 userid              | 10
 dbid                | 16485
 query               | SELECT * from table1 ;
 calls               | 2
 total_time          | 0.491229
 rows                | 420000
 shared_blks_hit     | 61
 shared_blks_read    | 2251
 shared_blks_written | 0
 local_blks_hit      | 0
 local_blks_read     | 0
 local_blks_written  | 0
 temp_blks_read      | 0
 temp_blks_written   | 0
 -[ RECORD 2 ]-------+---------------------
 userid              | 10
 dbid                | 16485
 query               | SELECT * from table2;
 calls               | 2
 total_time          | 0.141445
 rows                | 200000
 shared_blks_hit     | 443
 shared_blks_read    | 443
 shared_blks_written | 0
 local_blks_hit      | 0
 local_blks_read     | 0
 local_blks_written  | 0
 temp_blks_read      | 0
 temp_blks_written   | 0

安装此 contrib 后，现在可以回答以下问题

哪个查询的累积运行时间最长？
哪个查询生成最多的 I/O 操作？（我们仍然无法知道数据是否已在操作系统的缓存中找到）
哪个查询主要使用缓存（因此即使我们将其变大也不会更快）？
哪个查询修改了最多的块？
谁在进行排序？

'local' 和 'temp' 是与临时表和其他本地操作（排序、哈希）相关的缓冲区操作，这些操作针对数据库后端。如果其中有很多读取和写入，你最好增加 temp_buffers（对于 'local'）或 work_mem（对于 'temp'）。

存储过程

PostgreSQL 不仅仅是一个数据库，它是一个完整的应用程序平台。许多用户使用存储过程和函数编写完整的应用程序。因此，9.0 在数据库过程代码中带来了一些改进也就不足为奇了。

默认情况下使用 PL/pgSQL

你不需要在数据库中添加 PL/pgSQL，因为它将默认安装。这已经被人请求了很长时间。

对 PL 语言的许多改进。

许多语言都得到了巨大的改进，例如 PLPerl。如果你想了解更多细节，请阅读发行说明，这里有很多细节无法一一列举。

匿名函数（又名匿名块）

这个新功能用于创建一次性运行的函数。实际上，这允许你在命令行或动态地运行存储过程代码，就像你在 SQL Server 和 Oracle 上一样。但是，与它们不同的是，PostgreSQL 允许你在任何已安装的程序语言中运行匿名函数，而 PostgreSQL 支持的程序语言超过十多种。

这个功能对于模式升级脚本非常有用。下面是稍后将在本文档中看到的 'GRANT SELECT ON ALL TABLES' 的一个略微不同的版本，它根据表所有者授予一堆表的 SELECT 权限，并排除两个模式

 DO language plpgsql $$
 DECLARE
 vr record;
 
 BEGIN
 
 FOR vr IN SELECT tablename FROM pg_tables WHERE tableowner = 'marc' AND schemaname NOT IN ('pg_catalog','information_schema')
 LOOP
   EXECUTE 'GRANT SELECT ON ' || vr.tablename || ' TO toto';
 END LOOP;
 END
 $$;

从 8.4 开始，这将需要创建函数（使用 CREATE FUNCTION），运行它，然后删除它（使用 DROP FUNCTION）。所有这些都需要拥有执行此操作的权限。9.0 简化了执行此类过程。

匿名函数在软件行业中也被称为“匿名代码块”。

命名参数调用

结合 8.4 版本中引入的默认参数，命名参数允许动态调用具有可变数量参数的函数，就像它们在编程语言中一样。命名参数对 SQL Server 或 Sybase 的用户来说很熟悉，但 PostgreSQL 更胜一筹，因为它同时支持命名参数调用和函数重载。

选择用于命名参数的语法如下

 CREATE FUNCTION test (a int, b text) RETURNS text AS $$
 DECLARE
   value text;
 BEGIN
   value := 'a is ' || a::text || ' and b is ' || b;
   RETURN value;
 END;
 $$ LANGUAGE plpgsql;

到目前为止，我们编写的是

 SELECT test(1,'foo');
 test          
 -------------------------
 a is 1 and b is foo
 (1 row)

现在可以使用此显式语法

 SELECT test( b:='foo', a:=1);
 test          
 -------------------------
 a is 1 and b is foo
 (1 row)

命名参数应该消除编写许多重载的“包装器”函数的需要。请注意，这确实会增加向后兼容性问题；你不再能够使用 REPLACE 命令重命名函数参数，而现在必须删除并重新创建函数。

ALIAS 关键字

现在可以使用 ALIAS。顾名思义，它可以用于将变量名别名为其他名称。

语法是new_name ALIAS FOR old_name。这将放在 PL/pgSQL 代码的 DECLARE 部分。

它有两个主要用例

为 PL 函数变量命名

 myparam ALIAS FOR $0

重命名可能冲突的变量。例如，在一个触发器中

 new_value ALIAS FOR new

（如果没有它，我们可能会与触发器函数中的 NEW 变量冲突）。

高级功能

PostgreSQL 中的一些功能是尖端的数据库功能，基本上是“PostgreSQL 独有的”。这就是为什么我们是“最先进的数据库”。这些功能支持新型应用程序。

排除约束

排除约束与唯一约束非常相似。它们可以被视为使用除 '=' 之外的其他运算符的唯一约束：唯一约束定义一组列，表中的两条记录不能在这些列上相同。

为了说明这一点，我们将使用该功能作者提供的示例，使用他开发的 temporal 数据类型。这种数据类型存储时间范围，即 '从 10:15 到 11:15 的时间范围'。

首先，我们需要在这里检索 temporal 模块：http://pgfoundry.org/projects/temporal/ ，然后将其编译并安装为 contrib（运行提供的 sql 脚本）。我们可能还需要安装 btree_gist 模块作为 contrib。从源代码中，可以在 contrib/btree_gist 目录中运行 'make install' 以执行相同的操作。

 CREATE TABLE reservation
 (
   room      TEXT,
   professor TEXT,
   during    PERIOD);

 ALTER TABLE reservation ADD CONSTRAINT test_exclude EXCLUDE   USING gist (room WITH =,during WITH &&);

通过这样做，我们声明如果已经存在一条记录验证了两个条件 '相同的房间' 和 '在时间范围的交集内'（&& 运算符），则应该拒绝该记录（排除约束）。

 marc=# INSERT INTO reservation (professor,room,during) VALUES ( 'mark', 'tech room', period('2010-06-16 09:00:00', '2010-06-16 10:00:00'));
 INSERT 0 1
 marc=# INSERT INTO reservation (professor,room,during) VALUES ( 'john', 'chemistry room', period('2010-06-16 09:00:00', '2010-06-16 11:00:00'));
 INSERT 0 1
 marc=# INSERT INTO reservation (professor,room,during) VALUES ( 'mark', 'chemistry room', period('2010-06-16 10:00:00', '2010-06-16 11:00:00'));
 ERROR:  conflicting key value violates exclusion constraint "test_exclude"
 DETAIL:  Key (room, during)=(chemistry room, [2010-06-16 10:00:00+02, 2010-06-16 11:00:00+02)) conflicts with existing key (room, during)=(chemistry room, [2010-06-16 09:00:00+02, 2010-06-16 11:00:00+02)).

插入被禁止，因为化学实验室已经从 9 点到 11 点被预订。

排除约束也可以与数组、地理数据或其他非标量数据一起使用，以实现高级科学和日历应用程序。没有其他数据库系统具有此功能。

在 NOTIFY/pg_notify 中传递消息

现在可以使用 NOTIFY 传递消息。以下是如何操作

在会话 1 中订阅 'instant_messenging' 队列。

会话 1

 marc=# LISTEN instant_messenging;
 LISTEN

从另一个会话发送一个通知到 'instant_messenging'。

会话 2

 marc=# NOTIFY instant_messenging, 'You just received a message';
 NOTIFY

检查第一个会话中队列的内容

会话 1

 marc=# LISTEN instant_messenging;
 LISTEN
 Asynchronous notification "instant_messenging" with payload "You just received a message" received from server process with PID 5943.

因此，我们现在可以将消息（有效负载）与通知关联起来，使 NOTIFY 更加有用。

我们还要提到新的 pg_notify 函数。有了它，第二个会话的代码也可以是

 SELECT pg_notify('instant_messenging','You just received a message');

这可以简化一些代码，例如，在一个管理许多不同队列的程序中。

Hstore contrib 增强功能

这个已经非常强大的 contrib 模块变得更加强大

已删除键和值的大小限制。
现在可以使用 GROUP BY 和 DISTINCT。
添加了新的运算符和函数。

举个例子太长了，这个模块有很多功能。请立即阅读文档！

无重音过滤字典

现在可以设置过滤字典。这是关于全文搜索字典的。

这些字典的目的是在对单词进行词素分析之前，对其应用第一个过滤器。这里介绍的模块是第一个使用这种机制的模块。过滤可以包括删除单词或修改单词。

Unaccent 不会删除单词，它会删除重音（实际上是所有变音符号），用非重音字符替换重音字符（至少在法语中，许多人不会输入重音字符）。Unaccent 是一个 contrib 模块。

安装它，就像所有 contrib 模块一样，非常简单，只需要执行以下操作

 psql mydb < contribs_path/unaccent.sql.

我们现在将遵循 unaccent 的文档，该示例将过滤法语单词。

让我们创建一个新的 'fr' 字典（保持标准的 'french' 字典干净）。

 marc=# CREATE TEXT SEARCH CONFIGURATION fr ( COPY = french );
 CREATE TEXT SEARCH CONFIGURATION

下一条语句更改了 'fr' 对单词和类似词素的设置。现在，它们必须经过 unaccent 和 french_stem，而不仅仅是 french_stem。

 marc=# ALTER TEXT SEARCH CONFIGURATION fr
 >ALTER MAPPING FOR hword, hword_part, word
 >WITH unaccent, french_stem;
 >ALTER TEXT SEARCH CONFIGURATION
 
 SELECT to_tsvector('fr','Hôtels de la Mer');
 to_tsvector   
 -------------------
 'hotel':1 'mer':4
 (1 row)
 
 marc=# SELECT to_tsvector('fr','Hôtel de la Mer') @@ to_tsquery('fr','Hotels');
 ?column?
 ----------
 t
 (1 row)

现在很容易，即使在客户端应用程序中不更改一行代码，并且在数据库中保留重音字符，也可以在不考虑重音的情况下查找单词。

用于位字符串的 get_bit 和 set_bit

这是一个非常简单的示例。此工具可以独立地操作 bit() 中的位。

 marc=# SELECT set_bit('1111'::bit(4),2,0);
 set_bit
 ---------
 1101
 (1 row)

 marc=# SELECT get_bit('1101'::bit(4),2);
 get_bit
 ---------
       0
 (1 row)

向后兼容性和升级问题

PostgreSQL 项目承诺在可能的情况下不破坏向后兼容性。但是，有时我们必须破坏某些东西才能添加新功能或修复长期存在的问题行为。下面记录了其中的一些问题。

可能导致回归的 PL/pgSQL 更改

PL/pgSQL 中有两个更改可能会破坏在 8.4 或更早版本中工作的代码，这意味着在迁移到 9.0 之前应该审核 PL/pgSQL 函数以防止可能的运行时错误。其中许多是由于将 SQL 和 PL/pgSQL 的词法分析器统一起来造成的，这是一个重要的体系结构改进，它使几个新功能成为可能。

删除列/变量名称歧义

在 8.4 和更早版本中，PL/PgSQL 变量将优先于具有相同名称的表或视图列。虽然这种行为是一致的，但它可能是编码错误的潜在来源。9.0 会在发生这种情况时抛出运行时错误。

 marc=# DO LANGUAGE plpgsql
 $$
 DECLARE
   a int;
 BEGIN
   SELECT a FROM test;
 END
 $$
 ;
 ERROR:  column reference "a" is ambiguous
 LINE 1: select a from test
 DETAIL:  It could refer to either a PL/pgSQL variable or a table column.
 QUERY:  select a from test
 CONTEXT:  PL/pgSQL function "inline_code_block" line 4 at SQL statement

可以在 postgresql.conf 中全局更改此行为，或者通过在函数声明中插入以下三个选项之一来在每个函数的基础上进行更改。

 #variable_conflict error        (default)
 #variable_conflict use_variable (variable name name takes precedence - pre-9.0 behaviour)
 #variable_conflict use_column   (column name takes precedence)

手册包含更多详细信息。

保留字

从 9.0 开始，不再允许使用未加引号的保留字作为 PL/PgSQL 变量名称。

 marc=# DO LANGUAGE plpgsql
 $$
 DECLARE
 table int;
 BEGIN
 table :=table+1;
 END
 $$
 ;
 ERROR:  syntax error at or near "table"
 LINE 6: table :=table+1;

正确的语法是

 marc=# DO LANGUAGE plpgsql
 $$
 DECLARE
 "table" int;
 BEGIN
 "table" :="table"+1;
 END
 $$
 ;
 DO

当然，最佳实践是完全避免使用保留字。

PostgreSQL 9.0 新特性

不容错过的两个特性

热备

流式复制

其他新特性

安全和认证

SCHEMA 中的 GRANT/REVOKE

ALTER DEFAULT PRIVILEGES

passwordcheck

SQL 特性

列触发器

WHEN 触发器

可延迟唯一约束

窗口函数的新框架选项

聚合中的排序

数据库管理

更好的 VACUUM FULL

pg_stat_activity 中的 application_name

按数据库+角色配置

记录 postgresql.conf 重新加载时所有已更改的参数

更好的唯一约束错误消息

vacuumdb --analyze-only

性能

适用于 Windows 的 64 位二进制文件

联接去除

IS NOT NULL 现在可以使用索引

使用索引来获取更好的动态统计信息

解释缓冲区、哈希统计、xml、json、yaml、新的可选解释语法

每个表空间的 seq_page_cost/random_page_cost

对列强制使用不同的统计信息

由 auto_explain 记录的语句

针对 pg_stat_statements 的缓冲区统计

存储过程

默认情况下使用 PL/pgSQL

对 PL 语言的许多改进。

匿名函数（又名匿名块）

命名参数调用

ALIAS 关键字

高级功能

排除约束

在 NOTIFY/pg_notify 中传递消息

Hstore contrib 增强功能

无重音过滤字典

用于位字符串的 get_bit 和 set_bit

向后兼容性和升级问题

可能导致回归的 PL/pgSQL 更改

删除列/变量名称歧义

保留字

导航菜单

搜索