Postgres 8.4 升迁注意事项
A dump/restore using pg_dump is required for those wishing to migrate data from any previous release.
从旧版本迁移数据之前,必须要用 pg_dump 备份数据。
Observe the following incompatibilities:
注意以下不兼容问题:
General 普遍性
- Use 64-bit integer datetimes by default (Neil Conway) 默认使用 64 位整数计时 Previously this was selected by configure's —enable-integer-datetimes option. To retain the old behavior, build with —disable-integer-datetimes.
这里预设的配置项是 —enable-integer-datetimes,如果想要兼容旧版本,用 —disable-integer-datetimes 编译
- Remove ipcclean utility command (Bruce) 去掉了 ipcclean 工具命令
The utility only worked on a few platforms. Users should use their operating system tools instead.
该工具只在少数几个平台可以工作,用户应该改用操作系统工具
Server Settings 服务器设置
- Change default setting for log_min_messages to warning (previously it was notice) to reduce log file volume (Tom) 将服务器日志的默认级别改为警告 (以前默认为通知)以优化日志文件。
- Change default setting for max_prepared_transactions to zero (previously it was 5) (Tom) max_prepared_transactions 的默认设置为 0 (之前的版本为 5)。
- Make debug_print_parse, debug_print_rewritten, and debug_print_plan output appear at LOG message level, not DEBUG1 as formerly (Tom) debug_print_parse, debug_print_rewritten, 以及 debug_print_plan 出现于消息 (message)级日志,而不是众所周知的 DEBUG 级日志。
- Make debug_pretty_print default to on (Tom)
默认 debug_pretty_print 打开。
- Remove explain_pretty_print parameter (no longer needed) (Tom)
删除 explain_pretty_print 参数(不再需要了)。
- Make log_temp_files settable by superusers only, like other logging options (Simon Riggs) 只有 superusers 可以设置 log_temp_files 之类的选项。
- Remove automatic appending of the epoch timestamp when no % escapes are present in log_filename (Robert Haas) log_filename 中没有 % 符号的话就删除时间戳之前的自增计数。
This change was made because some users wanted a fixed log filename, for use with an external log rotation tool.
这个改变主要因为有些用户想要一个可变的日志文件名,以便使用外部的日志切分工具。
- Remove log_restartpoints from recovery.conf; instead use log_checkpoints (Simon) 从 recovery.conf 中删除了 log_restartpoints, 用 log_checkpoints 取而代之。
- Remove krb_realm and krb_server_hostname; these are now set in pg_hba.conf instead (Magnus) 删除了 krb_realm 和 krb_server_hostname;它们现在在 pg_hba.conf 中设置
- There are also significant changes in pg_hba.conf, as described below. 同上, significant 也改为在 pg_hba.conf中设置。
Queries 查询
- Change TRUNCATE and LOCK to apply to child tables of the specified table(s) (Peter) 事务和锁改为作用于指定表的子表。
These commands now accept an ONLY option that prevents processing child tables; this option must be used if the old behavior is needed.
这些命令现在接受 ONLY 选项以避免锁定子表;如果想要表现的跟过去一样,就要使用这个选项。
- SELECT
DISTINCT and UNION/INTERSECT/EXCEPT no longer always produce sorted
output (Tom) SELECT DISTINCT 和 UNION/INTERSECT/EXCEPT 不再预排输出。
Previously, these types of queries always removed duplicate rows by means of Sort/Unique processing (i.e., sort then remove adjacent duplicates). Now they can be implemented by hashing, which will not produce sorted output. If an application relied on the output being in sorted order, the recommended fix is to add an ORDER BY clause. As a short-term workaround, the previous behavior can be restored by disabling enable_hashagg, but that is a very performance-expensive fix. SELECT DISTINCT ON never uses hashing, however, so its behavior is unchanged.
以 前,这些类型的查询总是删除重行,进行排序/去重处理(也就是说,排序,然后去除重复行)。现在他们可能由不处理排序输出的 hash 实现。如果应用需要排序,就需要加ORDER BY 子句。有个简单的办法,禁止 enable_hashagg 选项可以恢复以前的行为,不过这个修正(即指新的hash式的查询—— 译者)有极大的性能提高。 SELECT DISTINCT ON 从不使用 hash,所以这个行为没有改变。
- Force child tables to inherit CHECK constraints from parents (Alex) Hunsaker, Nikhil Sontakke, Tom) 强制子表继承主表的 CHECK约束。
Formerly it was possible to drop such a constraint from a child table, allowing rows that violate the constraint to be visible when scanning the parent table. This was deemed inconsistent, as well as contrary to SQL standard.
以前可能会因为子表允许违反约束,从而在扫描主表的时候破坏主表的约束。与SQL标准相反,这样不安全。
- Disallow negative LIMIT or OFFSET values, rather than treating them as zero (Simon) 不允许负的 LIMITE 或 OFFSET,将它们截断为0。
- Disallow LOCK TABLE outside a transaction block (Tom) 在事务块之外不允许 LOCK TABLE。
Such an operation is useless because the lock would be released immediately.
这种操作没用,因为锁立即就释放了。
- Sequences now contain an additional start_value column (Zoltan Boszormenyi) 序列现在包括一个附加的 start_value。
This supports ALTER SEQUENCE ... RESTART.
由此支持 ALTER SEQUENCESE ... RESTART。
Functions and Operators 函数和操作符
- Make
numeric zero raised to a fractional power return 0, rather than
throwing an error, and make numeric zero raised to the zero power
return 1, rather than error (Bruce)
数值0的极小幂运算得到0,而不是抛出错误,0的0次幂返回1,而不是错误。
This matches the longstanding float8 behavior.
这与 float8 一贯的行为一致。
- Allow unary minus of floating-point values to produce minus zero (Tom) 允许一元负浮点数生成负0.
The changed behavior is more IEEE-standard compliant.
这个改变与 IEEE-标准 更为符合。
- Throw an error if an escape character is the last character in a LIKE pattern (i.e., it has nothing to escape) (Tom)
如果 LIKE 匹配模式的最后一个字符是逃逸字符(反斜杠/——译者)就抛出错误(也就是说,此时没有什么可以逃逸的)
Previously, such an escape character was silently ignored, thus possibly masking application logic errors.
以前,这么个逃逸字符杵在后面,就被默默的无视了,然后应用程序吃了这个数据可能就挂了。
- Remove ~=~ and ~<>~ operators formerly used for LIKE index comparisons (Tom) 去掉 Like 索引中的 ~=~ 和 ~<>~ 比较操作符
Pattern indexes now use the regular equality operator.
模式索引现在使用正则判等操作符。
- xpath() now passes its arguments to libxml without any changes (Andrew) xpath() 现在传递参数的时候跟 libxml 实话实说了。
This means that the XML argument must be a well-formed XML document. The previous coding attempted to allow XML fragments, but it did not work well.
也就是说,XML 参数必须是格式良好的 XML 文档。以前你传个 XML 片段它也默默的承受了,但是干起活来不怎么理想。
- Make xmlelement() format attribute values just like content values (Peter) xmlelement() 像正文格式一样格式化属性值。
Previously, attribute values were formatted according to the normal SQL output behavior, which is sometimes at odds with XML rules.
以前,属性和普通的 SQL 输出一样格式化,有时候会与 XML 规则不符。
- Rewrite memory management for libxml-using functions (Tom) 为使用 libxml 的函数重写了内存管理。
This change should avoid some compatibility problems with use of libxml in PL/Perl and other add-on code.
这样在 PL/Perl 之类的代码中就避免了一些水土不服的问题。
- Adopt a faster algorithm for hash functions (Kenneth Marshall, based on work of Bob Jenkins) hash 函数使用了一个更快的算法。
Many of the built-in hash functions now deliver different results on little-endian and big-endian platforms.
很多内置的hash函数在高位和低位字节存储平台上会有不同的结果。
Temporal Functions and Operators 临时函数和操作符
- DateStyle no longer controls interval output formatting; instead there is a new variable IntervalStyle (Ron Mayer) DateStyle 不再控制输出格式间隙,现在由一个新的变量 IntervalStyle 来做这个事。
- Improve consistency of handling of
fractional seconds in timestamp and interval output (Ron Mayer)
优化时间戳(timestamp)的分秒控制和间隔输出。
This may result in displaying a different number of fractional digits than before, or rounding instead of truncating.
这样显示结果可能会跟以前有些不同,有更多的小数位,或者舍入而不是截断。
- Make to_char()'s localized month/day names depend on LC_TIME, not LC_MESSAGES (Euler Taveira de Oliveira) 现在 to_char() 的月/日本地化显示依赖于 LC_TIME ,而不是 LC_MESSAGES。
- Cause to_date() and to_timestamp() to
more consistently report errors for invalid input (Brendan Jurd)
to_date 和 to_timestamp 传入无效数据时都会有更多的错误报告。
Previous versions would often ignore or silently misread input that did not match the format string. Such cases will now result in an error.
以前的版本经常无视或者默默的吃掉不匹配的格式化字符串。现在会报错。
- Fix to_timestamp() to not require upper/lower case matching for meridian (AM/PM) and era (BC/AD) format designations (Brendan Jurd) to_timestamp() 已经调教好,它不再要求正午(AM/PM)和纪元(BC/AD)大小写匹配。
For example, input value ad now matches the format string AD.
例如,现在输入个 ad ,就相当于 AD。