PostgreSQL 9.0 新功能

来自 PostgreSQL wiki
跳转到导航跳转到搜索

本文档展示了 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 在此领域添加了一些新功能。

GRANT/REVOKE IN SCHEMA

PostgreSQL 中的一个恼人的限制是缺乏全局 GRANT/REVOKE 功能。使用 9.0，现在可以设置对模式内所有表、序列和函数的权限，而无需编写脚本或存储过程

 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' 表被更新时触发。

如果列设置为 DEFAULT，则不会执行列触发器。

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。它们使编写某些类型的查询变得容易得多。

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

 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 的记录不用于其平均值）。

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

 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' 列存在唯一约束。在这种情况下，理论上讲，对 t3 的连接毫无用处：返回的行数不会改变，它们的内容也不会改变。这是因为该列是唯一的，连接是 LEFT JOIN，并且没有检索到 t3 的任何列。如果该列不是唯一的，连接可能会带来更多行。如果不是 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 使用索引条件）。在这个具体的用例中，收益非常大。

使用索引即时获取更好的统计信息

在开始解释此新功能之前，让我们谈谈直方图：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 自行估计不同值）。

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

由 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 后，现在可以回答以下问题

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

'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 独有的”。这就是我们成为“最先进的数据库”的原因。这些功能使新型应用程序成为可能。

排他约束

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

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

首先，我们需要在此处检索时间模块：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。
  • 添加了新的运算符和函数。

一个示例会花费太长时间，这个模块有许多功能。立即阅读文档！

去重过滤字典

现在可以设置过滤字典。这与全文搜索字典有关。

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

去重不会删除词语，而是删除重音（实际上是所有变音符号），将重音字符替换为非重音字符（至少在法语中，许多人不打它们）。去重是一个 contrib 模块。

安装它（与所有 contrib 模块一样）就像

 psql mydb < contribs_path/unaccent.sql.

我们现在将遵循去重文档，示例是过滤法语单词。

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

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

下一条语句更改了 'fr' 的设置，以用于词语和类似词形。这些词语现在必须经过去重和 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

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

检索自 "https://wiki.postgresql.ac.cn/index.php?title=What%27s_new_in_PostgreSQL_9.0&oldid=22182"