Release 7.2
PostgreSQL 9.3.24 Documentation | ||||
---|---|---|---|---|
Prev | Up | Appendix E. Release Notes | Next |
Release date: 2002-02-04
E.283.1. Overview
This release improves PostgreSQL for use in high-volume applications.
Major changes in this release:
- VACUUM
-
Vacuuming no longer locks tables, thus allowing normal user access during the vacuum. A new VACUUM FULL command does old-style vacuum by locking the table and shrinking the on-disk copy of the table.
- Transactions
-
There is no longer a problem with installations that exceed four billion transactions.
- OIDs
-
OIDs are now optional. Users can now create tables without OIDs for cases where OID usage is excessive.
- Optimizer
-
The system now computes histogram column statistics during ANALYZE , allowing much better optimizer choices.
- Security
-
A new MD5 encryption option allows more secure storage and transfer of passwords. A new Unix-domain socket authentication option is available on Linux and BSD systems.
- Statistics
-
Administrators can use the new table access statistics module to get fine-grained information about table and index usage.
- Internationalization
-
Program and library messages can now be displayed in several languages.
E.283.2. Migration to Version 7.2
A dump/restore using pg_dump is required for those wishing to migrate data from any previous release.
Observe the following incompatibilities:
-
The semantics of the VACUUM command have changed in this release. You might wish to update your maintenance procedures accordingly.
-
In this release, comparisons using = NULL will always return false (or NULL, more precisely). Previous releases automatically transformed this syntax to IS NULL . The old behavior can be re-enabled using a postgresql.conf parameter.
-
The pg_hba.conf and pg_ident.conf configuration is now only reloaded after receiving a SIGHUP signal, not with each connection.
-
The function octet_length() now returns the uncompressed data length.
-
The date/time value 'current' is no longer available. You will need to rewrite your applications.
-
The timestamp() , time() , and interval() functions are no longer available. Instead of timestamp() , use timestamp 'string' or CAST .
The SELECT ... LIMIT #,# syntax will be removed in the next release. You should change your queries to use separate LIMIT and OFFSET clauses, e.g. LIMIT 10 OFFSET 20 .
E.283.3. Changes
E.283.3.1. Server Operation
-
Create temporary files in a separate directory (Bruce)
-
Delete orphaned temporary files on postmaster startup (Bruce)
-
Added unique indexes to some system tables (Tom)
-
System table operator reorganization (Oleg Bartunov, Teodor Sigaev, Tom)
-
Renamed pg_log to pg_clog (Tom)
-
Enable SIGTERM, SIGQUIT to kill backends (Jan)
-
Removed compile-time limit on number of backends (Tom)
-
Better cleanup for semaphore resource failure (Tatsuo, Tom)
-
Allow safe transaction ID wraparound (Tom)
-
Removed OIDs from some system tables (Tom)
-
Removed "triggered data change violation" error check (Tom)
-
SPI portal creation of prepared/saved plans (Jan)
-
Allow SPI column functions to work for system columns (Tom)
-
Long value compression improvement (Tom)
-
Statistics collector for table, index access (Jan)
-
Truncate extra-long sequence names to a reasonable value (Tom)
-
Measure transaction times in milliseconds (Thomas)
-
Fix TID sequential scans (Hiroshi)
-
Superuser ID now fixed at 1 (Peter E)
-
New pg_ctl "reload" option (Tom)
E.283.3.2. Performance
-
Optimizer improvements (Tom)
-
New histogram column statistics for optimizer (Tom)
-
Reuse write-ahead log files rather than discarding them (Tom)
-
Cache improvements (Tom)
-
IS NULL, IS NOT NULL optimizer improvement (Tom)
-
Improve lock manager to reduce lock contention (Tom)
-
Keep relcache entries for index access support functions (Tom)
-
Allow better selectivity with NaN and infinities in NUMERIC (Tom)
-
R-tree performance improvements (Kenneth Been)
-
B-tree splits more efficient (Tom)
E.283.3.3. Privileges
-
Change UPDATE, DELETE privileges to be distinct (Peter E)
-
New REFERENCES, TRIGGER privileges (Peter E)
-
Allow GRANT/REVOKE to/from more than one user at a time (Peter E)
-
New has_table_privilege() function (Joe Conway)
-
Allow non-superuser to vacuum database (Tom)
-
New SET SESSION AUTHORIZATION command (Peter E)
-
Fix bug in privilege modifications on newly created tables (Tom)
-
Disallow access to pg_statistic for non-superuser, add user-accessible views (Tom)
E.283.3.4. Client Authentication
-
Fork postmaster before doing authentication to prevent hangs (Peter E)
-
Add ident authentication over Unix domain sockets on Linux, *BSD (Helge Bahmann, Oliver Elphick, Teodor Sigaev, Bruce)
-
Add a password authentication method that uses MD5 encryption (Bruce)
-
Allow encryption of stored passwords using MD5 (Bruce)
-
PAM authentication (Dominic J. Eidson)
-
Load pg_hba.conf and pg_ident.conf only on startup and SIGHUP (Bruce)
E.283.3.5. Server Configuration
-
Interpretation of some time zone abbreviations as Australian rather than North American now settable at run time (Bruce)
-
New parameter to set default transaction isolation level (Peter E)
-
New parameter to enable conversion of "expr = NULL" into "expr IS NULL", off by default (Peter E)
-
New parameter to control memory usage by VACUUM (Tom)
-
New parameter to set client authentication timeout (Tom)
-
New parameter to set maximum number of open files (Tom)
E.283.3.6. Queries
-
Statements added by INSERT rules now execute after the INSERT (Jan)
-
Prevent unadorned relation names in target list (Bruce)
-
NULLs now sort after all normal values in ORDER BY (Tom)
-
New IS UNKNOWN, IS NOT UNKNOWN Boolean tests (Tom)
-
New SHARE UPDATE EXCLUSIVE lock mode (Tom)
-
New EXPLAIN ANALYZE command that shows run times and row counts (Martijn van Oosterhout)
-
Fix problem with LIMIT and subqueries (Tom)
-
Fix for LIMIT, DISTINCT ON pushed into subqueries (Tom)
-
Fix nested EXCEPT/INTERSECT (Tom)
E.283.3.7. Schema Manipulation
-
Fix SERIAL in temporary tables (Bruce)
-
Allow temporary sequences (Bruce)
-
Sequences now use int8 internally (Tom)
-
New SERIAL8 creates int8 columns with sequences, default still SERIAL4 (Tom)
-
Make OIDs optional using WITHOUT OIDS (Tom)
-
Add %TYPE syntax to CREATE TYPE (Ian Lance Taylor)
-
Add ALTER TABLE / DROP CONSTRAINT for CHECK constraints (Christopher Kings-Lynne)
-
New CREATE OR REPLACE FUNCTION to alter existing function (preserving the function OID) (Gavin Sherry)
-
Add ALTER TABLE / ADD [ UNIQUE | PRIMARY ] (Christopher Kings-Lynne)
-
Allow column renaming in views
-
Make ALTER TABLE / RENAME COLUMN update column names of indexes (Brent Verner)
-
Fix for ALTER TABLE / ADD CONSTRAINT ... CHECK with inherited tables (Stephan Szabo)
-
ALTER TABLE RENAME update foreign-key trigger arguments correctly (Brent Verner)
-
DROP AGGREGATE and COMMENT ON AGGREGATE now accept an aggtype (Tom)
-
Add automatic return type data casting for SQL functions (Tom)
-
Allow GiST indexes to handle NULLs and multikey indexes (Oleg Bartunov, Teodor Sigaev, Tom)
-
Enable partial indexes (Martijn van Oosterhout)
E.283.3.8. Utility Commands
-
Add RESET ALL, SHOW ALL (Marko Kreen)
-
CREATE/ALTER USER/GROUP now allow options in any order (Vince)
-
Add LOCK A, B, C functionality (Neil Padgett)
-
New ENCRYPTED/UNENCRYPTED option to CREATE/ALTER USER (Bruce)
-
New light-weight VACUUM does not lock table; old semantics are available as VACUUM FULL (Tom)
-
Disable COPY TO/FROM on views (Bruce)
-
COPY DELIMITERS string must be exactly one character (Tom)
-
VACUUM warning about index tuples fewer than heap now only appears when appropriate (Martijn van Oosterhout)
-
Fix privilege checks for CREATE INDEX (Tom)
-
Disallow inappropriate use of CREATE/DROP INDEX/TRIGGER/VIEW (Tom)
E.283.3.9. Data Types and Functions
-
SUM(), AVG(), COUNT() now uses int8 internally for speed (Tom)
-
Add convert(), convert2() (Tatsuo)
-
New function bit_length() (Peter E)
-
Make the "n" in CHAR(n)/VARCHAR(n) represents letters, not bytes (Tatsuo)
-
CHAR(), VARCHAR() now reject strings that are too long (Peter E)
-
BIT VARYING now rejects bit strings that are too long (Peter E)
-
BIT now rejects bit strings that do not match declared size (Peter E)
-
INET, CIDR text conversion functions (Alex Pilosov)
-
INET, CIDR operators << and <<= indexable (Alex Pilosov)
-
Bytea \### now requires valid three digit octal number
-
Bytea comparison improvements, now supports =, <>, >, >=, <, and <=
-
Bytea now supports B-tree indexes
-
Bytea now supports LIKE, LIKE...ESCAPE, NOT LIKE, NOT LIKE...ESCAPE
-
Bytea now supports concatenation
-
New bytea functions: position, substring, trim, btrim, and length
-
New encode() function mode, "escaped", converts minimally escaped bytea to/from text
-
Add pg_database_encoding_max_length() (Tatsuo)
-
Add pg_client_encoding() function (Tatsuo)
-
now() returns time with millisecond precision (Thomas)
-
New TIMESTAMP WITHOUT TIMEZONE data type (Thomas)
-
Add ISO date/time specification with "T", yyyy-mm-ddThh:mm:ss (Thomas)
-
New xid/int comparison functions (Hiroshi)
-
Add precision to TIME, TIMESTAMP, and INTERVAL data types (Thomas)
-
Modify type coercion logic to attempt binary-compatible functions first (Tom)
-
New encode() function installed by default (Marko Kreen)
-
Improved to_*() conversion functions (Karel Zak)
-
Optimize LIKE/ILIKE when using single-byte encodings (Tatsuo)
-
New functions in contrib/pgcrypto: crypt(), hmac(), encrypt(), gen_salt() (Marko Kreen)
-
Correct description of translate() function (Bruce)
-
Add INTERVAL argument for SET TIME ZONE (Thomas)
-
Add INTERVAL YEAR TO MONTH (etc.) syntax (Thomas)
-
Optimize length functions when using single-byte encodings (Tatsuo)
-
Fix path_inter, path_distance, path_length, dist_ppath to handle closed paths (Curtis Barrett, Tom)
-
octet_length(text) now returns non-compressed length (Tatsuo, Bruce)
-
Handle "July" full name in date/time literals (Greg Sabino Mullane)
-
Some datatype() function calls now evaluated differently
-
Add support for Julian and ISO time specifications (Thomas)
E.283.3.10. Internationalization
-
National language support in psql, pg_dump , libpq, and server (Peter E)
-
Message translations in Chinese (simplified, traditional), Czech, French, German, Hungarian, Russian, Swedish (Peter E, Serguei A. Mokhov, Karel Zak, Weiping He, Zhenbang Wei, Kovacs Zoltan)
-
Make trim, ltrim, rtrim, btrim, lpad, rpad, translate multibyte aware (Tatsuo)
-
Add LATIN5,6,7,8,9,10 support (Tatsuo)
-
Add ISO 8859-5,6,7,8 support (Tatsuo)
-
Correct LATIN5 to mean ISO-8859-9, not ISO-8859-5 (Tatsuo)
-
Make mic2ascii() non-ASCII aware (Tatsuo)
-
Reject invalid multibyte character sequences (Tatsuo)
E.283.3.11. PL/pgSQL
-
Now uses portals for SELECT loops, allowing huge result sets (Jan)
-
CURSOR and REFCURSOR support (Jan)
-
Can now return open cursors (Jan)
-
Add ELSEIF (Klaus Reger)
-
Improve PL/pgSQL error reporting, including location of error (Tom)
-
Allow IS or FOR key words in cursor declaration, for compatibility (Bruce)
-
Fix for SELECT ... FOR UPDATE (Tom)
-
Fix for PERFORM returning multiple rows (Tom)
-
Make PL/pgSQL use the server's type coercion code (Tom)
-
Memory leak fix (Jan, Tom)
-
Make trailing semicolon optional (Tom)
E.283.3.12. PL/Perl
-
New untrusted PL/Perl (Alex Pilosov)
-
PL/Perl is now built on some platforms even if libperl is not shared (Peter E)
E.283.3.15. psql
-
\d displays indexes in unique, primary groupings (Christopher Kings-Lynne)
-
Allow trailing semicolons in backslash commands (Greg Sabino Mullane)
-
Read password from /dev/tty if possible
-
Force new password prompt when changing user and database (Tatsuo, Tom)
-
Format the correct number of columns for Unicode (Patrice)
E.283.3.16. libpq
-
New function PQescapeString() to escape quotes in command strings (Florian Weimer)
-
New function PQescapeBytea() escapes binary strings for use as SQL string literals
E.283.3.17. JDBC
-
Return OID of INSERT (Ken K)
-
Handle more data types (Ken K)
-
Handle single quotes and newlines in strings (Ken K)
-
Handle NULL variables (Ken K)
-
Fix for time zone handling (Barry Lind)
-
Improved Druid support
-
Allow eight-bit characters with non-multibyte server (Barry Lind)
-
Support BIT, BINARY types (Ned Wolpert)
-
Reduce memory usage (Michael Stephens, Dave Cramer)
-
Update DatabaseMetaData (Peter E)
-
Add DatabaseMetaData.getCatalogs() (Peter E)
-
Encoding fixes (Anders Bengtsson)
-
Get/setCatalog methods (Jason Davies)
-
DatabaseMetaData.getColumns() now returns column defaults (Jason Davies)
-
DatabaseMetaData.getColumns() performance improvement (Jeroen van Vianen)
-
Some JDBC1 and JDBC2 merging (Anders Bengtsson)
-
Transaction performance improvements (Barry Lind)
-
Array fixes (Greg Zoller)
-
Serialize addition
-
Fix batch processing (Rene Pijlman)
-
ExecSQL method reorganization (Anders Bengtsson)
-
GetColumn() fixes (Jeroen van Vianen)
-
Fix isWriteable() function (Rene Pijlman)
-
Improved passage of JDBC2 conformance tests (Rene Pijlman)
-
Add bytea type capability (Barry Lind)
-
Add isNullable() (Rene Pijlman)
-
JDBC date/time test suite fixes (Liam Stewart)
-
Fix for SELECT 'id' AS xxx FROM table (Dave Cramer)
-
Fix DatabaseMetaData to show precision properly (Mark Lillywhite)
-
New getImported/getExported keys (Jason Davies)
-
MD5 password encryption support (Jeremy Wohl)
-
Fix to actually use type cache (Ned Wolpert)
E.283.3.18. ODBC
-
Remove query size limit (Hiroshi)
-
Remove text field size limit (Hiroshi)
-
Fix for SQLPrimaryKeys in multibyte mode (Hiroshi)
-
Allow ODBC procedure calls (Hiroshi)
-
Improve boolean handing (Aidan Mountford)
-
Most configuration options now settable via DSN (Hiroshi)
-
Multibyte, performance fixes (Hiroshi)
-
Allow driver to be used with iODBC or unixODBC (Peter E)
-
MD5 password encryption support (Bruce)
-
Add more compatibility functions to odbc.sql (Peter E)
E.283.3.19. ECPG
-
EXECUTE ... INTO implemented (Christof Petig)
-
Multiple row descriptor support (e.g. CARDINALITY) (Christof Petig)
-
Fix for GRANT parameters (Lee Kindness)
-
Fix INITIALLY DEFERRED bug
-
Various bug fixes (Michael, Christof Petig)
-
Auto allocation for indicator variable arrays (int *ind_p=NULL)
-
Auto allocation for string arrays (char **foo_pp=NULL)
-
ECPGfree_auto_mem fixed
-
All function names with external linkage are now prefixed by ECPG
-
Fixes for arrays of structures (Michael)
E.283.3.20. Misc. Interfaces
-
Python fix fetchone() (Gerhard Haring)
-
Use UTF, Unicode in Tcl where appropriate (Vsevolod Lobko, Reinhard Max)
-
Add Tcl COPY TO/FROM (ljb)
-
Prevent output of default index op class in pg_dump (Tom)
-
Fix libpgeasy memory leak (Bruce)
E.283.3.21. Build and Install
-
Configure, dynamic loader, and shared library fixes (Peter E)
-
Fixes in QNX 4 port (Bernd Tegge)
-
Fixes in Cygwin and Windows ports (Jason Tishler, Gerhard Haring, Dmitry Yurtaev, Darko Prenosil, Mikhail Terekhov)
-
Fix for Windows socket communication failures (Magnus, Mikhail Terekhov)
-
Hurd compile fix (Oliver Elphick)
-
BeOS fixes (Cyril Velter)
-
Remove configure --enable-unicode-conversion, now enabled by multibyte (Tatsuo)
-
AIX fixes (Tatsuo, Andreas)
-
Fix parallel make (Peter E)
-
Install SQL language manual pages into OS-specific directories (Peter E)
-
Rename config.h to pg_config.h (Peter E)
-
Reorganize installation layout of header files (Peter E)
E.283.3.22. Source Code
-
Remove SEP_CHAR (Bruce)
-
New GUC hooks (Tom)
-
Merge GUC and command line handling (Marko Kreen)
-
Remove EXTEND INDEX (Martijn van Oosterhout, Tom)
-
New pgjindent utility to indent java code (Bruce)
-
Remove define of true/false when compiling under C++ (Leandro Fanzone, Tom)
-
pgindent fixes (Bruce, Tom)
-
Replace strcasecmp() with strcmp() where appropriate (Peter E)
-
Dynahash portability improvements (Tom)
-
Add 'volatile' usage in spinlock structures
-
Improve signal handling logic (Tom)
E.283.3.23. Contrib
-
New contrib/rtree_gist (Oleg Bartunov, Teodor Sigaev)
-
New contrib/tsearch full-text indexing (Oleg, Teodor Sigaev)
-
Add contrib/dblink for remote database access (Joe Conway)
-
contrib/ora2pg Oracle conversion utility (Gilles Darold)
-
contrib/xml XML conversion utility (John Gray)
-
contrib/fulltextindex fixes (Christopher Kings-Lynne)
-
New contrib/fuzzystrmatch with levenshtein and metaphone, soundex merged (Joe Conway)
-
Add contrib/intarray boolean queries, binary search, fixes (Oleg Bartunov)
-
New pg_upgrade utility (Bruce)
-
Add new pg_resetxlog options (Bruce, Tom)