E.17. 版本 7.3

发布日期: 2002-11-27

E.17.1. 概述

这个版本的主要改变:

模式

模式允许用户在独立的名字空间里创建对象,这样两个人或者应用 就可以有同样名字的表。还有一个public模式用于共享的表。 表/索引的创建可以通过删除在 public 模式上的权限来约束。

删除字段

PostgreSQL 现在支持 ALTER TABLE ... DROP COLUMN 功能。

表函数

返回多行和/和多字段的函数现在用起来比以前更方便了。 你可以在 SELECTFROM 子句里 调用这样的"表函数",把它的输出当作一个表来看待。 还有,PL/pgSQL 函数现在可以返回集合了。

准备好的查询

PostgreSQL 现在支持准备好的查询,用来改善性能。

依赖性追踪

PostgreSQL 现在记录对象的依赖性,这样就允许它在许多方面 的改进。DROP 语句现在可以接受 CASCADE 或者 RESTRICT 来控制依赖的对象是否同样删除。

权限

函数和过程语言现在有权限了,并且函数可以定义为以他们的创建者 的权限运行。

国际化

现在多字节和区域支持总是打开的。

日志

增强了一系列日志选项。

接口

大量接口转移到了 http://gborg.postgresql.org, 这样它们可以独立开发和发布。

函数/标识符

缺省时,函数现在可以接受最多 32 个参数,而标识符可以有 63 个字节长。 同样,OPAQUE 现在废弃了:有专用的"伪类型"来代表 以前在函数参数和结果类型里的每种OPAQUE的含义。

E.17.2. 移植到版本 7.3

对于使用任何以前版本的人来说,用 pg_dump 进行一次转储/恢复是必需的. 如果你的应用检查系统表,那么还需要做一些额外的修改, 因为 7.3 里引入了模式;更多的相关信息,参阅: ttp://www.ca.postgresql.org/docs/momjian/upgrade_tips_7.3

观察下列不兼容的地方:

E.17.3. 修补

E.17.3.1. 服务器操作

  • Add pg_locks view to show locks (Neil)

  • Security fixes for password negotiation memory allocation (Neil)

  • Remove support for version 0 FE/BE protocol (PostgreSQL 6.2 and earlier) (Tom)

  • Reserve the last few backend slots for superusers, add parameter superuser_reserved_connections to control this (Nigel J. Andrews)

E.17.3.2. 性能

  • Improve startup by calling localtime() only once (Tom)

  • Cache system catalog information in flat files for faster startup (Tom)

  • Improve caching of index information (Tom)

  • Optimizer improvements (Tom, Fernando Nasser)

  • Catalog caches now store failed lookups (Tom)

  • Hash function improvements (Neil)

  • Improve performance of query tokenization and network handling (Peter)

  • Speed improvement for large object restore (Mario Weilguni)

  • Mark expired index entries on first lookup, saving later heap fetches (Tom)

  • Avoid excessive NULL bitmap padding (Manfred Koizar)

  • Add BSD-licensed qsort() for Solaris, for performance (Bruce)

  • Reduce per-row overhead by four bytes (Manfred Koizar)

  • Fix GEQO optimizer bug (Neil Conway)

  • Make WITHOUT OID actually save four bytes per row (Manfred Koizar)

  • Add default_statistics_target variable to specify ANALYZE buckets (Neil)

  • Use local buffer cache for temporary tables so no WAL overhead (Tom)

  • Improve free space map performance on large tables (Stephen Marshall, Tom)

  • Improved WAL write concurrency (Tom)

E.17.3.3. 权限

  • Add privileges on functions and procedural languages (Peter)

  • Add OWNER to CREATE DATABASE so superusers can create databases on behalf of unprivileged users (Gavin Sherry, Tom)

  • Add new object privilege bits EXECUTE and USAGE (Tom)

  • Add SET SESSION AUTHORIZATION DEFAULT and RESET SESSION AUTHORIZATION (Tom)

  • Allow functions to be executed with the privilege of the function owner (Peter)

E.17.3.4. 服务器配置

  • Server log messages now tagged with LOG, not DEBUG (Bruce)

  • Add user column to pg_hba.conf (Bruce)

  • Have log_connections output two lines in log file (Tom)

  • Remove debug_level from postgresql.conf, now server_min_messages (Bruce)

  • New ALTER DATABASE/USER ... SET command for per-user/database initialization (Peter)

  • New parameters server_min_messages and client_min_messages to control which messages are sent to the server logs or client applications (Bruce)

  • Allow pg_hba.conf to specify lists of users/databases separated by commas, group names prepended with +, and file names prepended with @ (Bruce)

  • Remove secondary password file capability and pg_password utility (Bruce)

  • Add variable db_user_namespace for database-local user names (Bruce)

  • SSL improvements (Bear Giles)

  • Make encryption of stored passwords the default (Bruce)

  • Allow pg_statistics to be reset by calling pg_stat_reset() (Christopher)

  • Add log_duration parameter (Bruce)

  • Rename debug_print_query to log_statement (Bruce)

  • Rename show_query_stats to show_statement_stats (Bruce)

  • Add param log_min_error_statement to print commands to logs on error (Gavin)

E.17.3.5. 查询

  • Make cursors insensitive, meaning their contents do not change (Tom)

  • Disable LIMIT #,# syntax; now only LIMIT # OFFSET # supported (Bruce)

  • Increase identifier length to 63 (Neil, Bruce)

  • UNION fixes for merging >= 3 columns of different lengths (Tom)

  • Add DEFAULT key word to INSERT, e.g., INSERT ... (..., DEFAULT, ...) (Rod)

  • Allow views to have default values using ALTER COLUMN ... SET DEFAULT (Neil)

  • Fail on INSERTs with column lists that don't supply all column values, e.g., INSERT INTO tab (col1, col2) VALUES ('val1'); (Rod)

  • Fix for join aliases (Tom)

  • Fix for FULL OUTER JOINs (Tom)

  • Improve reporting of invalid identifier and location (Tom, Gavin)

  • Fix OPEN cursor(args) (Tom)

  • Allow 'ctid' to be used in a view and currtid(viewname) (Hiroshi)

  • Fix for CREATE TABLE AS with UNION (Tom)

  • SQL99 syntax improvements (Thomas)

  • Add statement_timeout variable to cancel queries (Bruce)

  • Allow prepared queries with PREPARE/EXECUTE (Neil)

  • Allow FOR UPDATE to appear after LIMIT/OFFSET (Bruce)

  • Add variable autocommit (Tom, David Van Wie)

E.17.3.6. 对象操作

  • Make equals signs optional in CREATE DATABASE (Gavin Sherry)

  • Make ALTER TABLE OWNER change index ownership too (Neil)

  • New ALTER TABLE tabname ALTER COLUMN colname SET STORAGE controls TOAST storage, compression (John Gray)

  • Add schema support, CREATE/DROP SCHEMA (Tom)

  • Create schema for temporary tables (Tom)

  • Add variable search_path for schema search (Tom)

  • Add ALTER TABLE SET/DROP NOT NULL (Christopher)

  • New CREATE FUNCTION volatility levels (Tom)

  • Make rule names unique only per table (Tom)

  • Add 'ON tablename' clause to DROP RULE and COMMENT ON RULE (Tom)

  • Add ALTER TRIGGER RENAME (Joe)

  • New current_schema() and current_schemas() inquiry functions (Tom)

  • Allow functions to return multiple rows (table functions) (Joe)

  • Make WITH optional in CREATE DATABASE, for consistency (Bruce)

  • Add object dependency tracking (Rod, Tom)

  • Add RESTRICT/CASCADE to DROP commands (Rod)

  • Add ALTER TABLE DROP for non-CHECK CONSTRAINT (Rod)

  • Autodestroy sequence on DROP of table with SERIAL (Rod)

  • Prevent column dropping if column is used by foreign key (Rod)

  • Automatically drop constraints/functions when object is dropped (Rod)

  • Add CREATE/DROP OPERATOR CLASS (Bill Studenmund, Tom)

  • Add ALTER TABLE DROP COLUMN (Christopher, Tom, Hiroshi)

  • Prevent inherited columns from being removed or renamed (Alvaro Herrera)

  • Fix foreign key constraints to not error on intermediate database states (Stephan)

  • Propagate column or table renaming to foreign key constraints

  • Add CREATE OR REPLACE VIEW (Gavin, Neil, Tom)

  • Add CREATE OR REPLACE RULE (Gavin, Neil, Tom)

  • Have rules execute alphabetically, returning more predictable values (Tom)

  • Triggers are now fired in alphabetical order (Tom)

  • Add /contrib/adddepend to handle pre-7.3 object dependencies (Rod)

  • Allow better casting when inserting/updating values (Tom)

E.17.3.7. 工具命令

  • Have COPY TO output embedded carriage returns and newlines as \r and \n (Tom)

  • Allow DELIMITER in COPY FROM to be 8-bit clean (Tatsuo)

  • Make pg_dump use ALTER TABLE ADD PRIMARY KEY, for performance (Neil)

  • Disable brackets in multistatement rules (Bruce)

  • Disable VACUUM from being called inside a function (Bruce)

  • Allow dropdb and other scripts to use identifiers with spaces (Bruce)

  • Restrict database comment changes to the current database

  • Allow comments on operators, independent of the underlying function (Rod)

  • Rollback SET commands in aborted transactions (Tom)

  • EXPLAIN now outputs as a query (Tom)

  • Display condition expressions and sort keys in EXPLAIN (Tom)

  • Add 'SET LOCAL var = value' to set configuration variables for a single transaction (Tom)

  • Allow ANALYZE to run in a transaction (Bruce)

  • Improve COPY syntax using new WITH clauses, keep backward compatibility (Bruce)

  • Fix pg_dump to consistently output tags in non-ASCII dumps (Bruce)

  • Make foreign key constraints clearer in dump file (Rod)

  • Add COMMENT ON CONSTRAINT (Rod)

  • Allow COPY TO/FROM to specify column names (Brent Verner)

  • Dump UNIQUE and PRIMARY KEY constraints as ALTER TABLE (Rod)

  • Have SHOW output a query result (Joe)

  • Generate failure on short COPY lines rather than pad NULLs (Neil)

  • Fix CLUSTER to preserve all table attributes (Alvaro Herrera)

  • New pg_settings table to view/modify GUC settings (Joe)

  • Add smart quoting, portability improvements to pg_dump output (Peter)

  • Dump serial columns out as SERIAL (Tom)

  • Enable large file support, >2G for pg_dump (Peter, Philip Warner, Bruce)

  • Disallow TRUNCATE on tables that are involved in referential constraints (Rod)

  • Have TRUNCATE also auto-truncate the toast table of the relation (Tom)

  • Add clusterdb utility that will auto-cluster an entire database based on previous CLUSTER operations (Alvaro Herrera)

  • Overhaul pg_dumpall (Peter)

  • Allow REINDEX of TOAST tables (Tom)

  • Implemented START TRANSACTION, per SQL99 (Neil)

  • Fix rare index corruption when a page split affects bulk delete (Tom)

  • Fix ALTER TABLE ... ADD COLUMN for inheritance (Alvaro Herrera)

E.17.3.8. 数据类型和函数

  • Fix factorial(0) to return 1 (Bruce)

  • Date/time/timezone improvements (Thomas)

  • Fix for array slice extraction (Tom)

  • Fix extract/date_part to report proper microseconds for timestamp (Tatsuo)

  • Allow text_substr() and bytea_substr() to read TOAST values more efficiently (John Gray)

  • Add domain support (Rod)

  • Make WITHOUT TIME ZONE the default for TIMESTAMP and TIME data types (Thomas)

  • Allow alternate storage scheme of 64-bit integers for date/time types using --enable-integer-datetimes in configure (Thomas)

  • Make timezone(timestamptz) return timestamp rather than a string (Thomas)

  • Allow fractional seconds in date/time types for dates prior to 1BC (Thomas)

  • Limit timestamp data types to 6 decimal places of precision (Thomas)

  • Change timezone conversion functions from timetz() to timezone() (Thomas)

  • Add configuration variables datestyle and timezone (Tom)

  • Add OVERLAY(), which allows substitution of a substring in a string (Thomas)

  • Add SIMILAR TO (Thomas, Tom)

  • Add regular expression SUBSTRING(string FROM pat FOR escape) (Thomas)

  • Add LOCALTIME and LOCALTIMESTAMP functions (Thomas)

  • Add named composite types using CREATE TYPE typename AS (column) (Joe)

  • Allow composite type definition in the table alias clause (Joe)

  • Add new API to simplify creation of C language table functions (Joe)

  • Remove ODBC-compatible empty parentheses from calls to SQL99 functions for which these parentheses do not match the standard (Thomas)

  • Allow macaddr data type to accept 12 hex digits with no separators (Mike Wyer)

  • Add CREATE/DROP CAST (Peter)

  • Add IS DISTINCT FROM operator (Thomas)

  • Add SQL99 TREAT() function, synonym for CAST() (Thomas)

  • Add pg_backend_pid() to output backend pid (Bruce)

  • Add IS OF / IS NOT OF type predicate (Thomas)

  • Allow bit string constants without fully-specified length (Thomas)

  • Allow conversion between 8-byte integers and bit strings (Thomas)

  • Implement hex literal conversion to bit string literal (Thomas)

  • Allow table functions to appear in the FROM clause (Joe)

  • Increase maximum number of function parameters to 32 (Bruce)

  • No longer automatically create index for SERIAL column (Tom)

  • Add current_database() (Rod)

  • Fix cash_words() to not overflow buffer (Tom)

  • Add functions replace(), split_part(), to_hex() (Joe)

  • Fix LIKE for bytea as a right-hand argument (Joe)

  • Prevent crashes caused by SELECT cash_out(2) (Tom)

  • Fix to_char(1,'FM999.99') to return a period (Karel)

  • Fix trigger/type/language functions returning OPAQUE to return proper type (Tom)

E.17.3.9. 国际化

  • Add additional encodings: Korean (JOHAB), Thai (WIN874), Vietnamese (TCVN), Arabic (WIN1256), Simplified Chinese (GBK), Korean (UHC) (Eiji Tokuya)

  • Enable locale support by default (Peter)

  • Add locale variables (Peter)

  • Escape byes >= 0x7f for multibyte in PQescapeBytea/PQunescapeBytea (Tatsuo)

  • Add locale awareness to regular expression character classes

  • Enable multibyte support by default (Tatsuo)

  • Add GB18030 multibyte support (Bill Huang)

  • Add CREATE/DROP CONVERSION, allowing loadable encodings (Tatsuo, Kaori)

  • Add pg_conversion table (Tatsuo)

  • Add SQL99 CONVERT() function (Tatsuo)

  • pg_dumpall, pg_controldata, and pg_resetxlog now national-language aware (Peter)

  • New and updated translations

E.17.3.10. 服务器端语言

  • Allow recursive SQL function (Peter)

  • Change PL/Tcl build to use configured compiler and Makefile.shlib (Peter)

  • Overhaul the PL/pgSQL FOUND variable to be more Oracle-compatible (Neil, Tom)

  • Allow PL/pgSQL to handle quoted identifiers (Tom)

  • Allow set-returning PL/pgSQL functions (Neil)

  • Make PL/pgSQL schema-aware (Joe)

  • Remove some memory leaks (Nigel J. Andrews, Tom)

E.17.3.11. psql

  • Don't lowercase psql \connect database name for 7.2.0 compatibility (Tom)

  • Add psql \timing to time user queries (Greg Sabino Mullane)

  • Have psql \d show index information (Greg Sabino Mullane)

  • New psql \dD shows domains (Jonathan Eisler)

  • Allow psql to show rules on views (Paul ?)

  • Fix for psql variable substitution (Tom)

  • Allow psql \d to show temporary table structure (Tom)

  • Allow psql \d to show foreign keys (Rod)

  • Fix \? to honor \pset pager (Bruce)

  • Have psql reports its version number on startup (Tom)

  • Allow \copy to specify column names (Tom)

E.17.3.12. libpq

  • Add ~/.pgpass to store host/user password combinations (Alvaro Herrera)

  • Add PQunescapeBytea() function to libpq (Patrick Welche)

  • Fix for sending large queries over non-blocking connections (Bernhard Herzog)

  • Fix for libpq using timers on Win9X (David Ford)

  • Allow libpq notify to handle servers with different-length identifiers (Tom)

  • Add libpq PQescapeString() and PQescapeBytea() to Windows (Bruce)

  • Fix for SSL with non-blocking connections (Jack Bates)

  • Add libpq connection timeout parameter (Denis A Ustimenko)

E.17.3.13. JDBC

  • Allow JDBC to compile with JDK 1.4 (Dave)

  • Add JDBC 3 support (Barry)

  • Allows JDBC to set loglevel by adding ?loglevel=X to the connection URL (Barry)

  • Add Driver.info() message that prints out the version number (Barry)

  • Add updateable result sets (Raghu Nidagal, Dave)

  • Add support for callable statements (Paul Bethe)

  • Add query cancel capability

  • Add refresh row (Dave)

  • Fix MD5 encryption handling for multibyte servers (Jun Kawai)

  • Add support for prepared statements (Barry)

E.17.3.14. 杂项接口

  • Fixed ECPG bug concerning octal numbers in single quotes (Michael)

  • Move src/interfaces/libpgeasy to http://gborg.postgresql.org (Marc, Bruce)

  • Improve Python interface (Elliot Lee, Andrew Johnson, Greg Copeland)

  • Add libpgtcl connection close event (Gerhard Hintermayer)

  • Move src/interfaces/libpq++ to http://gborg.postgresql.org (Marc, Bruce)

  • Move src/interfaces/odbc to http://gborg.postgresql.org (Marc)

  • Move src/interfaces/libpgeasy to http://gborg.postgresql.org (Marc, Bruce)

  • Move src/interfaces/perl5 to http://gborg.postgresql.org (Marc, Bruce)

  • Remove src/bin/pgaccess from main tree, now at http://www.pgaccess.org (Bruce)

  • Add pg_on_connection_loss command to libpgtcl (Gerhard Hintermayer, Tom)

E.17.3.15. 源代码

  • Fix for parallel make (Peter)

  • AIX fixes for linking Tcl (Andreas Zeugswetter)

  • Allow PL/Perl to build under Cygwin (Jason Tishler)

  • Improve MIPS compiles (Peter, Oliver Elphick)

  • Require Autoconf version 2.53 (Peter)

  • Require readline and zlib by default in configure (Peter)

  • Allow Solaris to use Intimate Shared Memory (ISM), for performance (Scott Brunza, P.J. Josh Rovero)

  • Always enable syslog in compile, remove --enable-syslog option (Tatsuo)

  • Always enable multibyte in compile, remove --enable-multibyte option (Tatsuo)

  • Always enable locale in compile, remove --enable-locale option (Peter)

  • Fix for Win9x DLL creation (Magnus Naeslund)

  • Fix for link() usage by WAL code on Windows, BeOS (Jason Tishler)

  • Add sys/types.h to c.h, remove from main files (Peter, Bruce)

  • Fix AIX hang on SMP machines (Tomoyuki Niijima)

  • AIX SMP hang fix (Tomoyuki Niijima)

  • Fix pre-1970 date handling on newer glibc libraries (Tom)

  • Fix PowerPC SMP locking (Tom)

  • Prevent gcc -ffast-math from being used (Peter, Tom)

  • Bison >= 1.50 now required for developer builds

  • Kerberos 5 support now builds with Heimdal (Peter)

  • Add appendix in the User's Guide which lists SQL features (Thomas)

  • Improve loadable module linking to use RTLD_NOW (Tom)

  • New error levels WARNING, INFO, LOG, DEBUG[1-5] (Bruce)

  • New src/port directory holds replaced libc functions (Peter, Bruce)

  • New pg_namespace system catalog for schemas (Tom)

  • Add pg_class.relnamespace for schemas (Tom)

  • Add pg_type.typnamespace for schemas (Tom)

  • Add pg_proc.pronamespace for schemas (Tom)

  • Restructure aggregates to have pg_proc entries (Tom)

  • System relations now have their own namespace, pg_* test not required (Fernando Nasser)

  • Rename TOAST index names to be *_index rather than *_idx (Neil)

  • Add namespaces for operators, opclasses (Tom)

  • Add additional checks to server control file (Thomas)

  • New Polish FAQ (Marcin Mazurek)

  • Add Posix semaphore support (Tom)

  • Document need for reindex (Bruce)

  • Rename some internal identifiers to simplify Windows compile (Jan, Katherine Ward)

  • Add documentation on computing disk space (Bruce)

  • Remove KSQO from GUC (Bruce)

  • Fix memory leak in rtree (Kenneth Been)

  • Modify a few error messages for consistency (Bruce)

  • Remove unused system table columns (Peter)

  • Make system columns NOT NULL where appropriate (Tom)

  • Clean up use of sprintf in favor of snprintf() (Neil, Jukka Holappa)

  • Remove OPAQUE and create specific subtypes (Tom)

  • Cleanups in array internal handling (Joe, Tom)

  • Disallow pg_atoi('') (Bruce)

  • Remove parameter wal_files because WAL files are now recycled (Bruce)

  • Add version numbers to heap pages (Tom)

E.17.3.16. 贡献

  • Allow inet arrays in /contrib/array (Neil)

  • GiST fixes (Teodor Sigaev, Neil)

  • Upgrade /contrib/mysql

  • Add /contrib/dbsize which shows table sizes without vacuum (Peter)

  • Add /contrib/intagg, integer aggregator routines (mlw)

  • Improve /contrib/oid2name (Neil, Bruce)

  • Improve /contrib/tsearch (Oleg, Teodor Sigaev)

  • Cleanups of /contrib/rserver (Alexey V. Borzov)

  • Update /contrib/oracle conversion utility (Gilles Darold)

  • Update /contrib/dblink (Joe)

  • Improve options supported by /contrib/vacuumlo (Mario Weilguni)

  • Improvements to /contrib/intarray (Oleg, Teodor Sigaev, Andrey Oktyabrski)

  • Add /contrib/reindexdb utility (Shaun Thomas)

  • Add indexing to /contrib/isbn_issn (Dan Weston)

  • Add /contrib/dbmirror (Steven Singer)

  • Improve /contrib/pgbench (Neil)

  • Add /contrib/tablefunc table function examples (Joe)

  • Add /contrib/ltree data type for tree structures (Teodor Sigaev, Oleg Bartunov)

  • Move /contrib/pg_controldata, pg_resetxlog into main tree (Bruce)

  • Fixes to /contrib/cube (Bruno Wolff)

  • Improve /contrib/fulltextindex (Christopher)