Chapter 0. Release Notes

Table of Contents

Release 7.0

Release 6.5.3

Release 6.5.2

Release 6.5.1

Postgres95 Beta 0.01

Timing Results

Release 7.0

-----------

This release shows the continued growth of PostgreSQL. There are more

updated items in 7.0 than in any previous release. Don't be concerned

this is a dot-zero release. PostgreSQL does its best to put

out only solid releases, and this one is no exception.

Major changes in this release:

Foreign Keys: Foreign keys are now implemented, with the exception of

PARTIAL MATCH foreign keys. Many users have been asking for this

feature, and we are pleased to finally offer it.

Optimizer Overhaul: Continuing on work started a year ago, the

optimizer has been overhauled in many significant ways, allowing better

query execution processing with faster performance and less memory

usage.

Updated psql: psql, our interactive terminal monitor, has been updated,

with a variety of new features. See the psql manual page for the details.

Upcoming Features: In 7.1, we plan to have outer joins, storage for very long

rows, and a write-ahead logging system.

Bug Fixes

---------

Prevent function calls with more than maximum number of arguments (Tom)

Many fixes for CASE (Tom)

Many array fixes (Tom)

Fix SELECT coalesce(f1,0) FROM int4_tbl GROUP BY f1 (Tom)

Fix SELECT sentence.words[0] FROM sentence GROUP BY sentence.words[0] (Tom)

Allow utility statements in plpgsql (Tom)

Fix GROUP BY scan bug (Tom)

Optimize btree searching for cases where many equal keys exist (Tom)

Allow bare column names to be subscripted as arrays (Tom)

Improvements in SQL grammar processing(Tom)

Fix for views involved in INSERT ... SELECT ... (Tom)

Fix for SELECT a/2, a/2 FROM test_missing_target GROUP BY a/2 (Tom)

Fix for subselects in INSERT ... SELECT (Tom)

Prevent INSERT ... SELECT ... ORDER BY (Tom)

Improve type casting of int and float constants (Tom)

Cleanups for int8 inputs, range checking, and type conversion (Tom)

Fix for SELECT timespan('21:11:26'::time) (Tom)

Fixes for relations greater than 2GB, including vacuum

Improve communication of system table changes to other running backends (Tom)

Improve communication of user table modifications to other running backends (Tom)

Fix handling of temp tables in complex situations (Bruce, Tom)

Disallow DROP TABLE/DROP INDEX inside a transaction block

Prevent exponential space consumption with many AND's and OR's (Tom)

Collect attribute selectivity values for system columns (Tom)

Allow table locking when tables opened, improving concurrent reliability (Tom)

Fix for netmask('x.x.x.x/0') is 255.255.255.255 instead of 0.0.0.0

(Oleg Sharoiko)

Properly quote sequence names in pg_dump (Ross J. Reedstrom)

Prevent DESTROY DATABASE while others accessing

Prevent any rows from being returned by GROUP BY if no rows processed (Tom)

Reduce memory usage of aggregates (Tom)

Fix SELECT COUNT(1) FROM table WHERE ...' if no rows matching WHERE (Tom)

Fix pg_upgrade so it works for MVCC(Tom)

Add nbtree operator class for NUMERIC(Jan)

Fix for SELECT ... WHERE x IN (SELECT ... HAVING SUM(x) > 1) (Tom)

Make TABLE optional keyword in LOCK TABLE (Bruce)

Fix for "f1 datetime default 'now'" (Tom)

Allow comment-only lines, and ;;; lines too. (Tom)

Improve recovery after failed disk writes, disk full (Hiroshi)

Fix cases where table is mentioned in FROM but not joined (Tom)

Allow HAVING clause without aggregate functions (Tom)

Fix for "--" comment and no trailing newline, as seen in Perl

Improve pg_dump failure error reports (Bruce)

Perl fix for large objects containing NUL characters (Douglas Thomson)

Allow sorts and hashes to exceed 2GB file sizes (Tom)

ODBC fix for for large objects (free)

Fix for pg_dump dumping of inherited rules (Tom)

Fix for NULL handling comparisons (Tom)

Fix inconsistent state caused by failed CREATE/DROP commands (Hiroshi)

Fix for dbname with dash

Fix problems with CURRENT_DATE used in DEFAULT (Tom)

Prevent DROP INDEX from interfering with other backends (Tom)

Fix file descriptor leak in verify_password()

Fix for "Unable to identify an operator =$" problem

Fix ODBC so no segfault if CommLog and Debug enabled (Dirk Niggemann)

Fix for recursive exit call (Massimo)

Fix indexing of cidr

Fix for extra-long timezones (Jeroen van Vianen)

Make pg_dump preserve primary key information (Peter E)

Prevent databases with single quotes (Peter E)

Prevent DROP DATABASE inside transaction (Peter E)

ecpg memory leak fixes (Stephen Birch)

Fix for Ethernet MAC addresses (macaddr type) comparisons

Fix for SELECT null::text, SELECT int4fac(null) and SELECT 2 + (null) (Tom)

Fix for LIKE optimization to use indexes with multi-byte encodings (Tom)

Y2K timestamp fix (Massimo)

Fix for date/time types when overflows happened in computations (Tom)

Fix for VACUUM 'HEAP_MOVED_IN was not expected' errors (Tom)

Fix for views with tables/columns containing spaces (Tom)

Allow array on int8 (Peter E)

Prevent permissions on indexes (Peter E)

Fix for rounding/overflow of NUMERIC type, like NUMERIC(4,4) (Tom)

Fix for spinlock stuck problem when error is generated (Hiroshi)

Allow NUMERIC arrays

Fix ipcclean on Linux

Fix handling of NULL constraint conditions (Tom)

Fix bugs in NUMERIC ceil() and floor() functions (Tom)

Make char_length()/octet_length including trailing blanks (Tom)

Made abstime/reltime use int4 instead of time_t (Peter E)

Fix memory leak in odbc driver (Nick Gorham)

Fix r-tree index optimizer selectivity (Thomas)

Enhancements

------------

New CLI interface include file sqlcli.h, based on SQL3/SQL98

Remove all limits on query length, row length limit still exists (Tom)

Improve optimizer selectivity computations and functions (Tom)

Enable fast LIKE index processing only if index present (Tom)

Revise parse_coerce() to handle coercion of int and float constants (Tom)

Re-use free space on index pages with duplicates (Tom)

Improve hash join processing (Tom)

Prevent descending sort if result is already sorted(Hiroshi)

Allow commuting of index scan query qualifications (Tom)

Prefer index scans in cases where ORDER BY/GROUP BY is required (Tom)

Allocate large memory requests in fix-sized chunks for performance (Tom)

Fix vacuum's performance by reducing memory allocation requests (Tom)

Update jdbc protocol to 2.0 (Jens Glaser jens@jens.de)

Add TRUNCATE command to quickly truncate relation (Mike Mascari)

Implement constant-expression simplification (Bernard Frankpitt, Tom)

Fix to give super user and createdb user proper update catalog rights (Peter E)

Allow more than first column to be used to determine start of index scan

(Hiroshi)

Allow ecpg bool variables to have NULL values (Christof)

Issue ecpg error if NULL value is returned to variable with no NULL

indicator (Christof)

Allow ^C to cancel COPY command (Massimo)

Add SET FSYNC and SHOW PG_OPTIONS commands(Massimo)

Improve CREATE FUNCTION to allow type conversion specification

(Bernie Frankpitt)

Add CmdTuples() to libpq++(Vince)

New CREATE CONSTRAINT TRIGGER and SET CONSTRAINTS commands(Jan)

Allow CREATE FUNCTION WITH clause to be used for all language types

configure --enable-debug adds -g (Peter E)

configure --disable-debug removes -g (Peter E)

Allow more complex default expressions (Tom)

First real FOREIGN KEY constraint trigger functionality (Jan)

Add FOREIGN KEY ... MATCH FULL ... ON DELETE CASCADE (Jan)

Add FOREIGN KEY ... MATCH <unspecified> referential actions (Don Baccus)

Allow WHERE restriction on ctid (physical heap location) (Hiroshi)

Move pginterface from contrib to interface directory, rename to pgeasy (Bruce)

Add DEC and SESSION_USER as reserved words

Prevent quadruple use of disk space when doing internal sorting (Tom)

Require SELECT DISTINCT target list to have all ORDER BY columns (Tom)

Add Oracle's COMMENT ON command (Mike Mascari <mascarim@yahoo.

libpq's PQsetNoticeProcessor function now returns previous hook(Peter E)

Prevent PQsetNoticeProcessor from being set to NULL (Peter E)

Make USING in COPY optional (Bruce)

Faster sorting by calling fewer functions (Tom)

Create system indexes to match all system caches(Bruce, Hiroshi)

Make system caches use system indexes(Bruce)

Make all system indexes unique(Bruce)

Allow subselects in the target list (Tom)

Allow subselects on the left side of comparison operators (Tom)

New parallel regression test (Jan)

Change backend-side COPY to write files with permissions 644 not 666 (Tom)

Force permissions on PGDATA directory to be secure, even if it exists (Tom)

Added psql LastOid variable to return last inserted oid (Peter E)

Improve pg_statistics management for VACUUM speed improvement (Tom)

Allow concurrent vacuum and remove pg_vlock vacuum lock file (Tom)

Add permissions check so only Postgres superuser or table owner can

vacuum (Peter E)

New C-routines to implement a BIT and BIT VARYING type in /contrib

(Adriaan Joubert)

New libpq functions to allow asynchronous connections: PQconnectStart(),

PQconnectPoll(), PQresetStart(), PQresetPoll(), PQsetenvStart(),

PQsetenvPoll(), PQsetenvAbort (Ewan Mellor)

New libpq PQsetenv() function (Ewan Mellor)

create/alter user extension (Peter E)

New postmaster.pid and postmaster.opts under $PGDATA (Tatsuo)

New scripts for create/drop user/db (Peter E)

Major psql overhaul(Peter E)

Add const to libpq interface(Peter E)

New libpq function PQoidValue (Peter E)

Show specific non-aggregate causing problem with GROUP BY (Tom)

Force changes to pg_shadow recreate pg_pwd file (Peter E)

Add aggregate(DISTINCT ...) (Tom)

Allow flag to control COPY input/output of NULLs (Peter E)

Make postgres user have a password by default (Peter E)

Add CREATE/ALTER/DROP GROUP (Peter E)

All administration scripts now support --long options (Peter E, Karel)

Vacuumdb script now supports --alldb option (Peter E)

ecpg new portable FETCH syntax

Add ecpg EXEC SQL IFDEF, EXEC SQL IFNDEF, EXEC SQL ELSE, EXEC SQL ELIF

and EXEC SQL ENDIF directives

Add pg_ctl script to control backend startup (Tatsuo)

Add postmaster.opts.default file to store startup flags (Tatsuo)

Allow --with-mb=SQL_ASCII

Increase maximum number of index keys to 16 (Bruce)

Increase maximum number of function arguments to 16 (Bruce)

Allow user configuration of maximum number of index keys and arguments

(Bruce)

Flush backend cache less frequently (Tom, Hiroshi)

Allow unprivileged users to change their passwords (Peter E)

With password authentication enabled, new users without passwords can't

connect (Peter E)

Disallow dropping a user who owns a database (Peter E)

Add initdb --enable-multibyte option (Peter E)

Add option for initdb to prompts for superuser password (Peter E)

COPY now reuses previous memory allocation, improving performance (Tom)

Allow complex type casts like col::numeric(9,2) and col::int2::float8 (Tom)

Updated user interfaces on initdb, initlocation, pg_dump, ipcclean

(Peter E)

NUMERIC now accepts scientific notation (Tom)

NUMERIC to int4 rounds (Tom)

Convert float4/8 to NUMERIC properly (Tom)

New pg_char_to_encoding() and pg_encoding_to_char() functions

Libpq non-blocking mode (Alfred Perlstein)

Improve conversion of types in casts that don't specify a length

New plperl internal programming language (Mark Hollomon)

Allow COPY IN to read file that do not end with a newline (Tom)

Improve optimization cost estimation (Tom)

Indicate when long identifiers are truncated (Tom)

Improve optimizer estimate of range queries x > lowbound AND x < highbound (Tom)

Allow aggregates to use type equivalency (Peter E)

Add Oracle's to_char(), to_date(), to_datetime(), to_timestamp(), to_number()

conversion functions (Karel Zak <zakkr@zf.jcu.cz>)

Add SELECT DISTINCT ON (expr [, expr ...]) targetlist ... (Tom)

Check to be sure ORDER BY is compatible with the DISTINCT operation (Tom)

Use DNF instead of CNF where appropriate (Tom, Taral)

Add NUMERIC and int8 types to ODBC

Improve EXPLAIN results for Append, Group, Agg, Unique (Tom)

Added ALTER TABLE ... ADD FOREIGN KEY (Stephan Szabo)

Further cleanup for OR-of-AND WHERE-clauses (Tom)

Make use of index in OR clauses (x = 1 AND y = 2) OR (x = 2 AND y = 4) (Tom)

Allow SELECT .. FOR UPDATE in PL/pgSQL (Hiroshi)

Enable backward sequential scan even after reaching EOF (Hiroshi)

Add btree indexing of boolean values, >= and <= (Don Baccus)

Print current line number when COPY FROM fails (Massimo)

Recognize special case of POSIX time zone: "GMT+8" and "GMT-8" (Thomas)

Add "DEC" as synonym for "DECIMAL (Thomas)

Add "SESSION_USER" as SQL92 keyword, same as CURRENT_USER (Thomas)

Implement column aliases (aka correlation names) and more join syntax

(Thomas)

Allow queries like SELECT a FROM t1 tx (a) (Thomas)

Allow queries like SELECT * FROM t1 NATURAL JOIN t2 (Thomas)

Smarter optimizer computations for random index page access (Tom)

New SET variable to control optimizer costs (Tom)

Optimizer queries based on LIMIT, OFFSET, and EXISTS qualifications (Tom)

Reduce optimizer internal housekeeping of join paths for speedup (Tom)

Make "INTERVAL" reserved word allowed as a column identifier (Thomas)

Allow type conversion with NUMERIC (Thomas)

Make ISO date style (2000-02-16 09:33) the default (Thomas)

Implement REINDEX command (Hiroshi)

Accept ALL in aggregate function SUM(ALL col) (Tom)

Prevent GROUP BY from using column aliases (Tom)

New psql \encoding option (Tatsuo)

Allow PQrequestCancel() to terminate when in waiting-for-lock state (Hiroshi)

Allow negation of a negative number in all cases

Add ecpg descriptors

Allow CREATE VIEW v AS SELECT f1::char(8) FROM tbl

Source Tree Changes

-------------------

Fix for linux PPC compile

New generic expression-tree-walker subroutine (Tom)

Change form() to varargform() to prevent portability problems.

Improved range checking for large integers on Alpha's

Clean up #include in /include directory (Bruce)

Add scripts for checking includes (Bruce)

Remove un-needed #include's from *.c files (Bruce)

Change #include's to use <> and "" as appropriate (Bruce)

Enable WIN32 compilation of libpq

Alpha spinlock fix from Uncle George <gatgul@voicenet.com>

Overhaul of optimizer data structures (Tom)

Fix to cygipc library (Yutaka Tanida)

Allow pgsql to work on newer Cygwin snapshots(Dan)

New catalog version number (Tom)

Add Linux ARM.

Rename heap_replace to heap_update

Update for QNX (Kardos, Dr. Andrea)

New platform-specific regression handling (Tom)

Rename oid8 -> oidvector and int28 -> int2vector (Bruce)

Included all yacc and lex files into the distribution (Peter E.)

Remove lextest, no longer needed (Peter E)

Fix for libpq and psql on Win32 (Magnus)

Internally change datetime and timespan into timestamp and interval (Thomas)

Fix for plpgsql on BSDI

Release 6.5.3

This is basically a cleanup release for 6.5.2. We have added a new pgaccess