Appendix E. 版本信息

Table of Contents
E.1. 版本 8.0
E.2. 版本 7.4.6
E.3. 版本 7.4.5
E.4. 版本 7.4.4
E.5. 版本 7.4.3
E.6. 版本 7.4.2
E.7. 版本 7.4.1
E.8. 版本 7.4
E.9. 版本 7.3.8
E.10. 版本 7.3.7
E.11. 版本 7.3.6
E.12. 版本 7.3.5
E.13. 版本 7.3.4
E.14. 版本 7.3.3
E.15. 版本 7.3.2
E.16. 版本 7.3.1
E.17. 版本 7.3
E.18. 版本 7.2.6
E.19. 版本 7.2.5
E.20. 版本 7.2.4
E.21. 版本 7.2.3
E.22. 版本 7.2.2
E.23. 版本 7.2.1
E.24. 版本 7.2
E.25. 版本 7.1.3
E.26. 版本 7.1.2
E.27. 版本 7.1.1
E.28. 版本 7.1
E.29. 版本 7.0.3
E.30. 版本 7.0.2
E.31. 版本 7.0.1
E.32. 版本 7.0
E.33. 版本 6.5.3
E.34. 版本 6.5.2
E.35. 版本 6.5.1
E.36. 版本 6.5
E.37. 版本 6.4.2
E.38. 版本 6.4.1
E.39. 版本 6.4
E.40. 版本 6.3.2
E.41. 版本 6.3.1
E.42. 版本 6.3
E.43. 版本 6.2.1
E.44. 版本 6.2
E.45. 版本 6.1.1
E.46. 版本 6.1
E.47. 版本 6.0
E.48. 版本 1.09
E.49. 版本 1.02
E.50. 版本 1.01
E.51. 版本 1.0
E.52. Postgres95版本 0.03
E.53. Postgres95版本 0.02
E.54. Postgres95版本 0.01

E.1. 版本 8.0

发布日期: 2005-01-19

E.1.1. 概述

这个版本中的主要变化:

Microsoft Windows 本机服务器

这是第一个可以在 Microsoft Windows 上作为服务器本地运行的 PostgreSQL 版本。 它可以以 Windows 服务运行。 这个版本支持 NT 为基础的 Windows 版本,比如Windows 2000,,Windows XP, 和 Windows 2003。它不支持更早的版本,比如 Windows 95Windows 98,和 Windows ME 等,因为这些操作系统没有支持 PostgreSQL 的足够的基础设施。 还有一个独立的安装器项目以简化 Windows 上的安装 — 参阅 http://pgfoundry.org/projects/pginstaller

尽管在我们的发布周期里认真测试过, 但是 Windows 移植仍然没有像 Unix 平台下 PostgreSQL 那么多年的使用经验, 因此,应该像新产品一样小心对待它。

以前的版本要求 Unix 模拟工具 Cygwin 才能支持 Win32。 PostgreSQL 多年来一直支持 Win32 上的本机客户端。

保存点

保存点允许回滚一个事务的特定部分而不会影响事务其它的部分。 以前的版本没有这样的功能;除了退出整个事务外,我们无法从事务中的一个语句的失效恢复。 这个特性用于那些要求复杂的事务控制的应用作者特别有用。

即时恢复

在以前的版本里,我们没有办法从磁盘崩溃中恢复, 除非从以前的备份中恢复或者使用一个独立的复制服务器。 即时恢复允许对服务器进行连续的备份。你既可以恢复到失败那个点,也可以恢复到以前的任意事务。

表空间

表空间允许管理员选择用于存储表,索引和整个数据库所用的文件系统。 这样做改善了性能以及提高了对磁盘空间使用的控制。 以前的版本使用 initlocation 和手工的符号连接用于这样的任务。

改进的缓冲区管理,CHECKPOINTVACUUM

这个版本有着更聪明的缓冲区替换策略,它可以更好地利用可用地共享缓冲区并且改进性能。 vacuum和checkpoint对性能的影响也降低了。

改变字段的类型

现在我们可以用 ALTER TABLE 修改字段的数据类型。

新的 Perl 服务器端编程语言

新版本的 PL/Perl 服务器端编程语言现在支持永久的,共享存储区,触发器, 返回记录和记录数组,以及 SPI 调用。

COPY 里的逗号分隔数值(CSV)支持

COPY 现在可以读写逗号分隔数值(CSV)文件。它还具有解析非标准引号包围和非标准分隔字符的灵活性。

E.1.2. 移植到版本 8.0

对于使用任何以前版本的用户来说,需要使用 pg_dump 进行一次转储/恢复。

请注意下列不兼容:

  • READ COMMITTED 串行化模式里, 易失函数现在看到的是当前事务在函数内每条语句开始的时候提交的结果, 而不是调用函数的交互命令开始的时候提交的结果。

  • 声明为 STABLE 或者 IMMUTABLE 的函数总是使用调用查询的快照, 因此无法看到调用查询开始之后的行为的结果,不管是自己事务内部的还是其它事务的。 这样的函数还必须是只读的,意味着它除了使用 SELECT 之外不能使用任何其它 SQL 命令。

  • 不能推迟的 AFTER 触发器现在是在出发他们的查询结束后立即出发, 而不是在完成当前交互命令的时候。如果触发触发器的查询在一个函数里,那么就和以前有所不同: 触发器是在函数继续其下一步操作之前调用的。

  • 服务器配置参数 virtual_hosttcpip_socket 被更通用的参数 listen_addresses 取代。还有,现在服务器缺省时在 localhost 上监听, 它在很多场合下消除了 postmaster 开关 -i 的需要。

  • 服务器配置参数 SortMemVacuumMem 改名为 work_memmaintenance_work_mem 以便更好地反映它们地用途。原来的两个仍然在 SETSHOW 里支持。

  • 服务器配置参数 log_pidlog_timestamp,和 log_source_port 现在删除掉了, 用一个更灵活的 log_line_prefix 替换掉了。

  • 服务器配置参数 syslog,用一个更有逻辑性的 log_destination 替换掉了,用于配置日志输出目的地。

  • 服务器配置参数 log_statement 修改了一些行为,这样它可以限制只记录数据库修改或者数据定义语句。 服务器配置参数 log_duration 现在只是在 log_statement 打印查询的时候才答应。

  • 服务器配置参数 max_expr_depthmax_stack_depth 替换,它测量的是堆栈的大小,而不是堆栈深度的多少。 这样就避免了会话因为递规函数导致的堆栈溢出而终止。

  • length() 函数不再计算 CHAR(n) 数值里面结尾的空白。

  • 把一个整数转换成 BIT(N) 选取最右边 N 位整数,而不是以前的最左边 N 位。

  • 现在 UPDATE 一个 NULL 数组的元素或者片断生成一个非 NULL 的数组结果, 也就是一个只包含赋值了的位置的数组。

  • 在匹配 GROUP BY 名字的时候,首先优先选择本地的 FROM 字段,然后是 SELECT 的别名和上层子查询的 FROM。

  • 数组输入值的语法检查变严了。以前那些在奇怪位置有着奇怪结果的,允许输入的垃圾现在导致一个错误。 孔字串元素现在必须写成 "",而不是什么都不写。 还改变了包围数组元素的空白的行为:末尾的空白现在被忽略, 开头的也是(总是被忽略)。

  • 整数算术操作的溢出现在被检测并会报告一个错误。

  • 与单字节数据类型 "char" 关联的算术操作运算符被删除。

  • extract() 函数(也叫作 date_part)现在给 BC (公元前)的日期返回合适的年份。 以前它返回比正确年份小一年。现在这个函数也返回正确的千年和世纪值。

  • CIDR 值现在必须是非掩码位为零。比如,我们不再允许 204.248.199.1/31 作为一个 CIDR 数值。 这样的数值将不会被 PostgreSQL 接受并且被拒绝。

  • 现在 EXECUTE 返回一个匹配执行的语句的完成标志。

  • psql\copy 命令现在读写查询的 stdin/stdout, 而不是 psqlstdin/stdout。 以前的行为可以通过新的 pstdin/pstdout 参数获取。

  • JDBC 客户端接口从核心版本中删除,现在它在 http://jdbc.postgresql.org 承载。

  • Tcl 客户端接口从核心版本中删除,现在有几个 Tcl 接口在 http://gborg.postgresql.org 承载。

  • 服务器现在使用自己的时区数据库,而不是操作系统使用的那个。 这样将在不同平台上提供一致的行为。在大多数情况下,时区行为上应该没有什么可见的区别, 只有 SET/SHOW TimeZone 使用的时区名字可能和你的平台提供的有些区别。

  • Configure 的线程选项不再需要用户运行测试或者编辑配置文件了;线程选项现在是自动探测的。

  • 既然已经实现了表空间,initlocation 就删除掉了。

  • 数组值输入的语法检查处理相应严格化了。以前允许的那些在奇怪位置输入并导致奇怪结果的垃圾现在会生成一个 ERROR。 空字串值现在必须写成 "",而不是以前那样可以啥都不写。 还改变了在数组元素周围包围的空白的行为:结尾的空白现在总是忽略,和开头的空白类似(以前总是忽略)。

  • READ COMMITTED 串行模式下,易失的函数现在看到函数里每个语句开始的时候并发的事务提交的结果, 而不是调用函数的交互命令开始的时候的东西。

  • 声明为 STABLE 或者 IMMUTABLE 的函数总是使用调用它的查询的快照, 因此不会看到在调用查询开始之后的动作的效果,不管是他们自己的事务里面的还是其它事务里面的。 这样的函数也必须是只读的,这就意味着它不能使用除了 SELECT 之外的 SQL 命令。

  • 不推迟的 AFTER 触发器现在在触发之的查询完成后立即触发,而不是在完成当前的交互命令之后。 这种做法下,如果触发之的查询发生在一个函数梨,那么就有区别:触发器在函数进行它的下一个操作之前就触发了。

E.1.3. 废弃的特性

PostgreSQL 的有些方面行为已经认为是不够优化的。 为了向后兼容,这些东西还没有从 8.0 里面删除,但是我们认为它们已经废弃了并且将在下一个主版本中删除。

  • 8.1 版本将删除函数 to_char(interval, text)

  • 服务器现在警告传递给 oid/float4/float8 数据类型的空字串,但是和以前一样把它们解析为零。 在下一个主版本里,空字串将被认为是这些数据类型的无效输入。

  • 缺省时,PostgreSQL 8.0 以及更早的版本里的表都带着 OID 创建。 在下一个版本里,情况将不是这样:要创建一个包含 OID 的表, 必须声明 WITH OIDS 子句或者必须打开 default_with_oids 配置参数。 如果用户的表因为兼容性原因需要使用 OID,那么我们鼓励用户明确声明 WITH OIDS, 这样方便和将来的 PostgreSQL 版本兼容。

E.1.4. 修改

下面是你会找到的在 8.0 里和以前的主版本之间的变化细节。

E.1.4.1. 性能改善

  • 支持跨数据类型的索引使用 (Tom)

    在这个改变之前,如果数据类型不是精确匹配,那么许多查询将不能使用索引。 这个改进令索引的使用更加直观和连贯。

  • 改善缓冲得新的缓冲区替换策略 (Jan)

    以前的版本使用当前最少使用(LRU)缓冲来保持最近引用过的页面在内存里。 LRU 算法并不考虑特定缓冲项访问的次数。大表的选择可能会导致缓冲页出局。 新的缓冲算法使用四个独立的列表以记录最近使用的和最频繁使用的缓冲页, 并且根据工作负荷动态地优化它们。这样可以让共享的缓冲区的使用更加高效。 以前测试过共享缓冲区大小的管理员应该重新测试,以便使用新的缓冲替换策略。

  • 增加了新的子进程来周期性的写入脏缓冲区以减少 checkpoint 的写入 (Jan)

    在以前的版本里,检查点进程会每隔几分钟运行一次,把所有脏缓冲区的数据写到操作系统的缓冲区, 然后把所有操作系统的脏缓冲区的数据都刷新到磁盘上。 这样就会导致周期性地磁盘使用高峰,因此也会影响性能。 新的代码使用一个后台写进程以稳定的步调跟踪磁盘写,这样检查点需要写的脏页面就少得多。 还有,新的代码并不发出全局的 sync() 调用,而只是 fsync() 那些自上次检查点以来写入过的文件。 这样应该可以改善性能并且在检查点的时候最小化性能的下降。

  • 增加了拖延 vacuum 的能力,降低了对性能的影响 (Jan)

    在繁忙的系统上,VACUUM 进行大量的 I/O 请求,可能会导致对其它用户的性能的影响。 这个版本允许你降低 VACUUM 的速度,这样它就会少用一些资源, 不过这个会延长 VACUUM 的时间。

  • 改善了重复键字的 B-tree 索引的性能(Dmitry Tkach, Tom)

    这个变化改进了索引中存在多个重复值的索引扫描。

  • 规划时使用动态生成的表大小估计(Tom)

    以前的规划器对表大小的估计使用最后一次 VACUUM 或者 ANALYZE 看到的数值,包括物理表大小(页面数)和行数。 现在,当前物理表大小是从内核获取的,而行数是用表大小乘以最后一次 VACUUM 或者 ANALYZE 看到的行密度(每页的行数)得到的。 这样可以获得更可靠的估计,以免自最后一次清理命令以来表的大小有显著改变。

  • 改善了带有 OR 子句的索引使用 (Tom)

    这个变化允许优化器在带有许多 OR 子句的语句中使用索引,而在以前是不可能的。 如果声明了第一个字段并且第二个字段是一个 OR 字句的一部分,它还可以使用多字段索引。

  • 改进部分索引子句的匹配 (Tom)

    现在服务器对涉及复杂 WHERE 子句的查询使用部分索引的时候更聪明。

  • 改善了 GEQO 优化器的性能 (Tom)

    GEQO 优化器用于连接很多表(缺省是十二个)。这个版本提高了分析查询的速度,降低了用于优化的时间。

  • 杂项的优化器改进

    这里可不够地方列出所有小的改进,但是大量的特例在这个版本里比以前的版本干的要好。

  • 改进了 C 函数的装载速度 (Tom)

    这个版本现在使用一个散列来查找外部装载的 C 函数的信息。 这样就改进了他们的装载速度,和那些本身是服务器一部分的内置函数一样快。

  • 增加了类型相关的 ANALYZE 统计功能 (Mark Cave-Ayland)

    这个特性允许为非标准数据类型有更灵活的统计生成。

  • ANALYZE 现在为表达式索引收集统计(Tom)

    Expression indexes (also called functional indexes) allow users to index not just columns but the results of expressions and function calls. With this release, the optimizer can gather and use statistics about the contents of expression indexes. This will greatly improve the quality of planning for queries in which an expression index is relevant.

  • New two-stage sampling method for ANALYZE (Manfred Koizar)

    This gives better statistics when the density of valid rows is very different in different regions of a table.

  • 加快了 TRUNCATE (Tom)

    这个修补弥补了一些在 7.4 里观察到的性能丢失,并且仍然保持了 TRUNCATE 的事务安全性。

E.1.4.2. 服务器修改

  • 增加了 WAL 文件归档和及时恢复 (Simon Riggs)

  • 增加了表空间,管理员可以控制磁盘布局 (Gavin)

  • Add a built-in log rotation program (Andreas Pflug)

    It is now possible to log server messages conveniently without relying on either syslog or an external log rotation program.

  • Add new read-only server configuration parameters to show server compile-time settings: block_size, integer_datetimes, max_function_args, max_identifier_length, max_index_keys (Joe)

  • Make quoting of sameuser, samegroup, and all remove special meaning of these terms in pg_hba.conf (Andrew)

  • Use clearer IPv6 name ::1/128 for localhost in default pg_hba.conf (Andrew)

  • Use CIDR format in pg_hba.conf examples (Andrew)

  • Rename server configuration parameters SortMem and VacuumMem to work_mem and maintenance_work_mem (Old names still supported) (Tom)

    This change was made to clarify that bulk operations such as index and foreign key creation use maintenance_work_mem, while work_mem is for workspaces used during query execution.

  • Allow logging of session disconnections using server configuration parameter log_disconnections (Andrew)

  • Add new server configuration parameter log_line_prefix to allow control of information emitted in each log line (Andrew)

    Information includes user name, database name, remote IP address, and session start time.

  • Remove server configuration parameters log_pid, log_timestamp, log_source_port; functionality superseded by log_line_prefix (Andrew)

  • Replace the virtual_host and tcpip_socket parameters with a unified listen_addresses parameter (Andrew, Tom)

    virtual_host could only specify a single IP address to listen on. listen_addresses allows multiple addresses to be specified.

  • Listen on localhost by default, which eliminates the need for the -i postmaster switch in many scenarios (Andrew)

    Listening on localhost (127.0.0.1) opens no new security holes but allows configurations like Windows and JDBC, which do not support local sockets, to work without special adjustments.

  • Remove syslog server configuration parameter, and add more logical log_destination variable to control log output location (Magnus)

  • Change server configuration parameter log_statement to take values all, mod, ddl, or none to select which queries are logged (Bruce)

    This allows administrators to log only data definition changes or only data modification statements.

  • Some logging-related configuration parameters could formerly be adjusted by ordinary users, but only in the "more verbose" direction. They are now treated more strictly: only superusers can set them. However, a superuser may use ALTER USER to provide per-user settings of these values for non-superusers. Also, it is now possible for superusers to set values of superuser-only configuration parameters via PGOPTIONS.

  • Allow configuration files to be placed outside the data directory (mlw)

    By default, configuration files sit in the cluster's top directory. With this addition, configuration files can be placed outside the data directory, easing administration.

  • Plan prepared queries only when first executed so constants can be used for statistics (Oliver Jowett)

    Prepared statements plan queries once and execute them many times. While prepared queries avoid the overhead of re-planning on each use, the quality of the plan suffers from not knowing the exact parameters to be used in the query. In this release, planning of unnamed prepared statements is delayed until the first execution, and the actual parameter values of that execution are used as optimization hints. This allows use of out-of-line parameter passing without incurring a performance penalty.

  • Allow DECLARE CURSOR to take parameters (Oliver Jowett)

    It is now useful to issue DECLARE CURSOR in a Parse message with parameters. The parameter values sent at Bind time will be substituted into the execution of the cursor's query.

  • Fix hash joins and aggregates of inet and cidr data types (Tom)

    Release 7.4 handled hashing of mixed inet and cidr values incorrectly. (This bug did not exist in prior releases because they wouldn't try to hash either data type.)

  • Make log_duration print only when log_statement prints the query (Ed L.)

E.1.4.3. Query Changes

  • Add savepoints (nested transactions) (Alvaro)

  • Unsupported isolation levels are now accepted and promoted to the nearest supported level (Peter)

    The SQL specification states that if a database doesn't support a specific isolation level, it should use the next more restrictive level. This change complies with that recommendation.

  • Allow BEGIN WORK to specify transaction isolation levels like START TRANSACTION (Bruce)

  • Fix table permission checking for cases in which rules generate a query type different from the originally submitted query (Tom)

  • Implement dollar quoting to simplify single-quote usage (Andrew Tom, David Fetter)

    In previous releases, because single quotes had to be used to quote a function's body, the use of single quotes inside the function text required use of two single quotes or other error-prone notations. With this release we add the ability to use "dollar quoting" to quote a block of text. The ability to use different quoting delimiters at different nesting levels greatly simplifies the task of quoting correctly, especially in complex functions. Dollar quoting can be used anywhere quoted text is needed.

  • Make CASE val WHEN compval1 THEN ... evaluate val only once (Tom)

    CASE no longer evaluates the test expression multiple times. This has benefits when the expression is complex or is volatile.

  • Test HAVING before computing target list of an aggregate query (Tom)

    Fixes improper failure of cases such as SELECT SUM(win)/SUM(lose) ... GROUP BY ... HAVING SUM(lose) > 0. This should work but formerly could fail with divide-by-zero.

  • Replace max_expr_depth parameter with max_stack_depth parameter, measured in kilobytes of stack size (Tom)

    This gives us a fairly bulletproof defense against crashing due to runaway recursive functions. Instead of measuring the depth of expression nesting, we now directly measure the size of the execution stack.

  • Allow arbitrary row expressions (Tom)

    This release allows SQL expressions to contain arbitrary composite types, that is, row values. It also allows functions to more easily take rows as arguments and return row values.

  • Allow LIKE/ILIKE to be used as the operator in row and subselect comparisons (Fabien Coelho)

  • Avoid locale-specific case conversion of basic ASCII letters in identifiers and keywords (Tom)

    This solves the "Turkish problem" with mangling of words containing I and i. Folding of characters outside the 7-bit-ASCII set is still locale-aware.

  • Improve syntax error reporting (Fabien, Tom)

    Syntax error reports are more useful than before.

  • Change EXECUTE to return a completion tag matching the executed statement (Kris Jurka)

    Previous releases return an EXECUTE tag for any EXECUTE call. In this release, the tag returned will reflect the command executed.

  • Avoid emitting NATURAL CROSS JOIN in rule listings (Tom)

    Such a clause makes no logical sense, but in some cases the rule decompiler formerly produced this syntax.

  • Allow arbitrary row expressions (Tom)

    This allows columns to contain arbitrary composite types like rows from other tables. It also allows functions to more easily take rows as arguments and return row values.

E.1.4.4. Object Manipulation Changes

  • Add COMMENT ON for casts, conversions, languages, operator classes, and large objects (Christopher)

  • Add new server configuration parameter default_with_oids to control whether tables are created with OIDs by default (Neil)

    This allows administrators to control whether CREATE TABLE commands create tables with or without OID columns by default. (Note: the current factory default setting for default_with_oids is TRUE, but the default will become FALSE in future releases.)

  • Add WITH / WITHOUT OIDS clause to CREATE TABLE AS (Neil)

  • Allow ALTER TABLE DROP COLUMN to drop an OID column (ALTER TABLE SET WITHOUT OIDS still works) (Tom)

  • Allow composite types as table columns (Tom)

  • Allow ALTER ... ADD COLUMN with defaults and NOT NULL constraints; works per SQL spec (Rod)

    It is now possible for ADD COLUMN to create a column that is not initially filled with NULLs, but with a specified default value.

  • Add ALTER COLUMN TYPE to change column's type (Rod)

    It is now possible to alter a column's data type without dropping and re-adding the column.

  • Allow multiple ALTER actions in a single ALTER TABLE command (Rod)

    This is particularly useful for ALTER commands that rewrite the table (which include ALTER COLUMN TYPE and ADD COLUMN with a default). By grouping ALTER commands together, the table need be rewritten only once.

  • Allow ALTER TABLE to add SERIAL columns (Tom)

    This falls out from the new capability of specifying defaults for new columns.

  • Allow changing the owners of aggregates, conversions, databases, functions, operators, operator classes, schemas, types, and tablespaces (Christopher, Euler Taveira de Oliveira)

    Previously this required modifying the system tables directly.

  • Allow temporary object creation to be limited to SECURITY DEFINER functions (Sean Chittenden)

  • Add ALTER TABLE ... SET WITHOUT CLUSTER (Christopher)

    Prior to this release, there was no way to clear an auto-cluster specification except to modify the system tables.

  • Constraint/Index/SERIAL names are now table_column_type with numbers appended to guarantee uniqueness within the schema (Tom)

    The SQL specification states that such names should be unique within a schema.

  • Add pg_get_serial_sequence() to return a SERIAL column's sequence name (Christopher)

    This allows automated scripts to reliably find the SERIAL sequence name.

  • Warn when primary/foreign key data type mismatch requires costly lookup

  • New ALTER INDEX command to allow moving of indexes between tablespaces (Gavin)

  • Make ALTER TABLE OWNER change dependent sequence ownership too (Alvaro)

E.1.4.5. Utility Command Changes

  • Allow CREATE SCHEMA to create triggers, indexes, and sequences (Neil)

  • Add ALSO keyword to CREATE RULE (Fabien Coelho)

    This allows ALSO to be added to rule creation to contrast it with INSTEAD rules.

  • Add NOWAIT option to LOCK (Tatsuo)

    This allows the LOCK command to fail if it would have to wait for the requested lock.

  • Allow COPY to read and write comma-separated-value (CSV) files (Andrew, Bruce)

  • Generate error if the COPY delimiter and NULL string conflict (Bruce)

  • GRANT/REVOKE behavior follows the SQL spec more closely

  • Avoid locking conflict between CREATE INDEX and CHECKPOINT (Tom)

    In 7.3 and 7.4, a long-running B-tree index build could block concurrent CHECKPOINTs from completing, thereby causing WAL bloat because the WAL log could not be recycled.

  • Database-wide ANALYZE does not hold locks across tables (Tom)

    This reduces the potential for deadlocks against other backends that want exclusive locks on tables. To get the benefit of this change, do not execute database-wide ANALYZE inside a transaction block (BEGIN block); it must be able to commit and start a new transaction for each table.

  • Generate error if the COPY delimiter and NULL string conflict (Bruce)

  • Allow COPY to read comma-separated-value (CSV) files (Andrew, Bruce)

  • REINDEX does not exclusively lock the index's parent table anymore

    The index itself is still exclusively locked, but readers of the table can continue if they are not using the particular index being rebuilt.

  • Erase MD5 user passwords when a user is renamed (Bruce)

    PostgreSQL uses the user name as salt when encrypting passwords via MD5. When a user's name is changed, the salt will no longer match the stored MD5 password, so the stored password becomes useless. In this release a notice is generated and the password is cleared. A new password must then be assigned if the user is to be able to log in with a password.

  • New pg_ctl kill option for Windows (Andrew)

    Windows does not have a kill command to send signals to backends so this capability was added to pg_ctl.

  • Information schema improvements

  • Add --pwfile option to initdb so the initial password can be set by GUI tools (Magnus)

  • Detect locale/encoding mismatch in initdb (Peter)

  • Add register command to pg_ctl to register Windows operating system service (Dave Page)

E.1.4.6. Data Type and Function Changes

  • More complete support for composite types (row types) (Tom)

    Composite values can be used in many places where only scalar values worked before.

  • Reject non-rectangular array literals as erroneous (Joe)

    Formerly, array_in would silently build a surprising result.

  • Overflow in integer arithmetic operations is now detected (Tom)

  • The arithmetic operators associated with the single-byte "char" data type have been removed.

    Formerly, the parser would select these operators in many situations where an "unable to select an operator" error would be more appropriate, such as null * null. If you actually want to do arithmetic on a "char" column, you can cast it to integer explicitly.

  • Syntax checking of array input processing considerably tighened up (Joe)

    Junk that was previously allowed in odd places with odd results now causes an ERROR, for example, non-whitespace after the closing right brace.

  • Empty-string array element values must now be written as "", rather than writing nothing (Joe)

    Formerly, both ways of writing an empty-string element value were allowed, but now a quoted empty string is required. The case where nothing at all appears will probably be considered to be a NULL element value in some future release.

  • Array element trailing whitespace is now ignored (Joe)

    Formerly leading whitespace was ignored, but trailing whitespace between an element and the delimiter or right brace was significant. Now trailing whitespace is also ignored.

  • Emit array values with explicit array bounds when lower bound is not one (Joe)

  • Accept YYYY-monthname-DD as a date string (Tom)

  • Make netmask and hostmask functions return maximum-length mask length (Tom)

  • Change factorial function to return numeric (Gavin)

    Returning numeric allows the factorial function to work for a wider range of input values.

  • to_char/to_date() date conversion improvements (Kurt Roeckx, Fabien Coelho)

  • Make length() disregard trailing spaces in CHAR(n) (Gavin)

    This change was made to improve consistency: trailing spaces are semantically insignificant in CHAR(n) data, so they should not be counted by length().

  • Warn about empty string being passed to OID/float4/float8 data types (Neil)

    8.1 will throw an error instead.

  • Allow leading or trailing whitespace in int2/int4/int8/float4/float8 input routines (Neil)

  • Better support for IEEE Infinity and NaN values in float4/float8 (Neil)

    These should now work on all platforms that support IEEE-compliant floating point arithmetic.

  • Add week option to date_trunc() (Robert Creager)

  • Fix to_char for 1 BC (previously it returned 1 AD) (Bruce)

  • Fix to_char for 1 BC (previously it returned 1 AD) (Bruce)

  • Fix date_part(year) for BC dates (previously it returned one less than the correct year) (Bruce)

  • Fix date_part() to return the proper millennium and century (Fabien Coelho)

    In previous versions, the century and millennium results had a wrong number and started in the wrong year, as compared to standard reckoning of such things.

  • Add ceiling() as an alias for ceil(), and power() as an alias for pow() for standards compliance (Neil)

  • Change ln(), log(), power(), and sqrt() to emit the correct SQLSTATE error codes for certain error conditions, as specified by SQL:2003 (Neil)

  • Add width_bucket() function as defined by SQL:2003 (Neil)

  • Add generate_series() functions to simplify working with numeric sets (Joe)

  • Fix upper/lower/initcap() functions to work with multibyte encodings (Tom)

  • Add boolean and bitwise integer AND/OR aggregates (Fabien Coelho)

  • New session information functions to return network addresses for client and server (Sean Chittenden)

  • Add function to determine the area of a closed path (Sean Chittenden)

  • Add function to send cancel request to other backends (Magnus)

  • Add interval plus datetime operators (Tom)

    The reverse ordering, datetime plus interval, was already supported, but both are required by the SQL standard.

  • Casting an integer to BIT(N) selects the rightmost N bits of the integer (Tom)

    In prior releases, the leftmost N bits were selected, but this was deemed unhelpful, not to mention inconsistent with casting from bit to int.

  • Require CIDR values to have all non-masked bits be zero (Kevin Brintnall)

E.1.4.7. 服务器端语言变化

  • READ COMMITTED 串行模式下,易失的函数现在看到函数里每个语句开始的时候并发的事务提交的结果, 而不是调用函数的交互命令开始的时候的东西。

  • 声明为 STABLE 或者 IMMUTABLE 的函数总是使用调用它的查询的快照, 因此不会看到在调用查询开始之后的动作的效果,不管是他们自己的事务里面的还是其它事务里面的。 这样的函数也必须是只读的,这就意味着它不能使用除了 SELECT 之外的 SQL 命令。 把函数声明为 STABLE 或者 IMMUTABLE 而不是 VOLATILE 有可观的性能提升。

  • 不推迟的 AFTER 触发器现在在触发之的查询完成后立即触发,而不是在完成当前的交互命令之后。 这种做法下,如果触发之的查询发生在一个函数梨,那么就有区别:触发器在函数进行它的下一个操作之前就触发了。 比如,如果一个函数向一个表里插入了一个新行,那么任何未推迟的外键检查将在函数继续之前发生。

  • Allow function parameters to be declared with names (Dennis Bjorklund)

    This allows better documentation of functions. Whether the names actually do anything depends on the specific function language being used.

  • Allow PL/pgSQL parameter names to be referenced in the function (Dennis Bjorklund)

    This basically creates an automatic alias for each named parameter.

  • Do minimal syntax checking of PL/pgSQL functions at creation time (Tom)

    This allows us to catch simple errors sooner.

  • More support for composite types (row and record variables) in PL/pgSQL

    For example, it now works to pass a rowtype variable to another function as a single variable.

  • Default values for PL/pgSQL variables can now reference previously declared variables

  • Improve parsing of PL/pgSQL FOR loops (Tom)

    Parsing is now driven by presence of ".." rather than datatype of FOR variable. This makes no difference for correct functions, but should result in more understandable error messages when a mistake is made.

  • Major overhaul of PL/Perl server-side language (Command Prompt, Andrew Dunstan)

  • In PL/Tcl, SPI commands are now run in subtransactions. If an error occurs, the subtransaction is cleaned up and the error is reported as an ordinary Tcl error, which can be trapped with catch. Formerly, it was not possible to catch such errors.

  • Accept ELSEIF in PL/pgSQL (Neil)

    Previously PL/pgSQL only allowed ELSIF, but many people are accustomed to spelling this keyword ELSEIF.

E.1.4.8. psql 变化

  • Improve psql information display about database objects (Christopher)

  • Allow psql to display group membership in \du and \dg (Markus Bertheau)

  • Prevent psql \dn from showing temporary schemas (Bruce)

  • Allow psql to handle tilde user expansion for file names (Zach Irmen)

  • Allow psql to display fancy prompts, including color, via readline (Reece Hart, Chet Ramey)

  • Make psql \copy match COPY command syntax

  • Show the location of syntax errors (Fabien Coelho, Tom)

  • Add CLUSTER information to psql \d display

  • Change psql \copy stdin/stdout to read from command input/output (Bruce)

  • Add pstdin/pstdout to read from psql's stdin/stdout (Mark Feit)

  • Add global psql configuration file, psqlrc.sample

    This allows a central file where global psql startup commands can be stored.

  • Have psql \d+ indicate if the table has an OID column (Neil)

  • On Windows, use binary mode in psql when reading files so control-Z is not seen as end-of-file

  • Have \dn+ show permissions and description for schemas (Dennis Bjorklund)

  • Improve tab completion support (Stefan Kaltenbrunn, Greg Sabino Mullane)

  • Allow boolean settings to be set using upper or lower case (Michael Paesold)

E.1.4.9. pg_dump 改变

  • Use dependency information to improve the reliability of pg_dump (Tom)

    This should solve the longstanding problems with related objects sometimes being dumped in the wrong order.

  • Have pg_dump output objects in alphabetical order if possible (Tom)

    This should make it easier to identify changes between dump files.

  • Allow pg_restore to ignore some SQL errors (Fabien Coelho)

    This makes pg_restore's behavior similar to the results of feeding a pg_dump output script to psql. In most cases, ignoring errors and plowing ahead is the most useful thing to do. Also added was a pg_restore option to give the old behavior of exiting on an error.

  • pg_restore -l display now includes objects' schema names

  • New begin/end markers in pg_dump text output (Bruce)

  • Add start/stop times for pg_dump/pg_dumpall in verbose mode (Bruce)

  • Allow most pg_dump options in pg_dumpall (Christopher)

  • Have pg_dump use ALTER OWNER rather than SET SESSION AUTHORIZATION by default (Christopher)

E.1.4.10. libpq Changes

  • Make libpq's SIGPIPE handling thread-safe (Bruce)

  • Add PQmbdsplen() which returns the display length of a character (Tatsuo)

  • Add thread locking to SSL and Kerberos connections (Manfred Spraul)

  • Allow PQoidValue(), PQcmdTuples(), and PQoidStatus() to work on EXECUTE commands (Neil)

  • Add PQserverVersion() to provide more convenient access to the server version number (Greg Sabino Mullane)

  • Add PQprepare/PQsendPrepared() functions to support preparing statements without necessarily specifying the data types of their parameters (Abhijit Menon-Sen)

  • Many ECPG improvements, including SET DESCRIPTOR (Michael)

E.1.4.11. Source Code Changes

  • Allow the database server to run natively on Windows (Claudio, Magnus, Andrew)

  • Shell script commands converted to C versions for Windows support (Andrew)

  • Use --with-docdir to choose installation location of documentation; also allow --infodir (Peter)

  • Create an extension makefile framework (Fabien Coelho, Peter)

    This simplifies the task of building extensions outside the original source tree.

  • Support relocatable installations (Bruce)

    Directory paths for installed files (such as the /share directory) are now computed relative to the actual location of the executables, so that an installation tree can be moved to another place without reconfiguring and rebuilding.

  • Use --with-docdir to choose installation location of documentation; also allow --infodir (Peter)

  • Add --without-docdir to prevent installation of documentation (Peter)

  • Upgrade to DocBook V4.2 SGML (Peter)

  • New PostgreSQL CVS tag (Marc)

    This was done to make it easier for organizations to manage their own copies of the PostgreSQL CVS repository. File version stamps from the master repository will not get munged by checking into or out of a copied repository.

  • Clarify locking code (Manfred Koizar)

  • Buffer manager cleanup (Neil)

  • Decouple platform tests from CPU spinlock code (Bruce, Tom)

  • Add inlined test-and-set code on PA-RISC for gcc (ViSolve, Tom)

  • Improve i386 spinlock code (Manfred Spraul)

  • Clean up spinlock assembly code to avoid warnings from newer gcc releases (Tom)

  • Remove JDBC from source tree; now a separate project

  • Remove the libpgtcl client interface; now a separate project

  • More accurately estimate memory and file descriptor usage (Tom)

  • Improvements to the MAC OS X startup scripts (Ray A.)

  • New fsync() test program (Bruce)

  • Major documentation improvements (Neil, Peter)

  • Remove pg_encoding; not needed anymore

  • Remove pg_id; not needed anymore

  • Remove initlocation; not needed anymore

  • Auto-detect thread flags (no more manual testing) (Bruce)

  • Use Olson's public domain timezone library (Magnus)

  • With threading enabled, use thread flags on Unixware for backend executables too (Bruce)

    Unixware can not mix threaded and non-threaded object files in the same executable, so everything must be compiled as threaded.

  • psql now uses a flex-generated lexical analyzer to process command strings

  • Reimplement the linked list data structure used throughout the backend (Neil)

    This improvs performance by allowing list append operations to be more efficient.

  • Allow dynamically loaded modules to create their own server configuration parameters (Thomas Hallgren)

  • New Brazilian version of FAQ (Euler Taveira de Oliveira)

  • Add French FAQ (Guillaume Lelarge)

  • New pgevent for Windows logging

  • Make libpq and ECPG build as proper shared libraries on OS X (Tom)

E.1.4.12. Contrib Changes

  • Overhaul of contrib/dblink (Joe)

  • contrib/dbmirror improvements (Steven Singer)

  • New contrib/xml2 (John Gray, Torchbox)

  • Updated contrib/mysql

  • New version of contrib/btree_gist (Teodor)

  • New contrib/trgm, trigram matching for PostgreSQL (Teodor)

  • Many contrib/tsearch2 improvements (Teodor)

  • Add double metaphone to contrib/fuzzystrmatch (Andrew)

  • Allow contrib/pg_autovacuum to run as a Windows service (Dave Page)

  • Add functions to contrib/dbsize (Andreas Pflug)

  • Removed contrib/pg_logger: obsoleted by integrated logging subprocess

  • Removed contrib/rserv: obsoleted by various separate projects