2013-10-10 This release contains a variety of fixes from 9.2.4. For information about new features in the 9.2 major release, see . A dump/restore is not required for those running 9.2.X. Also, if you are upgrading from a version earlier than 9.2.2, see the release notes for 9.2.2. Prevent downcasing of non-ASCII non-double-quoted identifiers in multi-byte encodings (Andrew Dunstan) The previous behavior was wrong and confusing. Fix memory leak when creating range indexes (Heikki Linnakangas) Fix checkpoint memory leak in background writer when wal_level = hot_standby (Naoya Anzai) Fix memory leak caused by lo_open() failure (Heikki Linnakangas) Fix memory overcommit bug when work_mem is using more than 24GB of memory (Stephen Frost) Serializable snapshot fixes (Kevin Grittner, Heikki Linnakangas) Fix libpq SSL deadlock bug (Stephen Frost) Fix possible SSL network stack corruption in threaded libpq applications (Nick Phillips, Stephen Frost) Improve estimate of planner cost when choosing between generic and custom plans (Tom Lane) This change will favor generic plans when planning cost is high. Properly compute row estimates for boolean columns containing many NULL values (Andrew Gierth) Previously tests like col IS NOT TRUE and col IS NOT FALSE did not properly factor in NULL values when estimating plan costs. Fix UNION ALL and inheritance queries to properly recheck parameterized paths (Tom Lane) This fixes cases where suboptimal query plans could potentially be chosen. Prevent pushing down WHERE clauses into unsafe UNION/INTERSECT subqueries (Tom Lane) Previously such push downs could generate errors. Fix rare GROUP BY query error caused by improperly processed data type modifiers (Tom Lane) Fix pg_dump of foreign tables with dropped columns (Andrew Dunstan) Previously such cases could cause a pg_upgrade error. Reorder pg_dump processing of extension-related rules and event triggers (Joe Conway) Force dumping of extension tables if specified by pg_dump -t or -n (Joe Conway) Allow view dump code to better handle dropped columns on base tables (Tom Lane) Fix pg_restore -l with the directory archive to display the correct format name (Fujii Masao) Properly record index comments created using UNIQUE and PRIMARY KEY syntax (Andres Freund) This fixes a parallel pg_restore failure. Cause pg_basebackup -x with an empty xlog directory to throw an error rather than crashing (Magnus Hagander, Haruka Takatsuka) Properly guarantee transmission of WAL files before clean switchover (Fujii Masao) Previously, the streaming replication connection might close before all WAL files had been replayed on the standby. Fix WAL segment timeline handling during recovery (Mitsumasa Kondo, Heikki Linnakangas) WAL file recycling during standby recovery could lead to premature recovery completion, resulting in data loss. Fix REINDEX TABLE and REINDEX DATABASE to properly revalidate constraints and mark invalidated indexes as valid (Noah Misch) REINDEX INDEX has always worked properly. Avoid deadlocks during insertion into SP-GiST indexes (Teodor Sigaev) Fix possible deadlock during concurrent CREATE INDEX CONCURRENTLY operations (Tom Lane) Fix GiST index lookup crash (Tom Lane) Fix regexp_matches() handling of zero-length matches (Jeevan Chalke) Previously, zero-length matches like '^' could return too many matches. Fix crash for overly-complex regular expressions (Heikki Linnakangas) Fix regular expression match failures for back references combined with non-greedy quantifiers (Jeevan Chalke) Prevent CREATE FUNCTION from checking SET variables unless function body checking is enabled (Tom Lane) Allow ALTER DEFAULT PRIVILEGES to operate on schemas without requiring CREATE permission (Tom Lane) Loosen restriction on keywords used in queries (Tom Lane) Specifically, lessen keyword restrictions for role names, language names, EXPLAIN and COPY options, and SET values. This allows COPY ... (FORMAT BINARY) previously BINARY required single-quotes. Print proper line number during COPY failure (Heikki Linnakangas) Fix pgp_pub_decrypt() so it works for secret keys with passwords (Marko Kreen) Have pg_upgrade use >pg_dump --quote-all-identifiers to avoid problems with keyword changes between releases (Tom Lane) Remove rare inaccurate warning during vacuum of index-less tables (Heikki Linnakangas) Improve analyze statistics generation after a cancelled file truncate request (Kevin Grittner) Avoid possible failure when performing transaction control commands (e.g ROLLBACK) in prepared queries (Tom Lane) Allow various spellings of infinity on all platforms (Tom Lane) Supported infinity values are "inf", "+inf", "-inf", "infinity", "+infinity", and "-infinity". Avoid unnecessary reporting when track_activities is off (Tom Lane) Expand ability to compare rows to records and arrays (Rafal Rzepecki, Tom Lane) Prevent crash when psql's PSQLRC variable contains a tilde (Bruce Momjian) Add spinlock support for ARM64 (Mark Salter) Update time zone data files to tzdata release 2013d for DST law changes in DST law changes in Israel, Morocco, Palestine, Paraguay. Also, historical zone data corrections for Macquarie Island (Tom Lane) 2013-04-04 This release contains a variety of fixes from 9.2.3. For information about new features in the 9.2 major release, see . A dump/restore is not required for those running 9.2.X. However, this release corrects several errors in management of GiST indexes. After installing this update, it is advisable to REINDEX any GiST indexes that meet one or more of the conditions described below. Also, if you are upgrading from a version earlier than 9.2.2, see the release notes for 9.2.2. Fix insecure parsing of server command-line switches (Mitsumasa Kondo, Kyotaro Horiguchi) A connection request containing a database name that begins with - could be crafted to damage or destroy files within the server's data directory, even if the request is eventually rejected. (CVE-2013-1899) Reset OpenSSL randomness state in each postmaster child process (Marko Kreen) This avoids a scenario wherein random numbers generated by contrib/pgcrypto functions might be relatively easy for another database user to guess. The risk is only significant when the postmaster is configured with ssl = on but most connections don't use SSL encryption. (CVE-2013-1900) Make REPLICATION privilege checks test current user not authenticated user (Noah Misch) An unprivileged database user could exploit this mistake to call pg_start_backup() or pg_stop_backup(), thus possibly interfering with creation of routine backups. (CVE-2013-1901) Fix GiST indexes to not use fuzzy geometric comparisons when it's not appropriate to do so (Alexander Korotkov) The core geometric types perform comparisons using fuzzy equality, but gist_box_same must do exact comparisons, else GiST indexes using it might become inconsistent. After installing this update, users should REINDEX any GiST indexes on box, polygon, circle, or point columns, since all of these use gist_box_same. Fix erroneous range-union and penalty logic in GiST indexes that use contrib/btree_gist for variable-width data types, that is text, bytea, bit, and numeric columns (Tom Lane) These errors could result in inconsistent indexes in which some keys that are present would not be found by searches, and also in useless index bloat. Users are advised to REINDEX such indexes after installing this update. Fix bugs in GiST page splitting code for multi-column indexes (Tom Lane) These errors could result in inconsistent indexes in which some keys that are present would not be found by searches, and also in indexes that are unnecessarily inefficient to search. Users are advised to REINDEX multi-column GiST indexes after installing this update. Fix gist_point_consistent to handle fuzziness consistently (Alexander Korotkov) Index scans on GiST indexes on point columns would sometimes yield results different from a sequential scan, because gist_point_consistent disagreed with the underlying operator code about whether to do comparisons exactly or fuzzily. Fix buffer leak in WAL replay (Heikki Linnakangas) This bug could result in incorrect local pin count errors during replay, making recovery impossible. Ensure we do crash recovery before entering archive recovery, if the database was not stopped cleanly and a recovery.conf file is present (Heikki Linnakangas, Kyotaro Horiguchi, Mitsumasa Kondo) This is needed to ensure that the database is consistent in certain scenarios, such as initializing a standby server with a filesystem snapshot from a running server. Avoid deleting not-yet-archived WAL files during crash recovery (Heikki Linnakangas, Fujii Masao) Fix race condition in DELETE RETURNING (Tom Lane) Under the right circumstances, DELETE RETURNING could attempt to fetch data from a shared buffer that the current process no longer has any pin on. If some other process changed the buffer meanwhile, this would lead to garbage RETURNING output, or even a crash. Fix infinite-loop risk in regular expression compilation (Tom Lane, Don Porter) Fix potential null-pointer dereference in regular expression compilation (Tom Lane) Fix to_char() to use ASCII-only case-folding rules where appropriate (Tom Lane) This fixes misbehavior of some template patterns that should be locale-independent, but mishandled I and i in Turkish locales. Fix unwanted rejection of timestamp 1999-12-31 24:00:00 (Tom Lane) Fix SQL-language functions to be safely usable as support functions for range types (Tom Lane) Fix logic error when a single transaction does UNLISTEN then LISTEN (Tom Lane) The session wound up not listening for notify events at all, though it surely should listen in this case. Fix possible planner crash after columns have been added to a view that's depended on by another view (Tom Lane) Fix performance issue in EXPLAIN (ANALYZE, TIMING OFF) (Pavel Stehule) Remove useless picksplit doesn't support secondary split log messages (Josh Hansen, Tom Lane) This message seems to have been added in expectation of code that was never written, and probably never will be, since GiST's default handling of secondary splits is actually pretty good. So stop nagging end users about it. Remove vestigial secondary-split support in gist_box_picksplit() (Tom Lane) Not only was this implementation of secondary-split not better than the default implementation, it's actually worse. So remove it and let the default code path handle the case. Fix possible failure to send a session's last few transaction commit/abort counts to the statistics collector (Tom Lane) Eliminate memory leaks in PL/Perl's spi_prepare() function (Alex Hunsaker, Tom Lane) Fix pg_dumpall to handle database names containing = correctly (Heikki Linnakangas) Avoid crash in pg_dump when an incorrect connection string is given (Heikki Linnakangas) Ignore invalid indexes in pg_dump and pg_upgrade (Michael Paquier, Bruce Momjian) Dumping invalid indexes can cause problems at restore time, for example if the reason the index creation failed was because it tried to enforce a uniqueness condition not satisfied by the table's data. Also, if the index creation is in fact still in progress, it seems reasonable to consider it to be an uncommitted DDL change, which pg_dump wouldn't be expected to dump anyway. pg_upgrade now also skips invalid indexes rather than failing. In pg_basebackup, include only the current server version's subdirectory when backing up a tablespace (Heikki Linnakangas) Add a server version check in pg_basebackup and pg_receivexlog, so they fail cleanly with version combinations that won't work (Heikki Linnakangas) Fix contrib/dblink to handle inconsistent settings of DateStyle or IntervalStyle safely (Daniel Farina, Tom Lane) Previously, if the remote server had different settings of these parameters, ambiguous dates might be read incorrectly. This fix ensures that datetime and interval columns fetched by a dblink query will be interpreted correctly. Note however that inconsistent settings are still risky, since literal values appearing in SQL commands sent to the remote server might be interpreted differently than they would be locally. Fix contrib/pg_trgm's similarity() function to return zero for trigram-less strings (Tom Lane) Previously it returned NaN due to internal division by zero. Enable building PostgreSQL with Microsoft Visual Studio 2012 (Brar Piening, Noah Misch) Update time zone data files to tzdata release 2013b for DST law changes in Chile, Haiti, Morocco, Paraguay, and some Russian areas. Also, historical zone data corrections for numerous places. Also, update the time zone abbreviation files for recent changes in Russia and elsewhere: CHOT, GET, IRKT, KGT, KRAT, MAGT, MAWT, MSK, NOVT, OMST, TKT, VLAT, WST, YAKT, YEKT now follow their current meanings, and VOLT (Europe/Volgograd) and MIST (Antarctica/Macquarie) are added to the default abbreviations list. 2013-02-07 This release contains a variety of fixes from 9.2.2. For information about new features in the 9.2 major release, see . A dump/restore is not required for those running 9.2.X. However, if you are upgrading from a version earlier than 9.2.2, see the release notes for 9.2.2. Prevent execution of enum_recv from SQL (Tom Lane) The function was misdeclared, allowing a simple SQL command to crash the server. In principle an attacker might be able to use it to examine the contents of server memory. Our thanks to Sumit Soni (via Secunia SVCRP) for reporting this issue. (CVE-2013-0255) Fix multiple problems in detection of when a consistent database state has been reached during WAL replay (Fujii Masao, Heikki Linnakangas, Simon Riggs, Andres Freund) Fix detection of end-of-backup point when no actual redo work is required (Heikki Linnakangas) This mistake could result in incorrect WAL ends before end of online backup errors. Update minimum recovery point when truncating a relation file (Heikki Linnakangas) Once data has been discarded, it's no longer safe to stop recovery at an earlier point in the timeline. Fix recycling of WAL segments after changing recovery target timeline (Heikki Linnakangas) Properly restore timeline history files from archive on cascading standby servers (Heikki Linnakangas) Fix lock conflict detection on hot-standby servers (Andres Freund, Robert Haas) Fix missing cancellations in hot standby mode (Noah Misch, Simon Riggs) The need to cancel conflicting hot-standby queries would sometimes be missed, allowing those queries to see inconsistent data. Prevent recovery pause feature from pausing before users can connect (Tom Lane) Fix SQL grammar to allow subscripting or field selection from a sub-SELECT result (Tom Lane) Fix performance problems with autovacuum truncation in busy workloads (Jan Wieck) Truncation of empty pages at the end of a table requires exclusive lock, but autovacuum was coded to fail (and release the table lock) when there are conflicting lock requests. Under load, it is easily possible that truncation would never occur, resulting in table bloat. Fix by performing a partial truncation, releasing the lock, then attempting to re-acquire the lock and continue. This fix also greatly reduces the average time before autovacuum releases the lock after a conflicting request arrives. Improve performance of SPI_execute and related functions, thereby improving PL/pgSQL's EXECUTE (Heikki Linnakangas, Tom Lane) Remove some data-copying overhead that was added in 9.2 as a consequence of revisions in the plan caching mechanism. This eliminates a performance regression compared to 9.1, and also saves memory, especially when the query string to be executed contains many SQL statements. A side benefit is that multi-statement query strings are now processed fully serially, that is we complete execution of earlier statements before running parse analysis and planning on the following ones. This eliminates a long-standing issue, in that DDL that should affect the behavior of a later statement will now behave as expected. Restore pre-9.2 cost estimates for index usage (Tom Lane) An ill-considered change of a fudge factor led to undesirably high cost estimates for use of very large indexes. Fix intermittent crash in DROP INDEX CONCURRENTLY (Tom Lane) Fix potential corruption of shared-memory lock table during CREATE/DROP INDEX CONCURRENTLY (Tom Lane) Fix COPY's multiple-tuple-insertion code for the case of a tuple larger than page size minus fillfactor (Heikki Linnakangas) The previous coding could get into an infinite loop. Protect against race conditions when scanning pg_tablespace (Stephen Frost, Tom Lane) CREATE DATABASE and DROP DATABASE could misbehave if there were concurrent updates of pg_tablespace entries. Prevent DROP OWNED from trying to drop whole databases or tablespaces (Álvaro Herrera) For safety, ownership of these objects must be reassigned, not dropped. Fix error in vacuum_freeze_table_age implementation (Andres Freund) In installations that have existed for more than vacuum_freeze_min_age transactions, this mistake prevented autovacuum from using partial-table scans, so that a full-table scan would always happen instead. Prevent misbehavior when a RowExpr or XmlExpr is parse-analyzed twice (Andres Freund, Tom Lane) This mistake could be user-visible in contexts such as CREATE TABLE LIKE INCLUDING INDEXES. Improve defenses against integer overflow in hashtable sizing calculations (Jeff Davis) Fix some bugs associated with privileges on datatypes (Tom Lane) There were some issues with default privileges for types, and pg_dump failed to dump such privileges at all. Fix failure to ignore leftover temporary tables after a server crash (Tom Lane) Fix failure to rotate postmaster log files for size reasons on Windows (Jeff Janes, Heikki Linnakangas) Reject out-of-range dates in to_date() (Hitoshi Harada) Fix pg_extension_config_dump() to handle extension-update cases properly (Tom Lane) This function will now replace any existing entry for the target table, making it usable in extension update scripts. Fix PL/pgSQL's reporting of plan-time errors in possibly-simple expressions (Tom Lane) The previous coding resulted in sometimes omitting the first line in the CONTEXT traceback for the error. Fix PL/Python's handling of functions used as triggers on multiple tables (Andres Freund) Ensure that non-ASCII prompt strings are translated to the correct code page on Windows (Alexander Law, Noah Misch) This bug affected psql and some other client programs. Fix possible crash in psql's \? command when not connected to a database (Meng Qingzhong) Fix possible error if a relation file is removed while pg_basebackup is running (Heikki Linnakangas) Tolerate timeline switches while pg_basebackup -X fetch is backing up a standby server (Heikki Linnakangas) Make pg_dump exclude data of unlogged tables when running on a hot-standby server (Magnus Hagander) This would fail anyway because the data is not available on the standby server, so it seems most convenient to assume --no-unlogged-table-data automatically. Fix pg_upgrade to deal with invalid indexes safely (Bruce Momjian) Fix pg_upgrade's -O/-o options (Marti Raudsepp) Fix one-byte buffer overrun in libpq's PQprintTuples (Xi Wang) This ancient function is not used anywhere by PostgreSQL itself, but it might still be used by some client code. Make ecpglib use translated messages properly (Chen Huajun) Properly install ecpg_compat and pgtypes libraries on MSVC (Jiang Guiqing) Include our version of isinf() in libecpg if it's not provided by the system (Jiang Guiqing) Rearrange configure's tests for supplied functions so it is not fooled by bogus exports from libedit/libreadline (Christoph Berg) Ensure Windows build number increases over time (Magnus Hagander) Make pgxs build executables with the right .exe suffix when cross-compiling for Windows (Zoltan Boszormenyi) Add new timezone abbreviation FET (Tom Lane) This is now used in some eastern-European time zones. 2012-12-06 This release contains a variety of fixes from 9.2.1. For information about new features in the 9.2 major release, see . A dump/restore is not required for those running 9.2.X. However, you may need to perform REINDEX operations to correct problems in concurrently-built indexes, as described in the first changelog item below. Also, if you are upgrading from version 9.2.0, see the release notes for 9.2.1. Fix multiple bugs associated with CREATE/DROP INDEX CONCURRENTLY (Andres Freund, Tom Lane, Simon Riggs, Pavan Deolasee) An error introduced while adding DROP INDEX CONCURRENTLY allowed incorrect indexing decisions to be made during the initial phase of CREATE INDEX CONCURRENTLY; so that indexes built by that command could be corrupt. It is recommended that indexes built in 9.2.X with CREATE INDEX CONCURRENTLY be rebuilt after applying this update. In addition, fix CREATE/DROP INDEX CONCURRENTLY to use in-place updates when changing the state of an index's pg_index row. This prevents race conditions that could cause concurrent sessions to miss updating the target index, thus again resulting in corrupt concurrently-created indexes. Also, fix various other operations to ensure that they ignore invalid indexes resulting from a failed CREATE INDEX CONCURRENTLY command. The most important of these is VACUUM, because an auto-vacuum could easily be launched on the table before corrective action can be taken to fix or remove the invalid index. Also fix DROP INDEX CONCURRENTLY to not disable insertions into the target index until all queries using it are done. Also fix misbehavior if DROP INDEX CONCURRENTLY is canceled: the previous coding could leave an un-droppable index behind. Correct predicate locking for DROP INDEX CONCURRENTLY (Kevin Grittner) Previously, SSI predicate locks were processed at the wrong time, possibly leading to incorrect behavior of serializable transactions executing in parallel with the DROP. Fix buffer locking during WAL replay (Tom Lane) The WAL replay code was insufficiently careful about locking buffers when replaying WAL records that affect more than one page. This could result in hot standby queries transiently seeing inconsistent states, resulting in wrong answers or unexpected failures. Fix an error in WAL generation logic for GIN indexes (Tom Lane) This could result in index corruption, if a torn-page failure occurred. Fix an error in WAL replay logic for SP-GiST indexes (Tom Lane) This could result in index corruption after a crash, or on a standby server. Fix incorrect detection of end-of-base-backup location during WAL recovery (Heikki Linnakangas) This mistake allowed hot standby mode to start up before the database reaches a consistent state. Properly remove startup process's virtual XID lock when promoting a hot standby server to normal running (Simon Riggs) This oversight could prevent subsequent execution of certain operations such as CREATE INDEX CONCURRENTLY. Avoid bogus out-of-sequence timeline ID errors in standby mode (Heikki Linnakangas) Prevent the postmaster from launching new child processes after it's received a shutdown signal (Tom Lane) This mistake could result in shutdown taking longer than it should, or even never completing at all without additional user action. Fix the syslogger process to not fail when log_rotation_age exceeds 2^31 milliseconds (about 25 days) (Tom Lane) Fix WaitLatch() to return promptly when the requested timeout expires (Jeff Janes, Tom Lane) With the previous coding, a steady stream of non-wait-terminating interrupts could delay return from WaitLatch() indefinitely. This has been shown to be a problem for the autovacuum launcher process, and might cause trouble elsewhere as well. Avoid corruption of internal hash tables when out of memory (Hitoshi Harada) Prevent file descriptors for dropped tables from being held open past transaction end (Tom Lane) This should reduce problems with long-since-dropped tables continuing to occupy disk space. Prevent database-wide crash and restart when a new child process is unable to create a pipe for its latch (Tom Lane) Although the new process must fail, there is no good reason to force a database-wide restart, so avoid that. This improves robustness when the kernel is nearly out of file descriptors. Avoid planner crash with joins to unflattened subqueries (Tom Lane) Fix planning of non-strict equivalence clauses above outer joins (Tom Lane) The planner could derive incorrect constraints from a clause equating a non-strict construct to something else, for example WHERE COALESCE(foo, 0) = 0 when foo is coming from the nullable side of an outer join. 9.2 showed this type of error in more cases than previous releases, but the basic bug has been there for a long time. Fix SELECT DISTINCT with index-optimized MIN/MAX on an inheritance tree (Tom Lane) The planner would fail with failed to re-find MinMaxAggInfo record given this combination of factors. Make sure the planner sees implicit and explicit casts as equivalent for all purposes, except in the minority of cases where there's actually a semantic difference (Tom Lane) Include join clauses when considering whether partial indexes can be used for a query (Tom Lane) A strict join clause can be sufficient to establish an x IS NOT NULL predicate, for example. This fixes a planner regression in 9.2, since previous versions could make comparable deductions. Limit growth of planning time when there are many indexable join clauses for the same index (Tom Lane) Improve planner's ability to prove exclusion constraints from equivalence classes (Tom Lane) Fix partial-row matching in hashed subplans to handle cross-type cases correctly (Tom Lane) This affects multicolumn NOT IN subplans, such as WHERE (a, b) NOT IN (SELECT x, y FROM ...) when for instance b and y are int4 and int8 respectively. This mistake led to wrong answers or crashes depending on the specific datatypes involved. Fix btree mark/restore functions to handle array keys (Tom Lane) This oversight could result in wrong answers from merge joins whose inner side is an index scan using an indexed_column = ANY(array) condition. Revert patch for taking fewer snapshots (Tom Lane) The 9.2 change to reduce the number of snapshots taken during query execution led to some anomalous behaviors not seen in previous releases, because execution would proceed with a snapshot acquired before locking the tables used by the query. Thus, for example, a query would not be guaranteed to see updates committed by a preceding transaction even if that transaction had exclusive lock. We'll probably revisit this in future releases, but meanwhile put it back the way it was before 9.2. Acquire buffer lock when re-fetching the old tuple for an AFTER ROW UPDATE/DELETE trigger (Andres Freund) In very unusual circumstances, this oversight could result in passing incorrect data to a trigger WHEN condition, or to the precheck logic for a foreign-key enforcement trigger. That could result in a crash, or in an incorrect decision about whether to fire the trigger. Fix ALTER COLUMN TYPE to handle inherited check constraints properly (Pavan Deolasee) This worked correctly in pre-8.4 releases, and now works correctly in 8.4 and later. Fix ALTER EXTENSION SET SCHEMA's failure to move some subsidiary objects into the new schema (Álvaro Herrera, Dimitri Fontaine) Handle CREATE TABLE AS EXECUTE correctly in extended query protocol (Tom Lane) Don't modify the input parse tree in DROP RULE IF NOT EXISTS and DROP TRIGGER IF NOT EXISTS (Tom Lane) This mistake would cause errors if a cached statement of one of these types was re-executed. Fix REASSIGN OWNED to handle grants on tablespaces (Álvaro Herrera) Ignore incorrect pg_attribute entries for system columns for views (Tom Lane) Views do not have any system columns. However, we forgot to remove such entries when converting a table to a view. That's fixed properly for 9.3 and later, but in previous branches we need to defend against existing mis-converted views. Fix rule printing to dump INSERT INTO table DEFAULT VALUES correctly (Tom Lane) Guard against stack overflow when there are too many UNION/INTERSECT/EXCEPT clauses in a query (Tom Lane) Prevent platform-dependent failures when dividing the minimum possible integer value by -1 (Xi Wang, Tom Lane) Fix possible access past end of string in date parsing (Hitoshi Harada) Fix failure to advance XID epoch if XID wraparound happens during a checkpoint and wal_level is hot_standby (Tom Lane, Andres Freund) While this mistake had no particular impact on PostgreSQL itself, it was bad for applications that rely on txid_current() and related functions: the TXID value would appear to go backwards. Fix pg_terminate_backend() and pg_cancel_backend() to not throw error for a non-existent target process (Josh Kupershmidt) This case already worked as intended when called by a superuser, but not so much when called by ordinary users. Fix display of pg_stat_replication.sync_state at a page boundary (Kyotaro Horiguchi) Produce an understandable error message if the length of the path name for a Unix-domain socket exceeds the platform-specific limit (Tom Lane, Andrew Dunstan) Formerly, this would result in something quite unhelpful, such as Non-recoverable failure in name resolution. Fix memory leaks when sending composite column values to the client (Tom Lane) Save some cycles by not searching for subtransaction locks at commit (Simon Riggs) In a transaction holding many exclusive locks, this useless activity could be quite costly. Make pg_ctl more robust about reading the postmaster.pid file (Heikki Linnakangas) This fixes race conditions and possible file descriptor leakage. Fix possible crash in psql if incorrectly-encoded data is presented and the client_encoding setting is a client-only encoding, such as SJIS (Jiang Guiqing) Make pg_dump dump SEQUENCE SET items in the data not pre-data section of the archive (Tom Lane) This fixes an undesirable inconsistency between the meanings of --data-only and --section=data, and also fixes dumping of sequences that are marked as extension configuration tables. Fix pg_dump's handling of DROP DATABASE commands in --clean mode (Guillaume Lelarge) Beginning in 9.2.0, pg_dump --clean would issue a DROP DATABASE command, which was either useless or dangerous depending on the usage scenario. It no longer does that. This change also fixes the combination of --clean and --create to work sensibly, i.e., emit DROP DATABASE then CREATE DATABASE before reconnecting to the target database. Fix pg_dump for views with circular dependencies and no relation options (Tom Lane) The previous fix to dump relation options when a view is involved in a circular dependency didn't work right for the case that the view has no options; it emitted ALTER VIEW foo SET () which is invalid syntax. Fix bugs in the restore.sql script emitted by pg_dump in tar output format (Tom Lane) The script would fail outright on tables whose names include upper-case characters. Also, make the script capable of restoring data in --inserts mode as well as the regular COPY mode. Fix pg_restore to accept POSIX-conformant tar files (Brian Weaver, Tom Lane) The original coding of pg_dump's tar output mode produced files that are not fully conformant with the POSIX standard. This has been corrected for version 9.3. This patch updates previous branches so that they will accept both the incorrect and the corrected formats, in hopes of avoiding compatibility problems when 9.3 comes out. Fix tar files emitted by pg_basebackup to be POSIX conformant (Brian Weaver, Tom Lane) Fix pg_resetxlog to locate postmaster.pid correctly when given a relative path to the data directory (Tom Lane) This mistake could lead to pg_resetxlog not noticing that there is an active postmaster using the data directory. Fix libpq's lo_import() and lo_export() functions to report file I/O errors properly (Tom Lane) Fix ecpg's processing of nested structure pointer variables (Muhammad Usama) Fix ecpg's ecpg_get_data function to handle arrays properly (Michael Meskes) Prevent pg_upgrade from trying to process TOAST tables for system catalogs (Bruce Momjian) This fixes an error seen when the information_schema has been dropped and recreated. Other failures were also possible. Improve pg_upgrade performance by setting synchronous_commit to off in the new cluster (Bruce Momjian) Make contrib/pageinspect's btree page inspection functions take buffer locks while examining pages (Tom Lane) Work around unportable behavior of malloc(0) and realloc(NULL, 0) (Tom Lane) On platforms where these calls return NULL, some code mistakenly thought that meant out-of-memory. This is known to have broken pg_dump for databases containing no user-defined aggregates. There might be other cases as well. Ensure that make install for an extension creates the extension installation directory (Cédric Villemain) Previously, this step was missed if MODULEDIR was set in the extension's Makefile. Fix pgxs support for building loadable modules on AIX (Tom Lane) Building modules outside the original source tree didn't work on AIX. Update time zone data files to tzdata release 2012j for DST law changes in Cuba, Israel, Jordan, Libya, Palestine, Western Samoa, and portions of Brazil. 2012-09-24 This release contains a variety of fixes from 9.2.0. For information about new features in the 9.2 major release, see . A dump/restore is not required for those running 9.2.X. However, you may need to perform REINDEX and/or VACUUM operations to recover from the effects of the data corruption bug described in the first changelog item below. Fix persistence marking of shared buffers during WAL replay (Jeff Davis) This mistake can result in buffers not being written out during checkpoints, resulting in data corruption if the server later crashes without ever having written those buffers. Corruption can occur on any server following crash recovery, but it is significantly more likely to occur on standby slave servers since those perform much more WAL replay. There is a low probability of corruption of btree and GIN indexes. There is a much higher probability of corruption of table visibility maps, which might lead to wrong answers from index-only scans. Table data proper cannot be corrupted by this bug. While no index corruption due to this bug is known to have occurred in the field, as a precautionary measure it is recommended that production installations REINDEX all btree and GIN indexes at a convenient time after upgrading to 9.2.1. Also, it is recommended to perform a VACUUM of all tables while having vacuum_freeze_table_age set to zero. This will fix any incorrect visibility map data. vacuum_cost_delay can be adjusted to reduce the performance impact of vacuuming, while causing it to take longer to finish. Fix possible incorrect sorting of output from queries involving WHERE indexed_column IN (list_of_values) (Tom Lane) Fix planner failure for queries involving GROUP BY expressions along with window functions and aggregates (Tom Lane) Fix planner's assignment of executor parameters (Tom Lane) This error could result in wrong answers from queries that scan the same WITH subquery multiple times. Improve planner's handling of join conditions in index scans (Tom Lane) Improve selectivity estimation for text search queries involving prefixes, i.e. word:* patterns (Tom Lane) Fix delayed recognition of permissions changes (Tom Lane) A command that needed no locks other than ones its transaction already had might fail to notice a concurrent GRANT or REVOKE that committed since the start of its transaction. Fix ANALYZE to not fail when a column is a domain over an array type (Tom Lane) Prevent PL/Perl from crashing if a recursive PL/Perl function is redefined while being executed (Tom Lane) Work around possible misoptimization in PL/Perl (Tom Lane) Some Linux distributions contain an incorrect version of pthread.h that results in incorrect compiled code in PL/Perl, leading to crashes if a PL/Perl function calls another one that throws an error. Remove unnecessary dependency on pg_config from pg_upgrade (Peter Eisentraut) Update time zone data files to tzdata release 2012f for DST law changes in Fiji 2012-09-10 This release has been largely focused on performance improvements, though new SQL features are not lacking. Work also continues in the area of replication support. Major enhancements include: Allow queries to retrieve data only from indexes, avoiding heap access (index-only scans) Allow the planner to generate custom plans for specific parameter values even when using prepared statements Improve the planner's ability to use nested loops with inner index scans Allow streaming replication slaves to forward data to other slaves (cascading replication) Allow pg_basebackup to make base backups from standby servers Add a pg_receivexlog tool to archive WAL file changes as they are written Add the SP-GiST (Space-Partitioned GiST) index access method Add support for range data types Add a JSON data type Add a security_barrier option for views Allow libpq connection strings to have the format of a URI Add a single-row processing mode to libpq for better handling of large result sets The above items are explained in more detail in the sections below. A dump/restore using pg_dump, or use of pg_upgrade, is required for those wishing to migrate data from any previous release. Version 9.2 contains a number of changes that may affect compatibility with previous releases. Observe the following incompatibilities: Remove the spclocation field from pg_tablespace (Magnus Hagander) 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. Move tsvector most-common-element statistics to new pg_stats columns (Alexander Korotkov) 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. Remove hstore's => operator (Robert Haas) 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. Ensure that xpath() escapes special characters in string values (Florian Pflug) Without this it is possible for the result not to be valid XML. Make pg_relation_size() and friends return NULL if the object does not exist (Phil Sorber) This prevents queries that call these functions from returning errors immediately after a concurrent DROP. Make EXTRACT(EPOCH FROM timestamp without time zone) measure the epoch from local midnight, not UTC midnight (Tom Lane) This change reverts an ill-considered change made in release 7.3. Measuring from UTC midnight was inconsistent because it made the result dependent on the timezone setting, which computations for timestamp without time zone should not be. The previous behavior remains available by casting the input value to timestamp with time zone. Properly parse time strings with trailing yesterday, today, and tomorrow (Dean Rasheed) Previously, SELECT '04:00:00 yesterday'::timestamp returned yesterday's date at midnight. Fix to_date() and to_timestamp() to wrap incomplete dates toward 2020 (Bruce Momjian) Previously, supplied years and year masks of less than four digits wrapped inconsistently. Prevent ALTER DOMAIN from working on non-domain types (Peter Eisentraut) Owner and schema changes were previously possible on non-domain types. No longer forcibly lowercase procedural language names in CREATE FUNCTION (Robert Haas) While unquoted language identifiers are still lowercased, strings and quoted identifiers are no longer forcibly down-cased. Thus for example CREATE FUNCTION ... LANGUAGE 'C' will no longer work; it must be spelled 'c', or better omit the quotes. 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. Provide consistent backquote, variable expansion, and quoted substring behavior in psql meta-command arguments (Tom Lane) 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). No longer treat clusterdb table names as double-quoted; no longer treat reindexdb table and index names as double-quoted (Bruce Momjian) Users must now include double-quotes in the command arguments if quoting is wanted. createuser no longer prompts for option settings by default (Peter Eisentraut) Use --interactive to obtain the old behavior. Disable prompting for the user name in dropuser unless --interactive is specified (Peter Eisentraut) Add server parameters for specifying the locations of server-side SSL files (Peter Eisentraut) 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 the silent_mode parameter (Heikki Linnakangas) Similar behavior can be obtained with pg_ctl start -l postmaster.log. Remove the wal_sender_delay parameter, as it is no longer needed (Tom Lane) Remove the custom_variable_classes parameter (Tom Lane) The checking provided by this setting was dubious. Now any setting can be prefixed by any class name. Rename pg_stat_activity.procpid to pid, to match other system tables (Magnus Hagander) Create a separate pg_stat_activity column to report process state (Scott Mead, Magnus Hagander) 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 completes (Magnus Hagander) Change all SQL-level statistics timing values to be float8 columns measured in milliseconds (Tom Lane) This change eliminates the designed-in assumption that the values are accurate to microseconds and no more (since the float8 values can be fractional). 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. The statistics functions underlying these columns now also return float8 milliseconds, rather than bigint microseconds. contrib/pg_stat_statements' total_time column is now also measured in milliseconds. Below you will find a detailed account of the changes between PostgreSQL 9.2 and the previous major release. Allow queries to retrieve data only from indexes, avoiding heap access (Robert Haas, Ibrar Ahmed, Heikki Linnakangas, Tom Lane) 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. 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. 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. Allow uncontended locks to be managed using a new fast-path lock mechanism (Robert Haas) Reduce overhead of creating virtual transaction ID locks (Robert Haas) Reduce the overhead of serializable isolation level locks (Dan Ports) Improve PowerPC and Itanium spinlock performance (Manabu Ori, Robert Haas, Tom Lane) Reduce overhead for shared invalidation cache messages (Robert Haas) Move the frequently accessed members of the PGPROC shared memory array to a separate array (Pavan Deolasee, Heikki Linnakangas, Robert Haas) Improve COPY performance by adding tuples to the heap in batches (Heikki Linnakangas) Improve GiST index performance for geometric data types by producing better trees with less memory allocation overhead (Alexander Korotkov) Improve GiST index build times (Alexander Korotkov, Heikki Linnakangas) Allow hint bits to be set sooner for temporary and unlogged tables (Robert Haas) Allow sorting to be performed by inlined, non-SQL-callable comparison functions (Peter Geoghegan, Robert Haas, Tom Lane) Make the number of CLOG buffers scale based on shared_buffers (Robert Haas, Simon Riggs, Tom Lane) Improve performance of buffer pool scans that occur when tables or databases are dropped (Jeff Janes, Simon Riggs) Improve performance of checkpointer's fsync-request queue when many tables are being dropped or truncated (Tom Lane) Pass the safe number of file descriptors to child processes on Windows (Heikki Linnakangas) This allows Windows sessions to use more open file descriptors than before. Create a dedicated background process to perform checkpoints (Simon Riggs) 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 the walwriter sooner (Simon Riggs) Previously, only wal_writer_delay triggered WAL flushing to disk; now filling a WAL buffer also triggers WAL writes. 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) This series of changes reduces the frequency of process wake-ups when there is nothing to do, dramatically reducing power consumption on idle servers. Allow the planner to generate custom plans for specific parameter values even when using prepared statements (Tom Lane) 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). Improve the planner's ability to use nested loops with inner index scans (Tom Lane) The new parameterized path mechanism allows inner index scans 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. Improve the planning API for foreign data wrappers (Etsuro Fujita, Shigeru Hanada, Tom Lane) Wrappers can now provide multiple access paths for their tables, allowing more flexibility in join planning. Recognize self-contradictory restriction clauses for non-table relations (Tom Lane) This check is only performed when constraint_exclusion is on. Allow indexed_col op ANY(ARRAY[...]) conditions to be used in plain index scans and index-only scans (Tom Lane) Formerly such conditions could only be used in bitmap index scans. Support MIN/MAX index optimizations on boolean columns (Marti Raudsepp) Account for set-returning functions in SELECT target lists when setting row count estimates (Tom Lane) Fix planner to handle indexes with duplicated columns more reliably (Tom Lane) Collect and use element-frequency statistics for arrays (Alexander Korotkov, Tom Lane) This change improves selectivity estimation for the array <@, &&, and @> operators (array containment and overlaps). Allow statistics to be collected for foreign tables (Etsuro Fujita) Improve cost estimates for use of partial indexes (Tom Lane) Improve the planner's ability to use statistics for columns referenced in subqueries (Tom Lane) Improve statistical estimates for subqueries using DISTINCT (Tom Lane) Do not treat role names and samerole specified in pg_hba.conf as automatically including superusers (Andrew Dunstan) This makes it easier to use reject lines with group roles. Adjust pg_hba.conf processing to handle token parsing more consistently (Brendan Jurd, Álvaro Herrera) Disallow empty pg_hba.conf files (Tom Lane) This was done to more quickly detect misconfiguration. Make superuser privilege imply replication privilege (Noah Misch) This avoids the need to explicitly assign such privileges. Attempt to log the current query string during a backend crash (Marti Raudsepp) Make logging of autovacuum I/O activity more verbose (Greg Smith, Noah Misch) This logging is triggered by log_autovacuum_min_duration. Make WAL replay report failures sooner (Fujii Masao) There were some cases where failures were only reported once the server went into master mode. Add pg_xlog_location_diff() to simplify WAL location comparisons (Euler Taveira de Oliveira) This is useful for computing replication lag. Support configurable event log application names on Windows (MauMau, Magnus Hagander) This allows different instances to use the event log with different identifiers, by setting the event_source server parameter, which is similar to how syslog_ident works. Change unexpected EOF messages to DEBUG1 level, except when there is an open transaction (Magnus Hagander) This change reduces log chatter caused by applications that close database connections ungracefully. Track temporary file sizes and file counts in the pg_stat_database system view (Tomas Vondra) Add a deadlock counter to the pg_stat_database system view (Magnus Hagander) Add a server parameter track_io_timing to track I/O timings (Ants Aasma, Robert Haas) Report checkpoint timing information in pg_stat_bgwriter (Greg Smith, Peter Geoghegan) Silently ignore nonexistent schemas specified in search_path (Tom Lane) This makes it more convenient to use generic path settings, which might include some schemas that don't exist in all databases. Allow superusers to set deadlock_timeout 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 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 a server parameter temp_file_limit to constrain temporary file space usage per session (Mark Kirkwood) Allow a superuser to SET an extension's superuser-only custom variable before loading the associated extension (Tom Lane) The system now remembers whether a SET was performed by a superuser, so that proper privilege checking can be done when the extension is loaded. Add postmaster -C option to query configuration parameters (Bruce Momjian) This allows pg_ctl to better handle cases where PGDATA or -D points to a configuration-only directory. 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. Allow multiple errors in postgresql.conf to be reported, rather than just the first one (Alexey Klyukin, Tom Lane) Allow a reload of postgresql.conf to be processed by all sessions, even if there are some settings that are invalid for particular sessions (Alexey Klyukin) Previously, such not-valid-within-session values would cause all setting changes to be ignored by that session. Add an include_if_exists facility for configuration files (Greg Smith) This works the same as include, except that an error is not thrown if the file is missing. Identify the server time zone during initdb, and set postgresql.conf entries timezone and log_timezone accordingly (Tom Lane) This avoids expensive time zone probes during server start. Fix pg_settings to report postgresql.conf line numbers on Windows (Tom Lane) 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. Add new synchronous_commit mode remote_write (Fujii Masao, Simon Riggs) 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. Add a pg_receivexlog tool to archive WAL file changes as they are written, rather than waiting for completed WAL files (Magnus Hagander) Allow pg_basebackup to make base backups from standby servers (Jun Ishizuka, Fujii Masao) This feature lets the work of making new base backups be off-loaded from the primary server. Allow streaming of WAL files while pg_basebackup is performing a backup (Magnus Hagander) This allows passing of WAL files to the standby before they are discarded on the primary. Cancel the running query if the client gets disconnected (Florian Pflug) If the backend detects loss of client connection during a query, it will now cancel the query rather than attempting to finish it. Retain column names at run time for 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. Improve column labels used for sub-SELECT results (Marti Raudsepp) Previously, the generic label ?column? was used. 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. Warn about creating casts to or from domain types (Robert Haas) Such casts have no effect. When a row fails a CHECK or NOT NULL constraint, show the row's contents as error detail (Jan Kundrát) This should make it easier to identify which row is problematic when an insert or update is processing many rows. Provide more reliable operation during concurrent DDL (Robert Haas, Noah Misch) 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 (Simon Riggs) This allows index removal without blocking other sessions. Allow foreign data wrappers to have per-column options (Shigeru Hanada) Improve pretty-printing of view definitions (Andrew Dunstan) Allow CHECK constraints to be declared NOT VALID (Álvaro Herrera) 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. The new ALTER TABLE VALIDATE command allows NOT VALID constraints to be checked for existing rows, after which they are converted into ordinary constraints. Allow CHECK constraints to be declared NO INHERIT (Nikhil Sontakke, Alex Hunsaker, Álvaro Herrera) This makes them enforceable only on the parent table, not on child tables. Add the ability to rename constraints (Peter Eisentraut) Reduce need to rebuild tables and indexes for certain ALTER TABLE ... ALTER COLUMN TYPE operations (Noah Misch) Increasing the length limit for a varchar or varbit column, or removing the limit 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. Avoid having ALTER TABLE revalidate foreign key constraints in some cases where it is not necessary (Noah Misch) Add IF EXISTS options to some ALTER commands (Pavel Stehule) For example, ALTER FOREIGN TABLE IF EXISTS foo RENAME TO bar. Add ALTER FOREIGN DATA WRAPPER ... RENAME and ALTER SERVER ... RENAME (Peter Eisentraut) Add ALTER DOMAIN ... RENAME (Peter Eisentraut) You could already rename domains using ALTER TYPE. Throw an error for ALTER DOMAIN ... DROP CONSTRAINT on a nonexistent constraint (Peter Eisentraut) An IF EXISTS option has been added to provide the previous behavior. Allow CREATE TABLE (LIKE ...) from foreign tables, views, and composite types (Peter Eisentraut) For example, this 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 ... AS EXECUTE to handle WITH NO DATA and column name specifications (Tom Lane) Add a security_barrier option for views (KaiGai Kohei, Robert Haas) 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. Add a new LEAKPROOF function attribute to mark functions that can safely be pushed down into security_barrier views (KaiGai Kohei) Add support for privileges on data types (Peter Eisentraut) This adds support for the SQL-conforming USAGE privilege on types and domains. The intent is to be able to restrict which users can create dependencies on types, since such dependencies limit the owner's ability to alter the type. Check for INSERT privileges in SELECT INTO / CREATE TABLE AS (KaiGai Kohei) 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. Allow VACUUM to more easily skip pages that cannot be locked (Simon Riggs, Robert Haas) This change should greatly reduce the incidence of VACUUM getting stuck waiting for other sessions. Make EXPLAIN (BUFFERS) count blocks dirtied and written (Robert Haas) Make EXPLAIN ANALYZE report the number of rows rejected by filter steps (Marko Tiikkaja) Allow EXPLAIN ANALYZE to avoid timing overhead when time values are not wanted (Tomas Vondra) This is accomplished by setting the new TIMING option to FALSE. Add support for range data types (Jeff Davis, Tom Lane, Alexander Korotkov) 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 a JSON data type (Robert Haas) This type stores JSON (JavaScript Object Notation) data with proper validation. Add array_to_json() and row_to_json() (Andrew Dunstan) Add a SMALLSERIAL data type (Mike Pultz) This is like SERIAL, except it stores the sequence in a two-byte integer column (int2). Allow domains to be declared NOT VALID (Álvaro Herrera) 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) 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) Allow xpath() to return a single-element XML array when supplied a scalar value (Florian Pflug) Previously, it returned an empty array. This change will also cause xpath_exists() to return true, not false, for such expressions. Improve XML error handling to be more robust (Florian Pflug) Allow non-superusers to use pg_cancel_backend() and pg_terminate_backend() on other sessions belonging to the same user (Magnus Hagander, Josh Kupershmidt, Dan Farina) Previously only superusers were allowed to use these functions. Allow importing and exporting of transaction snapshots (Joachim Wieland, Tom Lane) 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 from currently-running transactions can be imported. Support COLLATION FOR on expressions (Peter Eisentraut) This returns a string representing the collation of the expression. Add pg_opfamily_is_visible() (Josh Kupershmidt) Add a numeric variant of pg_size_pretty() for use with pg_xlog_location_diff() (Fujii Masao) Add a pg_trigger_depth() function (Kevin Grittner) This reports the current trigger call depth. Allow string_agg() to process bytea values (Pavel Stehule) Fix regular expressions in which a back-reference occurs within a larger quantified subexpression (Tom Lane) For example, ^(\w+)( \1)+$. Previous releases did not check that the back-reference actually matched the first occurrence. Add information schema views role_udt_grants, udt_privileges, and user_defined_types (Peter Eisentraut) Add composite-type attributes to the information schema element_types view (Peter Eisentraut) Implement interval_type columns in the information schema (Peter Eisentraut) Formerly these columns read as nulls. Implement collation-related columns in the information schema attributes, columns, domains, and element_types views (Peter Eisentraut) Implement the with_hierarchy column in the information schema table_privileges view (Peter Eisentraut) Add display of sequence USAGE privileges to information schema (Peter Eisentraut) Make the information schema show default privileges (Peter Eisentraut) Previously, non-empty default permissions were not represented in the views. Allow the PL/pgSQL OPEN cursor command to supply parameters by name (Yeb Havinga) Add a GET STACKED DIAGNOSTICS PL/pgSQL command to retrieve exception info (Pavel Stehule) Speed up PL/pgSQL array assignment by caching type information (Pavel Stehule) Improve performance and memory consumption for long chains of ELSIF clauses (Tom Lane) Output the function signature, not just the name, in PL/pgSQL error messages (Pavel Stehule) Add PL/Python SPI cursor support (Jan Urbanski) This allows PL/Python to read partial result sets. Add result metadata functions to PL/Python (Peter Eisentraut) Specifically, this adds result object functions .colnames, .coltypes, and .coltypmods. Remove support for Python 2.2 (Peter Eisentraut) Allow SQL-language functions to reference parameters by name (Matthew Draper) To use this, simply name the function arguments and then reference the argument names in the SQL function body. Add initdb options --auth-local and --auth-host (Peter Eisentraut) This allows separate control of local and host pg_hba.conf authentication settings. --auth still controls both. Add --replication/--no-replication flags to createuser to control replication permission (Fujii Masao) Add the --if-exists option to dropdb and dropuser (Josh Kupershmidt) Give command-line tools the ability to specify the name of the database to connect to, and fall back to template1 if a postgres database connection fails (Robert Haas) Add a display mode to auto-expand output based on the display width (Peter Eisentraut) This adds the auto option to the \x command, which switches to the expanded mode when the normal output would be wider than the screen. Allow inclusion of a script file that is named relative to the directory of the file from which it was invoked (Gurjeet Singh) This is done with a new command \ir. Add support for non-ASCII characters in psql variable names (Tom Lane) Add support for major-version-specific .psqlrc files (Bruce Momjian) psql already supported minor-version-specific .psqlrc files. Provide environment variable overrides for psql history and startup file locations (Andrew Dunstan) PSQL_HISTORY and PSQLRC now determine these file names if set. Add a \setenv command to modify the environment variables passed to child processes (Andrew Dunstan) Name psql's temporary editor files with a .sql extension (Peter Eisentraut) This allows extension-sensitive editors to select the right mode. Allow psql to use zero-byte field and record separators (Peter Eisentraut) Various shell tools use zero-byte (NUL) separators, e.g. find. Make the \timing option report times for failed queries (Magnus Hagander) Previously times were reported only for successful queries. Unify and tighten psql's treatment of \copy and SQL COPY (Noah Misch) This fix makes failure behavior more predictable and honors \set ON_ERROR_ROLLBACK. Make \d on a sequence show the table/column name owning it (Magnus Hagander) Show statistics target for columns in \d+ (Magnus Hagander) Show role password expiration dates in \du (Fabrízio de Royes Mello) Display comments for casts, conversions, domains, and languages (Josh Kupershmidt) These are included in the output of \dC+, \dc+, \dD+, and \dL respectively. Display comments for SQL/MED objects (Josh Kupershmidt) These are included in the output of \des+, \det+, and \dew+ for foreign servers, foreign tables, and foreign data wrappers respectively. Change \dd to display comments only for object types without their own backslash command (Josh Kupershmidt) In psql tab completion, complete SQL keywords in either upper or lower case according to the new COMP_KEYWORD_CASE setting (Peter Eisentraut) Add tab completion support for EXECUTE (Andreas Karlsson) Allow tab completion of role references in GRANT/REVOKE (Peter Eisentraut) Allow tab completion of file names to supply quotes, when necessary (Noah Misch) Change tab completion support for TABLE to also include views (Magnus Hagander) Add an --exclude-table-data option to pg_dump (Andrew Dunstan) This allows dumping of a table's definition but not its data, on a per-table basis. Add a --section option to pg_dump and pg_restore (Andrew Dunstan) Valid values are pre-data, data, and post-data. The option can be given more than once to select two or more sections. Make pg_dumpall dump all roles first, then all configuration settings on roles (Phil Sorber) This allows a role's configuration settings to mention other roles without generating an error. Allow pg_dumpall to avoid errors if the postgres database is missing in the new cluster (Robert Haas) Dump foreign server user mappings in user name order (Peter Eisentraut) This helps produce deterministic dump files. Dump operators in a predictable order (Peter Eisentraut) Tighten rules for when extension configuration tables are dumped by pg_dump (Tom Lane) Make pg_dump emit more useful dependency information (Tom Lane) The dependency links included in archive-format dumps were formerly of very limited use, because they frequently referenced objects that appeared nowhere in the dump. Now they represent actual dependencies (possibly indirect) among the dumped objects. Improve pg_dump's performance when dumping many database objects (Tom Lane) Allow libpq connection strings to have the format of a URI (Alexander Shulgin) The syntax begins with postgres://. This can allow applications to avoid implementing their own parser for URIs representing database connections. Add a connection option to disable SSL compression (Laurenz Albe) This can be used to remove the overhead of SSL compression on fast networks. Add a single-row processing 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. Add const qualifiers to the declarations of the functions PQconnectdbParams, PQconnectStartParams, and PQpingParams (Lionel Elie Mamane) Allow the .pgpass file to include escaped characters in the password field (Robert Haas) Make library functions use abort() instead of exit() when it is necessary to terminate the process (Peter Eisentraut) 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. Remove dead ports (Peter Eisentraut) The following platforms are no longer supported: dgux, nextstep, sunos4, svr4, ultrix4, univel, bsdi. Add support for building with MS Visual Studio 2010 (Brar Piening) Enable compiling with the MinGW-w64 32-bit compiler (Lars Kanis) Install plpgsql.h into include/server during installation (Heikki Linnakangas) Improve the latch facility to include detection of postmaster death (Peter Geoghegan, Heikki Linnakangas, Tom Lane) This eliminates one of the main reasons that background processes formerly had to wake up to poll for events. Use C flexible array members, where supported (Peter Eisentraut) Improve the concurrent transaction regression tests (isolationtester) (Noah Misch) Modify thread_test to create its test files in the current directory, rather than /tmp (Bruce Momjian) Improve flex and bison warning and error reporting (Tom Lane) Add memory barrier support (Robert Haas) This is currently unused. Modify pgindent to use a typedef file (Bruce Momjian) Add a hook for processing messages due to be sent to the server log (Martin Pihlak) Add object access hooks for DROP commands (KaiGai Kohei) Centralize DROP handling for some object types (KaiGai Kohei) Add a pg_upgrade test suite (Peter Eisentraut) Sync regular expression code with TCL 8.5.11 and improve internal processing (Tom Lane) Move CRC tables to libpgport, and provide them in a separate include file (Daniel Farina) Add options to git_changelog for use in major release note creation (Bruce Momjian) Support Linux's /proc/self/oom_score_adj API (Tom Lane) Improve efficiency of dblink by using libpq's new single-row processing mode (Kyotaro Horiguchi, Marko Kreen) This improvement does not apply to dblink_send_query()/dblink_get_result(). Support force_not_null option in file_fdw (Shigeru Hanada) Implement dry-run mode for pg_archivecleanup (Gabriele Bartolini) This only outputs the names of files to be deleted. Add new pgbench switches --unlogged-tables, --tablespace, and --index-tablespace (Robert Haas) Change pg_test_fsync to test for a fixed amount of time, rather than a fixed number of cycles (Bruce Momjian) The -o/cycles option was removed, and -s/seconds added. Add a pg_test_timing utility to measure clock monotonicity and timing overhead (Ants Aasma, Greg Smith) Add a tcn (triggered change notification) module to generate NOTIFY events on table changes (Kevin Grittner) 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. Overhaul pg_upgrade logging and failure reporting (Bruce Momjian) Create four append-only log files, and delete them on success. Add -r/--retain option to unconditionally retain these files. Also remove pg_upgrade options -g/-G/-l options as unnecessary, and tighten log file permissions. Make pg_upgrade create a script to incrementally generate more accurate optimizer statistics (Bruce Momjian) This reduces the time needed to generate minimal cluster statistics after an upgrade. Allow pg_upgrade to upgrade an old cluster that does not have a postgres database (Bruce Momjian) Allow pg_upgrade to handle cases where some old or new databases are missing, as long as they are empty (Bruce Momjian) Allow pg_upgrade to handle configuration-only directory installations (Bruce Momjian) In pg_upgrade, add -o/-O options to pass parameters to the servers (Bruce Momjian) This is useful for configuration-only directory installs. Change pg_upgrade to use port 50432 by default (Bruce Momjian) This helps avoid unintended client connections during the upgrade. Reduce cluster locking in pg_upgrade (Bruce Momjian) Specifically, only lock the old cluster if link mode is used, and do it right after the schema is restored. Allow pg_stat_statements to aggregate similar 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 detailed log analysis. Add dirtied and written block counts and read/write times to pg_stat_statements (Robert Haas, Ants Aasma) Prevent pg_stat_statements from double-counting PREPARE and EXECUTE commands (Tom Lane) Support SECURITY LABEL on global objects (KaiGai Kohei, Robert Haas) Specifically, add security labels to databases, tablespaces, and roles. Allow sepgsql to honor database labels (KaiGai Kohei) Perform sepgsql permission checks during the creation of various objects (KaiGai Kohei) Add sepgsql_setcon() and related functions to control the sepgsql security domain (KaiGai Kohei) Add a user space access cache to sepgsql to improve performance (KaiGai Kohei) Add a rule to optionally build HTML documentation using the stylesheet from the website (Magnus Hagander) Use gmake STYLE=website draft. Improve EXPLAIN documentation (Tom Lane) Document that user/database names are preserved with double-quoting by command-line tools like vacuumdb (Bruce Momjian) Document the actual string returned by the client for MD5 authentication (Cyan Ogilvie) Deprecate use of GLOBAL and LOCAL in CREATE TEMP TABLE (Noah Misch) PostgreSQL has long treated these keyword as no-ops, and continues to do so; but in future they might mean what the SQL standard says they mean, so applications should avoid using them.