A.21. 版本 6.5

发布日期: 1999-06-09

这个版本标志着开发队伍对从伯克利继承过来的代码的掌握和理解 达到了一个新的阶段。 你将看到现在我们更容易增加新的特性, 这些得益于我们全世界开发队伍的不断壮大和经验的不断丰富。

下面是一些最引人注意的改变的简介:

多版本并行控制(MVCC)

这个东西废止了我们老式的表级别的锁, 取而代之的是一个比大多数商用数据库系统都先进的锁系统。 在传统的系统里,每行的修改是先锁住,直到提交, 以此避免被其他用户读取。MVCC 利用 PostgreSQL 天生 的多版本特性允许读者在写活动中读取连贯的数据。 写入者继续使用紧凑的 pg_log 事务系统。所有这些都是 在不需要为每行分配一个锁(象传统的数据库系统那样)的情况下进行的。 因此,原则上我们不再受制于简单 的表级别锁;我们有比行级别锁更好的技术。

pg_dump的热备份

pg_dump 利用了新的 MVCC 特性, 可以在数据库保持在线和可以执行查询的情况下, 进行一次持续的数据倾倒 /备份。

数字(Numeric)数据类型

我们现在有了真正的数字数据类型,可以由用户定义精度。

临时表

我们保证临时表在一次数据库会话过程中唯一,并且在会话结束时删除。

新的 SQL 特性

我们现在有了 CASE,INTERSECT 和 EXCEPT 语句。 我们有了新的 LIMIT/OFFSET,SET TRANSACTION ISOLATION LEVEL,SELECT ... FOR UPDATE 和一个改进了的 LOCK TABLE 命令。

提速

感谢我们队伍里的许多聪明的头脑,我们继续给 PostgreSQL 提速。 我们加速了存储器分配,优化,表联合以及行传输 过程。

移植

我们继续扩展我们的移植列表,这回包括了 WinNT/ix86 和 NetBSD/arm32。

接口

大多数接口有了新的版本,现有的功能也被改进了。

文档

在这个文档里所有地方都有新的和更新了的内容。 新的 FAQ已经包含了 SGI 和 AIX 平台的内容。 教程 里包含了来自Stefan Simkovics 的关于 SQL 的介绍性信息。 对于用户手册, 我们包括了关于 postmaster 和更多工具程序的参考 内容,还有一个描述日期时间详细特性的附件。 管理员手册包含了一个 Tom Lane 的新的关于错误分析的章节。 程序员手册 包括了 Stefan 写的查询处理的描述, 以及通过匿名 CVSCVSup获取 PostgreSQL 源码树的详细内容。

A.21.1. 迁移到 v6.5

要想从以前的 PostgreSQL 版本迁移到新的版本, 我们需要进行一次用 pg_dump 进行倾倒/恢复的工作. 不能pg_upgrade 升级到这个版本,因为和以前版本相比, 表在磁盘上的(存储)结构已经经过修改了。

新的多版本并行控制(MVCC)特性在多用户环境里可能有一些不同的表现。 阅读并理解下面段落,确保你现有 的应用将表现出你所希望的特性。

A.21.1.1. 多版本并行控制

因为不管事务的隔离级别是什么,6.5 里的读动作不锁定数据, 一个事务读的数据可能被其他事务覆盖。换句话说,如果 SELECT 返回了一行并不意味着该行在被返回的时候 (也就是说在语句或者事务开始后的某个时间) 真正存在, 也不意味着在当前事务做提交或者回卷之前该行被保护免于被 并行事务删除或者修改。

要保证一行的确实存在或者保护它免于被并行更新,我们必须使用 SELECT FOR UPDATE 或 一个合适的 LOCK TABLE 语句。我们在从以前的 PostgreSQL 和其他环境移植应用时必须考虑这一点。

如果你正用 contrib/refint.* 触发器保证参考完整性, 你就必须记住上面几条。 现在还需要额外的技巧。一个方法是: 如果一个事务准备更新/删除一个主键时,使用 LOCK parent_table IN SHARE ROW EXCLUSIVE MODE 命令,如果一个事务准备更新/插入一个外键时使用 LOCK parent_table IN SHARE MODE命令。

注意: 要注意如果你运行一个处于 SERIALIZABLE 模式下的事务, 在该事务里,你必须在执行任何 DML (SELECT/INSERT/DELETE/UPDATE/FETCH/COPY_TO) 语句之前执行上面提到的 LOCK 命令。

这些不便将在今后我们实现了读污染(未提交)数据的能力 (不管什么隔离级别)和真正的参考完整性后消失。

A.21.2. 修改列表

修补
---------
Fix text<->float8 and text<->float4 conversion functions(Thomas)
Fix for creating tables with mixed-case constraints(Billy)
Change exp()/pow() behavior to generate error on underflow/overflow(Jan)
Fix bug in pg_dump -z
Memory overrun cleanups(Tatsuo)
Fix for lo_import crash(Tatsuo)
Adjust handling of data type names to suppress double quotes(Thomas)
Use type coercion for matching columns and DEFAULT(Thomas)
Fix deadlock so it only checks once after one second of sleep(Bruce)
Fixes for aggregates and PL/pgsql(Hiroshi)
Fix for subquery crash(Vadim)
Fix for libpq function PQfnumber and case-insensitive names(Bahman Rafatjoo)
Fix for large object write-in-middle, no extra block, memory consumption(Tatsuo)
Fix for pg_dump -d or -D and  quote special characters in INSERT
Repair serious problems with dynahash(Tom)
Fix INET/CIDR portability problems
Fix problem with selectivity error in ALTER TABLE ADD COLUMN(Bruce)
Fix executor so mergejoin of different column types works(Tom)
Fix for Alpha OR selectivity bug
Fix OR index selectivity problem(Bruce)
Fix so \d shows proper length for char()/varchar()(Ryan)
Fix tutorial code(Clark)
Improve destroyuser checking(Oliver)
Fix for Kerberos(Rodney McDuff)
Fix for dropping database while dirty buffers(Bruce)
Fix so sequence nextval() can be case-sensitive(Bruce)
Fix !!= operator
Drop buffers before destroying database files(Bruce)
Fix case where executor evaluates functions twice(Tatsuo)
Allow sequence nextval actions to be case-sensitive(Bruce)
Fix optimizer indexing not working for negative numbers(Bruce)
Fix for memory leak in executor with fjIsNull
Fix for aggregate memory leaks(Erik Riedel)
Allow username containing a dash GRANT permissions
Cleanup of NULL in inet types
Clean up system table bugs(Tom)
Fix problems of PAGER and \? command(Masaaki Sakaida)
Reduce default multisegment file size limit to 1GB(Peter)
Fix for dumping of CREATE OPERATOR(Tom)
Fix for backward scanning of cursors(Hiroshi Inoue)
Fix for COPY FROM STDIN when using \i(Tom)
Fix for subselect is compared inside an expression(Jan)
Fix handling of error reporting while returning rows(Tom)
Fix problems with reference to array types(Tom,Jan)
Prevent UPDATE SET oid(Jan)
Fix pg_dump so -t option can handle case-sensitive tablenames
Fixes for GROUP BY in special cases(Tom, Jan)
Fix for memory leak in failed queries(Tom)
DEFAULT now supports mixed-case identifiers(Tom)
Fix for multisegment uses of DROP/RENAME table, indexes(Ole Gjerde)
Disable use of pg_dump with both -o and -d options(Bruce)
Allow pg_dump to properly dump GROUP permissions(Bruce)
Fix GROUP BY in INSERT INTO table SELECT * FROM table2(Jan)
Fix for computations in views(Jan)
Fix for aggregates on array indexes(Tom)
Fix for DEFAULT handles single quotes in value requiring too many quotes
Fix security problem with non-super users importing/exporting large objects(Tom)
Rollback of transaction that creates table cleaned up properly(Tom)
Fix to allow long table and column names to generate proper serial names(Tom)

增强
------------
Add "vacuumdb" utility
Speed up libpq by allocating memory better(Tom)
EXPLAIN all indexes used(Tom)
Implement CASE, COALESCE, NULLIF  expression(Thomas)
New pg_dump table output format(Constantin)
Add string min()/max() functions(Thomas)
Extend new type coercion techniques to aggregates(Thomas)
New moddatetime contrib(Terry)
Update to pgaccess 0.96(Constantin)
Add routines for single-byte "char" type(Thomas)
Improved substr() function(Thomas)
Improved multibyte handling(Tatsuo)
Multiversion concurrency control/MVCC(Vadim)
New Serialized mode(Vadim)
Fix for tables over 2gigs(Peter)
New SET TRANSACTION ISOLATION LEVEL(Vadim)
New LOCK TABLE IN ... MODE(Vadim)
Update ODBC driver(Byron)
New NUMERIC data type(Jan)
New SELECT FOR UPDATE(Vadim)
Handle "NaN" and "Infinity" for input values(Jan)
Improved date/year handling(Thomas)
Improved handling of backend connections(Magnus)
New options ELOG_TIMESTAMPS and USE_SYSLOG options for log files(Massimo)
New TCL_ARRAYS option(Massimo)
New INTERSECT and EXCEPT(Stefan)
New pg_index.indisprimary for primary key tracking(D'Arcy)
New pg_dump option to allow dropping of tables before creation(Brook)
Speedup of row output routines(Tom)
New READ COMMITTED isolation level(Vadim)
New TEMP tables/indexes(Bruce)
Prevent sorting if result is already sorted(Jan)
New memory allocation optimization(Jan)
Allow psql to do \p\g(Bruce)
Allow multiple rule actions(Jan)
Added LIMIT/OFFSET functionality(Jan)
Improve optimizer when joining a large number of tables(Bruce)
New intro to SQL from S. Simkovics' Master's Thesis (Stefan, Thomas)
New intro to backend processing from S. Simkovics' Master's Thesis (Stefan)
Improved int8 support(Ryan Bradetich, Thomas, Tom)
New routines to convert between int8 and text/varchar types(Thomas)
New bushy plans, where meta-tables are joined(Bruce)
Enable right-hand queries by default(Bruce)
Allow reliable maximum number of backends to be set at configure time
      (--with-maxbackends and postmaster switch (-N backends))(Tom)
GEQO default now 10 tables because of optimizer speedups(Tom)
Allow NULL=Var for MS-SQL portability(Michael, Bruce)
Modify contrib check_primary_key() so either "automatic" or "dependent"(Anand)
Allow psql \d on a view show query(Ryan)
Speedup for LIKE(Bruce)
Ecpg fixes/features, see src/interfaces/ecpg/ChangeLog file(Michael)
JDBC fixes/features, see src/interfaces/jdbc/CHANGELOG(Peter)
Make % operator have precedence like /(Bruce)
Add new postgres -O option to allow system table structure changes(Bruce)
Update contrib/pginterface/findoidjoins script(Tom)
Major speedup in vacuum of deleted rows with indexes(Vadim) 
Allow non-SQL functions to run different versions based on arguments(Tom)
Add -E option that shows actual queries sent by \dt and friends(Masaaki Sakaida)
Add version number in start-up banners for psql(Masaaki Sakaida)
New contrib/vacuumlo removes large objects not referenced(Peter)
New initialization for table sizes so non-vacuumed tables perform better(Tom)
Improve error messages when a connection is rejected(Tom)
Support for arrays of char() and varchar() fields(Massimo)
Overhaul of hash code to increase reliability and performance(Tom)
Update to PyGreSQL 2.4(D'Arcy)
Changed debug options so -d4 and -d5 produce different node displays(Jan)
New pg_options: pretty_plan, pretty_parse, pretty_rewritten(Jan)
Better optimization statistics for system table access(Tom)
Better handling of non-default block sizes(Massimo)
Improve GEQO optimizer memory consumption(Tom)
UNION now suppports ORDER BY of columns not in target list(Jan)
Major libpq++ improvements(Vince Vielhaber)
pg_dump now uses -z(ACL's) as default(Bruce)
backend cache, memory speedups(Tom)
have pg_dump do everything in one snapshot transaction(Vadim)
fix for large object memory leakage, fix for pg_dumping(Tom)
INET type now respects netmask for comparisons
Make VACUUM ANALYZE only use a readlock(Vadim)
Allow VIEWs on UNIONS(Jan)
pg_dump now can generate consistent snapshots on active databases(Vadim)

源代码树修改
-------------------
Improve port matching(Tom)
Portability fixes for SunOS
Add NT/Win32 backend port and enable dynamic loading(Magnus and Daniel Horak)
New port to Cobalt Qube(Mips) running Linux(Tatsuo)
Port to NetBSD/m68k(Mr. Mutsuki Nakajima)
Port to NetBSD/sun3(Mr. Mutsuki Nakajima)
Port to NetBSD/macppc(Toshimi Aoki)
Fix for tcl/tk configuration(Vince)
Removed CURRENT keyword for rule queries(Jan)
NT dynamic loading now works(Daniel Horak)
Add ARM32 support(Andrew McMurry)
Better support for HPUX 11 and UnixWare
Improve file handling to be more uniform, prevent file descriptor leak(Tom)
New install commands for plpgsql(Jan)