PostgreSQL 9.2 新特性
本文档展示了 PostgreSQL 9.2 中相较于上一个主要版本 PostgreSQL 9.1 的许多最新改进。此版本有许多改进，因此本 Wiki 页面详细介绍了其中一些最重要的更改。完整的更改列表在“发布说明”中列出。
主要新特性
索引仅扫描
在 PostgreSQL 中，索引没有“可见性”信息。这意味着当您通过索引访问记录时，PostgreSQL 必须访问表中的实际元组才能确保它对您可见：索引指向的元组可能只是您要查找的记录的旧版本。
这可能是一个非常大的性能问题：索引大多是排序的，因此访问它的记录非常高效，而记录可能分散在各处（这就是 PostgreSQL 有集群命令的原因，但那是另一回事）。在 9.2 中，PostgreSQL 将尽可能使用“索引仅扫描”，如果不需要，则不会访问记录本身。
索引中仍然没有可见性信息。因此，为了做到这一点，PostgreSQL 使用可见性映射（可见性映射），它告诉 PostgreSQL 整个（通常为）8K 页面内容是否对所有事务可见。当索引记录指向包含在“全部可见”页面中的元组时，PostgreSQL 不必访问元组，它可以直接从索引中构建它。当然，查询请求的所有列都必须在索引中。
可见性映射由 VACUUM 维护（它设置可见位），以及执行 SQL 工作的后端（它们取消设置可见位）。
如果数据自上次 VACUUM 以来只读，则数据是全部可见的，并且索引仅扫描功能可以提高性能。
以下是一个示例。
CREATE TABLE demo_ios (col1 float, col2 float, col3 text);
在这个表中，我们将放入随机数据，以便拥有“分散”数据。我们将放入 1 亿条记录，以便拥有一个大型记录集，并且使其不适合内存（这台机器有 4GB 内存）。这是一个理想的情况，为演示而制作。在现实生活中，收益不会那么大。
INSERT INTO demo_ios SELECT generate_series(1,100000000),random(), 'mynotsolongstring';
SELECT pg_size_pretty(pg_total_relation_size('demo_ios'));
pg_size_pretty
----------------
6512 MB
假设查询是这样的
SELECT col1,col2 FROM demo_ios where col2 BETWEEN 0.01 AND 0.02
为了在这个查询上使用索引仅扫描，我们需要在 col2、col1 上创建一个索引（col2 放在前面，因为它在 WHERE 子句中使用）。
CREATE index idx_demo_ios on demo_ios(col2,col1);
我们对表进行真空清理，以便可见性映射保持最新
VACUUM demo_ios;
下面您将看到的所有计时都在冷操作系统和 PostgreSQL 缓存上完成（这就是收益所在，因为索引仅扫描的目的是减少 I/O）。
首先让我们尝试不使用索引仅扫描
SET enable_indexonlyscan to off;
EXPLAIN (analyze,buffers) select col1,col2 FROM demo_ios where col2 between 0.01 and 0.02;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on demo_ios (cost=25643.01..916484.44 rows=993633 width=16) (actual time=763.391..362963.899 rows=1000392 loops=1)
Recheck Cond: ((col2 >= 0.01::double precision) AND (col2 <= 0.02::double precision))
Rows Removed by Index Recheck: 68098621
Buffers: shared hit=2 read=587779
-> Bitmap Index Scan on idx_demo_ios (cost=0.00..25394.60 rows=993633 width=0) (actual time=759.011..759.011 rows=1000392 loops=1)
Index Cond: ((col2 >= 0.01::double precision) AND (col2 <= 0.02::double precision))
Buffers: shared hit=2 read=3835
Total runtime: 364390.127 ms
使用索引仅扫描
explain (analyze,buffers) select col1,col2 from demo_ios where col2 between 0.01 and 0.02;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using idx_demo_ios on demo_ios (cost=0.00..35330.93 rows=993633 width=16) (actual time=58.100..3250.589 rows=1000392 loops=1)
Index Cond: ((col2 >= 0.01::double precision) AND (col2 <= 0.02::double precision))
Heap Fetches: 0
Buffers: shared hit=923073 read=3848
Total runtime: 4297.405 ms
由于没有免费的东西，所以要记住几件事
- 为索引仅扫描添加索引显然会为您的表添加索引。因此更新会变慢。
- 您将为以前没有索引的列创建索引。因此，将会有更少的 HOT 更新机会。
- 在现实生活中的场景中，收益可能更小，尤其是在 VACUUM 之间更改数据时。
这需要对可见性映射更改进行崩溃安全处理，因此可见性映射位更改现在记录在 WAL 中。
复制改进
流式复制在此版本中更加完善。
关于流式复制的主要抱怨之一是，所有从属服务器都必须连接到同一个唯一的主服务器，并消耗其资源。此外，如果发生故障转移，如果用户没有使用 repmgr 之类的工具，则可能难以将所有剩余的从属服务器重新连接到新提升的主服务器。
在 9.2 中，备用服务器也可以发送复制更改，允许级联复制。
让我们构建它。我们从一个已经运行的 9.2 数据库开始。
我们将其设置为复制
postgresql.conf
wal_level=hot_standby #(could be archive too) max_wal_senders=5 hot_standby=on
您可能还想在生产环境中激活归档，在这里不会这样做。
pg_hba.conf（在生产环境中不要使用信任）
host replication replication_user 0.0.0.0/0 md5
创建用户
create user replication_user replication password 'secret';
克隆集群
pg_basebackup -h localhost -U replication_user -D data2 Password:
我们有一个全新的集群在 data2 目录中。我们将更改端口，以便它可以启动（postgresql.conf），因为两个集群都在同一台机器上运行
port=5433
我们在 data2 目录中创建一个 recovery.conf 文件，告诉它如何从主数据库中流式传输
standby_mode = on primary_conninfo = 'host=localhost port=5432 user=replication_user password=secret'
pg_ctl -D data2 start server starting LOG: database system was interrupted; last known up at 2012-07-03 17:58:09 CEST LOG: creating missing WAL directory "pg_xlog/archive_status" LOG: entering standby mode LOG: streaming replication successfully connected to primary LOG: redo starts at 0/9D000020 LOG: consistent recovery state reached at 0/9D0000B8 LOG: database system is ready to accept read only connections
现在，让我们添加第二个从属服务器，它将使用这个从属服务器
pg_basebackup -h localhost -U replication_user -D data3 -p 5433 Password:
我们编辑 data3 的 postgresql.conf 以更改端口
port=5434
我们将 recovery.conf 修改为从从属服务器流式传输
standby_mode = on primary_conninfo = 'host=localhost port=5433 user=replication_user password=secret' # e.g. 'host=localhost port=5432'
我们启动第三个集群
pg_ctl -D data3 start server starting LOG: database system was interrupted while in recovery at log time 2012-07-03 17:58:09 CEST HINT: If this has occurred more than once some data might be corrupted and you might need to choose an earlier recovery target. LOG: creating missing WAL directory "pg_xlog/archive_status" LOG: entering standby mode LOG: streaming replication successfully connected to primary LOG: redo starts at 0/9D000020 LOG: consistent recovery state reached at 0/9E000000 LOG: database system is ready to accept read only connections
现在，主集群上修改的所有内容都将流式传输到第一个从属服务器，然后从第一个从属服务器流式传输到第二个从属服务器。这个第二个复制必须从第一个从属服务器监控（主服务器对此一无所知）。
正如您可能从示例中注意到的，pg_basebackup 现在可以从从属服务器工作。
还有一个用例没有涵盖：如果用户不关心拥有一个完整的从属服务器，而只想将 WAL 文件流式传输到另一个位置，以从减少的数据丢失中受益，而无需维护从属服务器的负担呢？
pg_receivexlog 就是为了这个目的提供的：它假装是一个 PostgreSQL 从属服务器，但只将日志文件存储在目录中，就像它们被流式传输一样。
pg_receivexlog -D /tmp/new_logs -h localhost -U replication_user
将连接到主服务器（或从属服务器），并开始创建文件
ls /tmp/new_logs/ 00000001000000000000009E.partial
文件的大小与段大小相同，因此它们可以用于数据库的正常恢复。它与归档命令相同，但粒度更细。
请记住，在使用 PITR 恢复或任何其他操作之前，将最后一个段重命名以删除 .partial 后缀。
synchronous_commit 参数有一个新的值：remote_write。当有一个同步从属服务器时可以使用它（synchronous_standby_names 设置），这意味着主服务器不必等待从属服务器将数据写入磁盘，只需要等待从属服务器确认数据即可。设置了此参数后，数据可以防止主服务器崩溃，但如果从属服务器在同一时间崩溃（即在将飞行中的数据写入磁盘之前）仍然会丢失。由于这是一种非常不可能的情况，并且性能改进将很大，因此有些人会对这种折衷方案感兴趣。
JSON 数据类型
JSON 数据类型用于存储 JSON 结构化数据。它将验证输入 JSON 字符串是否为正确的 JSON。
=# SELECT '{"username":"john","posts":121,"emailaddress":"[email protected]"}'::json;
json
-------------------------------------------------------------------
{"username":"john","posts":121,"emailaddress":"[email protected]"}
(1 row)
=# SELECT '{"username","posts":121,"emailaddress":"[email protected]"}'::json;
ERROR: invalid input syntax for type json at character 8
DETAIL: Expected ":", but found ",".
CONTEXT: JSON data, line 1: {"username",...
STATEMENT: SELECT '{"username","posts":121,"emailaddress":"[email protected]"}'::json;
ERROR: invalid input syntax for type json
LINE 1: SELECT '{"username","posts":121,"emailaddress":"john@nowhere...
^
DETAIL: Expected ":", but found ",".
CONTEXT: JSON data, line 1: {"username",...
您也可以将行类型转换为 JSON
=#SELECT * FROM demo ; username | posts | emailaddress ----------+-------+--------------------- john | 121 | [email protected] mickael | 215 | [email protected] (2 rows) =# SELECT row_to_json(demo) FROM demo; row_to_json ------------------------------------------------------------------------- {"username":"john","posts":121,"emailaddress":"[email protected]"} {"username":"mickael","posts":215,"emailaddress":"[email protected]"} (2 rows)
或数组类型
=# select array_to_json(array_agg(demo)) from demo;
array_to_json
---------------------------------------------------------------------------------------------------------------------------------------------
[{"username":"john","posts":121,"emailaddress":"[email protected]"},{"username":"mickael","posts":215,"emailaddress":"[email protected]"}]
(1 row)
范围类型
范围类型用于存储给定类型的范围数据。有一些预定义的类型。它们是整数 (int4range)、大整数 (int8range)、数值 (numrange)、无时区时间戳 (tsrange)、有时区时间戳 (tstzrange) 和日期 (daterange)。
范围可以由连续（数值、时间戳...）或离散（整数、日期...）数据类型构成。它们可以是开放的（边界不属于范围）或封闭的（边界属于范围）。边界也可以是无限的。
如果没有这些数据类型，大多数人通过在表中使用两列来解决范围问题。这些范围类型要强大得多，因为您可以在它们上使用许多运算符。
以下是 1000（开放）-2000（封闭）和 1000（封闭）-1200（封闭）数值范围之间的交集
SELECT '(1000,2000]'::numrange * '[1000,1200]'::numrange; ?column? ------------- (1000,1200] (1 row)
因此，您可以查询诸如“给我所有与该范围相交的范围”之类的东西
=# SELECT * from test_range ;
period
-----------------------------------------------------
["2012-01-01 00:00:00+01","2012-01-02 12:00:00+01"]
["2012-01-01 00:00:00+01","2012-03-01 00:00:00+01"]
["2008-01-01 00:00:00+01","2015-01-01 00:00:00+01"]
(3 rows)
=# SELECT * FROM test_range WHERE period && '[2012-01-03 00:00:00,2012-01-03 12:00:00]';
period
-----------------------------------------------------
["2012-01-01 00:00:00+01","2012-03-01 00:00:00+01"]
["2008-01-01 00:00:00+01","2015-01-01 00:00:00+01"]
(2 rows)
此查询可以使用以下定义的索引
=# CREATE INDEX idx_test_range on test_range USING gist (period);
您也可以使用这些范围数据类型来定义排除约束
CREATE EXTENSION btree_gist ; CREATE TABLE reservation (room_id int, period tstzrange); ALTER TABLE reservation ADD EXCLUDE USING GIST (room_id WITH =, period WITH &&);
这意味着现在禁止在此表中拥有两条记录，其中 room_id 相等且 period 重叠。需要 btree_gist 扩展才能在 room_id 上创建 GiST 索引（它是一个整数，通常使用 btree 索引进行索引）。
=# INSERT INTO reservation VALUES (1,'(2012-08-23 14:00:00,2012-08-23 15:00:00)');
INSERT 0 1
=# INSERT INTO reservation VALUES (2,'(2012-08-23 14:00:00,2012-08-23 15:00:00)');
INSERT 0 1
=# INSERT INTO reservation VALUES (1,'(2012-08-23 14:45:00,2012-08-23 15:15:00)');
ERROR: conflicting key value violates exclusion constraint "reservation_room_id_period_excl"
DETAIL: Key (room_id, period)=(1, ("2012-08-23 14:45:00+02","2012-08-23 15:15:00+02"))
conflicts with existing key (room_id, period)=(1, ("2012-08-23 14:00:00+02","2012-08-23 15:00:00+02")).
STATEMENT: INSERT INTO reservation VALUES (1,'(2012-08-23 14:45:00,2012-08-23 15:15:00)');
还可以声明新的范围类型。
性能改进
此版本对非常广泛的领域（非详尽）进行了性能改进
- 最显而易见的大概是索引仅扫描，这已经在本文档中介绍过了。
- 几个大型锁的锁争用已显著减少，从而导致更好的多处理器可扩展性，主要针对具有超过 32 个内核的机器。
- 内存排序的性能在某些情况下提高了高达 25%，其中引入了某些专门的排序函数。
- 空闲的 PostgreSQL 服务器现在产生的唤醒次数更少，从而降低了功耗。这在虚拟化和嵌入式环境中尤其有用。
- COPY 已得到改进，它将生成更少的 WAL 卷和更少的表页面的锁定。
- 收集有关数组内容的统计信息，以便更好地估计数组操作的选择性。
- 文本到任何类型连接和 quote_literal/quote_nullable 函数不再是易变的，从而在某些情况下可以更好地进行优化
- 系统现在可以跟踪 IO 时长
这个需要解释一下，因为它可能有点复杂。跟踪 IO 持续时间意味着反复向操作系统询问时间。根据操作系统和硬件的不同，这可能非常便宜，也可能非常昂贵。这里最重要的因素是系统从哪里获取时间。它可以从处理器 (TSC) 直接获取，也可以从专用硬件（如 HPET）或 ACPI 调用获取。最重要的是获取时间的成本可能相差数千倍。
如果您对该计时数据感兴趣，最好先检查一下您的系统是否支持它，而不会造成太多性能损失。PostgreSQL 为您提供了 pg_test_timing 工具。
$ pg_test_timing
Testing timing overhead for 3 seconds.
Per loop time including overhead: 28.02 nsec
Histogram of timing durations:
< usec: count percent
32: 41 0.00004%
16: 1405 0.00131%
8: 200 0.00019%
4: 388 0.00036%
2: 2982558 2.78523%
1: 104100166 97.21287%
这里一切正常：获取时间大约需要 28 纳秒，并且变化非常小。任何低于 100 纳秒的值都应该适合生产环境。如果得到更高的值，您仍然可以找到调整系统的方法。您最好查看 文档。
无论如何，以下是在您的系统已准备好接受此数据的情况下，您将能够收集的数据。
首先，您将获得每个数据库的统计信息，现在这些统计信息将提供有关哪个数据库执行了最多 IO 的准确信息。
=# SELECT * FROM pg_stat_database WHERE datname = 'mydb'; -[ RECORD 1 ]--+------------------------------ datid | 16384 datname | mydb numbackends | 1 xact_commit | 270 xact_rollback | 2 blks_read | 1961 blks_hit | 17944 tup_returned | 269035 tup_fetched | 8850 tup_inserted | 16 tup_updated | 4 tup_deleted | 45 conflicts | 0 temp_files | 0 temp_bytes | 0 deadlocks | 0 blk_read_time | 583.774 blk_write_time | 0 stats_reset | 2012-07-03 17:18:54.796817+02
我们看到这里 mydb 只消耗了 583.774 毫秒的读取时间。
Explain 也将从此中受益。
=# EXPLAIN (analyze,buffers) SELECT count(*) FROM mots ;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Aggregate (cost=1669.95..1669.96 rows=1 width=0) (actual time=21.943..21.943 rows=1 loops=1)
Buffers: shared read=493
I/O Timings: read=2.578
-> Seq Scan on mots (cost=0.00..1434.56 rows=94156 width=0) (actual time=0.059..12.933 rows=94156 loops=1)
Buffers: shared read=493
I/O Timings: read=2.578
Total runtime: 22.059 ms
我们现在有了关于从操作系统检索数据所花费时间的单独信息。显然，在这里，数据位于操作系统的缓存中（读取 493 个块需要 2 毫秒）。
最后，如果您启用了 pg_stat_statements。
select * from pg_stat_statements where query ~ 'words'; -[ RECORD 1 ]-------+--------------------------- userid | 10 dbid | 16384 query | select count(*) from words; calls | 2 total_time | 78.332 rows | 2 shared_blks_hit | 0 shared_blks_read | 986 shared_blks_dirtied | 0 shared_blks_written | 0 local_blks_hit | 0 local_blks_read | 0 local_blks_dirtied | 0 local_blks_written | 0 temp_blks_read | 0 temp_blks_written | 0 blk_read_time | 58.427 blk_write_time | 0
- 对于每个版本，优化器都得到了其份额的改进。
- 准备好的语句过去曾经只优化一次，没有任何关于参数值的信息。在 9.2 中，规划器将根据发送的参数使用特定的计划（查询将在执行时规划），除非查询执行多次，并且规划器决定通用计划并不比特定计划贵太多。
- 添加了一个新功能：参数化路径。简单来说，这意味着查询计划的子部分可以使用从父节点获得的参数。它修复了可能发生的几个错误计划，尤其是在优化器无法重新排序联接以将嵌套循环放在它本来会很高效的位置时。
此示例直接来自开发人员邮件列表。
CREATE TABLE a (
a_id serial PRIMARY KEY NOT NULL,
b_id integer
);
CREATE INDEX a__b_id ON a USING btree (b_id);
CREATE TABLE b (
b_id serial NOT NULL,
c_id integer
);
CREATE INDEX b__c_id ON b USING btree (c_id);
CREATE TABLE c (
c_id serial PRIMARY KEY NOT NULL,
value integer UNIQUE
);
INSERT INTO b (b_id, c_id)
SELECT g.i, g.i FROM generate_series(1, 50000) g(i);
INSERT INTO a(b_id)
SELECT g.i FROM generate_series(1, 50000) g(i);
INSERT INTO c(c_id,value)
VALUES (1,1);
所以我们有一个引用 b，b 引用 c。
以下是在 PostgreSQL 9.1 中查询效果不佳的示例。
EXPLAIN ANALYZE SELECT 1
FROM
c
WHERE
EXISTS (
SELECT *
FROM a
JOIN b USING (b_id)
WHERE b.c_id = c.c_id)
AND c.value = 1;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Nested Loop Semi Join (cost=1347.00..3702.27 rows=1 width=0) (actual time=13.799..13.802 rows=1 loops=1)
Join Filter: (c.c_id = b.c_id)
-> Index Scan using c_value_key on c (cost=0.00..8.27 rows=1 width=4) (actual time=0.006..0.008 rows=1 loops=1)
Index Cond: (value = 1)
-> Hash Join (cost=1347.00..3069.00 rows=50000 width=4) (actual time=13.788..13.788 rows=1 loops=1)
Hash Cond: (a.b_id = b.b_id)
-> Seq Scan on a (cost=0.00..722.00 rows=50000 width=4) (actual time=0.007..0.007 rows=1 loops=1)
-> Hash (cost=722.00..722.00 rows=50000 width=8) (actual time=13.760..13.760 rows=50000 loops=1)
Buckets: 8192 Batches: 1 Memory Usage: 1954kB
-> Seq Scan on b (cost=0.00..722.00 rows=50000 width=8) (actual time=0.008..5.702 rows=50000 loops=1)
Total runtime: 13.842 ms
还不算太差，13 毫秒。尽管如此，我们仍在 a 和 b 上进行顺序扫描，而我们的常识告诉我们 c.value=1 应该用于更积极地过滤行。
以下是 9.2 对此查询的处理方式。
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Nested Loop Semi Join (cost=0.00..16.97 rows=1 width=0) (actual time=0.035..0.037 rows=1 loops=1)
-> Index Scan using c_value_key on c (cost=0.00..8.27 rows=1 width=4) (actual time=0.007..0.009 rows=1 loops=1)
Index Cond: (value = 1)
-> Nested Loop (cost=0.00..8.69 rows=1 width=4) (actual time=0.025..0.025 rows=1 loops=1)
-> Index Scan using b__c_id on b (cost=0.00..8.33 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=1)
Index Cond: (c_id = c.c_id)
-> Index Only Scan using a__b_id on a (cost=0.00..0.35 rows=1 width=4) (actual time=0.014..0.014 rows=1 loops=1)
Index Cond: (b_id = b.b_id)
Total runtime: 0.089 ms
«参数化路径»是
-> Nested Loop (cost=0.00..8.69 rows=1 width=4) (actual time=0.025..0.025 rows=1 loops=1)
-> Index Scan using b__c_id on b (cost=0.00..8.33 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=1)
Index Cond: (c_id = c.c_id)
-> Index Only Scan using a__b_id on a (cost=0.00..0.35 rows=1 width=4) (actual time=0.014..0.014 rows=1 loops=1)
Index Cond: (b_id = b.b_id)
Total runtime: 0.089 ms
计划的这部分取决于父节点 (c_id=c.c_id)。计划的这部分每次都会使用来自父节点的不同参数调用。
当然，这个计划快得多，因为没有必要完全扫描 a，也没有必要完全扫描和散列 b。
SP-GiST
SP-GiST 代表空间分区 GiST，GiST 代表广义搜索树。GiST 是一种索引类型，在 PostgreSQL 中已经可以使用相当一段时间了。GiST 在索引复杂数据类型方面已经非常高效，但在源数据分布不均匀时，性能往往会下降。SP-GiST 试图解决这个问题。
与 PostgreSQL 中可用的所有索引方法一样，SP-GiST 是一种通用的索引方法，这意味着它的目的是索引您扔给它的任何东西，使用您提供的运算符。这意味着如果您想创建一种新的数据类型，并使其通过 SP-GiST 可索引，则必须遵循记录的 API。
SP-GiST 可用于实现 3 种类型的索引：trie（后缀）索引、四叉树（数据被划分为象限）和 k-d 树（k 维树）。
目前，SP-GiST 提供了名为“quad_point_ops”、“kd_point_ops”和“text_ops”的运算符族。
顾名思义，第一个索引点类型，使用四叉树，第二个索引点类型使用 k-d 树，第三个索引文本，使用后缀。
pg_stat_statements
此 contrib 模块在这个版本中得到了很多改进。
- 查询被规范化：除了常数值以外，其他完全相同的查询将被视为相同，只要它们的解析后分析查询树（即，在规则扩展之前查询的内部表示）相同即可。这也意味着对查询在语义上不重要的差异，例如空格或别名名称的变化，或使用一种特定语法而不是另一种等效语法，将不会区分查询。
=#SELECT * FROM words WHERE word= 'foo'; word ------ (0 ligne) =# SELECT * FROM words WHERE word= 'bar'; word ------ bar =#select * from pg_stat_statements where query like '%words where%'; -[ RECORD 1 ]-------+----------------------------------- userid | 10 dbid | 16384 query | SELECT * FROM words WHERE word= ?; calls | 2 total_time | 142.314 rows | 1 shared_blks_hit | 3 shared_blks_read | 5 shared_blks_dirtied | 0 shared_blks_written | 0 local_blks_hit | 0 local_blks_read | 0 local_blks_dirtied | 0 local_blks_written | 0 temp_blks_read | 0 temp_blks_written | 0 blk_read_time | 142.165 blk_write_time | 0
这两个查询在 pg_stat_statements 中显示为一个。
- 对于准备好的语句，执行部分（执行语句）将被计入准备语句。这使得它更容易解释，并避免了 PostgreSQL 9.1 中存在的双重计数问题。
- pg_stat_statements 以毫秒为单位显示计时，以与其他系统视图保持一致。
Explain 改进
- 现在可以使用 EXPLAIN（analyze on，timing off）禁用计时，从而在获取当前时间成本很高的平台上降低开销。
=# EXPLAIN (analyze on,timing off) SELECT * FROM reservation ;
QUERY PLAN
----------------------------------------------------------------------------------------
Seq Scan on reservation (cost=0.00..22.30 rows=1230 width=36) (actual rows=2 loops=1)
Total runtime: 0.045 ms
- 让 EXPLAIN ANALYZE 报告过滤器步骤拒绝的行数。
这个新功能使了解有多少行被过滤器删除（以及发现可能放置索引的位置）变得容易得多。
=# EXPLAIN ANALYZE SELECT * FROM test WHERE a ~ 'tra';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------
Seq Scan on test (cost=0.00..106876.56 rows=2002 width=11) (actual time=2.914..8538.285 rows=120256 loops=1)
Filter: (a ~ 'tra'::text)
Rows Removed by Filter: 5905600
Total runtime: 8549.539 ms
(4 rows)
向后兼容性
这些更改可能会导致您的应用程序出现回归。
确保 xpath() 对字符串值中的特殊字符进行转义
在 9.2 之前
SELECT (XPATH('/*/text()', '<root><</root>'))[1];
xpath
-------
<
'<' Isn't valid XML.
在 9.2 中
SELECT (XPATH('/*/text()', '<root><</root>'))[1];
xpath
-------
<
删除 hstore 的 => 运算符
直到 9.1，人们可以使用 => 运算符创建 hstore。Hstore 是一种 contrib，用于在列中存储键值对。
在 9.1 中
=# SELECT 'a'=>'b';
?column?
----------
"a"=>"b"
(1 row)
=# SELECT pg_typeof('a'=>'b');
pg_typeof
-----------
hstore
(1 row)
在 9.2 中
SELECT 'a'=>'b';
ERROR: operator does not exist: unknown => unknown at character 11
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
STATEMENT: SELECT 'a'=>'b';
ERROR: operator does not exist: unknown => unknown
LINE 1: SELECT 'a'=>'b';
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
这并不意味着不能在 hstore 中使用 '=>'，它只是不再是运算符了。
=# select hstore('a=>b');
hstore
----------
"a"=>"b"
(1 row)
=# select hstore('a','b');
hstore
----------
"a"=>"b"
(1 row)
仍然是输入 hstore 的两种有效方式。
“=>” 作为运算符被删除，因为它在 SQL 中是保留字。
如果对象不存在，则让 pg_relation_size() 及其朋友返回 NULL
在某人对关系进行 pg_relation_size 操作时，可能会有并发会话删除该关系，从而导致 SQL 异常。现在，它只是为此记录返回 NULL。
从 pg_tablespace 中删除 spclocation 字段。
spclocation 字段提供了表空间的实际位置。它在 CREATE 或 ALTER TABLESPACE 命令期间填充。所以它可能是错误的：有人只需关闭集群，移动表空间的目录，在 pg_tblspc 中重新创建符号链接，并忘记更新 spclocation 字段。集群仍然可以运行，因为 spclocation 没有被使用。
因此，该字段已被删除。要获取表空间的位置，请使用 pg_tablespace_location()。
=# SELECT *, pg_tablespace_location(oid) AS spclocation FROM pg_tablespace; spcname | spcowner | spcacl | spcoptions | spclocation ------------+----------+--------+------------+---------------- pg_default | 10 | | | pg_global | 10 | | | tmptblspc | 10 | | | /tmp/tmptblspc
让非时区感知值的 EXTRACT 从本地午夜测量纪元，而不是从 UTC 午夜测量
在 PostgreSQL 9.1 中
=# SELECT extract(epoch FROM '2012-07-02 00:00:00'::timestamp); date_part ------------ 1341180000 (1 row) =# SELECT extract(epoch FROM '2012-07-02 00:00:00'::timestamptz); date_part ------------ 1341180000 (1 row)
带有时区和不带有时区的戳记在行为上没有区别。
在 9.2 中
=# SELECT extract(epoch FROM '2012-07-02 00:00:00'::timestamp); date_part ------------ 1341187200 (1 row) =# SELECT extract(epoch FROM '2012-07-02 00:00:00'::timestamptz); date_part ------------ 1341180000 (1 row)
当时间戳没有时区时，纪元将使用“本地午夜”计算，即 1970 年 1 月 1 日午夜，本地时间。
修复 to_date() 和 to_timestamp() 以便将不完整的日期环绕到 2020 年
环绕在两位数日期和三位数日期之间不一致：两位数日期始终选择最接近 2020 年的日期，三位数日期将 100 到 999 的日期映射到 1100 到 1999，将 000 到 099 映射到 2000 到 2099。
现在，对于两位数日期和三位数日期，PostgreSQL 选择最接近 2020 年的日期。
在 9.1 中
=# SELECT to_date('200-07-02','YYY-MM-DD');
to_date
------------
1200-07-02
在 9.2 中
SELECT to_date('200-07-02','YYY-MM-DD');
to_date
------------
2200-07-02
pg_stat_activity 和 pg_stat_replication 的定义已更改
视图 pg_stat_activity 已更改。它不向后兼容，但让我们看看这个新定义为我们带来了什么。
- current_query 消失，并被两列替换
- state：会话是正在运行查询，还是在等待。
- query：最近运行的查询（如果状态为“活动”，则仍在运行）是什么。
- 列 procpid 重命名为 pid，以与其他系统视图保持一致。
好处主要在于跟踪“事务空闲”会话。到目前为止，我们所能知道的就是这些会话之一在事务中处于空闲状态，这意味着它已经启动了一个事务，可能执行了一些操作，但尚未提交。如果该会话长时间保持在该状态，则无法知道它如何进入该状态。
以下是一个示例。
-[ RECORD 1 ]----+--------------------------------- datid | 16384 datname | postgres pid | 20804 usesysid | 10 usename | postgres application_name | psql client_addr | client_hostname | client_port | -1 backend_start | 2012-07-02 15:02:51.146427+02 xact_start | 2012-07-02 15:15:28.386865+02 query_start | 2012-07-02 15:15:30.410834+02 state_change | 2012-07-02 15:15:30.411287+02 waiting | f state | idle in transaction query | DELETE FROM test;
在 PostgreSQL 9.1 中，我们所能拥有的只是“事务空闲”。
由于此更改不向后兼容，因此 procpid 也被重命名为 pid，以与其他系统视图更加一致。视图 pg_stat_replication 也已更改。列 procpid 重命名为 pid，以与其他系统视图保持一致。
将所有 SQL 级别的统计计时值更改为以 float8 存储的毫秒。
pg_stat_user_functions.total_time、pg_stat_user_functions.self_time、pg_stat_xact_user_functions.total_time、pg_stat_xact_user_functions.self_time 和 pg_stat_statements.total_time（contrib）现在以毫秒为单位，以与其他计时值保持一致。
postgresql.conf 参数更改
- silent_mode 已被删除。请使用 pg_ctl -l postmaster.log。
- wal_sender_delay 已被删除。它不再需要。
- custom_variable_classes 已被删除。现在所有“类”都无需声明即可被接受。
- ssl_ca_file、ssl_cert_file、ssl_crl_file、ssl_key_file 已被添加，这意味着您现在可以指定 ssl 文件。
其他新功能
DROP INDEX CONCURRENTLY
常规 DROP INDEX 命令会对表进行排他锁定。大多数情况下，这不是问题，因为此锁定持续时间很短。问题通常出现在以下情况下：
- 一个长时间运行的事务正在运行，并且对该表拥有（共享）锁定。
- 在另一个会话中对该表运行 DROP INDEX，请求排他锁定（并等待它，因为它在长时间运行的事务结束之前不会被授予）。
此时，所有其他需要对该表进行共享锁定的事务（例如，用于简单 SELECT）也将不得不等待：它们的锁定获取排队在 DROP INDEX 之后。
DROP INDEX CONCURRENTLY 会解决这个问题，并且不会锁定正常的 DML 语句，就像 CREATE INDEX CONCURRENTLY 一样。限制也是一样的：因为您只能使用 CONCURRENTLY 选项删除一个索引，并且不支持 CASCADE 选项。
NOT VALID CHECK 约束
PostgreSQL 9.1 引入了“NOT VALID” 外键。这已扩展到 CHECK 约束。在表上添加“NOT VALID” 约束意味着当前数据不会被验证，只有新行和更新的行会被验证。
=# CREATE TABLE test (a int); CREATE TABLE =# INSERT INTO test SELECT generate_series(1,100); INSERT 0 100 =# ALTER TABLE test ADD CHECK (a>100) NOT VALID; ALTER TABLE =# INSERT INTO test VALUES (99); ERROR: new row for relation "test" violates check constraint "test_a_check" DETAIL: Failing row contains (99). =# INSERT INTO test VALUES (101); INSERT 0 1
然后，稍后，我们可以验证整个表。
=# ALTER TABLE test VALIDATE CONSTRAINT test_a_check ; ERROR: check constraint "test_a_check" is violated by some row
域（带有附加约束的类型）也可以被声明为无效，并稍后验证。
现在也可以重命名检查约束。
=# ALTER TABLE test RENAME CONSTRAINT test_a_check TO validate_a; ALTER TABLE
NO INHERIT 约束
以下是对约束的另一个改进：它们可以被声明为不可继承，这在分区环境中将非常有用。让我们以 PostgreSQL 文档为例，看看它如何改进情况。
CREATE TABLE measurement (
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int,
CHECK (logdate IS NULL) NO INHERIT
);
CREATE TABLE measurement_y2006m02 (
CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' )
) INHERITS (measurement);
CREATE TABLE measurement_y2006m03 (
CHECK ( logdate >= DATE '2006-03-01' AND logdate < DATE '2006-04-01' )
) INHERITS (measurement);
INSERT INTO measurement VALUES (1,'2006-02-20',1,1);
ERROR: new row for relation "measurement" violates check constraint "measurement_logdate_check"
DETAIL: Failing row contains (1, 2006-02-20, 1, 1).
INSERT INTO measurement_y2006m02 VALUES (1,'2006-02-20',1,1);
INSERT 0 1
到目前为止，在 measurement 上创建的每个检查约束都将被子表继承。因此，在父表上添加禁止插入或只允许某些插入的约束是不可能的。
减少 ALTER TABLE 重写
在以下情况下，在更改列类型时，表在 ALTER TABLE 期间将不再被重写。
- 当 y>=x 时，将 varchar(x) 转换为 varchar(y)。从 varchar(x) 转换为 varchar 或 text（无大小限制）也适用。
- 当 y>=x 时，将 numeric(x,z) 转换为 numeric(y,z)，或转换为不带指定值的 numeric。
- 当 y>=x 时，将 varbit(x) 转换为 varbit(y)，或转换为不带指定值的 varbit。
- 当 y>=x 时，将 timestamp(x) 转换为 timestamp(y)，或转换为不带指定值的 timestamp。
- 当 y>=x 时，将 timestamptz(x) 转换为 timestamptz(y)，或转换为不带指定值的 timestamptz。
- 当 y>=x 时，将 interval(x) 转换为 interval(y)，或转换为不带指定值的 interval。
安全屏障和防泄漏
这个新功能与视图安全有关。首先，让我们用一个非常简单的例子来解释这个问题。
=# CREATE TABLE all_data (company_id int, company_data varchar); CREATE TABLE =# INSERT INTO all_data VALUES (1,'secret_data_for_company_1'); INSERT 0 1 =# INSERT INTO all_data VALUES (2,'secret_data_for_company_2'); INSERT 0 1 =# CREATE VIEW company1_data AS SELECT * FROM all_data WHERE company_id = 1; CREATE VIEW
这是一个非常经典的为用户提供对表一部分的访问权限的方法：我们将为公司 ID 1 创建一个用户，授予他访问 company1_data 的权限，并拒绝他访问 all_data 的权限。
该查询的计划如下所示
=# explain SELECT * FROM company1_data ;
QUERY PLAN
----------------------------------------------------------
Seq Scan on all_data (cost=0.00..25.38 rows=6 width=36)
Filter: (company_id = 1)
即使有更多数据，也可以强制进行顺序扫描：只需“SET enable_indexscan to OFF”之类的操作。
因此，此查询会读取 all_data 中的所有记录，对其进行过滤，并将匹配的行返回给用户。有一种方法可以在过滤记录之前显示已扫描的记录：只需创建一个成本很低的函数，并在执行查询时调用它。
CREATE OR REPLACE FUNCTION peek(text) RETURNS boolean LANGUAGE plpgsql AS $$ BEGIN RAISE NOTICE '%',$1; RETURN true; END $$ COST 0.1;
此函数只需比 = 运算符的成本低（成本为 1），即可首先执行。
结果如下
=# SELECT * FROM company1_data WHERE peek(company1_data.company_data);
NOTICE: secret_data_for_company_1
NOTICE: secret_data_for_company_2
company_id | company_data
------------+---------------------------
1 | secret_data_for_company_1
(1 row)
我们获得了对第二家公司记录的访问权限（在 NOTICE 消息中）。
因此，这是第一个新功能：视图可以声明为实现“安全屏障”。
=# CREATE VIEW company1_data WITH (security_barrier) AS SELECT * FROM all_data WHERE company_id = 1;
CREATE VIEW
=# SELECT * FROM company1_data WHERE peek(company1_data.company_data);
NOTICE: secret_data_for_company_1
company_id | company_data
------------+---------------------------
1 | secret_data_for_company_1
(1 row)
视图不再泄漏。当然，问题在于存在性能影响：“窥视”函数可能通过在计划的早期过滤大量行来使查询更快。现在，行在两个独立的计划步骤中被过滤。
=# explain SELECT * FROM company1_data WHERE peek(company1_data.company_data);
QUERY PLAN
--------------------------------------------------------------------
Subquery Scan on company1_data (cost=0.00..25.44 rows=2 width=36)
Filter: peek((company1_data.company_data)::text)
-> Seq Scan on all_data (cost=0.00..25.38 rows=6 width=36)
Filter: (company_id = 1)
这导致了补充功能：某些函数可以声明为“防泄漏”，这意味着它们不会将传递给错误或通知消息的数据泄漏。
将我们的 peek 函数声明为 LEAKPROOF 是一个非常糟糕的主意，但让我们这样做只是为了演示它的使用方法。
CREATE OR REPLACE FUNCTION peek(text) RETURNS boolean LEAKPROOF LANGUAGE plpgsql AS $$ BEGIN RAISE NOTICE '%',$1; RETURN true; END $$ COST 0.1;
LEAKPROOF 函数以“正常”方式执行。
=# SELECT * FROM company1_data WHERE peek(company1_data.company_data);
NOTICE: secret_data_for_company_1
NOTICE: secret_data_for_company_2
company_id | company_data
------------+---------------------------
1 | secret_data_for_company_1
(1 row)
当然，在我们的例子中，peek 不是 LEAKPROOF，不应该被声明为 LEAKPROOF。只有超级用户有权声明 LEAKPROOF 函数。
pg_dump 的新选项
到目前为止，可以使用 pg_dump 导出表的 data 或表的 meta-data（用于创建表结构、索引和约束的 DDL 语句）。出于性能原因，某些 meta-data 最好在 data 之前恢复（表的结构、检查约束），而某些 meta-data 最好在 data 之后恢复（索引、唯一约束、外键...）。
因此，现在有几个额外的选项：
- --section=pre-data: 导出恢复 data 之前需要的内容。当然，这可以与 -t 结合使用，例如，指定一个表。
- --section=post-data: 导出恢复 data 之后需要的内容。
- --section=data: 导出 data。
- --exclude-table-data: 导出所有内容，除了此表的 data。这意味着 pg_dump 仍然会导出其他表的 data。
