From 979f8f0431ab2a727a45a4568f25d852aa0b94aa Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Wed, 22 Aug 2012 17:37:02 -0400 Subject: [PATCH] Make an editorial pass over the 9.2 release notes. A very large number of small improvements ... --- doc/src/sgml/datatype.sgml | 4 +- doc/src/sgml/func.sgml | 2 +- doc/src/sgml/release-9.2.sgml | 1256 ++++++++++++++++----------------- 3 files changed, 598 insertions(+), 664 deletions(-) diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml index e64e4ea64a..7f6e661adb 100644 --- a/doc/src/sgml/datatype.sgml +++ b/doc/src/sgml/datatype.sgml @@ -4196,7 +4196,9 @@ SET xmloption TO { DOCUMENT | CONTENT }; - The json data type can be used to store JSON data. Such + The json data type can be used to store JSON (JavaScript + Object Notation) data, as specified in RFC 4627. Such data can also be stored as text, but the json data type has the advantage of checking that each stored value is a valid JSON value. There are also related support diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index d5ad4718b1..5caf10711a 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -9620,7 +9620,7 @@ table2-mapping JSON Functions - + JSON Functions and operators diff --git a/doc/src/sgml/release-9.2.sgml b/doc/src/sgml/release-9.2.sgml index b62c9e7cdc..360b115560 100644 --- a/doc/src/sgml/release-9.2.sgml +++ b/doc/src/sgml/release-9.2.sgml @@ -45,92 +45,95 @@ - Functions + System Catalogs - Ensure that xpath() - escapes special characters in string values (Florian Pflug) + Remove the spclocation field from pg_tablespace + (Magnus Hagander) - Without this it is possible to output illegal - XML. + This field was duplicative of the symbolic links that actually define + tablespace locations, and thus risked errors of omission when moving + a tablespace. This change allows tablespace directories to be moved + while the server is down, by manually adjusting the symbolic links. + To replace this field, we have added pg_tablespace_location() + to allow querying of the symbolic links. - Remove hstore's => - operator (Robert Haas) + Move tsvector most-common-element statistics to new + pg_stats columns + (Alexander Korotkov) - Users should now use hstore(text, text). Since - PostgreSQL 9.0, a warning message is - emitted when an operator named => is created because - the SQL standard reserves that token for - another use. + Consult most_common_elems + and most_common_elem_freqs for the data formerly + available in most_common_vals + and most_common_freqs for a tsvector column. + + + + + + Functions + + + - Have pg_relation_size() - and friends return NULL if the object does not exist (Phil Sorber) + Remove hstore's => + operator (Robert Haas) - This prevents queries that call these functions from returning - errors during concurrent drop operations. + Users should now use hstore(text, text). Since + PostgreSQL 9.0, a warning message has been + emitted when an operator named => is created because + the SQL standard reserves that token for + another use. - Remove the spclocation - field from pg_tablespace (Magnus Hagander) + Ensure that xpath() + escapes special characters in string values (Florian Pflug) - This field was duplicative of the symbolic links already - present in the data directory. This allows tablespace - directories to be moved while the server is down. Also add - pg_tablespace_location() to allow querying of the - symbolic links. + Without this it is possible to output invalid XML. - Move tsvector most-common-element statistics to new - pg_stats columns - (Alexander Korotkov) + Make pg_relation_size() + and friends return NULL if the object does not exist (Phil Sorber) - Consult most_common_elems - and most_common_elem_freqs for the data formerly - available in most_common_vals - and most_common_freqs. + This prevents queries that call these functions from returning + errors immediately after a concurrent DROP. - - - - Temporal Functions - - - - Have EXTRACT of a non-timezone-aware value measure the epoch from local midnight, not UTC midnight (Tom Lane) @@ -150,9 +153,8 @@ - Previously, SELECT '04:00:00 - yesterday'::timestamp returned yesterday's date at - midnight. + Previously, SELECT '04:00:00 yesterday'::timestamp + returned yesterday's date at midnight. @@ -170,8 +172,7 @@ - - + @@ -195,7 +196,7 @@ - No longer auto-lowercase procedural language names (Robert Haas) + No longer forcibly lowercase procedural language names (Robert Haas) @@ -207,6 +208,18 @@ + + + Change system-generated names of foreign key enforcement triggers + (Tom Lane) + + + + This change ensures that the triggers fire in the correct order in + some corner cases involving self-referential foreign key constraints. + + + @@ -225,9 +238,11 @@ - Previously, such usage was only properly expanded when isolated by - whitespace, e.g. 'FOO'BAR was output as FOO - BAR. + Previously, such references were treated oddly when not separated by + whitespace from adjacent text. For example 'FOO'BAR was + output as FOO BAR (unexpected insertion of a space) and + FOO'BAR'BAZ was output unchanged (not removing the quotes + as most would expect). @@ -241,40 +256,27 @@ - Users must supply double-quotes in the command arguments to - accomplish double-quoting behavior. - - - - - - Disable prompting for the user name in dropdb unless - - No longer have createuser prompt - for settings (Peter Eisentraut) + createuser + no longer prompts for option settings by default (Peter Eisentraut) - Previously createuser prompted regarding various - user settings. + Use - Remove initdb - support for the crypt authentication method (Peter Eisentraut) - - - - This was removed from the backend years ago. + Disable prompting for the user name in dropuser unless + @@ -286,50 +288,51 @@ Server Settings + - Remove the postgresql.conf setting - silent_mode (Heikki Linnakangas) + Add server parameters for specifying the locations of server-side + SSL files (Peter Eisentraut) - This behavior could already be accomplished with pg_ctl - -l postmaster.log. + This allows changing the names and locations of the files that were + previously hard-coded as server.crt, + server.key, root.crt, and + root.crl in the data directory. + The server will no longer examine root.crt or + root.crl by default; to load these files, the + associated parameters must be set to non-default values. - Remove GUC wal_sender_delay, - as it is no longer needed (Tom Lane) + Remove the silent_mode parameter (Heikki Linnakangas) - - - Remove the custom_variable_classes - postgresql.conf parameter (Tom Lane) + Similar behavior can be obtained with pg_ctl start + -l postmaster.log. + + - The checking provided by this setting was dubious. Now any - setting can be prefixed by any class name. + Remove the wal_sender_delay parameter, + as it is no longer needed (Tom Lane) - Add GUC parameters for - specifying the locations of server-side SSL - files (Peter Eisentraut) + Remove the custom_variable_classes parameter (Tom Lane) - This allows changing the location of the files that were previously - hard-coded to server.crt, server.key, - root.crt, root.crl. CA - (crt) and CRL files no longer have default - names, and if specified, these files must exist. + The checking provided by this setting was dubious. Now any + setting can be prefixed by any class name. @@ -344,26 +347,28 @@ Rename pg_stat_activity.procpid - to pid, to match other system tables (Magnus Hagander) + linkend="monitoring-stats-views-table">pg_stat_activity.procpid + to pid, to match other system tables (Magnus Hagander) - Create a separate pg_stat_activity column to - report state information, e.g. idle (Scott Mead, Magnus Hagander) + Create a separate pg_stat_activity column to + report process state (Scott Mead, Magnus Hagander) - The last query values are preserved, allowing for enhanced analysis. + The previous query and query_start + values now remain available for an idle session, allowing enhanced + analysis. - Rename pg_stat_activity.current_query to - query because it is not cleared when the query + Rename pg_stat_activity.current_query to + query because it is not cleared when the query completes (Magnus Hagander) @@ -371,22 +376,23 @@ Change all SQL-level statistics timing values - to float8-stored milliseconds (Tom Lane) + to be float8 columns measured in milliseconds (Tom Lane) - The columns affected are: - pg_stat_user_functions.total_time, - pg_stat_user_functions.self_time, - pg_stat_xact_user_functions.total_time, - pg_stat_xact_user_functions.self_time. + The columns affected are + pg_stat_user_functions.total_time, + pg_stat_user_functions.self_time, + pg_stat_xact_user_functions.total_time, + and + pg_stat_xact_user_functions.self_time. Change pg_stat_statements' - total_time column to be measured in milliseconds + total_time column to be measured in milliseconds (Tom Lane) @@ -420,83 +426,95 @@ - This is often called "index-only scans", a feature which now enables - the use of indexes with additional columns, or "covering indexes". - This is possible for heap pages with exclusively all-visible - tuples, as reported by the visibility map. The visibility map was - made crash-safe as a necessary part of implementing this feature. + This feature is often called index-only scans. + Heap access can be skipped for heap pages containing only tuples that + are visible to all sessions, as reported by the visibility map; so + the benefit applies mainly to mostly-static data. The visibility map + was made crash-safe as a necessary part of implementing this feature. - Allow frequently uncontended locks to be recorded using a new - fast-path lock mechanism (Robert Haas) + Add the SP-GiST (Space-Partitioned + GiST) index access method (Teodor Sigaev, Oleg Bartunov, Tom + Lane) + + + + SP-GiST is comparable to GiST in flexibility, but supports + unbalanced partitioned search structures rather than balanced + trees. For suitable problems, SP-GiST can be faster than GiST in both + index build time and search time. - Move the frequently accessed members of the PGPROC - shared memory array to a separate section, for performance (Pavan - Deolasee, Heikki Linnakangas, Robert Haas) + Allow group commit to work effectively under heavy load (Peter + Geoghegan, Simon Riggs, Heikki Linnakangas) + + + + Previously, batching of commits became ineffective as the write + workload increased, because of internal lock contention. - Reduce overhead of creating virtual transaction id locks (Robert - Haas) + Allow uncontended locks to be managed using a new + fast-path lock mechanism (Robert Haas) - Reduce overhead for shared invalidation cache messages (Robert + Reduce overhead of creating virtual transaction id locks (Robert Haas) - Improve COPY performance by adding tuples to - the heap in batches (Heikki Linnakangas) + Reduce the overhead of serializable isolation level locks (Dan + Ports) - Improve GiST geometric type index performance by producing better - trees with less memory allocation overhead (Alexander Korotkov) + Improve PowerPC and Itanium spinlock performance (Manabu Ori, + Robert Haas, Tom Lane) - Allow hint bits to be set sooner for temporary and unlogged tables - (Robert Haas) + Reduce overhead for shared invalidation cache messages (Robert + Haas) - Allow sorting to be performed by inlined and faster, - non-SQL-callable comparison functions (Peter - Geoghegan, Robert Haas, Tom Lane) + Move the frequently accessed members of the PGPROC + shared memory array to a separate array (Pavan + Deolasee, Heikki Linnakangas, Robert Haas) - Add the SP-GiST (Space-Partitioned - GiST) index access method (Teodor Sigaev, Oleg Bartunov, Tom - Lane) + Improve COPY performance by adding tuples to + the heap in batches (Heikki Linnakangas) + + - SP-GiST is comparable to GiST in flexibility, but supports - non-balanced partitioned search structures rather than balanced - trees. CLARIFY? + Improve GiST index performance for geometric data types by producing + better trees with less memory allocation overhead (Alexander Korotkov) @@ -509,56 +527,56 @@ - Take fewer MVCC snapshots, for performance + Allow hint bits to be set sooner for temporary and unlogged tables (Robert Haas) - Have the number of clog buffers scale based on shared_buffers - (Robert Haas, Simon Riggs, Tom Lane) + Allow sorting to be performed by inlined, + non-SQL-callable comparison functions (Peter + Geoghegan, Robert Haas, Tom Lane) - Reduce the overhead of serializable isolation level locks (Dan - Ports) + Take fewer MVCC snapshots + (Robert Haas) - Allow group commit to work effectively under heavy load (Peter - Geoghegan, Simon Riggs, Heikki Linnakangas) + Make the number of CLOG buffers scale based on shared_buffers + (Robert Haas, Simon Riggs, Tom Lane) + + - Previously, batching of commits became ineffective as the write - workload increased because of internal lock contention. + Improve performance of buffer pool scans that occur when tables or + databases are dropped (Jeff Janes, Simon Riggs) - Improve PowerPC and Itanium spinlock performance (Manabu Ori, - Robert Haas, Tom Lane) + Improve performance of checkpointer's fsync-request queue + when many tables are being dropped or truncated (Tom Lane) - Improve performance of buffer pool scans that occur when tables or - databases are dropped (Jeff Janes, Simon Riggs) + Pass the safe number of file descriptors to child processes on Windows + (Heikki Linnakangas) - - - Improve performance of checkpoint process's fsync-request queue - when many tables are being dropped or truncated (Tom Lane) + This allows Windows sessions to use more open file descriptors. @@ -573,20 +591,21 @@ - Create a dedicated worker process to perform checkpoints (Simon + Create a dedicated background process to perform checkpoints (Simon Riggs) - Formerly the background writer did dirty page writing and - checkpointing. + Formerly the background writer did both dirty-page writing and + checkpointing. Separating this into two processes allows each goal + to be accomplished more predictably. - Improve asynchronous commit behavior by waking up sooner (Simon - Riggs) + Improve asynchronous commit behavior by waking the walwriter sooner + (Simon Riggs) @@ -600,9 +619,8 @@ - Allow the bgwriter, walwriter, checkpointer, statistics collector, log - collector, and archiver background processes to sleep more + Allow the bgwriter, walwriter, checkpointer, statistics collector, + log collector, and archiver background processes to sleep more efficiently during periods of inactivity (Peter Geoghegan, Tom Lane) @@ -613,17 +631,6 @@ - - - Pass the safe number of file descriptors to Windows processes - (Heikki Linnakangas) - - - - This allows Windows sessions to cache more open file descriptors. - - - @@ -635,57 +642,72 @@ - Improve the ability of the planner to choose parameterized plans + Allow the planner to generate custom plans for specific parameter + values even when using prepared statements (Tom Lane) - A prepared statement is now parsed, analyzed, and rewritten, - but not necessarily planned. When the prepared plan is executed - with parameters, the planner might replan it for every constant, - or it might execute a generic plan if its cost is close to that - of a constant-specific plan. CLARIFY + In the past, a prepared statement always had a single + generic plan that was used for all parameter values, which + was frequently much inferior to the plans used for non-prepared + statements containing explicit constant values. Now, the planner + attempts to generate custom plans for specific parameter values. + A generic plan will only be used after custom plans have repeatedly + proven to provide no benefit. This change should eliminate the + performance penalties formerly seen from use of prepared statements + (including non-dynamic statements in PL/pgSQL). - Allow the optimizer to use CHECK constraints defined - on tables referenced in subqueries in planning decisions (Tom Lane) + Improve the planner's ability to use nested loops with inner + index scans (Tom Lane) - This is only enabled when constraint_exclusion - = on. + The new parameterized path mechanism allows inner + indexscans to use values from relations that are more than one join + level up from the scan. This can greatly improve performance in + situations where semantic restrictions (such as outer joins) limit + the allowed join orderings. - Allow indexedcol op ANY(ARRAY[...]) conditions to be - used in plain indexscans (Tom Lane) + Improve the planning API for foreign data wrappers + (Etsuro Fujita, Shigeru Hanada, Tom Lane) - - - Improve heuristics for determining the type of unknown values by - considering other data types involved in the operation (Tom Lane) + Wrappers can now provide multiple access paths for their + tables, allowing more flexibility in join planning. - Allow better matching of index columns with non-default operator - qualifications (Tom Lane) + Recognize self-contradictory restriction clauses for non-table + relations (Tom Lane) + + + + This is only enabled when constraint_exclusion + is on. - Improve the planner's ability to use nested loops with inner - index scans (Tom Lane) + Allow indexed_col op ANY(ARRAY[...]) conditions to be + used in plain indexscans and index-only scans (Tom Lane) + + + + Formerly such conditions could only be used in bitmap index scans. @@ -703,55 +725,55 @@ - - - - Optimizer Statistics - - - Improve the ability to use statistics on columns referenced in - subqueries (Tom Lane) + Fix planner to handle indexes with duplicated columns more reliably + (Tom Lane) - Improve optimizer estimation for subqueries using - DISTINCT (Tom Lane) + Collect and use element-frequency statistics for arrays (Alexander + Korotkov, Tom Lane) + + + + This change improves selectivity estimation for the array + <@, &&, and + @> (containment and overlaps) array operators. - Collect and use element-frequency statistics for arrays (Alexander - Korotkov, Tom Lane) + Allow statistics to be collected for foreign tables + (Etsuro Fujita) + + - This patch improves selectivity estimation for the array <@, - &&, and @> (containment and overlaps) array operators. + Improve cost estimates for use of partial indexes (Tom Lane) - Improve costing of partial indexes (Tom Lane) + Improve the planner's ability to use statistics for columns + referenced in subqueries (Tom Lane) - Allow analyze statistics to be collected for foreign tables - (Etsuro Fujita) + Improve statistical estimates for subqueries using + DISTINCT (Tom Lane) - - @@ -761,41 +783,40 @@ - Do not assume roles and samerole specified in samerole specified in pg_hba.conf - include superusers (Andrew Dunstan) + as automatically including superusers (Andrew Dunstan) - This makes it easier to use reject lines with group roles. + This makes it easier to use reject lines with group roles. - Make superuser imply replication privilege (Noah Misch) + Adjust pg_hba.conf processing to handle token + parsing more consistently (Brendan Jurd, Álvaro Herrera) + + - This avoids the need to explicitly assign such privileges. + Disallow empty pg_hba.conf files (Tom Lane) - - - Adjust pg_hba.conf processing to more - consistently handle token parsing (Brendan Jurd, Álvaro - Herrera) + This was done to more quickly detect misconfiguration. - Disallow empty pg_hba.conf files (Tom Lane) + Make superuser privilege imply replication privilege (Noah Misch) - This was done to more quickly report misconfiguration. + This avoids the need to explicitly assign such privileges. @@ -810,7 +831,7 @@ - Attempt to log the current query string before a backend crash + Attempt to log the current query string during a backend crash (Marti Raudsepp) @@ -829,7 +850,7 @@ - Have WAL replay report failures sooner + Make WAL replay report failures sooner (Fujii Masao) @@ -843,7 +864,7 @@ Add pg_xlog_location_diff() - to simplify xlog comparisons (Euler Taveira de Oliveira) + to simplify WAL location comparisons (Euler Taveira de Oliveira) @@ -861,14 +882,14 @@ This allows different instances to use the eventlog with different identifiers, by setting the event_source - GUC, which is similar to how syslog_ident works. - Change "unexpected EOF" message to DEBUG1 level, + Change unexpected EOF messages to DEBUG1 level, except when there is an open transaction (Magnus Hagander) @@ -885,7 +906,7 @@ Track temporary file sizes and file counts in the pg_stat_database + linkend="pg-stat-database-view">pg_stat_database system view (Tomas Vondra) @@ -899,7 +920,7 @@ - Add a GUC setting track_io_timing to track I/O timings (Ants Aasma, Robert Haas) @@ -924,36 +945,35 @@ - Allow search_path (Tom Lane) + + + + + + Allow superusers to set deadlock_timeout - to be set per-session by superusers, not just per-cluster (Noah - Misch) + per-session, not just per-cluster (Noah Misch) This allows deadlock_timeout to be reduced for - transactions that are likely to be involved in a deadlock, - thus detecting it more quickly. It can also be used to reduce - the chances of a session being chosen for cancellation due to - a deadlock. + transactions that are likely to be involved in a deadlock, thus + detecting the failure more quickly. Alternatively, increasing the + value can be used to reduce the chances of a session being chosen for + cancellation due to a deadlock. - Add GUC parameter temp_file_limit to constrain temporary file space usage per session (Mark Kirkwood) - - - Add postgresql.conf category "Replication / - Sending Servers" (Fujii Masao) - - - Allow a superuser to SET an extension's @@ -974,15 +994,24 @@ - This allows pg_ctl to better handle - configuration-only directory installations. + This allows pg_ctl to better handle cases where + PGDATA or - Force the locale to be recorded more specifically, rather than - using the empty string to mean the default (Tom Lane) + Replace an empty locale name with the implied value in + CREATE DATABASE + (Tom Lane) + + + + This prevents cases where + pg_database.datcollate or + datctype could be interpreted differently after a + server restart. @@ -1004,13 +1033,13 @@ Allow a reload of postgresql.conf to be - processed by all backends even if there are some settings that - are invalid for that session (Alexey Klyukin) + processed by all sessions, even if there are some settings that + are invalid for particular sessions (Alexey Klyukin) - Previously, such not-valid-for-session errors would cause all - setting changes to be ignored by that backend. + Previously, such not-valid-within-session values would cause all + setting changes to be ignored by that session. @@ -1028,8 +1057,8 @@ - Check the server time zone during initdb, and set - postgresql.conf values + Identify the server time zone during initdb, and set + postgresql.conf entries timezone and log_timezone accordingly (Tom Lane) @@ -1042,9 +1071,9 @@ - Allow Windows to report - postgresql.conf line numbers in pg_settings + Fix pg_settings to + report postgresql.conf line numbers on Windows (Tom Lane) @@ -1060,21 +1089,18 @@ Replication and Recovery - - Streaming Replication - - + - Allow streaming replications slaves to serve other slaves (Fujii - Masao) + Allow streaming replication slaves to forward data to other slaves + (cascading + replication) (Fujii Masao) Previously, only the master server could supply streaming - replication log files to standby servers. Slaves can also now - do continuous archiving. (?) + replication log files to standby servers. @@ -1082,12 +1108,13 @@ Add new synchronous_commit - replication mode remote_write (Fujii Masao, Simon Riggs) + mode remote_write (Fujii Masao, Simon Riggs) - This waits for the remote server to acknowledge that it has received - the data, but does not wait for it to be written to the remote disk. + This mode waits for the standby server to write transaction data to + its own operating system, but does not wait for the data to be + flushed to the standby's disk. @@ -1095,37 +1122,19 @@ Add a pg_receivexlog - tool to archive xlog file changes as they are written, rather - than waiting for completed xlog files (Magnus Hagander) - - - - - - Send keepalive messages to standby servers (Simon Riggs) - - - - This allows administrators to accurately calculate streaming - replication transfer and application latency. + tool to archive WAL file changes as they are written, rather + than waiting for completed WAL files (Magnus Hagander) - - - - <link linkend="app-pgbasebackup"><application>pg_basebackup</></link> - - - - Allow streaming of xlog files while pg_basebackup + Allow streaming of WAL files while pg_basebackup is performing a backup (Magnus Hagander) - This allows passing of xlog files to the standby before they are + This allows passing of WAL files to the standby before they are discarded on the primary. @@ -1137,17 +1146,7 @@ - - - Exclude the postmaster.opts file from pg_basebackup - backups (Magnus Hagander) - - - - - - - + @@ -1158,30 +1157,45 @@ - Improve columns labels produced by subqueries (Marti Raudsepp) + Cancel queries if clients get disconnected (Florian Pflug) + + - Previously, the generic label ?column? was used. + Retain column names at runtime for row expressions + (Andrew Dunstan, Tom Lane) - - - Preserve column names in row expressions (Andrew Dunstan, Tom Lane) + This change allows better results when a row value is converted to + hstore or json type: the fields of the resulting + value will now have the expected names. - Cancel queries if clients get disconnected (Florian Pflug) + Improve column labels used for sub-SELECT results + (Marti Raudsepp) + + + + Previously, the generic label ?column? was used. - Silently ignore nonexistent schemas specified in search_path (Tom Lane) + Improve heuristics for determining the types of unknown values + (Tom Lane) + + + + The longstanding rule that an unknown constant might have the + same type as the value on the other side of the operator using it + is now applied when considering polymorphic operators, not only + for simple operator matches. @@ -1198,7 +1212,7 @@ When a row fails a CHECK or NOT NULL - constraint, show the row's contents in errdetail (Jan + constraint, show the row's contents as error detail (Jan Kundrát) @@ -1224,18 +1238,18 @@ - This eliminates "cache lookup failed" errors in many scenarios. - Also, it is no longer possible to add relations to a schema which - is being concurrently dropped, a scenario that formerly led to - inconsistent system catalog contents. + This change adds locking that should eliminate cache lookup + failed errors in many scenarios. Also, it is no longer possible + to add relations to a schema that is being concurrently dropped, a + scenario that formerly led to inconsistent system catalog contents. Add CONCURRENTLY option to DROP INDEX - CONCURRENTLY (Simon Riggs) + linkend="SQL-DROPINDEX">DROP INDEX + (Simon Riggs) @@ -1243,6 +1257,18 @@ + + + Allow foreign data wrappers to have per-column options (Shigeru Hanada) + + + + + + Improve pretty printing of view definitions (Andrew Dunstan) + + + @@ -1258,18 +1284,18 @@ - A NOT VALID CHECK constraint can be added - to a table without the table's data being verified against the - constraint. New rows are verified. + Adding a NOT VALID constraint does not cause the table to + be scanned to verify that existing rows meet the constraint. + Subsequently, newly added or updated rows are checked. + Such constraints are ignored by the planner when considering + constraint_exclusion, since it is not certain that all + rows meet the constraint. - Such constraints are ignored by the planner when considering - constraint_exclusion. ALTER TABLE / - VALIDATE allows NOT VALID CHECK - check constraints to be fully validated and considered for - constraint_exclusion, including re-planning of - cached plans. + The new ALTER TABLE VALIDATE command allows NOT + VALID constraints to be checked for existing rows, after which + they are converted into ordinary constraints. @@ -1302,19 +1328,19 @@ - Reduce need to rebuild tables and indexes for various ALTER TABLE operations (Noah Misch) - Increasing the length constraint on a varchar or - varbit column, or removing it altogether, no longer - requires a table rewrite. Increasing the allowable precision of - a numeric constraint, or changing a column from constrained - numeric to unconstrained numeric, no longer requires a - table rewrite. Table rewrites are also avoided in similar cases - involving the interval, timestamp, and + Increasing the length limit for a varchar or varbit + column, or removing it altogether, no longer requires a table + rewrite. Similarly, increasing the allowable precision of a + numeric column, or changing a column from constrained + numeric to unconstrained numeric, no longer + requires a table rewrite. Table rewrites are also avoided in similar + cases involving the interval, timestamp, and timestamptz types. @@ -1329,7 +1355,7 @@ - Add IF EXISTS clause to ALTER + Add IF EXISTS clause to some ALTER commands (Pavel Stehule) @@ -1342,16 +1368,16 @@ Add ALTER - FOREIGN DATA WRAPPER / RENAME + FOREIGN DATA WRAPPER ... RENAME and ALTER - SERVER / RENAME (Peter Eisentraut) + SERVER ... RENAME (Peter Eisentraut) Add ALTER - DOMAIN / RENAME (Peter Eisentraut) + DOMAIN ... RENAME (Peter Eisentraut) @@ -1362,12 +1388,12 @@ - Throw an error for ALTER DOMAIN / DROP + Throw an error for ALTER DOMAIN ... DROP CONSTRAINT on a nonexistent constraint (Peter Eisentraut) - An IF EXISTS option has been added to enable the + An IF EXISTS option has been added to provide the previous behavior. @@ -1377,43 +1403,37 @@ - <link linkend="SQL-CREATETABLE"><command>CREATE/ALTER - TABLE</></link> + <link linkend="SQL-CREATETABLE"><command>CREATE TABLE</></link> - Fix CREATE TABLE ... AS EXECUTE - to handle WITH NO DATA and column name specification - (Tom Lane) + Allow CREATE TABLE (LIKE ...) from foreign + tables, views, and composite types (Peter Eisentraut) - - - Support CREATE TABLE (LIKE ...) from foreign - tables and views (Peter Eisentraut) + For example, this allows a table to be created whose schema matches a + view. - Allow CREATE TABLE (LIKE ...) from composite - type (Peter Eisentraut) - - - - This also allows a table to be created whose schema matches a view. + Fix CREATE TABLE (LIKE ...) to avoid index name + conflicts when copying index comments (Tom Lane) - Fix CREATE TABLE (LIKE ...) to avoid index name - conflicts when copying index comments (Tom Lane) + Fix CREATE TABLE ... AS EXECUTE + to handle WITH NO DATA and column name specifications + (Tom Lane) + @@ -1431,9 +1451,11 @@ - This prevents security_barrier views from being moved - into other scopes, preventing possible leakage of view-prohibited - data. Such views might perform more poorly. + This option prevents optimizations that might allow view-protected + data to be exposed to users, for example pushing a clause involving + an insecure function into the WHERE clause of the view. + Such views can be expected to perform more poorly than ordinary + views. @@ -1441,7 +1463,7 @@ Add a new LEAKPROOF function - specification to mark functions that can be safely pushed down + attribute to mark functions that can safely be pushed down into security_barrier views (KaiGai Kohei) @@ -1466,11 +1488,11 @@ - Because the object is being created by SELECT - INTO or CREATE TABLE AS, it will - have default insert permissions, except if ALTER DEFAULT - PRIVILEGES has removed such permissions, hence the need for - a permission check. + Because the object is being created by SELECT INTO + or CREATE TABLE AS, the creator would ordinarily + have insert permissions; but there are corner cases where this is not + true, such as when ALTER DEFAULT PRIVILEGES has removed + such permissions. @@ -1493,46 +1515,37 @@ This change should greatly reduce the incidence of vacuum getting - "stuck". + stuck. - - - - <link linkend="SQL-EXPLAIN"><command>EXPLAIN</></link> - - - - Make EXPLAIN (BUFFERS) track blocks dirtied - and written (Robert Haas) + Make EXPLAIN + (BUFFERS) count blocks dirtied and written (Robert Haas) - Have EXPLAIN ANALYZE report the number of rows + Make EXPLAIN ANALYZE report the number of rows rejected by filter steps (Marko Tiikkaja) - Allow EXPLAIN to avoid timing overhead when - time values are unwanted (Tomas Vondra) + Allow EXPLAIN ANALYZE to avoid timing overhead when + time values are not wanted (Tomas Vondra) - This is accomplished by setting TIMING to + This is accomplished by setting the new TIMING option to FALSE. - - - + @@ -1543,45 +1556,46 @@ - Add a JSON - data type (Robert Haas) + Add support for range data types + (Jeff Davis, Tom Lane, Alexander Korotkov) - This stores JSON data as text after proper - validation. + A range data type stores a lower and upper bound belonging to its + base data type. It supports operations like contains, overlaps, and + intersection. - Add array_to_json() - and row_to_json() (Andrew Dunstan) + Add a JSON + data type (Robert Haas) - - - Add support for range data types - (Jeff Davis, Tom Lane, Alexander Korotkov) + This type stores JSON (JavaScript Object Notation) + data with proper validation. + + - The range data type records a lower and upper bound, and supports - operations like contains, overlaps, and intersection. + Add array_to_json() + and row_to_json() (Andrew Dunstan) - Add SMALLSERIAL + Add a SMALLSERIAL data type (Mike Pultz) This is like SERIAL, except it stores the sequence in - a two-byte integer column (int2). + a two-byte integer column (int2). @@ -1592,45 +1606,40 @@ - This can be set at domain creation time, or via - ALTER DOMAIN / ADD CONSTRAINT / NOT - VALID. ALTER DOMAIN / VALIDATE - CONSTRAINT fully validates the domain. + This option can be set at domain creation time, or via ALTER + DOMAIN ... ADD CONSTRAINT ... NOT + VALID. ALTER DOMAIN ... VALIDATE + CONSTRAINT fully validates the constraint. Support more locale-specific formatting options for the MONEY data type (Tom Lane) + linkend="datatype-money">money data type (Tom Lane) - Specifically, display the plus/minus sign, currency symbol, - and separators in a locale-specific order. CORRECT? + Specifically, honor all the POSIX options for ordering of the value, + sign, and currency symbol in monetary output. Also, make sure that + the thousands separator is only inserted to the left of the decimal + point, as required by POSIX. - Add bitwise "and", "or", and "not" operators for the macaddr - data type (Brendan Jurd) + Add bitwise and, or, and not + operators for the macaddr data type (Brendan Jurd) - - - - <link linkend="functions-xml"><acronym>XML</></link> - - - - Allow xpath() to return a single-element - XML array when supplied a scalar value (Florian - Pflug) + Allow xpath() to + return a single-element XML array when supplied a + scalar value (Florian Pflug) @@ -1647,9 +1656,7 @@ - - - + @@ -1663,8 +1670,8 @@ Allow non-superusers to use pg_cancel_backend() and pg_terminate_backend()() - on other sessions for the same user + linkend="functions-admin-signal">pg_terminate_backend() + on other sessions belonging to the same user (Magnus Hagander, Josh Kupershmidt, Dan Farina) @@ -1673,24 +1680,6 @@ - - - Allow string_agg() - to process bytea values (Pavel Stehule) - - - - - - Allow regular expressions to use back-references (Tom Lane) - - - - For example, '^(\w+)( \1)+$'. - - - Allow importing and exporting of transaction snapshots (Joachim @@ -1698,10 +1687,13 @@ + This allows multiple transactions to share identical views of the + database state. Snapshots are exported via pg_export_snapshot(), - and imported via SET TRANSACTION SNAPSHOT. - Only snapshots of currently-running transactions can be imported. + linkend="functions-snapshot-synchronization">pg_export_snapshot() + and imported via SET + TRANSACTION SNAPSHOT. Only snapshots from + currently-running transactions can be imported. @@ -1719,27 +1711,17 @@ - Add a NUMERIC variant of pg_size_pretty() - for use with pg_xlog_location_diff() (Fujii Masao) + Add pg_opfamily_is_visible() + (Josh Kupershmidt) - - - - Function and Trigger Creation - - - - Change default names of triggers to fire "action" triggers before - "check" triggers (Tom Lane) - - - - This allows default-named check triggers to check post-action rows. + Add a numeric variant of pg_size_pretty() + for use with pg_xlog_location_diff() (Fujii Masao) @@ -1755,53 +1737,38 @@ - - - - - - Object Information Functions - - - Add pg_opfamily_is_visible() - (Josh Kupershmidt) + Allow string_agg() + to process bytea values (Pavel Stehule) - Allow per-column foreign data wrapper options and - force_not_null (Shigeru Hanada) + Fix regular expressions in which a back-reference occurs within + a larger quantified subexpression (Tom Lane) - This allows SQL specification of per-column - information useful to foreign data sources. Includes - psql option display support. - - - - - - Improve pretty printing of view definitions (Andrew Dunstan) + For example, ^(\w+)( \1)+$. Previous releases did not + check that the back-reference actually matched the first occurrence. - + - - <link linkend="information-schema">Information Schema</link> + + <link linkend="information-schema">Information Schema</link> - Add information_schema views + Add information schema views role_udt_grants, udt_privileges, and user_defined_types (Peter Eisentraut) @@ -1810,60 +1777,60 @@ Add composite-type attributes to the - information_schema.element_types view + information schema element_types view (Peter Eisentraut) - Implement information schema interval_type columns - (Peter Eisentraut) + Implement interval_type columns in the information + schema (Peter Eisentraut) - - - Add collation columns to various information schema views (Peter - Eisentraut) + Formerly these columns read as nulls. + + - Specifically, provide collation columns for views referencing - attributes, columns, domains, and element_types. + Implement collation-related columns in the information schema + attributes, columns, + domains, and element_types + views (Peter Eisentraut) - Add display of sequence USAGE privileges to information - schema (Peter Eisentraut) + Implement the with_hierarchy column in the + information schema table_privileges view (Peter + Eisentraut) - Add a with_hierarchy column to the information schema - privileges display (Peter Eisentraut) + Add display of sequence USAGE privileges to information + schema (Peter Eisentraut) - Have the information schema show default privileges (Peter + Make the information schema show default privileges (Peter Eisentraut) - Previously, default permissions generated NULL fields. - (WAS IT NULL?) + Previously, non-empty default permissions were not represented in the + views. - - @@ -1897,7 +1864,7 @@ - Improve performance and memory consumption of the PL/pgSQL + Improve performance and memory consumption for long chains of ELSIF clauses (Tom Lane) @@ -1935,8 +1902,9 @@ - Specifically, this adds result object functions .colnames, - .coltypes, and .coltypmods. + Specifically, this adds result object functions + .colnames, .coltypes, and + .coltypmods. @@ -1961,7 +1929,7 @@ - To use this, simply name the function arguments and reference + To use this, simply name the function arguments and then reference the argument names in the SQL function body. @@ -1984,7 +1952,7 @@ - This allows individual control of local and + This allows separate control of local and host pg_hba.conf authentication settings. @@ -2010,7 +1978,7 @@ Give command-line tools the ability to specify the name of the - database to connect to, and fall back to template1 + database to connect to, and fall back to template1 if a postgres database connection fails (Robert Haas) @@ -2024,7 +1992,7 @@ - Add psql mode to auto-expand output based on the + Add a display mode to auto-expand output based on the display width (Peter Eisentraut) @@ -2037,13 +2005,12 @@ - Allow inclusion of psql files relative to the - directory of the input file from which it was invoked (Gurjeet - Singh) + Allow inclusion of a script file relative to the directory of the + file from which it was invoked (Gurjeet Singh) - The psql command is \ir. + This is done with a new command \ir. @@ -2062,8 +2029,7 @@ psql already supported minor-version-specific - .psqlrc - files. + .psqlrc files. @@ -2075,59 +2041,59 @@ Specifically, PSQL_HISTORY and - PSQLRC. + PSQLRC determine these file names if set. - Add a psql \setenv command to modify - the environment variables used by child processes (Andrew Dunstan) - - - - This is useful in .psqlrc files. + Add a \setenv command to modify + the environment variables passed to child processes (Andrew Dunstan) - Have psql temporary editor files use a + Make psql's temporary editor files use a .sql extension (Peter Eisentraut) + + + This allows extension-sensitive editors to select the right mode. + - Allows psql to use zero-byte field and record + Allow psql to use zero-byte field and record separators (Peter Eisentraut) - Various shell tools use zero/null-byte separators, e.g. find. + Various shell tools use zero-byte (NUL) separators, e.g. find. - Have psql \timing report times for + Make the \timing option report times for failed queries (Magnus Hagander) - Previously only successful query times were reported. + Previously times were reported only for successful queries. - Unify and tighten psql treatment of \copy - and plain COPY (Noah Misch) + Unify and tighten psql's treatment of \copy + and SQL COPY (Noah Misch) - This makes failure behavior more predictable and honors \set - ON_ERROR_ROLLBACK. + This fix makes failure behavior more predictable and honors + \set ON_ERROR_ROLLBACK. @@ -2141,74 +2107,59 @@ - Make psql \d on a sequence show the + Make \d on a sequence show the table/column name owning it (Magnus Hagander) - Show statistics target for columns in psql - \d+ (Magnus Hagander) + Show statistics target for columns in \d+ (Magnus + Hagander) - Show role password expiration dates in psql - \du (Fabrízio de Royes Mello) + Show role password expiration dates in \du + (Fabrízio de Royes Mello) - - - - - Comments - - - Allow psql to display comments for languages and - casts (Josh Kupershmidt) + Display comments for casts, conversions, domains, and languages + (Josh Kupershmidt) - These are output via \dL and \dC. + These are included in the output of \dC+, + \dc+, \dD+, and \dL respectively. - Allow psql to display comments for conversions - and domains (Josh Kupershmidt) + Display comments for SQL/MED + objects (Josh Kupershmidt) - This is accomplished with the addition of + to - \dc and \dD commands. + These are included in the output of \des+, + \det+, and \dew+ for foreign servers, foreign + tables, and foreign data wrappers respectively. - Allow psql to display the comments on - SQL/MED objects in verbose - mode (Josh Kupershmidt) - - - - - - Change psql's \dd command to display - only objects without their own backslash command (Josh Kupershmidt) + Change \dd to display comments only for object types + without their own backslash command (Josh Kupershmidt) - - @@ -2218,16 +2169,16 @@ - In psql tab completion, - complete SQL keywords based on - COMP_KEYWORD_CASE setting and the perhaps case of - the partially-supplied word (Peter Eisentraut) + In psql tab completion, complete SQL + keywords in either upper or lower case according to the new COMP_KEYWORD_CASE + setting (Peter Eisentraut) - Add psql tab completion support for + Add tab completion support for EXECUTE (Andreas Karlsson) @@ -2249,8 +2200,8 @@ - Change psql tab completion support for - TABLE to also display views (Magnus Hagander) + Change tab completion support for + TABLE to also include views (Magnus Hagander) @@ -2284,22 +2235,19 @@ Valid values are pre-data, data, and post-data. The option can be - given more than once. - Have pg_dumpall dump all roles first, then all configuration settings on roles (Phil Sorber) - This allows a role's configuration settings to rely on other + This allows a role's configuration settings to mention other roles without generating an error. @@ -2370,18 +2318,21 @@ - Add URIs support to libpq connection functions - (Alexander Shulgin) + Allow connection strings to have the format of a URI (Alexander + Shulgin) - The syntax begins with postgres://. + The syntax begins with postgres://. This can allow + applications to avoid implementing their own parser for URIs + representing database connections. - Add a libpq connection + Add a connection option to disable SSL compression (Laurenz Albe) @@ -2395,50 +2346,44 @@ Add a single-row processing - mode to libpq for better handling of large result sets + mode for better handling of large result sets (Kyotaro Horiguchi, Marko Kreen) - Previously, libpq always collected the entire query result in memory - before passing it back to the application. + Previously, libpq always collected the entire query + result in memory before passing it back to the application. - Enhance const qualifiers in the libpq functions - PQconnectdbParams, PQconnectStartParams, and PQpingParams (Lionel - Elie Mamane) + Add const qualifiers to the declarations of the functions + PQconnectdbParams, PQconnectStartParams, + and PQpingParams (Lionel Elie Mamane) - Allow libpq's .pgpass file to process escaped characters + Allow the .pgpass file to include escaped characters in the password field (Robert Haas) - Have library functions use abort() instead of - exit() to terminate the executable (Peter Eisentraut) + Make library functions use abort() instead of + exit() when it is necessary to terminate the process + (Peter Eisentraut) - This does not interfere with the normal exit codes used by the + This choice does not interfere with the normal exit codes used by the program, and generates a signal that can be caught by the caller. - - - Fix a libpq memory leak by freeing client encoding connection - parameters (Peter Eisentraut) - - - @@ -2454,7 +2399,7 @@ - The following platforms are no longer considered supported: dgux, + The following platforms are no longer supported: dgux, nextstep, sunos4, svr4, ultrix4, univel, bsdi. @@ -2500,14 +2445,14 @@ Improve the concurrent transaction regression tests - (isolationtester) (Noah Misch) + (isolationtester) (Noah Misch) Modify thread_test to create its test files in - the current directory, rather than /tmp (Bruce Momjian) + the current directory, rather than /tmp (Bruce Momjian) @@ -2611,9 +2556,8 @@ - Add tcn (triggered change notification) - to generate NOTIFY events on table changes - (Kevin Grittner) + Support force_not_null option in file_fdw (Shigeru Hanada) @@ -2637,18 +2581,6 @@ - - - Have vacuumlo remove large objects - in batches, to reduce locking (Tim Lewis, Tom Lane) - - - - The new - - Change + + + Add a tcn (triggered change notification) + module to generate NOTIFY events on table changes + (Kevin Grittner) + + + @@ -2681,86 +2621,86 @@ - Allow pg_upgrade to upgrade an old cluster that - does not have a postgres database (Bruce Momjian) + Adjust pg_upgrade environment variables (Bruce + Momjian) + + + + Rename data, bin, and port environment + variables to begin with PG, and support + PGPORTOLD/PGPORTNEW, to replace + PGPORT. - Allow pg_upgrade to handle cases where some - old or new databases are missing, as long as they are empty + Overhaul pg_upgrade logging and failure reporting (Bruce Momjian) - - - Allow pg_upgrade to handle configuration-only - directory installations (Bruce Momjian) + Create four append-only log files, and delete them on success. + Add - In pg_upgrade, add - This is useful for configuration-only directory installs. + This reduces the time needed to generate minimal cluster statistics + after an upgrade. - Change pg_upgrade to use port 50432 by default - (Bruce Momjian) - - - - This helps avoid unintended client connections during the upgrade. + Allow pg_upgrade to upgrade an old cluster that + does not have a postgres database (Bruce Momjian) - Overhaul pg_upgrade logging and failure reporting + Allow pg_upgrade to handle cases where some + old or new databases are missing, as long as they are empty (Bruce Momjian) + + - Create four append-only log files, and delete them on success. - Add - Adjust pg_upgrade environment variables (Bruce - Momjian) + In pg_upgrade, add - Rename data, bin, and port environment - variables to begin with PG, and support - PGPORTOLD/PGPORTNEW, to replace - PGPORT. INCOMPATIBILITY? + This is useful for configuration-only directory installs. - Have pg_upgrade create a script to incrementally - generate more accurate optimizer statistics (Bruce Momjian) + Change pg_upgrade to use port 50432 by default + (Bruce Momjian) - This reduces the time needed to generate minimal cluster statistics - after an upgrade. + This helps avoid unintended client connections during the upgrade. @@ -2788,13 +2728,12 @@ Allow pg_stat_statements to aggregate similar - queries via SQL text normalisation (Peter Geoghegan, Tom Lane) + queries via SQL text normalization (Peter Geoghegan, Tom Lane) Users with applications that use non-parameterized SQL will now - be able to monitor query performance without using log file - analysis. + be able to monitor query performance without detailed log analysis. @@ -2828,7 +2767,7 @@ - Specifically, add SECURITY LABELs to databases, + Specifically, add security labels to databases, tablespaces, and roles. @@ -2872,12 +2811,12 @@ - Add a rule to optionally build docs with the stylesheet from the - website (Magnus Hagander) + Add a rule to optionally build HTML documentation using the + stylesheet from the website (Magnus Hagander) - Use make STYLE=website draft. + Use gmake STYLE=website draft. @@ -2895,13 +2834,6 @@ - - - Improve the foreign data wrapper API and - documentation (Hanada Shigeru) - - - Document the actual string returned by the client for MD5 -- 2.40.0