From 93fd51f578f9abfa402291f072846af4f5d6bdae Mon Sep 17 00:00:00 2001 From: Bruce Momjian Date: Tue, 19 Sep 2006 00:57:07 +0000 Subject: [PATCH] Update migration changes, more cleanups. --- doc/src/sgml/release.sgml | 1555 +++++++++++++++++++------------------ 1 file changed, 783 insertions(+), 772 deletions(-) diff --git a/doc/src/sgml/release.sgml b/doc/src/sgml/release.sgml index abf8862e76..0cbf73e249 100644 --- a/doc/src/sgml/release.sgml +++ b/doc/src/sgml/release.sgml @@ -11,9 +11,9 @@ PostgreSQL pg_[A-Za-z0-9_] [A-Z][A-Z] , , non-ascii convert to & escapes - http://www.mountaindragon.com/html/iso.htm - encode multibytes as UTF8 - http://www.pemberley.com/janeinfo/latin1.html#latexta + http://www.mountaindragon.com/html/iso.htm + encode multibytes as UTF8 + http://www.pemberley.com/janeinfo/latin1.html#latexta wrap long lines For new features, add links to the documentation sections. @@ -36,24 +36,19 @@ For new features, add links to the documentation sections. Overview - Major changes in this release: + This release adds many improvements to commands and database + facilities that were requested by users. Rather than add a + few large new features, this release makes many features from + previous releases easier to use. For example, it is now much + easier to create standby point-in-time-recovery (PITR) servers. + Many performance bottlenecks have been eliminated, allowing + more functionality to be enabled by default. Various additions + will make porting from other databases easier. The changes + in this release continue the PostgreSQL + tradition of not only being the most advanced open source + database, but also the easiest to use. - - - - - XX - - - - - XX - - - - - @@ -71,1193 +66,1277 @@ For new features, add links to the documentation sections. - - - XX - - - YYY - - - - - - - - Additional Changes - - - Below you will find a detailed account of the additional - changes between PostgreSQL 8.2 and - the previous major release. - - - - Performance Improvements - - - Improve efficiency of IN (list-of-expressions) - clauses (Tom) + Improve cost estimation for nestloop index scans (Tom) - - - Reduce locking requirements of sequential scans (Qingqing - Zhou) + This may eliminate the need to set unrealistically small + values of random_page_cost. If you have been + using a very small random_page_cost, please + recheck your test cases. - Improve subtransaction performance (Alvaro, Itagaki Takahiro, - Tom) + Deprecate use of postmaster symlink (Peter) + + + + postmaster and postgres + commands now act identically, with the behavior determined + by switches. The postmaster symlink is + kept for compatibility, but is not really needed. - Improve locking performance by breaking locks into sections - (Tom) + Change log_duration to output even if the query + is not output (Tom) - This allows locking to be more fine-grained, reducing - contention. + In prior releases, log_duration only printed if + the query appeared earlier in the logs. - Allow the planner to re-order OUTER JOINs when - possible (Tom) + Make row comparisons follow SQL standard + semantics and allow them to be used in index scans (Tom) - This makes a considerable difference for queries involving - multiple outer joins. + Previously, row = and <> comparisons followed the + standard but < <= > >= did not. A row comparison + can now be used as an index constraint for a multicolumn + index matching the row value. - Improve planning of joins to inherited tables and UNION - ALL views (Tom) + Set escape_string_warning to on by + default (Bruce) + + + + This issues a warning if backslash escapes are used in + non-escape (non-E'') strings. - Improve cost estimation for nestloop index scans (Tom) + Disallow aggregate functions in UPDATE commands + (unless within a subquery) (Tom) - This may eliminate the need to set unrealistically small - values of random_page_cost. If you have been - using a very small random_page_cost, please - recheck your test cases. + The standard disallows this, and it leads to unpredictable + results. - Increase default values for shared_buffers and - max_fsm_pages(Andrew) + Change the row constructor syntax (ROW(...)) + so that list elements foo.* will be expanded + to a list of their member fields, rather than creating a + nested rowtype field as formerly (Tom) - - - Improve the optimizer statistics used by LIKE, - ILIKE, and regular expression operations (Tom) + The new behavior is substantially more useful since it + allows, for example, triggers to check for data changes + with IF row(new.*) IS DISTINCT FROM row(old.*). + The old behavior is still available by omitting .*. - Allow constraint exclusion to be applied to inherited - UPDATE/DELETE queries (Tom) + Have SET CONSTRAINT affect only one constraint + (Kris Jurka) - SELECT already honored constraint exclusion. + In previous releases, SET CONSTRAINT modified + all constraints with a matching name. In this release, + the schema search path is used to modify only the first + matching constraint. A schema specification is also + supported. - Improve sorting speed and reduce memory usage (Simon, Tom) + Remove rule permission for tables, for security reasons + (Tom) - - - MIN()/MAX() can now use indexes - in more cases (Tom) + In this release, only a table's owner can create or modify + rules for the table. For backwards compatibility, + GRANT/REVOKE RULE is still accepted, + but it does nothing. - Improve concurrency for database creation and destruction - (Tom) + Array comparison improvements (Tom) - - - Speed up vacuuming of btree indexes (Heikki Linnakangas, - Tom) + Now array dimensions are also compared. - Improve COPY performance (Alon Goldshuv, Tom) + Change array concatenation to match documented behavior + (Tom) + + + + This changes the previous behavior where concatenation + would adjust the lower array dimensions. - Add FILLFACTOR to table and index creation - (ITAGAKI Takahiro) - + Have to_char(time) and to_char(interval) + treat 'HH' and 'HH12' as 12-hour + intervals. - This adds extra free space to each heap or index page, - allowing improved performance as the database grows. This - is particularly valuable to maintain CLUSTERing. + Most applications should use 'HH24' unless they + want a 12-hour display. - Remove dead index entries before btree page split (Junji - Teramoto) + Zero unmasked bits in conversion from INET to + CIDR (Tom) - - - - Server Changes - - Improve performance of statistics monitoring, especially - stats_command_string (Tom, Bruce) + Remove australian_timezones configuration variable + (Joachim Wieland) - This release sets stats_command_string to - on by default, now that its overhead is minimal. - This means pg_stat_activity now will show all - active queries by default. + No longer needed now that timezone abbreviations are + configurable. - Add support for include directives in postgresql.conf - (Joachim Wieland) + Change libpq PQdsplen() to + return a useful value (Martijn van Oosterhout) - Add native LDAP authentication (Magnus Hagander) + Change libpq PQgetssl() to + return a void*, rather than SSL* + (Martijn van Oosterhout) - This is particularly useful for platforms that do not - support PAM, such as Win32. + This allows applications to use the function without + SSL headers. - Re-enable full_page_writes (Tom) + Remove QNX and BEOS ports (Bruce) - This flag can now be used even with PITR. It - is turned on automatically between pg_start_backup() - and pg_stop_backup() calls. + These ports no longer had active maintainers. - Add support for SSL Certificate Revocation List - (CRL) files, root.crl (Libor - Hohoš) + Make command-line options of postmaster and postgres + identical (Peter) - This was added to libpq as well. + This allows the postmaster to pass arguments to each backend + without using -o. - Remove routine autovacuum server log entries (Bruce) - - - - pg_stat_activity now shows autovacuum activity. + Remove /contrib/ora2pg, now at - Add GRANT CONNECT ON DATABASE, to be used in - addition to pg_hba.conf (Gevik Babakhani) + Remove contrib modules that have been migrated to pgfoundry: + adddepend, dbase, dbmirror, fulltextindex, mac, userlock + + - This gives SQL-control over database access, and works in - combination with the existing pg_hba.conf - controls. + Remove contrib abandoned modules: mSQL-interface, ips - Add last vacuum and analyze timestamp columns to the stats - collector (Larry Rosenman) + In /contrib/xml2, rename xml_valid() to + xml_is_well_formed() (Tom) - These values now appear in the pg_stat_*_tables - system views, and are used by autovacuum. ? + xml_valid() will remain for backward compability, + but its behavior will change to do schema checks in future + releases. + + + + + Changes + + + Below you will find a detailed account of the additional + changes between PostgreSQL 8.2 and + the previous major release. + + + + Performance Improvements + + - Deprecate use of postmaster symlink (Peter) + Improve efficiency of IN (list-of-expressions) + clauses (Tom) + + - postmaster and postgres - commands now act identically, with the behavior determined - by switches. The postmaster symlink is - kept for compatibility, but is not really needed. + Reduce locking requirements of sequential scans (Qingqing + Zhou) - Add GUC update_process_title to - control whether the ps display is updated - for every command, default to on (Bruce) + Improve subtransaction performance (Alvaro, Itagaki Takahiro, + Tom) - Track tables needing vacuum with more accuracy (Alvaro) + Improve locking performance by breaking locks into sections + (Tom) - This reduces the overhead involved in preventing transaction - ID wraparound. + This allows locking to be more fine-grained, reducing + contention. - Allow units to be specified in configuration settings - (Peter) + Allow the planner to re-order OUTER JOINs when + possible (Tom) - You can now set shared_buffer to 32000kB, - for example. + This makes a considerable difference for queries involving + multiple outer joins. - Improve logging of protocol-level prepare/bind/execute - messages (Bruce, Tom) + Improve planning of joins to inherited tables and UNION + ALL views (Tom) + + - Such logging now shows statement names, bind parameter - values, and the text of the query being executed. + Increase default values for shared_buffers and + max_fsm_pages(Andrew) - Change log_duration to output even if the query - is not output (Tom) + Improve the optimizer statistics used by LIKE, + ILIKE, and regular expression operations (Tom) + + - In prior releases, log_duration only printed if - the query appeared earlier in the logs. + Allow constraint exclusion to be applied to inherited + UPDATE/DELETE queries (Tom) - - - Allow a forced switch to a new xlog file (Simon Riggs, Tom) + SELECT already honored constraint exclusion. + + - This is valuable for keeping PITR standby - servers in sync with the master. xlog file switching also - happens automatically during pg_stop_backup(). - This ensures that PITR servers have all xlog - files needed for recovery. + Improve sorting speed and reduce memory usage (Simon, Tom) - Add WAL informational functions (Simon Riggs) + MIN()/MAX() can now use indexes + in more cases (Tom) + + - Add functions for interrogating the current xlog insertion - point and determining WAL filenames from the - hex WAL locations displayed by - pg_stop_backup() and friends. + Improve concurrency for database creation and destruction + (Tom) - Allow WAL replay to be restored quicker in case - of a crash (Simon Riggs) + Speed up vacuuming of btree indexes (Heikki Linnakangas, + Tom) + + - The server now does periodic checkpoints during WAL - recovery, so if there is a crash, future WAL - recovery is shortened. This also eliminates the need for - PITR standby servers to replay the entire log - since the base backup if they crash. + Improve COPY performance (Alon Goldshuv, Tom) - Add archive_timeout to force xlog file switches - at a given interval (Simon Riggs) + Add FILLFACTOR to table and index creation + (ITAGAKI Takahiro) + + + + This adds extra free space to each heap or index page, + allowing improved performance as the database grows. This + is particularly valuable to maintain CLUSTERing. + + - This enforces a maximum delay for PITR standby - servers. + Improve multicolumn GIST indexing (Oleg, Teodor) - Add a waiting column to pg_stat_activity - (Tom) + GIST indexes now are clusterable (Teodor) + + - This allows pg_stat_activity to show the same - information as the ps display. + Remove dead index entries before btree page split (Junji + Teramoto) + - Query Changes + Server Changes - Support portal parameters in EXPLAIN and - EXECUTE (Tom) + Improve performance of statistics monitoring, especially + stats_command_string (Tom, Bruce) - This allows, for example, ? parameters to work - in these commands in JDBC. + This release sets stats_command_string to + on by default, now that its overhead is minimal. + This means pg_stat_activity now will show all + active queries by default. - Add IS NOT DISTINCT FROM (Pavel Stehule) + Add support for include directives in postgresql.conf + (Joachim Wieland) + + + + + + Add native LDAP authentication (Magnus Hagander) - It is similar to equals (=), but is true when - both left and right arguments are NULL, and - false when just one is, rather than yielding NULL - in these cases. + This is particularly useful for platforms that do not + support PAM, such as Win32. - Make row comparisons follow SQL standard - semantics and allow them to be used in index scans (Tom) + Allow full_page_writes to be turned off + again (Tom) - Previously, row = and <> comparisons followed the - standard but < <= > >= did not. A row comparison - can now be used as an index constraint for a multicolumn - index matching the row value. + This flag can now be used even with PITR. It + is turned on automatically between pg_start_backup() + and pg_stop_backup() calls. - Allow additional row values comparison (Tom) + Add support for SSL Certificate Revocation List + (CRL) files, root.crl (Libor + Hohoš) - Add <, <=, >, >=. + This was added to libpq as well. - Add system view pg_prepared_statements to show - prepared statements (Joachim Wieland) + Remove routine autovacuum server log entries (Bruce) + + + + pg_stat_activity now shows autovacuum activity. - Add system view pg_cursors to show open cursors - (Joachim Wieland) + Add GRANT CONNECT ON DATABASE, to be used in + addition to pg_hba.conf (Gevik Babakhani) - Both this and pg_prepared_statements are very - useful for pooled connection setups. + This gives SQL-control over database access, and works in + combination with the existing pg_hba.conf + controls. - If SQL-level PREPARE parameters - are unknown, infer their types from the context of the - query (Neil) + Add last vacuum and analyze timestamp columns to the stats + collector (Larry Rosenman) + + + + These values now appear in the pg_stat_*_tables + system views, and are used by autovacuum. ? + + - Protocol-level PREPARE already did this. + Add GUC update_process_title to + control whether the ps display is updated + for every command, default to on (Bruce) - Allow UPDATE and DELETE to use an - alias for the target table (Atsushi Ogawa) + Track tables needing vacuum with more accuracy (Alvaro) - This allows these statements to support self-joins more - conveniently. UPDATE already supported as - FROM clause, but DELETE did not. + This reduces the overhead involved in preventing transaction + ID wraparound. - Add CASCADE option to TRUNCATE - (Joachim Wieland) + Allow units to be specified in configuration settings + (Peter) - This allows TRUNCATE also to automatically - truncate all foreign-key referencing tables. + You can now set shared_buffer to 32000kB, + for example. - Enable standard_conforming_strings to be turned - on (Kevin Grittner) + Improve logging of protocol-level prepare/bind/execute + messages (Bruce, Tom) - This allow special backslash escaping in strings to be - turned off so PostgreSQL is more - standards-compliant. The default is off, but - future releases will default this to on. + Such logging now shows statement names, bind parameter + values, and the text of the query being executed. - Set escape_string_warning to on by - default (Bruce) + Allow a forced switch to a new xlog file (Simon Riggs, Tom) - This issues a warning if backslash escapes are used in - non-escape (non-E'') strings. + This is valuable for keeping PITR standby + servers in sync with the master. xlog file switching also + happens automatically during pg_stop_backup(). + This ensures that PITR servers have all xlog + files needed for recovery. - Support FOR UPDATE and FOR SHARE - in the same command (Tom) + Add WAL informational functions (Simon Riggs) + + + + Add functions for interrogating the current xlog insertion + point and determining WAL filenames from the + hex WAL locations displayed by + pg_stop_backup() and friends. - Disallow aggregate functions in UPDATE commands - (unless within a subquery) (Tom) + Allow WAL replay to be restored quicker in case + of a crash (Simon Riggs) - The standard disallows this, and it leads to unpredictable - results. + The server now does periodic checkpoints during WAL + recovery, so if there is a crash, future WAL + recovery is shortened. This also eliminates the need for + PITR standby servers to replay the entire log + since the base backup if they crash. - Change the row constructor syntax (ROW(...)) - so that list elements foo.* will be expanded - to a list of their member fields, rather than creating a - nested rowtype field as formerly (Tom) + Add archive_timeout to force xlog file switches + at a given interval (Simon Riggs) - The new behavior is substantially more useful since it - allows, for example, triggers to check for data changes - with IF row(new.*) IS DISTINCT FROM row(old.*). - The old behavior is still available by omitting .*. + This enforces a maximum delay for PITR standby + servers. - Change LIMIT/OFFSET to use - int8 (Dhanaraj M) + Add a waiting column to pg_stat_activity + (Tom) - This allows LIMIT/OFFSET over - two-billion. + This allows pg_stat_activity to show the same + information as the ps display. + + + + Query Changes + + - Add support for multi-row VALUES clauses as - part of INSERT and SELECT statements - (Joe, Tom) + Support portal parameters in EXPLAIN and + EXECUTE (Tom) - This allows INSERT to insert multiple rows of - constants, or queries to generate result sets using constants. - For example, INSERT ... VALUES (...), (...), - ...., and SELECT * FROM (VALUES (...), (...), - ....) AS alias(f1, ...). + This allows, for example, ? parameters to work + in these commands in JDBC. - Improve the length output used by - UNION/INTERSECT/EXCEPT - (Tom) + Add IS NOT DISTINCT FROM (Pavel Stehule) - When all columns are of the same defined length, that length - is used for output, rather than a generic length. + It is similar to equals (=), but is true when + both left and right arguments are NULL, and + false when just one is, rather than yielding NULL + in these cases. - Add INSERT/UPDATE/DELETE - RETURNING (Jonah Harris, Tom) + Allow additional row value comparisons (Tom) - This allows these commands to return values, such as the - computed serial key for a new row. In the UPDATE - case, values from the new state of the row are returned. + Add <, <=, >, >=. + + + + + + Add system view pg_prepared_statements to show + prepared statements (Joachim Wieland) - Allow UPDATE to set multiple columns with a - list of values (Susanne Ebrecht) + Add system view pg_cursors to show open cursors + (Joachim Wieland) - This is basically as short-hand for assigning the columns - and values in pairs. The syntax is UPDATE tab - SET (col, ...) = (val, ...). + Both this and pg_prepared_statements are very + useful for pooled connection setups. - Allow ILIKE to work for multi-byte encodings - (Tom) + If SQL-level PREPARE parameters + are unknown, infer their types from the context of the + query (Neil) - Internally, ILIKE now calls lower() - and then uses LIKE. Locale-specific regular - expression operations still do not work in these encodings. + Protocol-level PREPARE already did this. ? - Protocol-level unnamed prepared statements are re-planned - for each set of BIND values (Tom) + Allow UPDATE and DELETE to use an + alias for the target table (Atsushi Ogawa) - This improves performance because the exact parameter values - can be used in the plan. + This allows these statements to support self-joins more + conveniently. UPDATE already supported as + FROM clause, but DELETE did not. - Do not flatten subqueries that contain VOLATILE - functions in their target lists (Jaime Casanova) + Add CASCADE option to TRUNCATE + (Joachim Wieland) - This prevents surprising behavior due to multiple evaluation - of a volatile function (such as random() - or nextval()). It may cause performance - degradation in the presence of functions that are unnecessarily - marked as volatile. + This allows TRUNCATE also to automatically + truncate all foreign-key referencing tables. - - - - Object Manipulation Changes - + - Add DROP object IF EXISTS for many - object types (Andrew) + Enable standard_conforming_strings to be turned + on (Kevin Grittner) - This allows DROP operations on non-existent - objects without generating an error. + This allow special backslash escaping in strings to be + turned off so PostgreSQL is more + standards-compliant. The default is off, but + future releases will default this to on. - Add DROP OWNED to drop all objects owned by a - role (Alvaro) + Support FOR UPDATE and FOR SHARE + in the same command (Tom) - Add REASSIGN OWNED to reassign ownership of - all objects owned by a role (Alvaro) + Change LIMIT/OFFSET to exceed + two billion (Dhanaraj M) + + - This, and DROP OWNED above, facilitate dropping - roles. + Add support for multi-row VALUES clauses as + part of INSERT and SELECT statements + (Joe, Tom) - - - Add CREATE/ALTER ROLE PASSWORD NULL, - which removes the role's password (Peter) + This allows INSERT to insert multiple rows of + constants, or queries to generate result sets using constants. + For example, INSERT ... VALUES (...), (...), + ...., and SELECT * FROM (VALUES (...), (...), + ....) AS alias(f1, ...). - Properly enforce DOMAIN check constraints - everywhere (Neil, Tom) + Improve the length output used by + UNION/INTERSECT/EXCEPT + (Tom) - For example, the result of a user-defined function that is - declared to return a domain type is now checked against - the constraints. + When all columns are of the same defined length, that length + is used for output, rather than a generic length. - Add GRANT ON SEQUENCE syntax (Bruce) + Add INSERT/UPDATE/DELETE + RETURNING (Jonah Harris, Tom) - This was added for setting sequence-specific permissions. - GRANT ON [TABLE] for sequences is still supported - for backward compatibility. + This allows these commands to return values, such as the + computed serial key for a new row. In the UPDATE + case, values from the new state of the row are returned. - Add USAGE permission for sequences that allows - only currval() and nextval(), - not setval() (Bruce) + Allow UPDATE to set multiple columns with a + list of values (Susanne Ebrecht) - USAGE permission allows more find-grained - control over sequence access. It allows users to increment - a sequence, but prevents them from setting the sequence to - an arbitrary value using setval(). + This is basically as short-hand for assigning the columns + and values in pairs. The syntax is UPDATE tab + SET (col, ...) = (val, ...). - Allow comments on global objects to be stored globally - (Kris Jurka) + Allow ILIKE to work for multi-byte encodings + (Tom) - Previously, global object comments were stored in individual - databases, making them ineffective. This adds a new - pg_shdescription table. + Internally, ILIKE now calls lower() + and then uses LIKE. Locale-specific regular + expression operations still do not work in these encodings. - Add ON COMMIT clauses to CREATE TABLE - AS (Neil) + Protocol-level unnamed prepared statements are re-planned + for each set of BIND values (Tom) - This allows temporary tables to be truncated or dropped on - transaction commit. The default behavior is for the table - to remain until the session ends. + This improves performance because the exact parameter values + can be used in the plan. - Add TABLESPACE and ON COMMIT clauses - to CREATE TABLE AS (Neil) + Do not flatten subqueries that contain VOLATILE + functions in their target lists (Jaime Casanova) - This allows the tablespace to be specified for the new - table. + This prevents surprising behavior due to multiple evaluation + of a volatile function (such as random() + or nextval()). It may cause performance + degradation in the presence of functions that are unnecessarily + marked as volatile. + + + + Object Manipulation Changes + + - Allow placeholder (shell) types to be created (Martijn van - Oosterhout) + Add DROP object IF EXISTS for many + object types (Andrew) - Shell types create a type reference, without specifying - any of the aspects of the type. It is useful for creating - types with input/output functions that reference the data - type. The syntax is CREATE TYPE typname. + This allows DROP operations on non-existent + objects without generating an error. - Add new aggregate creation syntax (Tom) + Add DROP OWNED to drop all objects owned by a + role (Alvaro) + + - The new syntax is CREATE AGGREGATE aggname - (input_type) (parameter_list). This more naturally supports - the new multi-parameter aggregate functionality. The - previous syntax is still supported. + Add REASSIGN OWNED to reassign ownership of + all objects owned by a role (Alvaro) + + + + This, and DROP OWNED above, facilitate dropping + roles. - Aggregate functions now support multiple input parameters - (Sergey Koposov, Tom) + Add CREATE/ALTER ROLE PASSWORD NULL, + which removes the role's password (Peter) - Have SET CONSTRAINT affect only one constraint - (Kris Jurka) + Properly enforce DOMAIN check constraints + everywhere (Neil, Tom) - In previous releases, SET CONSTRAINT modified - all constraints with a matching name. In this release, - the schema search path is used to modify only the first - matching constraint. A schema specification is also - supported. + For example, the result of a user-defined function that is + declared to return a domain type is now checked against + the constraints. - Add INCLUDING CONSTRAINTS to CREATE - TABLE LIKE (Greg Stark) + Add GRANT ON SEQUENCE syntax (Bruce) - This allows the new table to receive matching constraints. + This was added for setting sequence-specific permissions. + GRANT ON [TABLE] for sequences is still supported + for backward compatibility. - Add ALTER TABLE ... [NO] INHERIT (Greg Stark) + Add USAGE permission for sequences that allows + only currval() and nextval(), + not setval() (Bruce) - This allow inheritance to be added and removed dynamically, - rather than just at table creation and destruction. This - is very valuable for table partitioning using constraint - exclusion. + USAGE permission allows more find-grained + control over sequence access. It allows users to increment + a sequence, but prevents them from setting the sequence to + an arbitrary value using setval(). - Remove rule permission for tables, for security reasons - (Tom) + Allow comments on global objects to be stored globally + (Kris Jurka) - In this release, only a table's owner can create or modify - rules for the table. For backwards compatibility, - GRANT/REVOKE RULE is still accepted, - but it does nothing. + Previously, global object comments were stored in individual + databases, making them ineffective. This adds a new + pg_shdescription table. - - - - Utility Command Changes - + - Reduce progress messages displayed by initdb (Tom) + Add ON COMMIT clauses to CREATE TABLE + AS (Neil) + + + + This allows temporary tables to be truncated or dropped on + transaction commit. The default behavior is for the table + to remain until the session ends. - Have initdb detect the operating system locale and set the - default DateStyle accordingly (Peter) + Add TABLESPACE and ON COMMIT clauses + to CREATE TABLE AS (Neil) - This make it more likely that the installed - postgresql.conf DateStyle value will - be correct. + This allows the tablespace to be specified for the new + table. - Have the COPY command return a command tag that - includes the number of rows copied (Volkan Yazıcı) + Allow placeholder (shell) types to be created (Martijn van + Oosterhout) + + + + Shell types create a type reference, without specifying + any of the aspects of the type. It is useful for creating + types with input/output functions that reference the data + type. The syntax is CREATE TYPE typname. - Allow VACUUM to expire rows without being - affected by other concurrent VACUUMs (Hannu - Krossing, Alvaro, Tom) + Add new aggregate creation syntax (Tom) + + + + The new syntax is CREATE AGGREGATE aggname + (input_type) (parameter_list). This more naturally supports + the new multi-parameter aggregate functionality. The + previous syntax is still supported. - Avoid extra scan of tables during VACUUM of - index-less table (Greg Stark) + Aggregate functions now support multiple input parameters + (Sergey Koposov, Tom) - Add option to allow indexes to be created without blocking - concurrent writes to the table (Greg Stark) + Add INCLUDING CONSTRAINTS to CREATE + TABLE LIKE (Greg Stark) - The new syntax is CREATE INDEX CONCURRENTLY. - The default behavior is still to block table modification - while a index is being created. + This allows the new table to receive matching constraints. - Allow COPY to dump a SELECT query - (Zoltan Boszormenyi, Karel Zak) + Add ALTER TABLE ... [NO] INHERIT (Greg Stark) - This allows COPY to dump arbitrary SQL - queries. The syntax is COPY (SELECT ...) TO. + This allow inheritance to be added and removed dynamically, + rather than just at table creation and destruction. This + is very valuable for table partitioning using constraint + exclusion. + + - Data Type and Function Changes + Utility Command Changes - Allow arrays to contain NULL elements (Tom) + Reduce progress messages displayed by initdb (Tom) - Change array concatenation to match documented behavior - (Tom) + Have initdb detect the operating system locale and set the + default DateStyle accordingly (Peter) - This changes the previous behavior where concatenation - would adjust the lower array dimensions. + This make it more likely that the installed + postgresql.conf DateStyle value will + be correct. - Array comparison improvements (Tom) - - - - Now array dimensions are also compared. + Have the COPY command return a command tag that + includes the number of rows copied (Volkan Yazıcı) - Allow to_char(time) and to_char(interval) - to output AM/PM specifications - (Bruce) + Allow VACUUM to expire rows without being + affected by other concurrent VACUUMs (Hannu + Krossing, Alvaro, Tom) + + - Intervals and times are treated as 24-hour periods, e.g. - 25 hours is AM. + Avoid extra scan of tables without indexes during VACUUM + (Greg Stark) - Have to_char(time) and to_char(interval) - treat 'HH' and 'HH12' as 12-hour - intervals. + Add option to allow indexes to be created without blocking + concurrent writes to the table (Greg Stark) + - Most applications should use 'HH24' unless they - want a 12-hour display. + The new syntax is CREATE INDEX CONCURRENTLY. + The default behavior is still to block table modification + while a index is being created. - Add a server-side sleep function pg_sleep() - (Joachim Wieland) + Allow COPY to dump a SELECT query + (Zoltan Boszormenyi, Karel Zak) + + + + This allows COPY to dump arbitrary SQL + queries. The syntax is COPY (SELECT ...) TO. + + + + Data Type and Function Changes + + - Zero unmasked bits in conversion from INET to - CIDR (Tom) + Allow arrays to contain NULL elements (Tom) - Add convenient arithmetic operations on INET/CIDR - values (Stephen R. van den Berg) + Allow to_char(time) and to_char(interval) + to output AM/PM specifications + (Bruce) - The new operators are & (and), | - (or), ~ (not), + int8, - - int8, and inet - - inet. + Intervals and times are treated as 24-hour periods, e.g. + 25 hours is AM. - Allow to_char() to print localized month and - day names (Euler Taveira de Oliveira) + Add a server-side sleep function pg_sleep() + (Joachim Wieland) - Add all comparison operators for the tid data type - (Mark Kirkwood, Greg Stark, Tom) + Add convenient arithmetic operations on INET/CIDR + values (Stephen R. van den Berg) + + + + The new operators are & (and), | + (or), ~ (not), + int8, + - int8, and inet - + inet. - Add new function justify_interval() to adjust - interval units (Mark Dilger) + Allow to_char() to print localized month and + day names (Euler Taveira de Oliveira) - Add new aggregate functions from SQL2003 (Neil) + Add all comparison operators for the tid data type + (Mark Kirkwood, Greg Stark, Tom) + + - The new functions are var_pop(), - var_samp(), stddev_pop(), and - stddev_samp(). var_samp() and - stddev_samp() are merely renamings of the - existing aggregates variance() and - stddev(). The latter names have been kept - for backward compatibility. + Add new function justify_interval() to adjust + interval units (Mark Dilger) - New operators for array-subset comparisons (@>, - <@, &&) (Teodor, Tom) + Add new aggregate functions from SQL2003 (Neil) - The old operators were kept for backward compatibility. + The new functions are var_pop(), + var_samp(), stddev_pop(), and + stddev_samp(). var_samp() and + stddev_samp() are merely renamings of the + existing aggregates variance() and + stddev(). The latter names have been kept + for backward compatibility. - Allow domains to be created using other domains (Tom) + New operators for array-subset comparisons (@>, + <@, &&) (Teodor, Tom) - - - Add clock_timestamp(), - statement_timestamp(), and - transaction_timestamp() (Bruce) + The old operators were kept for backward compatibility. + + - clock_timestamp() is the true current time, - and statement_timestamp() is the time the - current command arrived at the server. - transaction_timestamp() is the same as - now(). + Allow domains to be created using other domains (Tom) - Allow full timezone names in timestamp values - (Joachim Wieland) + Add clock_timestamp(), + statement_timestamp(), and + transaction_timestamp() (Bruce) - For example, '2006-05-24 21:11 - America/New_York'::timestamptz. + clock_timestamp() is the true current time, + and statement_timestamp() is the time the + current command arrived at the server. + transaction_timestamp() is the same as + now(). - Create a configuration file of timezone abbreviations - (Joachim Wieland) + Allow full timezone names in timestamp values + (Joachim Wieland) - The file name is controlled by GUC variable - timezone_abbreviations. + For example, '2006-05-24 21:11 + America/New_York'::timestamptz. - Remove australian_timezones configuration variable - (Joachim Wieland) + Create a configuration file of timezone abbreviations + (Joachim Wieland) - No longer needed now that timezone abbreviations are - configurable. + The file name is controlled by GUC variable + timezone_abbreviations. - Add pg_timezone_abbrevs and pg_timezone_names - views to show supported timezones (Magnus Hagander) + Add pg_timezone_abbrevs and pg_timezone_names + views to show supported timezones (Magnus Hagander) - Add SQL2003-standard statistical aggregates (Sergey Koposov) + Add SQL2003-standard statistical aggregates (Sergey Koposov) - New functions: regr_intercept(), - regr_slope(), regr_r2(), - corr(), covar_samp(), - covar_pop(), regr_avgx(), - regr_avgy(), regr_sxy(), - regr_sxx(), regr_syy(), - regr_count() + New functions: regr_intercept(), + regr_slope(), regr_r2(), + corr(), covar_samp(), + covar_pop(), regr_avgx(), + regr_avgy(), regr_sxy(), + regr_sxx(), regr_syy(), + regr_count() - Fix problems with dumping renamed SERIAL columns - (Tom) + Fix problems with dumping renamed SERIAL columns + (Tom) - The fix is to dump a SERIAL column by explicitly - specifying its DEFAULT and sequence elements, - and reconstructing the SERIAL column on reload - using a new ALTER SEQUENCE OWNED BY command. - This also allows dropping a SERIAL column - specification. + The fix is to dump a SERIAL column by explicitly + specifying its DEFAULT and sequence elements, + and reconstructing the SERIAL column on reload + using a new ALTER SEQUENCE OWNED BY command. + This also allows dropping a SERIAL column + specification. - Interval computation improvements (Michael Glaesemann, - Bruce) + Interval computation improvements (Michael Glaesemann, + Bruce) + + @@ -1266,36 +1345,38 @@ For new features, add links to the documentation sections. - Allow FOR statements to return values to scalars - as well as records and row types (Pavel Stehule) + Allow FOR statements to return values to scalars + as well as records and row types (Pavel Stehule) - Add a BY clause to the FOR loop, - to control the iteration increment (Jaime Casanova) + Add a BY clause to the FOR loop, + to control the iteration increment (Jaime Casanova) - Add table_name and table_schema as - trigger data (Andrew) + Add table_name and table_schema as + trigger data (Andrew) - Add STRICT to SELECT INTO (Matt - Miller) + Add STRICT to SELECT INTO (Matt + Miller) - STRICT mode throws an exception if more or less - than one row is returned by the SELECT, for - Oracle PL/SQL compatibility. + STRICT mode throws an exception if more or less + than one row is returned by the SELECT, for + Oracle PL/SQL compatibility. + + @@ -1304,36 +1385,38 @@ For new features, add links to the documentation sections. - Honor check_function_bodies (Tom) + Honor check_function_bodies (Tom) - Add prepared queries (Dmitry Karasik) + Add prepared queries (Dmitry Karasik) - Add table_name and table_schema as - trigger data (Adam Sjøgren) + Add table_name and table_schema as + trigger data (Adam Sjøgren) - relname is kept but now deprecated + relname is kept but now deprecated - Make $_TD trigger data a global variable (Andrew) + Make $_TD trigger data a global variable (Andrew) - Previously, it was lexical, which caused unexpected sharing - violations. + Previously, it was lexical, which caused unexpected sharing + violations. + + @@ -1342,42 +1425,44 @@ For new features, add links to the documentation sections. - Allow functions to return void (Neil) + Allow functions to return void (Neil) - Add named parameters to the args[] array (Sven - Suursoho) + Add named parameters to the args[] array (Sven + Suursoho) - Return composite-types as dictionary (Sven Suursoho) + Return composite-types as dictionary (Sven Suursoho) - Return result-set as list, iterator, - or generator (Sven Suursoho) + Return result-set as list, iterator, + or generator (Sven Suursoho) - Allow returning of composite types and - result sets (Sven Suursoho) + Allow returning of composite types and + result sets (Sven Suursoho) - Add table_name and table_schema as - trigger data (Andrew) + Add table_name and table_schema as + trigger data (Andrew) + + @@ -1386,98 +1471,100 @@ For new features, add links to the documentation sections. - Add new command \password for changing role - password with client-side password encryption (Peter) + Add new command \password for changing role + password with client-side password encryption (Peter) - Allow multi-line values to align in the proper column - (Martijn van Oosterhout) + Allow multi-line values to align in the proper column + (Martijn van Oosterhout) - Save multi-line statements as a single entry, rather than - one line at a time (Sergey E. Koposov) + Save multi-line statements as a single entry, rather than + one line at a time (Sergey E. Koposov) - This makes up-arrow recall of queries easier. + This makes up-arrow recall of queries easier. - Allow \c to connect to a new host and port - number (David, Volkan Yazıcı) + Allow \c to connect to a new host and port + number (David, Volkan Yazıcı) - Add option to run the entire session in a single transaction - (Simon) + Add option to run the entire session in a single transaction + (Simon) - Use options -1 or --single-transaction. + Use options -1 or --single-transaction. - Improve highlighting of error location in query in more - cases (Tom) + Improve highlighting of error location in query in more + cases (Tom) - Add tablespace display to \l+ (Philip Yarra) + Add tablespace display to \l+ (Philip Yarra) - Support binary COPY (Andreas Pflug) + Support binary COPY (Andreas Pflug) - Improve \df slash command to include the argument - names and modes (OUT or INOUT) of - the function (David Fetter) + Improve \df slash command to include the argument + names and modes (OUT or INOUT) of + the function (David Fetter) - Make the line counter 64-bit so it can handle files over - two billion lines (David Fetter) + Make the line counter 64-bit so it can handle files over + two billion lines (David Fetter) - Report both the returned data and the command status tag - for INSERT/UPDATE/DELETE - RETURNING (Tom) + Report both the returned data and the command status tag + for INSERT/UPDATE/DELETE + RETURNING (Tom) - Support retrieving SELECT results in batches - using a cursor (Chris Mair) + Support retrieving SELECT results in batches + using a cursor (Chris Mair) - This is accomplished using \set FETCH_COUNT. + This is accomplished using \set FETCH_COUNT. + + @@ -1486,37 +1573,39 @@ For new features, add links to the documentation sections. - Allow complex selection of objects to be included or excluded - by pg_dump (Greg Sabino Mullane) + Allow complex selection of objects to be included or excluded + by pg_dump (Greg Sabino Mullane) - pg_dump now supports multiple -n - (schema) and -t (table) options, and adds - -T and -N options to exclude objects. - Also adds support for regular expressions for object names - in these switches. + pg_dump now supports multiple -n + (schema) and -t (table) options, and adds + -T and -N options to exclude objects. + Also adds support for regular expressions for object names + in these switches. - Add pg_dump -X - no-data-for-failed-tables option to suppress loading - data if table creation failed (the table already exists) - (Martin Pitt) + Add pg_dump -X + no-data-for-failed-tables option to suppress loading + data if table creation failed (the table already exists) + (Martin Pitt) - Add pg_restore option to run the entire - session in a single transaction (Simon) + Add pg_restore option to run the entire + session in a single transaction (Simon) - Use options -1 or --single-transaction. + Use options -1 or --single-transaction. + + @@ -1525,68 +1614,51 @@ For new features, add links to the documentation sections. - Add PQencryptPassword() to encrypt passwords - (Tom) + Add PQencryptPassword() to encrypt passwords + (Tom) - This allows passwords to be sent encrypted for commands - like ALTER USER ... PASSWORD. + This allows passwords to be sent encrypted for commands + like ALTER USER ... PASSWORD. - Change PQdsplen() to return a useful value - (Martijn van Oosterhout) + Allow the .pgpass hostname to match the default + socket directory, as well as a blank pghost + (Bruce) - Change PQgetssl() to return a void*, - rather than SSL* (Martijn van Oosterhout) + Add function PQisthreadsafe() (Bruce) - This allows applications to use the function without - SSL headers. + This allows applications to query the thread-safety status + of the library. - Allow the .pgpass hostname to match the default - socket directory, as well as a blank pghost - (Bruce) - - - - - - Add function PQisthreadsafe() (Bruce) - - - - This allows applications to query the thread-safety status - of the library. + Allow LDAP lookups from pg_service.conf + (Albe Laurenz) - Allow LDAP lookups from pg_service.conf - (Albe Laurenz) + Add PQdescribePrepared(), + PQdescribePortal(), and related functions + return information about previously prepared statements + and open cursors (Volkan Yazıcı) - - - Add PQdescribePrepared(), - PQdescribePortal(), and related functions - return information about previously prepared statements - and open cursors (Volkan Yazıcı) - @@ -1595,27 +1667,29 @@ For new features, add links to the documentation sections. - Allow SHOW to put its result into a variable - (Joachim Wieland) + Allow SHOW to put its result into a variable + (Joachim Wieland) - Add COPY TO STDOUT (Joachim Wieland) + Add COPY TO STDOUT (Joachim Wieland) - Add regression tests (Joachim Wieland, Michael) + Add regression tests (Joachim Wieland, Michael) - Major source code cleanups (Joachim Wieland, Michael) + Major source code cleanups (Joachim Wieland, Michael) + + @@ -1624,52 +1698,54 @@ For new features, add links to the documentation sections. - Improve handling of intermittent file system and resource - failures (Qingqing Zhou) + Improve handling of intermittent file system and resource + failures (Qingqing Zhou) - Stability fixes (Magnus) + Stability fixes (Magnus) - Particularly, prevent the postmaster from stopping if too - many connection requests arrive too rapidly. + Particularly, prevent the postmaster from stopping if too + many connection requests arrive too rapidly. - Add support for Windows code pages 1253, - 1254, 1255, and 1257 - (Kris Jurka) + Add support for Windows code pages 1253, + 1254, 1255, and 1257 + (Kris Jurka) - Allow MSVC to compile the PostgreSQL - server (Magnus, Hiroshi Saito) + Allow MSVC to compile the PostgreSQL + server (Magnus, Hiroshi Saito) - Add MSVC support for utility commands and - pg_dump (Hiroshi Saito) + Add MSVC support for utility commands and + pg_dump (Hiroshi Saito) - Add native semaphore implementation (Qingqing Zhou) + Add native semaphore implementation (Qingqing Zhou) - Previous implementations mimicked SysV semaphores. + Previous implementations mimicked SysV semaphores. + + @@ -1678,215 +1754,183 @@ For new features, add links to the documentation sections. - Remove R-tree indexing (Tom) + Remove R-tree indexing (Tom) - Rtree has been re-implemented using GIST. + Rtree has been re-implemented using GIST. - Reduce libraries linked into the backend needlessly (Martijn - van Oosterhout, Tom) + Reduce libraries linked into the backend needlessly (Martijn + van Oosterhout, Tom) - New macro PG_VERSION_NUM for use by third-party - applications wanting to test the backend version in C using - > and < comparisons (Bruce) - - - - - - Improve multicolumn GIST indexing (Oleg, Teodor) - - - - - - GIST indexes now are clusterable (Teodor) - - - - - - Add a configure flag to allow libedit to be preferred over - GNU readline (Bruce) - - - - Use configure --with-libedit-preferred. + New macro PG_VERSION_NUM for use by third-party + applications wanting to test the backend version in C using + > and < comparisons (Bruce) - Allow installation into directories containing spaces - (Peter) + Add a configure flag to allow libedit to be preferred over + GNU readline (Bruce) - - - Improve ability to relocate installs in more complex - configurations (Tom) + Use configure --with-libedit-preferred. - Add support for Solaris x86_64 using the - Solaris compiler (Pierre Girard, Theo - Schlossnagle, Bruce) + Allow installation into directories containing spaces + (Peter) - Remove QNX and BEOS ports (Bruce) - - - - These ports no longer had active maintainers. + Improve ability to relocate installs in more complex + configurations (Tom) - Make command-line options of postmaster and postgres - identical (Peter) - - - - This allows the postmaster to pass arguments to each backend - without using -o. + Add support for Solaris x86_64 using the + Solaris compiler (Pierre Girard, Theo + Schlossnagle, Bruce) - Add XLOG_BLCKSZ as independent from BLCKSZ - (Mark Wong) + Add XLOG_BLCKSZ as independent from BLCKSZ + (Mark Wong) - Add LWLOCK_STATS define to report locking - activity (Tom) + Add LWLOCK_STATS define to report locking + activity (Tom) - Add GIN (Generalized Inverted iNdex) index - access method (Teodor) + Add GIN (Generalized Inverted iNdex) index + access method (Teodor) - Emit warnings for unknown configure options - (Martijn van Oosterhout) + Emit warnings for unknown configure options + (Martijn van Oosterhout) - Add PG_MODULE_MAGIC header block to all shared - object files (Martijn van Oosterhout) + Add PG_MODULE_MAGIC header block to all shared + object files (Martijn van Oosterhout) - The magic blocks prevent version mismatches between object - files and servers. + The magic blocks prevent version mismatches between object + files and servers. - Add a GUC parameter seq_page_cost - (Tom) + Add a GUC parameter seq_page_cost + (Tom) - Re-implement the regression test script as a C program - (Magnus, Tom) + Re-implement the regression test script as a C program + (Magnus, Tom) - Add DTrace support (Robert Lor) + Add DTrace support (Robert Lor) - Allow loadable modules to allocate shared memory and - lightweight locks (Marc Munro) + Allow loadable modules to allocate shared memory and + lightweight locks (Marc Munro) - Add automatic initialization and finalization of dynamically - loaded libraries (Ralf Engelschall, Tom) + Add automatic initialization and finalization of dynamically + loaded libraries (Ralf Engelschall, Tom) - New functions _PG_init() and _PG_fini() - are called if the library defines such symbols. Hence we - no longer need to specify an initialization function in - shared_preload_libraries; we can assume that the - library used the _PG_init() convention instead. + New functions _PG_init() and _PG_fini() + are called if the library defines such symbols. Hence we + no longer need to specify an initialization function in + shared_preload_libraries; we can assume that the + library used the _PG_init() convention instead. - Add support for libraries that enhance server-side languages - (Korry Douglas) + Add support for libraries that enhance server-side languages + (Korry Douglas) - Such libraries can be used for debugging or performance - measurement. + Such libraries can be used for debugging or performance + measurement. - Rename existing GUC variable - preload_libraries to shared_preload_libraries - (Tom) + Rename existing GUC variable + preload_libraries to shared_preload_libraries + (Tom) - Add new variable server_version_num (Greg Sabino - Mullane) + Add new variable server_version_num (Greg Sabino + Mullane) - This is like server_version, but is an integer, e.g. - 80200. It allows easy applications version - checks. + This is like server_version, but is an integer, e.g. + 80200. It allows easy applications version + checks. - New XML documentation section (Bruce) + New XML documentation section (Bruce) + + @@ -1895,40 +1939,40 @@ For new features, add links to the documentation sections. - Major tsearch2 improvements (Oleg, Teodor) + Major tsearch2 improvements (Oleg, Teodor) - multibyte encoding support, including UTF8 + multibyte encoding support, including UTF8 - query rewriting support + query rewriting support - improved ranking functions + improved ranking functions - thesaurus dictionary support + thesaurus dictionary support - Ispell dictionaries now recognize MySpell - format, used by OpenOffice. + Ispell dictionaries now recognize MySpell + format, used by OpenOffice. - GIN support + GIN support @@ -1938,138 +1982,105 @@ For new features, add links to the documentation sections. - Add pg_freespacemap to display free space map information - (Mark Kirkwood) - - - - - - New uninstall scripts (David) + Add pg_freespacemap to display free space map information + (Mark Kirkwood) - Add pgrowlocks (Tatsuo) - - - - This shows row locking information for a specified table. + New uninstall scripts (David) - Add Pgadmin administration functions to - adminpack (Dave) + Add pgrowlocks (Tatsuo) - These functions provide additional file system access - routines not present in the default PostgreSQL - server. + This shows row locking information for a specified table. - Add index information to pgstattuple (ITAGAKI Takahiro, - Satoshi Nagayasu) + Add Pgadmin administration functions to + adminpack (Dave) - - - pgcrypto now has all planned functionality (Marko Kreen) + These functions provide additional file system access + routines not present in the default PostgreSQL + server. - Update cube (Joshua Reich) - - - - New functions are cube(float[]), - cube(float[], float[]), and - cube_subset(cube, int4[]). + Add index information to pgstattuple (ITAGAKI Takahiro, + Satoshi Nagayasu) - Added async query capability to dblink (Kai Londenberg, - Joe Conway) + pgcrypto now has all planned functionality (Marko Kreen) - Add sslinfo (Victor Wagner) + Update cube (Joshua Reich) - Reports information about the current SSL - certificate. + New functions are cube(float[]), + cube(float[], float[]), and + cube_subset(cube, int4[]). - Remove ora2pg, now at + Added async query capability to dblink (Kai Londenberg, + Joe Conway) - Remove contrib modules that have been migrated to pgfoundry: - adddepend, dbase, dbmirror, fulltextindex, mac, userlock + Add sslinfo (Victor Wagner) - - - Remove abandoned modules: mSQL-interface, ips + Reports information about the current SSL + certificate. - Add hstore module (Oleg, Teodor) + Add hstore module (Oleg, Teodor) - Add isn module, replacing isbn_issn (Jeremy Kronuz) + Add isn module, replacing isbn_issn (Jeremy Kronuz) - This now supports EAN13, UPC, - ISBN (books), ISMN (music), and - ISSN (serials). + This now supports EAN13, UPC, + ISBN (books), ISMN (music), and + ISSN (serials). - New operators for array-subset comparisons (@>, - <@, &&) (Tom) - - - - - - In xml, rename xml_valid() to - xml_is_well_formed() (Tom) - - - - xml_valid() will remain for backward compability, - but its behavior will change to do schema checks in future - releases. + New operators for array-subset comparisons (@>, + <@, &&) (Tom) -- 2.39.5