PostgreSQL 7.4beta5 Documentation | ||||
---|---|---|---|---|
Prev | Fast Backward | Fast Forward | Next |
Major changes in this release:
Full support for IPv6 connections and IPv6 address data types
Major improvements in SSL performance and reliability
Allow free space map to efficiently reuse empty index pages, and other free space management improvements.
Implement information schema Support for read-only transactions
Make cursors comply more closely with the SQL standard
New protocol improves connection speed/reliability, and adds error codes, status information, a binary protocol, error reporting verbosity, and cleaner startup packets.
IN/NOT IN subqueries now perform as efficiently as joins
Improved GROUP BY processing by using hash buckets
New multi-key hash join capability
ANSI joins are now better optimized
Faster and more powerful regular expression code
Function-inlining for simple SQL functions
Allow cursors to exist outside transactions
libpq and ecpg are now fully thread-safe with --enable-thread-safety
New version of full text indexing (tsearch2)
New autovacuum tool
Array handling has been improved and moved into the main server
A dump/restore using pg_dump is required for those wishing to migrate data from any previous release.
Observe the following incompatibilities:
The server-side autocommit setting was removed and reimplemented in client applications and languages.
Error message wording has changed substantially in this release, and error codes have been added.
ANSI joins may behave differently because they are now better optimized
A number of server variables have been renamed for clarity
MOVE/FETCH 0 now does nothing; return value of MOVE/FETCH 1/0 varies based on the current offset in the cursor
COPY now can process carriage-return/line-feed and carriage-return terminated files. Literal carriage-returns and line-feeds are no longer accepted as data values; use \r and \n instead.
Trailing spaces are now trimmed when converting from CHAR(n) to VARCHAR(n) / TEXT
FLOAT(p)
now measures 'p' in bits, not digits
Ambiguous date values now must match the ordering specified by DateStyle
The oidrand()
, oidsrand()
,
and userfntest()
functions have been removed.
'now' will no longer work as a column default; now()
should be
used instead
Dollar sign ($) is no longer allowed in operator names
Dollar sign ($) can be a non-first character in identifiers
Allow IPv6 server connections (Nigel Kukard, Johan Jordaan, Bruce, Tom, Kurt Roeckx, Andrew Dunstan)
Fix SSL to handle errors cleanly (Nathan Mueller)
SSL protocol security and performance improvements (Sean Chittenden)
Print lock information when a deadlock is detected (Tom)
Update /tmp socket mod. times regularly to avoid their removal (Tom)
Enable PAM for MAC OS X (Aaron Hillegass)
Make btree indexes fully WAL-safe (Tom)
Allow btree index compaction and empty page reuse (Tom)
Fix inconsistent index lookups during split of first root page (Tom)
Improve free space map allocation logic (Tom)
Preserve free space information between postmaster restarts (Tom)
Set proper schema permissions in initdb (Peter)
Add start time to pg_stat_activity (Neil)
New code to detect corrupt disk pages; erase with zero_damaged_pages (Tom)
New client/server protocol: faster, no username length limit, allow clean exit from COPY (Tom)
Add transaction status, tableid, columnid to backend protocol (Tom)
Add new binary I/O protocol (Tom)
Remove autocommit server setting; move to client applications (Tom)
New error message wording, error codes, and three levels of error detail (Tom)
Add hashing for GROUP BY aggregates (Tom)
Allow nested loops to be smarter about multicolumn indexes (Tom)
Allow multi-key hash joins (Tom)
Improve constant folding (Tom)
Add ability to inline simple SQL functions (Tom)
Reduce memory usage for queries using complex functions (Tom)
Improve GEQO optimizer performance (Tom)
Allow IN/NOT IN to be handled via hash tables (Tom)
Improve NOT IN (subquery) performance (Tom)
Allow most IN subqueries to be processed as joins (Tom)
Allow the postmaster to preload libraries using preload_libraries (Joe)
Improve optimizer cost computations, particularly for subqueries (Tom)
Avoid sort when subquery ORDER BY matches upper query (Tom)
Assume WHERE a.x = b.y and b.y = 42 also means a.x = 42 (Tom)
Allow hash/merge joins on complex joins (Tom)
Allow hash joins for more data types (Tom)
Allow join optimization of ANSI joins, disable with join_collapse_limit (Tom)
Add from_collapse_limit to control conversion of subqueries to joins (Tom)
Use faster regular expression code from TCL (Henry Spencer, Tom)
Use bit-mapped relation sets in the optimizer (Tom)
Improve backend startup time (Tom)
Improve trigger/constraint performance (Stephan)
Improve speed of col IN (const, const, const, ...) (Tom)
Fix hash indexes which were broken in rare cases (Tom)
Improve hash index concurrency and speed (Tom)
Align shared buffers on 32-byte boundary for copy speed improvement (Manfred Spraul)
Rename server parameter server_min_messages to log_min_messages (Bruce)
Rename show_*_stats to log_*_stats (Bruce)
Rename show_source_port to log_source_port (Bruce)
Rename hostname_lookup to log_hostname (Bruce)
Add checkpoint_warning to warn of excessive checkpointing (Bruce)
New read-only server parameters for localization (Tom)
Change debug server log messages to output as DEBUG rather than LOG (Bruce)
Prevent server log variables from being turned off by non-super users (Bruce)
log_min_messages/client_min_messages now controls debug_* output (Bruce)
Add Rendezvous server support (Chris Campbell)
Add ability to print only slow statements using log_min_duration_statement (Christopher)
Allow pg_hba.conf to accept netmasks in CIDR format (Andrew Dunstan)
New is_superuser read-only variable (Tom)
New server-side parameter log_error_verbosity to control error detail (Tom)
postgres --help-config now dumps server config variables (Aizaz Ahmed)
Make default shared_buffers 1000 and max_connections 100, if possible (Tom)
Add new columns in pg_settings: context, type, source , min_val, max_val (Joe)
New pg_hba.conf 'hostnossl' to prevent SSL connections (Jon Jensen)
Remove geqo_random_seed server parameter (Tom)
New SQL-standard information schema (Peter)
Add read-only transactions (Peter)
Add server variable regex_flavor to control regular expression processing (Tom)
Print key name and value in foreign-key violation messages (Dmitry Tkach)
Allow users to see their own queries in pg_stat_activity (Kevin Brown)
Fix subquery aggregates of upper query columns to match SQL spec. (Tom)
Add option to prevent auto-addition of tables referenced in query (Nigel J. Andrews)
Allow UPDATE ... SET col = DEFAULT (Rod)
Allow expressions to be used in LIMIT/OFFSET (Tom)
Change EXECUTE INTO to CREATE TABLE AS EXECUTE (Peter)
Make CREATE SEQUENCE grammar more SQL1999 standards compliant (Neil)
Add FOR EACH STATEMENT statement-level triggers (Neil)
Add DOMAIN CHECK constraints (Rod)
Add ALTER DOMAIN .. SET / DROP NOT NULL, SET / DROP DEFAULT, ADD / DROP CONSTRAINT (Rod)
Fix several zero-column table bugs (Tom)
Have ALTER TABLE ... ADD PRIMARY KEY add NOT NULL constraint (Rod)
Add ALTER DOMAIN OWNER (Rod)
Add ALTER TABLE ... WITHOUT OIDS (Rod)
Add ALTER SEQUENCE to modify min/max/increment/cache/cycle values (Rod)
Add ALTER TABLE ... CLUSTER ON (Alvaro Herrera)
Improve DOMAIN automatic type casting (Rod, Tom)
Allow dollar signs in identifiers, except as first character (Tom)
Disallow dollar signs in operator names, so x=$1 works (Tom)
Allow SQL200X inheritance syntax LIKE subtable, INCLUDING DEFAULTS (Rod)
Add WITH GRANT OPTION clause to GRANT, per SQL spec (Peter)
Add ON COMMIT PRESERVE ROWS for temp tables (Gavin)
Allow cursors outside transactions using WITH HOLD (Neil)
Make MOVE/FETCH 0 actually move/fetch 0 (Bruce)
Cause FETCH 1 to return the current cursor row, or zero if at beginning/end of cursor, per SQL spec (Bruce)
Have MOVE return 0 or 1 depending on cursor position (Bruce)
Properly handle SCROLL with cursors, or report an error (Neil)
Implement SQL92-compatible FIRST, LAST, ABSOLUTE n, RELATIVE n options for FETCH and MOVE (Tom)
Allow EXPLAIN on DECLARE CURSOR (Tom)
Allow CLUSTER to use index marked as pre-clustered by default (Alvaro Herrera)
Allow CLUSTER to cluster all tables (Alvaro Herrera)
Prevent CLUSTER on partial indexes (Tom)
Allow \r and \r\n termination for COPY files (Bruce)
Disallow literal carriage return as a data value, backslash-carriage-return and \r are still allowed (Bruce)
COPY changes (binary, \.)? (Tom)
Recover from COPY IN/OUT failure cleanly (Tom)
Prevent possible memory leaks in COPY (Tom)
Make TRUNCATE transaction-safe (Rod)
Multiple pg_dump fixes, including tar format and large objects
Allow pg_dump to dump specific schemas (Neil)
Allow pg_dump to preserve column storage characteristics (Christopher)
Allow pg_dump to preserve CLUSTER characteristics (Christopher)
Have pg_dumpall use GRANT/REVOKE to dump database-level permissions (Tom)
Allow pg_dumpall to support the -a, -s, -x options of pg_dump (Tom)
Prevent pg_dump from lowercasing identifiers specified on the command line (Tom)
Allow PREPARE/bind of utility commands like FETCH and EXPLAIN (Tom)
Add EXPLAIN EXECUTE (Neil)
Allow pg_get_constraintdef() to support UNIQUE, PRIMARY KEY and CHECK constraints (Christopher)
Improve VACUUM performance on indexes by reducing WAL traffic (Tom)
Allow pg_ctl to better handle non-standard ports (Greg)
Functional indexes now support indexes on column expressions (Tom)
Syntax errors now reported as 'syntax error' rather than 'parse error' (Tom)
Have SHOW TRANSACTION_ISOLATION match input to SET TRANSACTION_ISOLATION (Tom)
Have COMMENT ON DATABASE on non-local database generate a warning (Rod)
Improve reliability of LISTEN/NOTIFY (Tom)
Allow REINDEX to reliably reindex non-shared system catalog indexes (Tom)
pg_dump --use-set-session-authorization and --no-reconnect now do nothing, all dumps use SET SESSION AUTHORIZATION
New extra_float_digits server parameter to control float precision display (Pedro Ferreira, Tom)
Allow +1300 as a numeric timezone specifier, for FJST (Tom)
Remove rarely used oidrand(), oidsrand(), and userfntest() functions (Neil)
Add md5() function to main server, already in /contrib/pgcrypto (Joe)
Increase date range of timestamp (John Cochran)
Change EXTRACT(EPOCH FROM timestamp) so timestamp without time zone is assumed to be in local time, not GMT (Tom)
Trap division by zero in case the operating system doesn't prevent it (Tom)
Change the NUMERIC data type internally to base 10000 (Tom)
New hostmask() function (Greg Wickham)
Fixes for to_char() (Karel)
Allow functions that can take any argument data type and return any data type, using ANYELEMENT and ANYARRAY (Joe)
Arrays may now be specified as ARRAY[1,2,3], ARRAY[['a','b'],['c','d']], or ARRAY[ARRAY[ARRAY[2]]] (Joe)
Allow proper comparisons for arrays (Joe)
Allow array concatenation with '||' (Joe)
Allow indexes on array columns, and used in ORDER BY and DISTINCT (Joe)
Allow WHERE qualification 'expr >oper< ANY/SOME/ALL (array-expr)' (Joe)
Allow polymorphic SQL functions (Joe)
New array functions array_append(), array_cat(), array_lower(), array_prepend(), array_to_string(), array_upper(), string_to_array() (Joe)
Allow user defined aggregates to use polymorphic functions (Joe)
Allow polymorphic user defined aggregates (Joe)
Allow assignments to empty arrays (Joe)
Allow 60 in seconds fields of timestamp, time, interval input values (Tom)
Allow CIDR data type to be cast to text (Tom)
Allow the creation of special LIKE indexes for non-C locales (Peter)
Disallow invalid timezone names (Tom)
Trim trailing spaces when CHAR() data is cast to VARCHAR or TEXT (Tom)
Make FLOAT(p) measure the precision p in bits, not decimal digits (Tom)
Add IPv6 support to the inet and cidr data types (Michael Graff)
Add family() function to report whether address is IPv4 or IPv6 (Michael Graff)
Have SHOW DATESTYLE generate output similar to that used by SET DATESTYLE (Tom)
Change DATESTYLE to output its value in a more common format (Tom)
Make EXTRACT(TIMEZONE) and SET/SHOW TIMEZONE follow the SQL convention for the sign of timezone offsets, ie, positive is east from UTC (Tom)
Fix date_trunc('quarter',...) (B?jthe Zolt?n)
Make initcap() more compatible with Oracle (Mike Nolan)
Allow only DateStyle field order for date values not in ISO format (Greg)
Add new DateStyle values MDY, DMY, and YMD, honor US and European for backward compatibility (Tom)
'now' will no longer work as a column default, use now() (change required for prepared statements) (Tom)
Assume NaN value to be larger than any other value in MIN()/MAX() (Tom)
Prevent interval from suppressing ':00' seconds display
New pg_get_triggerdef(prettyprint) and pg_constraint_is_visible() functions
Allow time to be specified as '040506' or '0405' (Tom)
Prevent PL/pgSQL crash when RETURN NEXT is used on a zero-row record var. (Tom)
Make PL/python's spi_execute interface handle NULLs properly (Andrew Bosma)
Allow PL/pgSQL to declare variables of composite types without %ROWTYPE (Tom)
Fix PL/python _quote() function to handle big integers (?)
Make PL/python an untrusted language, now called plpythonu (Kevin Jacobs, Tom)
Allow polymorphic PL/pgSQL functions (Tom, Joe)
Improved compiled function caching mechanism in PL/pgSQL with full support for polymorphism (Joe)
Add new $0 parameter in PL/pgSQL representing the function's actual return type (Joe)
Allow pltcl and plpython use the same trigger on multiple tables (Tom)
Fixed PL/Tcl's spi_prepare to accept full qualified type names in the parameter type list (Jan)
Add "\pset pager always" to always use pager (Greg)
Improve tab completion (Rod, Ross Reedstrom, Ian Barwick)
Reorder \? help into groupings (Harald Armin Massa, Bruce)
Add schema, cast, and conversion backslash commands (Christopher)
\encoding now changes based on client_encoding server variable (Tom)
Save edit history into readline history (Ross)
Improve \d display (Christopher)
Enhance HTML mode to be more standards-compliant (Greg)
New '\set AUTOCOMMIT off' capability (Tom)
New '\set VERBOSITY' to control error detail (Tom)
New %T prompt string to show transaction status (Tom)
Allow PQcmdTuples() to return row counts for MOVE and FETCH (Neil)
Add PQfreemem() for freeing memory on Win32, suggest for NOTIFY (Bruce)
Document service capability, and add sample file (Bruce)
Make PQsetdbLogin() have the same defaults as PQconnectdb() (Tom)
Allow libpq to cleanly fail when result sets are too large (Tom)
Improve performance of PGunescapeBytea() (Ben Lamb)
Allow thread-safe libpq with --enable-thread-safety (Lee Kindness, Philip Yarra)
Allow pqInternalNotice() to accept a format string and args instead of just a preformatted message (Tom, Sean Chittenden)
Allow control SSL negotiation with sslmode values "disable", "allow", "Prefer", and "require" (Jon Jensen)
Allow new error codes and levels of text (Tom)
Allow access to the underlying table and column of a query result (Tom)
Allow access to the current transaction status (Tom)
Add ability to pass binary data directly to the backend (Tom)
Add PQexecPrepared() and PQsendQueryPrepared() functions which perform Bind/Execute of previously prepared statements (Tom)
Allow setNull on updateable resultsets
Allow executeBatch on a prepared statement (Barry)
Support SSL connections (Barry)
Handle schema names in result sets (Paul Sorenson)
Add refcursor support (Nic Ferrier)
Prevent possible memory leak or core dump during libpgtcl shutdown (Tom)
Add ecpg Informix compatibility (Michael)
Add ecpg DECIMAL type that is fixed length, for Informix (Michael)
Allow thread-safe ecpg with --enable-thread-safety (Lee Kindness, Bruce)
Move python client interface to http://www.pygresql.org (Marc)
Prevent need for separate platform geometry regression result files (Tom)
Improved PPC locking primitive (Reinhard Max)
Embed LD_LIBRARY_PATH used for build process into binaries (Billy)
New palloc0 to allocate and clear memory (Bruce)
Fix locking code for s390x CPU (64-bit) (Tom)
Allow OpenBSD to use local ident credentials (William Ahern)
Make query plan trees read-only to executor (Tom)
Add Darwin startup scripts (David Wheeler)
Allow libpq to compile with Borland C++ compiler (Lester Godwin, Karl Waclawek)
Use our own version of getopt_long() if needed (Peter)
Convert administration scripts to C (Peter)
Bison >=1.85 is now required for grammar changes
Merge documentation into one book (Peter)
Add Win32 compatibility functions (Bruce)
Allow client interfaces to compile under MinGW/Win32 (Bruce)
New ereport() function for error reporting (Tom)
Support Intel Linux compiler (Peter)
Improve Linux startup scripts (Slawomir Sudnik, Darko Prenosil)
Add support for AMD Opteron and Itanium (Jeffrey W. Baker, Bruce)
Remove configure --enable-recode
Generate a compile error if spinlock code is not found (Bruce)
Change dbmirror license to BSD
Improve earthdistance (Bruno Wolff III)
Portability improvements to pgcrypto (Marko Kreen)
Prevent xml crash (John Gray, Michael Richards)
Update oracle
Update mysql
Update cube (Bruno Wolff III)
Update earthdistance to use cube (Bruno Wolff III)
Update btree_gist (Oleg)
New tsearch2 full-text search module (Oleg, Teodor)
Add hashed based crosstab function to tablefuncs (Joe)
Add serial column to order connectby() siblings in tablefuncs (Nabil Sayegh,Joe)
Add named persistent connections to dblink (Shridhar Daithanka)
New pg_autovacuum allows automatic VACUUM (Matthew T. O'Connor)
Allow pgbench to honor PGHOST, PGPORT, PGUSER env. variables (Tatsuo)
Improve intarray (Teodor Sigaev)
Improve pgstattuple (Rod)
Fix bug in metaphone() in fuzzystrmatch
Improve adddepend (Rod)
Update spi/timetravel (B?jthe Zolt?n)
Fix dbase -s option and improve non-ASCII handling (Thomas Behr,M?rcio Smiderle)
Remove array module because features now included by default (Joe)
EXTRACT(TIMEZONE)
and SET/SHOW
TIMEZONE now follow SQL sign convention
(positive = east of UTC)
DATESTYLE can now be set to DMY, YMD, or MDY to specify input field order
Input date order must now be YYYY-MM-DD (with 4-digit year) or match DATESTYLE
Output of SHOW DATESTYLE is now in the same format accepted by SET DATESTYLE
PL/Python is now an untrusted language, and is renamed
to plpythonu
Dollar sign ($) is no longer allowed in operator names
Dollar sign ($) can be a non-first character in identifiers
Precision in FLOAT(p)
is now interpreted as bits, not decimal digits
Functional indexes have been generalized into expressional indexes
CHAR(n)
to TEXT
conversion automatically strips trailing blanks
Pattern matching operations can use indexes regardless of locale
New frontend/backend protocol supports many long-requested features
SET AUTOCOMMIT TO OFF is no longer supported; psql has an AUTOCOMMIT variable
Reimplementation of NUMERIC datatype for more speed
New regular expression package, many more regexp features (most of Perl5)
Can now do EXPLAIN ... EXECUTE to see plan used for a prepared query
Explicit JOINs no longer constrain query plan, unless JOIN_COLLAPSE_LIMIT = 1
Performance of foo IN (SELECT ...) queries has been considerably improved
FETCH 0 now re-fetches cursor's current row, per SQL spec
Revised executor state representation; plan trees are read-only to executor now
Information schema
Domains now support CHECK constraints
psql backslash commands for listing conversions, casts, and schemas
TRUNCATE TABLE is transaction-safe
CLUSTER can re-cluster a previously clustered table, or all such tables
Statement-level triggers
System can use either hash- or sort-based strategy for grouped aggregation
ON COMMIT options for temp tables
extra_float_digits option allows pg_dump to dump float data accurately
Long options for psql and pg_dump are now available on all platforms
Read-only transactions
Object owners can allow grantees to grant the privilege to others (grant option)
Added contrib module for pg_autovacuum which monitors inserts and deletes, and does ANALYZE or VACUUM ANALYZE when quantities exceed specified thresholds.