PostgreSQL Changelog

New in version 9.3.5

September 8th, 2014
  • In pg_upgrade, remove pg_multixact files left behind by initdb (Bruce Momjian)
  • Correctly initialize padding bytes in contrib/btree_gist indexes on bit columns (Heikki Linnakangas)
  • This error could result in incorrect query results due to values that should compare equal not being seen as equal. Users with GiST indexes on bit or bit varying columns should REINDEX those indexes after installing this update.
  • Protect against torn pages when deleting GIN list pages (Heikki Linnakangas)
  • This fix prevents possible index corruption if a system crash occurs while the page update is being written to disk.
  • Don't clear the right-link of a GiST index page while replaying updates from WAL (Heikki Linnakangas)
  • This error could lead to transiently wrong answers from GiST index scans performed in Hot Standby.
  • Fix corner-case infinite loop during insertion into an SP-GiST text index (Tom Lane)
  • Fix incorrect answers from SP-GiST index searches with -|- (range adjacency) operator (Heikki Linnakangas)
  • Fix wraparound handling for pg_multixact/members (Álvaro Herrera)
  • Truncate pg_multixact during checkpoints, not during VACUUM (Álvaro Herrera)
  • This change ensures that pg_multixact segments can't be removed if they'd still be needed during WAL replay after a crash.
  • Fix possible inconsistency of all-visible flags after WAL recovery (Heikki Linnakangas)
  • Fix possibly-incorrect cache invalidation during nested calls to ReceiveSharedInvalidMessages (Andres Freund)
  • Fix race condition when updating a tuple concurrently locked by another process (Andres Freund, Álvaro Herrera)
  • Fix "could not find pathkey item to sort" planner failures with UNION ALL over subqueries reading from tables with inheritance children (Tom Lane)
  • Don't assume a subquery's output is unique if there's a set-returning function in its targetlist (David Rowley)
  • This oversight could lead to misoptimization of constructs like WHERE x IN (SELECT y, generate_series(1,10) FROM t GROUP BY y).
  • Improve planner to drop constant-NULL inputs of AND/OR when possible (Tom Lane)
  • This change fixes some cases where the more aggressive parameter substitution done by 9.2 and later can lead to a worse plan than older versions produced.
  • Ensure that the planner sees equivalent VARIADIC and non-VARIADIC function calls as equivalent (Tom Lane)
  • This bug could for example result in failure to use expression indexes involving variadic functions. It might be necessary to re-create such indexes, and/or re-create views including variadic function calls that should match the indexes, for the fix to be effective for existing 9.3 installations.
  • Fix handling of nested JSON objects in json_populate_recordset() and friends (Michael Paquier, Tom Lane)
  • A nested JSON object could result in previous fields of the parent object not being shown in the output.
  • Fix identification of input type category in to_json() and friends (Tom Lane)
  • This is known to have led to inadequate quoting of money fields in the JSON result, and there may have been wrong results for other data types as well.
  • Fix failure to detoast fields in composite elements of structured types (Tom Lane)
  • This corrects cases where TOAST pointers could be copied into other tables without being dereferenced. If the original data is later deleted, it would lead to errors like "missing chunk number 0 for toast value ..." when the now-dangling pointer is used.
  • Fix "record type has not been registered" failures with whole-row references to the output of Append plan nodes (Tom Lane)
  • Fix possible crash when invoking a user-defined function while rewinding a cursor (Tom Lane)
  • Fix query-lifespan memory leak while evaluating the arguments for a function in FROM (Tom Lane)
  • Fix session-lifespan memory leaks in regular-expression processing (Tom Lane, Arthur O'Dwyer, Greg Stark)
  • Fix data encoding error in hungarian.stop (Tom Lane)
  • Prevent foreign tables from being created with OIDS when default_with_oids is true (Etsuro Fujita)
  • Fix liveness checks for rows that were inserted in the current transaction and then deleted by a now-rolled-back subtransaction (Andres Freund)
  • This could cause problems (at least spurious warnings, and at worst an infinite loop) if CREATE INDEX or CLUSTER were done later in the same transaction.
  • Clear pg_stat_activity.xact_start during PREPARE TRANSACTION (Andres Freund)
  • After the PREPARE, the originating session is no longer in a transaction, so it should not continue to display a transaction start time.
  • Fix REASSIGN OWNED to not fail for text search objects (Álvaro Herrera)
  • Prevent pg_class.relminmxid values from going backwards during VACUUM FULL (Álvaro Herrera)
  • Reduce indentation in rule/view dumps to improve readability and avoid excessive whitespace (Greg Stark, Tom Lane)
  • This change reduces the amount of indentation applied to nested constructs, including some cases that the user probably doesn't think of as nested, such as UNION lists. Previously, deeply nested constructs were printed with an amount of whitespace growing as O(N^2), which created a performance problem and even risk of out-of-memory failures. Now the indentation is reduced modulo 40, which is initially odd to look at but seems to preserve readability better than simply limiting the indentation would do. Redundant parenthesization of UNION lists has been reduced as well.
  • Fix dumping of rules/views when subsequent addition of a column has resulted in multiple input columns matching a USING specification (Tom Lane)
  • Repair view printing for some cases involving functions in FROM that return a composite type containing dropped columns (Tom Lane)
  • Block signals during postmaster startup (Tom Lane)
  • This ensures that the postmaster will properly clean up after itself if, for example, it receives SIGINT while still starting up.
  • Fix client host name lookup when processing pg_hba.conf entries that specify host names instead of IP addresses (Tom Lane)
  • Ensure that reverse-DNS lookup failures are reported, instead of just silently not matching such entries. Also ensure that we make only one reverse-DNS lookup attempt per connection, not one per host name entry, which is what previously happened if the lookup attempts failed.
  • Allow the root user to use postgres -C variable and postgres --describe-config (MauMau)
  • The prohibition on starting the server as root does not need to extend to these operations, and relaxing it prevents failure of pg_ctl in some scenarios.
  • Secure Unix-domain sockets of temporary postmasters started during make check (Noah Misch)
  • Any local user able to access the socket file could connect as the server's bootstrap superuser, then proceed to execute arbitrary code as the operating-system user running the test, as we previously noted in CVE-2014-0067. This change defends against that risk by placing the server's socket in a temporary, mode 0700 subdirectory of /tmp. The hazard remains however on platforms where Unix sockets are not supported, notably Windows, because then the temporary postmaster must accept local TCP connections.
  • A useful side effect of this change is to simplify make check testing in builds that override DEFAULT_PGSOCKET_DIR. Popular non-default values like /var/run/postgresql are often not writable by the build user, requiring workarounds that will no longer be necessary.
  • Fix tablespace creation WAL replay to work on Windows (MauMau)
  • Fix detection of socket creation failures on Windows (Bruce Momjian)
  • On Windows, allow new sessions to absorb values of PGC_BACKEND parameters (such as log_connections) from the configuration file (Amit Kapila)
  • Previously, if such a parameter were changed in the file post-startup, the change would have no effect.
  • Properly quote executable path names on Windows (Nikhil Deshpande)
  • This oversight could cause initdb and pg_upgrade to fail on Windows, if the installation path contained both spaces and @ signs.
  • Fix linking of libpython on OS X (Tom Lane)
  • The method we previously used can fail with the Python library supplied by Xcode 5.0 and later.
  • Avoid buffer bloat in libpq when the server consistently sends data faster than the client can absorb it (Shin-ichi Morita, Tom Lane)
  • libpq could be coerced into enlarging its input buffer until it runs out of memory (which would be reported misleadingly as "lost synchronization with server"). Under ordinary circumstances it's quite far-fetched that data could be continuously transmitted more quickly than the recv() loop can absorb it, but this has been observed when the client is artificially slowed by scheduler constraints.
  • Ensure that LDAP lookup attempts in libpq time out as intended (Laurenz Albe)
  • Fix ecpg to do the right thing when an array of char * is the target for a FETCH statement returning more than one row, as well as some other array-handling fixes (Ashutosh Bapat)
  • Fix pg_dump to cope with a materialized view that depends on a table's primary key (Tom Lane)
  • This occurs if the view's query relies on functional dependency to abbreviate a GROUP BY list. pg_dump got sufficiently confused that it dumped the materialized view as a regular view.
  • Fix parsing of pg_dumpall's -i switch (Tom Lane)
  • Fix pg_restore's processing of old-style large object comments (Tom Lane)
  • A direct-to-database restore from an archive file generated by a pre-9.0 version of pg_dump would usually fail if the archive contained more than a few comments for large objects.
  • Fix pg_upgrade for cases where the new server creates a TOAST table but the old version did not (Bruce Momjian)
  • This rare situation would manifest as "relation OID mismatch" errors.
  • In pg_upgrade, preserve pg_database.datminmxid and pg_class.relminmxid values from the old cluster, or insert reasonable values when upgrading from pre-9.3; also defend against unreasonable values in the core server (Bruce Momjian, Álvaro Herrera, Tom Lane)
  • These changes prevent scenarios in which autovacuum might insist on scanning the entire cluster's contents immediately upon starting the new cluster, or in which tracking of unfrozen MXID values might be disabled completely.
  • Prevent contrib/auto_explain from changing the output of a user's EXPLAIN (Tom Lane)
  • If auto_explain is active, it could cause an EXPLAIN (ANALYZE, TIMING OFF) command to nonetheless print timing information.
  • Fix query-lifespan memory leak in contrib/dblink (MauMau, Joe Conway)
  • In contrib/pgcrypto functions, ensure sensitive information is cleared from stack variables before returning (Marko Kreen)
  • Prevent use of already-freed memory in contrib/pgstattuple's pgstat_heap() (Noah Misch)
  • In contrib/uuid-ossp, cache the state of the OSSP UUID library across calls (Tom Lane)
  • This improves the efficiency of UUID generation and reduces the amount of entropy drawn from /dev/urandom, on platforms that have that.
  • Update time zone data files to tzdata release 2014e for DST law changes in Crimea, Egypt, and Morocco.

New in version 9.3.3 (February 20th, 2014)

  • Security Fixes:
  • This update fixes CVE-2014-0060, in which PostgreSQL did not properly enforce the WITH ADMIN OPTION permission for ROLE management. Before this fix, any member of a ROLE was able to grant others access to the same ROLE regardless if the member was given the WITH ADMIN OPTION permission. It also fixes multiple privilege escalation issues, including: CVE-2014-0061, CVE-2014-0062, CVE-2014-0063, CVE-2014-0064, CVE-2014-0065, and CVE-2014-0066. More information on these issues can be found on our security page and the security issue detail wiki page.
  • With this release, we are also alerting users to a known security hole that allows other users on the same machine to gain access to an operating system account while it is doing "make check": CVE-2014-0067. "Make check" is normally part of building PostgreSQL from source code. As it is not possible to fix this issue without causing significant issues to our testing infrastructure, a patch will be released separately and publicly. Until then, users are strongly advised not to run "make check" on machines where untrusted users have accounts.
  • Replication and Data Integrity Fixes:
  • This update also fixes some issues which affect binary replication and row locking, and can cause recoverable data corruption in some cases. There are several fixes to index page locking issue during replication which can cause indexes on the replica to be corrupted. There is a fix to a transaction freezing bug in version 9.3 which could cause databases which cycled through transaction ID wraparound several times to have old row versions reappear. We have also fixed three bugs which could cause new standbys to fail to start up. Finally, this update fixes an issue which could break foreign keys, although the keys themselves will still need to be fixed manually after applying the update.
  • In version 9.3, these fixes result in the addition of several new server configuration settings to control multixact freezing. Importantly, standby servers must be updated to 9.3.3 or later before the replication master is updated, or replication will be broken.
  • Other Improvements:
  • In addition to the above, the following issues are fixed in this release:
  • Fix WAL logging of visibility map change
  • Make sure that GIN indexes log all insertions
  • Get pause_at_recovery_target to pause at correct time
  • Ensure walreceiver sends hot-standby feedback messages on time
  • Prevent timeout interrupts from taking control away from mainline code
  • Eliminate several race conditions
  • Fix some broken HINTs in error messages
  • Prevent server lockup on SSL connection loss
  • Fix two Unicode handling issues
  • Prevent crash on certain subselect syntax
  • Prevent crash on select from zero column table
  • Fix two bugs with LATERAL
  • Fix issue with UNION ALL, partitioning, and updates
  • Ensure that ANALYZE understands domains over ranges
  • Eliminate permissions check when using default tablespace
  • Fix memory leakage in JSON functions
  • Allow extensions with event triggers
  • Distinguish numbers correctly in JSON output
  • Fix permissions for pg_start_backup() and pg_stop_backup()
  • Accept SHIFT_JIS as locale name
  • Fix .* expansion for SQL function variables
  • Prevent infinite loop on some COPY connection failures
  • Several fixes for client issues on Windows
  • Enable building PostgreSQL with Visual Studio 2013
  • Update time zone files for recent changes
  • There are also fixes to all of the following optional modules (extensions): ECPG, dblink, ISN, pgbench, pg_stat_statements and postgres_fdw. Additional changes and details of some of the above issues can be found in the Release Notes.
  • As with other minor releases, users are not required to dump and reload their database or use pg_upgrade in order to apply this update release; you may simply shut down PostgreSQL and update its binaries. Users who have skipped multiple update releases may need to perform additional post-update steps; see the Release Notes for details.

New in version 9.3.2 (December 5th, 2013)

  • Fix VACUUM's tests to see whether it can update relfrozenxid (Andres Freund)
  • In some cases VACUUM (either manual or autovacuum) could incorrectly advance a table's relfrozenxid value, allowing tuples to escape freezing, causing those rows to become invisible once 2^31 transactions have elapsed. The probability of data loss is fairly low since multiple incorrect advancements would need to happen before actual loss occurs, but it's not zero. In 9.2.0 and later, the probability of loss is higher, and it's also possible to get "could not access status of transaction" errors as a consequence of this bug. Users upgrading from releases 9.0.4 or 8.4.8 or earlier are not affected, but all later versions contain the bug.
  • The issue can be ameliorated by, after upgrading, vacuuming all tables in all databases while having vacuum_freeze_table_age set to zero. This will fix any latent corruption but will not be able to fix all pre-existing data errors. However, an installation can be presumed safe after performing this vacuuming if it has executed fewer than 2^31 update transactions in its lifetime (check this with SELECT txid_current() < 2^31).
  • Fix multiple bugs in MultiXactId freezing (Andres Freund, Álvaro Herrera)
  • These bugs could lead to "could not access status of transaction" errors, or to duplicate or vanishing rows. Users upgrading from releases prior to 9.3.0 are not affected.
  • The issue can be ameliorated by, after upgrading, vacuuming all tables in all databases while having vacuum_freeze_table_age set to zero. This will fix latent corruption but will not be able to fix all pre-existing data errors.
  • As a separate issue, these bugs can also cause standby servers to get out of sync with the primary, thus exhibiting data errors that are not in the primary. Therefore, it's recommended that 9.3.0 and 9.3.1 standby servers be re-cloned from the primary (e.g., with a new base backup) after upgrading.
  • Fix initialization of pg_clog and pg_subtrans during hot standby startup (Andres Freund, Heikki Linnakangas)
  • This bug can cause data loss on standby servers at the moment they start to accept hot-standby queries, by marking committed transactions as uncommitted. The likelihood of such corruption is small unless, at the time of standby startup, the primary server has executed many updating transactions since its last checkpoint. Symptoms include missing rows, rows that should have been deleted being still visible, and obsolete versions of updated rows being still visible alongside their newer versions.
  • This bug was introduced in versions 9.3.0, 9.2.5, 9.1.10, and 9.0.14. Standby servers that have only been running earlier releases are not at risk. It's recommended that standby servers that have ever run any of the buggy releases be re-cloned from the primary (e.g., with a new base backup) after upgrading.
  • Fix multiple bugs in update chain traversal (Andres Freund, Álvaro Herrera)
  • These bugs could result in incorrect behavior, such as locking or even updating the wrong row, in the presence of concurrent updates. Spurious "unable to fetch updated version of tuple" errors were also possible.
  • Fix dangling-pointer problem in fast-path locking (Tom Lane)
  • This could lead to corruption of the lock data structures in shared memory, causing "lock already held" and other odd errors.
  • Fix assorted race conditions in timeout management (Tom Lane)
  • These errors could result in a server process becoming unresponsive because it had blocked SIGALRM and/or SIGINT.
  • Truncate pg_multixact contents during WAL replay (Andres Freund)
  • This avoids ever-increasing disk space consumption in standby servers.
  • Ensure an anti-wraparound VACUUM counts a page as scanned when it's only verified that no tuples need freezing (Sergey Burladyan, Jeff Janes)
  • This bug could result in failing to advance relfrozenxid, so that the table would still be thought to need another anti-wraparound vacuum. In the worst case the database might even shut down to prevent wraparound.
  • Fix full-table-vacuum request mechanism for MultiXactIds (Andres Freund)
  • This bug could result in large amounts of useless autovacuum activity.
  • Fix race condition in GIN index posting tree page deletion (Heikki Linnakangas)
  • This could lead to transient wrong answers or query failures.
  • Fix "unexpected spgdoinsert() failure" error during SP-GiST index creation (Teodor Sigaev)
  • Fix assorted bugs in materialized views (Kevin Grittner, Andres Freund)
  • Re-allow duplicate table aliases if they're within aliased JOINs (Tom Lane)
  • Historically PostgreSQL has accepted queries like
  • SELECT ... FROM tab1 x CROSS JOIN (tab2 x CROSS JOIN tab3 y) z
  • although a strict reading of the SQL standard would forbid the duplicate usage of table alias x. A misguided change in 9.3.0 caused it to reject some such cases that were formerly accepted. Restore the previous behavior.
  • Avoid flattening a subquery whose SELECT list contains a volatile function wrapped inside a sub-SELECT (Tom Lane)
  • This avoids unexpected results due to extra evaluations of the volatile function.
  • Fix planner's processing of non-simple-variable subquery outputs nested within outer joins (Tom Lane)
  • This error could lead to incorrect plans for queries involving multiple levels of subqueries within JOIN syntax.
  • Fix incorrect planning in cases where the same non-strict expression appears in multiple WHERE and outer JOIN equality clauses (Tom Lane)
  • Fix planner crash with whole-row reference to a subquery (Tom Lane)
  • Fix incorrect generation of optimized MIN()/MAX() plans for inheritance trees (Tom Lane)
  • The planner could fail in cases where the MIN()/MAX() argument was an expression rather than a simple variable.
  • Fix premature deletion of temporary files (Andres Freund)
  • Prevent intra-transaction memory leak when printing range values (Tom Lane)
  • This fix actually cures transient memory leaks in any datatype output function, but range types are the only ones known to have had a significant problem.
  • Fix memory leaks when reloading configuration files (Heikki Linnakangas, Hari Babu)
  • Prevent incorrect display of dropped columns in NOT NULL and CHECK constraint violation messages (Michael Paquier and Tom Lane)
  • Allow default arguments and named-argument notation for window functions (Tom Lane)
  • Previously, these cases were likely to crash.
  • Suppress trailing whitespace on each line when pretty-printing rules and views (Tom Lane)
  • 9.3.0 generated such whitespace in many more cases than previous versions did. To reduce unexpected behavioral changes, suppress unnecessary whitespace in all cases.
  • Fix possible read past end of memory in rule printing (Peter Eisentraut)
  • Fix array slicing of int2vector and oidvector values (Tom Lane)
  • Expressions of this kind are now implicitly promoted to regular int2 or oid arrays.
  • Return a valid JSON value when converting an empty hstore value to json (Oskari Saarenmaa)
  • Fix incorrect behaviors when using a SQL-standard, simple GMT offset timezone (Tom Lane)
  • In some cases, the system would use the simple GMT offset value when it should have used the regular timezone setting that had prevailed before the simple offset was selected. This change also causes the timeofday function to honor the simple GMT offset zone.
  • Prevent possible misbehavior when logging translations of Windows error codes (Tom Lane)
  • Properly quote generated command lines in pg_ctl (Naoya Anzai and Tom Lane)
  • This fix applies only to Windows.
  • Fix pg_dumpall to work when a source database sets default_transaction_read_only via ALTER DATABASE SET (Kevin Grittner)
  • Previously, the generated script would fail during restore.
  • Fix pg_isready to handle its -d option properly (Fabrízio de Royes Mello and Fujii Masao)
  • Fix parsing of WAL file names in pg_receivexlog (Heikki Linnakangas)
  • This error made pg_receivexlog unable to restart streaming after stopping, once at least 4 GB of WAL had been written.
  • Report out-of-disk-space failures properly in pg_upgrade (Peter Eisentraut)
  • Make ecpg search for quoted cursor names case-sensitively (Zoltán Böszörményi)
  • Fix ecpg's processing of lists of variables declared varchar (Zoltán Böszörményi)
  • Make contrib/lo defend against incorrect trigger definitions (Marc Cousin)
  • Update time zone data files to tzdata release 2013h for DST law changes in Argentina, Brazil, Jordan, Libya, Liechtenstein, Morocco, and Palestine. Also, new timezone abbreviations WIB, WIT, WITA for Indonesia.

New in version 9.3.1 (October 10th, 2013)

  • In this release are fixes for the recently-released version 9.3, including:
  • Update hstore extension with JSON functionality
  • Prevent memory leak when creating range indexes
  • Fix libpq SSL deadlock bug
  • In addition to the above, there are a few dozen minor bug fixes for older versions. These patches were already included in 9.3.0 when released. Among them are:
  • Guarantee transmission of all WAL files before replica failover
  • Prevent downcasing of non-ASCII identifiers
  • Fix several minor memory leaks
  • Correct overcommit behavior when using more than 24GB of work memory
  • Improve planner cost estimates for choosing generic plans
  • Fix estimates of NULL rows in boolean columns
  • Make UNION ALL and inheritance query plans recheck parameterized paths
  • Correct pg_dump bugs for foreign tables, views, and extensions
  • Prevent a parallel pg_restore failure on certain indexes
  • Make REINDEX revalidate constraints
  • Prevent two deadlock issues in SP-GIST and REINDEX CONCURRENTLY
  • Prevent GiST index lookup crash
  • Fix several regular expression failures
  • Allow ALTER DEFAULT PRIVILEGES to work on all schemas
  • Loosen restrictions on keywords
  • Allow various spellings of infinity
  • Expand ability to compare rows to records and arrays
  • Prevent psql client crash on bad PSQLRC file
  • Add spinlock support for ARM64

New in version 9.3.0 (September 9th, 2013)

  • The PostgreSQL Global Development Group announces the release of PostgreSQL 9.3, the latest version of the world's leading open source relational database system. This release expands PostgreSQL's reliability, availability, and ability to integrate with other databases. Users are already finding that they can build applications using version 9.3 which would not have been possible before.
  • "PostgreSQL 9.3 provides features that as an app developer I can use immediately: better JSON functionality, regular expression indexing, and easily federating databases with the Postgres foreign data wrapper. I have no idea how I completed projects without 9.3," said Jonathan S. Katz, CTO of VenueBook.
  • Writable External Data:
  • Version 9.3 makes PostgreSQL's Foreign Data Wrappers writable, enabling two-way data interchange between systems. Today's complex IT environments involve multiple databases and semi-structured data sources, and PostgreSQL helps you integrate them into a coherent stack. The project has also released postgres_fdw, a higher-performance, read/write PostgreSQL-to-PostgreSQL federation driver.
  • "Writable foreign data wrappers enable us to plug in and seamlessly test various backend alternatives, allowing us to address different needs quickly and prototype intelligently," explained Lee Holloway, Co-founder and Lead Engineer at CloudFlare. "It is exciting to conceive and toss up new data stores (including our in-house experimental ones written in Go) and then watch them read, write, and even transact each other."
  • Enhanced Reliability and Availability:
  • "Postgres has been my favorite choice for its well known stability, robustness, strong coherency, safety, ACID, and SQL conformance," said Pascal Bouchareine, director of research and development at Gandi.net. "I'm especially excited to hear more about the fast failover coming in 9.3."
  • This new release includes features to further improve and extend PostgreSQL's well-known reliability and availability:
  • Data Page Checksums: help administrators quickly detect failing disks and bad hardware that corrupts data
  • Fast Failover: enables sub-second switchoffs from master to replica, supporting "carrier-grade" availability
  • Streaming-Only Remastering: easier, faster reconfiguration of cascading replicas after failover
  • Developer-friendly Features:
  • As with every annual release, PostgreSQL 9.3 includes many features to make working with PostgreSQL easier, more flexible and more fun for application developers, administrators and architects. These features include:
  • Additional JSON constructor and extractor methods
  • Automatically updatable VIEWs
  • Parallel pg_dump to speed backups of large databases
  • LATERAL JOINs
  • Further, PostgreSQL 9.3's User-Defined Background Workers allow developers to write task managers, request handlers, parallel processors, queuing tools and other helper applications to enable PostgreSQL as a workload coordinator. One example of this already released is Mongres, a background worker which accepts MongoDB queries, interprets them and passes them on to PostgreSQL.

New in version 9.3 Beta 1 (May 14th, 2013)

  • Writeable Foreign Tables, enabling pushing data to other databases
  • pgsql_fdw driver for federation of PostgreSQL databases
  • Automatically updatable VIEWs
  • MATERIALIZED VIEW declaration
  • LATERAL JOINs
  • Additional JSON constructor and extractor functions
  • Indexed regular expression search
  • Disk page checksums to detect filesystem failures
  • Fast failover to replicas for high availability
  • Streaming-only remastering of replicas
  • Performance and locking improvements for Foreign Key locks
  • Parallel pg_dump for faster backups
  • Directories for configuration files
  • pg_isready database connection checker
  • COPY FREEZE for reduced IO bulk loading
  • User-defined background workers for automating database tasks
  • Recursive view declaration
  • lock_timeout directive

New in version 9.2.4 (April 4th, 2013)

  • Fix GiST indexes to not use "fuzzy" geometric comparisons for box, polygon, circle, and point columns
  • Fix bugs in contrib/btree_gist for GiST indexes on text, bytea, bit, and numeric columns
  • Fix bugs in page splitting code for multi-column GiST indexes
  • Fix buffer leak in WAL replay causing "incorrect local pin count" errors
  • Ensure crash recovery before entering archive recovery during unclean shutdown when recovery.conf is present
  • Avoid deleting not-yet-archived WAL files during crash recovery
  • Fix race condition in DELETE RETURNING
  • Fix possible planner crash after adding columns to a view depending on another view
  • Eliminate memory leaks in PL/Perl's spi_prepare() function
  • Fix pg_dumpall to handle database names containing "=" correctly
  • Avoid crash in pg_dump when an incorrect connection string is given
  • Ignore invalid indexes in pg_dump and pg_upgrade
  • Include only the current server version's subdirectory when backing up a tablespace with pg_basebackup
  • Add a server version check in pg_basebackup and pg_receivexlog
  • Fix contrib/dblink to handle inconsistent settings of DateStyle or IntervalStyle safely
  • Fix contrib/pg_trgm's similarity() function to return zero for trigram-less strings
  • Enable building PostgreSQL with Microsoft Visual Studio 2012
  • Update time zone data files for DST law changes in Chile, Haiti, Morocco, Paraguay, and some Russian areas

New in version 9.2.3 (February 7th, 2013)

  • Prevent unnecessary table scans during vacuuming
  • Prevent spurious cached plan error in PL/pgSQL
  • Allow sub-SELECTs to be subscripted
  • Prevent DROP OWNED from dropping databases or tablespaces
  • Make ECPG use translated messages
  • Allow PL/Python to use multi-table trigger functions (again) in 9.1 and 9.2
  • Fix several activity log management issues on Windows
  • Prevent autovacuum file truncation from being cancelled by deadlock_timeout
  • Make extensions build with the .exe suffix automatically on Windows
  • Fix concurrency issues with CREATE/DROP DATABASE
  • Reject out-of-range values in to_date() conversion function
  • Revert cost estimation for large indexes back to pre-9.2 behavior
  • Make pg_basebackup tolerate timeline switches
  • Cleanup leftover temp table entries during crash recovery
  • Prevent infinite loop when COPY inserts a large tuple into a table with a large fillfactor
  • Prevent integer overflow in dynahash creation
  • Make pg_upgrade work with INVALID indexes
  • Fix bugs in TYPE privileges
  • Allow Contrib installchecks to run in their own databases
  • Many documentation updates
  • Add new timezone "FET".

New in version 9.2.0 (September 11th, 2012)

  • Improved Performance and Scalability:
  • With the addition of linear scalability to 64 cores, index-only scans and reductions in CPU power consumption, PostgreSQL 9.2 has significantly improved scalability and developer flexibility for the most demanding workloads. Organizations like the U.S. Federal Aviation Administration and Heroku.com run applications on PostgreSQL, and HP has adopted it for their remote support software and to power their HP-UX/Itanium solutions.
  • Improvements in vertical scalability increase PostgreSQL's ability to efficiently utilize hardware resources on larger servers. Advances in lock management, write efficiency, index-only access and other low-level operations allow the database engine to handle even larger-volume workloads.
  • Numerically, this means:
  • Up to 350,000 read queries per second (more than 4X faster)
  • Index-only scans for data warehousing queries (2–20X faster)
  • Up to 14,000 data writes per second (5X faster)
  • Also, the addition of cascading replication enables users to run even larger stacks of horizontally scaled servers under PostgreSQL 9.2.
  • "NewsBlur, a social news reader, relies on Postgres for storing millions of sites and subscriptions. Solid and reliable for years," said Samuel Clay, founder of newsblur.com. "We're consistently on the bleeding edge (9.1 now, moving to 9.2 soon for the cascading replication alone) and it's been a pleasure since 8.4."
  • Flexible Developer Support:
  • The flexibility of PostgreSQL is reflected in the diversity of organisations that have adopted it. For example NASA, the Chicago Mercantile Exchange and Instagram all rely on it to perform mission-critical applications. Version 9.2 extends this flexibility even further by including support for Range Types and JSON, allowing developers to use PostgreSQL in completely new ways.
  • Range Types allow developers to create better calendaring, scientific, and financial applications. No other major SQL database supports this feature, which enables intelligent handling of blocks of time and numbers.
  • With PostgreSQL 9.2, query results can be returned as JSON data types. Combined with the new PL/V8 Javascript and PL/Coffee database programming extensions, and the optional HStore key-value store, users can now utilize PostgreSQL like a "NoSQL" document database, while retaining PostgreSQL's reliability, flexibility and performance.
  • "Native JSON support in PostgresSQL provides an efficient mechanism for creating and storing documents for web APIs. We use front-end libraries like jQuery to request tabular and tree-structured data; and the new features make it convenient and provide performance advantages in retrieving that data as JSON, " said Taras Mitran, Senior Architect, IVC Inc.