E.67. リリース 7.3

リリース日: 2002-11-27

E.67.1. 概要

このリリースにおける大きな変更は以下の通りです。

スキーマ

スキーマを使用すると、ユーザはオブジェクトを独立した名前空間で作成することができます。これにより、2 人の人や、2 つのアプリケーションが同じ名前のテーブルを作成できます。 共有テーブル用のパブリックスキーマもあります。 テーブル / インデックスの作成は、パブリックスキーマへのアクセス許可を禁止することで制御できます。

列の削除

PostgreSQLALTER TABLE ... DROP COLUMN 機能をサポートするようになりました。

テーブル関数

複数の行および / または複数の列を返す関数が、以前よりもずっと使いやすくなりました。 そのような "テーブル関数" を、SELECT FROM 句で呼び出し、その出力をテーブルのように扱うことができます。 また、PL/pgSQL の関数はセットを返すことができるようになりました。

プリペアードクエリ

パフォーマンス向上のため、PostgreSQLはプリペアードクエリをサポートするようになりました。

依存関係追跡

PostgreSQLはオブジェクトの依存関係を記録するようになりました。これにより様々な点が向上されます。 DROP 文は、CASCADE または RESTRICT を使用して、依存関係のあるオブジェクトも削除されるかどうかを制御するようになりました。

権限

関数や手続き言語は権限をサポートするようになり、関数は作成者の権限で実行されるように定義可能になりました。

国際化

マルチバイトとロケールのサポートは、どちらも常に有効となりました。

ロギング

様々なロギングオプションが強化されました。

インタフェース

多数のインタフェースが http://gborg.postgresql.org に移動され、それぞれ独立して開発およびリリースされることが可能になりました。

関数 / 識別子

デフォルトで、関数は 32 個までのパラメータを取ることができ、識別子は 63 バイトの長さにできるようになりました。また、OPAQUE の使用は非難されるようになったため、関数の引数および結果の型において、以前 OPAQUE がそれぞれ意味していたことを表すために、特定の "擬似データ型" がサポートされるようになりました。

E.67.2. バージョン 7.3 への移行

以前のリリースからデータを移行する場合、pg_dump によるダンプ/リストアが必要です。 アプリケーションがシステムカタログを参照する場合、7.3 におけるスキーマの導入のために、追加的な変更が必要になります。詳しくは、http://developer.postgresql.org/~momjian/upgrade_tips_7.3 を参照してください。

以下の非互換性について検討して下さい。 >

E.67.3. 変更点

E.67.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.67.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.67.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.67.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.67.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.67.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.67.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.67.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.67.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.67.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.67.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.67.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.67.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.67.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.67.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.67.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)