Softpedia >Linux >Database >Database Servers >PostgreSQL >  Changelog

PostgreSQL Changelog

What's new in PostgreSQL 10.2

Feb 12, 2018
  • This release fixes two security issues. This release also fixes issues found with VACUUM, GIN & hash indexes, parallel query, logical replication, and other bugs reported over the past three months. All users using the affected versions of PostgreSQL should update as soon as possible.

New in PostgreSQL 9.6.4 (Aug 12, 2017)

  • Further restrict visibility of pg_user_mappings.umoptions, to protect passwords stored as user mapping options (Noah Misch)
  • The fix for CVE-2017-7486 was incorrect: it allowed a user to see the options in her own user mapping, even if she did not have USAGE permission on the associated foreign server. Such options might include a password that had been provided by the server owner rather than the user herself. Since information_schema.user_mapping_options does not show the options in such cases, pg_user_mappings should not either. (CVE-2017-7547)
  • By itself, this patch will only fix the behavior in newly initdb'd databases. If you wish to apply this change in an existing database, you will need to do the following:
  • Restart the postmaster after adding allow_system_table_mods = true to postgresql.conf. (In versions supporting ALTER SYSTEM, you can use that to make the configuration change, but you'll still need a restart.)
  • In each database of the cluster, run the following commands as superuser:
  • SET search_path = pg_catalog;
  • CREATE OR REPLACE VIEW pg_user_mappings AS
  • SELECT
  • U.oid AS umid,
  • S.oid AS srvid,
  • S.srvname AS srvname,
  • U.umuser AS umuser,
  • CASE WHEN U.umuser = 0 THEN
  • 'public'
  • ELSE
  • A.rolname
  • END AS usename,
  • CASE WHEN (U.umuser 0 AND A.rolname = current_user
  • AND (pg_has_role(S.srvowner, 'USAGE')
  • OR has_server_privilege(S.oid, 'USAGE')))
  • OR (U.umuser = 0 AND pg_has_role(S.srvowner, 'USAGE'))
  • OR (SELECT rolsuper FROM pg_authid WHERE rolname = current_user)
  • THEN U.umoptions
  • ELSE NULL END AS umoptions
  • FROM pg_user_mapping U
  • LEFT JOIN pg_authid A ON (A.oid = U.umuser) JOIN
  • pg_foreign_server S ON (U.umserver = S.oid);
  • Do not forget to include the template0 and template1 databases, or the vulnerability will still exist in databases you create later. To fix template0, you'll need to temporarily make it accept connections. In PostgreSQL 9.5 and later, you can use
  • ALTER DATABASE template0 WITH ALLOW_CONNECTIONS true;
  • and then after fixing template0, undo that with
  • ALTER DATABASE template0 WITH ALLOW_CONNECTIONS false;
  • In prior versions, instead use
  • UPDATE pg_database SET datallowconn = true WHERE datname = 'template0';
  • UPDATE pg_database SET datallowconn = false WHERE datname = 'template0';
  • Finally, remove the allow_system_table_mods configuration setting, and again restart the postmaster.
  • Disallow empty passwords in all password-based authentication methods (Heikki Linnakangas)
  • libpq ignores empty password specifications, and does not transmit them to the server. So, if a user's password has been set to the empty string, it's impossible to log in with that password via psql or other libpq-based clients. An administrator might therefore believe that setting the password to empty is equivalent to disabling password login. However, with a modified or non-libpq-based client, logging in could be possible, depending on which authentication method is configured. In particular the most common method, md5, accepted empty passwords. Change the server to reject empty passwords in all cases. (CVE-2017-7546)
  • Make lo_put() check for UPDATE privilege on the target large object (Tom Lane, Michael Paquier)
  • lo_put() should surely require the same permissions as lowrite(), but the check was missing, allowing any user to change the data in a large object. (CVE-2017-7548)
  • Correct the documentation about the process for upgrading standby servers with pg_upgrade (Bruce Momjian)
  • The previous documentation instructed users to start/stop the primary server after running pg_upgrade but before syncing the standby servers. This sequence is unsafe.
  • Fix concurrent locking of tuple update chains (Álvaro Herrera)
  • If several sessions concurrently lock a tuple update chain with nonconflicting lock modes using an old snapshot, and they all succeed, it was possible for some of them to nonetheless fail (and conclude there is no live tuple version) due to a race condition. This had consequences such as foreign-key checks failing to see a tuple that definitely exists but is being updated concurrently.
  • Fix potential data corruption when freezing a tuple whose XMAX is a multixact with exactly one still-interesting member (Teodor Sigaev)
  • Avoid integer overflow and ensuing crash when sorting more than one billion tuples in-memory (Sergey Koposov)
  • On Windows, retry process creation if we fail to reserve the address range for our shared memory in the new process (Tom Lane, Amit Kapila)
  • This is expected to fix infrequent child-process-launch failures that are probably due to interference from antivirus products.
  • Fix low-probability corruption of shared predicate-lock hash table in Windows builds (Thomas Munro, Tom Lane)
  • Avoid logging clean closure of an SSL connection as though it were a connection reset (Michael Paquier)
  • Prevent sending SSL session tickets to clients (Tom Lane)
  • This fix prevents reconnection failures with ticket-aware client-side SSL code.
  • Fix code for setting tcp_keepalives_idle on Solaris (Tom Lane)
  • Fix statistics collector to honor inquiry messages issued just after a postmaster shutdown and immediate restart (Tom Lane)
  • Statistics inquiries issued within half a second of the previous postmaster shutdown were effectively ignored.
  • Ensure that the statistics collector's receive buffer size is at least 100KB (Tom Lane)
  • This reduces the risk of dropped statistics data on older platforms whose default receive buffer size is less than that.
  • Fix possible creation of an invalid WAL segment when a standby is promoted just after it processes an XLOG_SWITCH WAL record (Andres Freund)
  • Fix walsender to exit promptly when client requests shutdown (Tom Lane)
  • Fix SIGHUP and SIGUSR1 handling in walsender processes (Petr Jelinek, Andres Freund)
  • Prevent walsender-triggered panics during shutdown checkpoints (Andres Freund, Michael Paquier)
  • Fix unnecessarily slow restarts of walreceiver processes due to race condition in postmaster (Tom Lane)
  • Fix leakage of small subtransactions spilled to disk during logical decoding (Andres Freund)
  • This resulted in temporary files consuming excessive disk space.
  • Reduce the work needed to build snapshots during creation of logical-decoding slots (Andres Freund, Petr Jelinek)
  • The previous algorithm was infeasibly expensive on a server with a lot of open transactions.
  • Fix race condition that could indefinitely delay creation of logical-decoding slots (Andres Freund, Petr Jelinek)
  • Reduce overhead in processing syscache invalidation events (Tom Lane)
  • This is particularly helpful for logical decoding, which triggers frequent cache invalidation.
  • Remove incorrect heuristic used in some cases to estimate join selectivity based on the presence of foreign-key constraints (David Rowley)
  • In some cases where a multi-column foreign key constraint existed but did not exactly match a query's join structure, the planner used an estimation heuristic that turns out not to work well at all. Revert such cases to the way they were estimated before 9.6.
  • Fix cases where an INSERT or UPDATE assigns to more than one element of a column that is of domain-over-array type (Tom Lane)
  • Allow window functions to be used in sub-SELECTs that are within the arguments of an aggregate function (Tom Lane)
  • Ensure that a view's CHECK OPTIONS clause is enforced properly when the underlying table is a foreign table (Etsuro Fujita)
  • Previously, the update might get pushed entirely to the foreign server, but the need to verify the view conditions was missed if so.
  • Move autogenerated array types out of the way during ALTER ... RENAME (Vik Fearing)
  • Previously, we would rename a conflicting autogenerated array type out of the way during CREATE; this fix extends that behavior to renaming operations.
  • Fix dangling pointer in ALTER TABLE when there is a comment on a constraint belonging to the table (David Rowley)
  • Re-applying the comment to the reconstructed constraint could fail with a weird error message, or even crash.
  • Ensure that ALTER USER ... SET accepts all the syntax variants that ALTER ROLE ... SET does (Peter Eisentraut)
  • Allow a foreign table's CHECK constraints to be initially NOT VALID (Amit Langote)
  • CREATE TABLE silently drops NOT VALID specifiers for CHECK constraints, reasoning that the table must be empty so the constraint can be validated immediately. But this is wrong for CREATE FOREIGN TABLE, where there's no reason to suppose that the underlying table is empty, and even if it is it's no business of ours to decide that the constraint can be treated as valid going forward. Skip this "optimization" for foreign tables.
  • Properly update dependency info when changing a datatype I/O function's argument or return type from opaque to the correct type (Heikki Linnakangas)
  • CREATE TYPE updates I/O functions declared in this long-obsolete style, but it forgot to record a dependency on the type, allowing a subsequent DROP TYPE to leave broken function definitions behind.
  • Allow parallelism in the query plan when COPY copies from a query's result (Andres Freund)
  • Reduce memory usage when ANALYZE processes a tsvector column (Heikki Linnakangas)
  • Fix unnecessary precision loss and sloppy rounding when multiplying or dividing money values by integers or floats (Tom Lane)
  • Tighten checks for whitespace in functions that parse identifiers, such as regprocedurein() (Tom Lane)
  • Depending on the prevailing locale, these functions could misinterpret fragments of multibyte characters as whitespace.
  • Use relevant #define symbols from Perl while compiling PL/Perl (Ashutosh Sharma, Tom Lane)
  • This avoids portability problems, typically manifesting as a "handshake" mismatch during library load, when working with recent Perl versions.
  • In libpq, reset GSS/SASL and SSPI authentication state properly after a failed connection attempt (Michael Paquier)
  • Failure to do this meant that when falling back from SSL to non-SSL connections, a GSS/SASL failure in the SSL attempt would always cause the non-SSL attempt to fail. SSPI did not fail, but it leaked memory.
  • In psql, fix failure when COPY FROM STDIN is ended with a keyboard EOF signal and then another COPY FROM STDIN is attempted (Thomas Munro)
  • This misbehavior was observed on BSD-derived platforms (including macOS), but not on most others.
  • Fix pg_dump and pg_restore to emit REFRESH MATERIALIZED VIEW commands last (Tom Lane)
  • This prevents errors during dump/restore when a materialized view refers to tables owned by a different user.
  • Improve pg_dump/pg_restore's reporting of error conditions originating in zlib (Vladimir Kunschikov, Álvaro Herrera)
  • Fix pg_dump with the --clean option to drop event triggers as expected (Tom Lane)
  • It also now correctly assigns ownership of event triggers; before, they were restored as being owned by the superuser running the restore script.
  • Fix pg_dump with the --clean option to not fail when the public schema doesn't exist (Stephen Frost)
  • Fix pg_dump to not emit invalid SQL for an empty operator class (Daniel Gustafsson)
  • Fix pg_dump output to stdout on Windows (Kuntal Ghosh)
  • A compressed plain-text dump written to stdout would contain corrupt data due to failure to put the file descriptor into binary mode.
  • Fix pg_get_ruledef() to print correct output for the ON SELECT rule of a view whose columns have been renamed (Tom Lane)
  • In some corner cases, pg_dump relies on pg_get_ruledef() to dump views, so that this error could result in dump/reload failures.
  • Fix dumping of outer joins with empty constraints, such as the result of a NATURAL LEFT JOIN with no common columns (Tom Lane)
  • Fix dumping of function expressions in the FROM clause in cases where the expression does not deparse into something that looks like a function call (Tom Lane)
  • Fix pg_basebackup output to stdout on Windows (Haribabu Kommi)
  • A backup written to stdout would contain corrupt data due to failure to put the file descriptor into binary mode.
  • Fix pg_rewind to correctly handle files exceeding 2GB (Kuntal Ghosh, Michael Paquier)
  • Ordinarily such files won't appear in PostgreSQL data directories, but they could be present in some cases.
  • Fix pg_upgrade to ensure that the ending WAL record does not have wal_level = minimum (Bruce Momjian)
  • This condition could prevent upgraded standby servers from reconnecting.
  • Fix pg_xlogdump's computation of WAL record length (Andres Freund)
  • In postgres_fdw, re-establish connections to remote servers after ALTER SERVER or ALTER USER MAPPING commands (Kyotaro Horiguchi)
  • This ensures that option changes affecting connection parameters will be applied promptly.
  • In postgres_fdw, allow cancellation of remote transaction control commands (Robert Haas, Rafia Sabih)
  • This change allows us to quickly escape a wait for an unresponsive remote server in many more cases than previously.
  • Increase MAX_SYSCACHE_CALLBACKS to provide more room for extensions (Tom Lane)
  • Always use -fPIC, not -fpic, when building shared libraries with gcc (Tom Lane)
  • This supports larger extension libraries on platforms where it makes a difference.
  • In MSVC builds, handle the case where the openssl library is not within a VC subdirectory (Andrew Dunstan)
  • In MSVC builds, add proper include path for libxml2 header files (Andrew Dunstan)
  • This fixes a former need to move things around in standard Windows installations of libxml2.
  • In MSVC builds, recognize a Tcl library that is named tcl86.lib (Noah Misch)
  • In MSVC builds, honor PROVE_FLAGS settings on vcregress.pl's command line (Andrew Dunstan)

New in PostgreSQL 9.6.3 (May 12, 2017)

  • Security Issues:
  • CVE-2017-7484: selectivity estimators bypass SELECT privilege checks
  • CVE-2017-7485: libpq ignores PGREQUIRESSL environment variable
  • CVE-2017-7486: pg_user_mappings view discloses foreign server passwords
  • The fix for CVE-2017-7486 applies to new databases, see the release notes for the procedure to apply the fix to an existing database.
  • Any user relying on the PGREQUIRESSL environment variable is encouraged to use the sslmode connection string option, as use of PGREQUIRESSL is deprecated. CVE-2017-7485 does not affect the 9.2 series. For more information on these issues and how they affect backwards-compatibility, see the Release Notes.
  • Bug Fixes and Improvements:
  • Fix to ensure consistent behavior for RLS policies
  • Fix ALTER TABLE ... VALIDATE CONSTRAINT to not recurse to child tables when the constraint is marked NO INHERIT
  • Fix incorrect support for certain box operators in SP-GiST which could yield incorrect results
  • Fixes for handling query cancellation
  • Skip tablespace privilege checks when ALTER TABLE ... ALTER COLUMN TYPE rebuilds an existing index
  • Fix possibly-invalid initial snapshot during logical decoding
  • Fix possible corruption of init forks of unlogged indexes
  • Several fixes to postmaster, including checks for when running as a Windows service
  • Several planner fixes, among others assorted minor fixes in planning of parallel queries
  • Avoid possible crashes in walsender and some index-only scans on GiST index
  • Fix cancelling of pg_stop_backup() when attempting to stop a non-exclusive backup
  • Updates to ecpg to support COMMIT PREPARED and ROLLBACK PREPARED
  • Several fixes for pg_dump/pg_restore, among others to handle privileges for procedural languages and when using --clean option
  • Several fixes for contrib modules, such as dblink, pg_trgm and postgres_fdw
  • Fixes to MSVC builds, such as using correct daylight-savings rules for POSIX-style time zone names and supporting Tcl 8.6
  • Several performance improvements
  • Fix cursor_to_xml() to produce valid output with tableforest = false
  • Fix roundoff problems in float8_timestamptz() and make_interval()
  • Fix pgbench to handle the combination of --connect and --rate options correctly
  • Fixes to commandline tools such as pg_upgrade and pg_basebackup
  • Several fixes to VACUUM and CLUSTER

New in PostgreSQL 9.6.1 (Oct 29, 2016)

  • The PostgreSQL Global Development Group has released an update to all supported versions of our database system, including 9.6.1, 9.5.5, 9.4.10, 9.3.15, 9.2.19, and 9.1.24. This is also the last update for the PostgreSQL 9.1 series as it is now end-of-life. This release fixes two issues that can cause data corruption, which are described in more detail below. It also patches a number of other bugs reported over the last three months. The project urges users to apply this update at the next possible downtime.
  • WAL-logging of truncated relations:
  • Prior to this release, there was a chance that a PostgreSQL instance would try to access data that no longer existed on disk. If the free space map was not updated to be aware of the truncation, a PostgreSQL database could return a page that was already truncated and produce an error such as:
  • ERROR: could not read block 28991 in file "base/16390/572026": read only 0 of 8192 bytes
  • If checksumming is enabled, checksum failures in the visibility map could also occur.
  • This issue is present in the 9.3, 9.4, 9.5, and 9.6 series of PostgreSQL releases.
  • pg_upgrade issues on big-endian machines
  • On big-endian machines (e.g. many non-Intel CPU architectures), pg_upgrade would incorrectly write the bytes of the visibility map leading to pg_upgrade failing to complete.
  • If you are using a big-endian machine (many non-Intel architectures are big-endian) and have used pg_upgrade to upgrade from a pre-9.6 release, you should assume that all visibility maps are incorrect and need to be regenerated. It is sufficient to truncate each relation's visibility map with contrib/pg_visibility's pg_truncate_visibility_map() function. Please read the "Updating" section for post-installation instructions on how to resolve this issue on your PostgreSQL instances.
  • This issue is present only in the PostgreSQL 9.6.0 release.
  • Bug Fixes and Improvements:
  • In addition to the above, this update also fixes a number of bugs reported in the last few months. Some of these issues affect only the 9.6 series, but many affect all supported versions. There are more than 50 fixes provided in this release, including...
  • Fix use-after-free hazard in execution of aggregate functions using DISTINCT, which could lead to crashes
  • Fix incorrect handling of polymorphic aggregates used as window functions, which could lead to crashes
  • Fix incorrect creation of GIN index WAL records on big-endian machines
  • Fix file descriptor leakage when truncating a temporary relation of more than 1GB
  • Fix query-lifespan memory leak in a bulk UPDATE on a table with a PRIMARY KEY or REPLICA IDENTITY index
  • Fix SELECT FOR UPDATE/SHARE to correctly lock tuples that have been updated by a subsequently-aborted transaction
  • Fix COPY with a column name list from a table that has row-level security enabled
  • Fix deletion of speculatively inserted TOAST tuples when backing out of INSERT ... ON CONFLICT
  • Fix timeout length when VACUUM is waiting for exclusive table lock so that it can truncate the table
  • Fix bugs in merging inherited CHECK constraints while creating or altering a table
  • Fix replacement of array elements in jsonb_set()
  • Fix possible sorting error when aborting use of abbreviated keys in btree indexes
  • On Windows, retry creation of the dynamic shared memory control segment after an access-denied error
  • Fix pgbench's calculation of average latency
  • Make pg_receivexlog work correctly with --synchronous without slots
  • Make pg_rewind turn off synchronous_commit in its session on the source servere
  • Don't try to share SSL contexts across multiple connections in libpq
  • Support OpenSSL 1.1.0
  • Install TAP test infrastructure so that it's available for extension testing
  • Several fixes for logical WAL decoding and replication slots
  • Several fixes for minor issues in pg_dump, pg_xlogdump, and pg_upgrade
  • Several fixes for minor issues in the query planner and in the output of EXPLAIN
  • Several fixes for timezone support
  • This update also contains tzdata release 2016h for DST law changes in Palestine and Turkey, plus historical corrections for Turkey and some regions of Russia. Switch to numeric abbreviations for some time zones in Antarctica, the former Soviet Union, and Sri Lanka.
  • The IANA time zone database previously provided textual abbreviations for all time zones, sometimes making up abbreviations that have little or no currency among the local population. They are in process of reversing that policy in favor of using numeric UTC offsets in zones where there is no evidence of real-world use of an English abbreviation. At least for the time being, PostgreSQL will continue to accept such removed abbreviations for timestamp input. But they will not be shown in the pg_timezone_names view nor used for output.
  • In this update, AMT is no longer shown as being in use to mean Armenia Time. Therefore, we have changed the Default abbreviation set to interpret it as Amazon Time, thus UTC-4 not UTC+4.

New in PostgreSQL 9.6.0 (Sep 29, 2016)

  • Scale Up with Parallel Query:
  • Version 9.6 adds support for parallelizing some query operations, enabling utilization of several or all of the cores on a server to return query results faster. This release includes parallel sequential (table) scan, aggregation, and joins. Depending on details and available cores, parallelism can speed up big data queries by as much as 32 times faster.
  • "I migrated our entire genomics data platform - all 25 billion legacy MySQL rows of it - to a single Postgres database, leveraging the row compression abilities of the JSONB datatype, and the excellent GIN, BRIN, and B-tree indexing modes. Now with version 9.6, I expect to harness the parallel query functionality to allow even greater scalability for queries against our rather large tables," said Mike Sofen, Chief Database Architect, Synthetic Genomics.
  • Scale Out with Synchronous Replication and postgres_fdw:
  • Two new options have been added to PostgreSQL's synchronous replication feature which allow it to be used to maintain consistent reads across database clusters. First, it now allows configuring groups of synchronous replicas. Second, the "remote_apply" mode creates a more consistent view of data across multiple nodes. These features support using built-in replication to maintain a set of "identical" nodes for load-balancing read workloads.
  • The PostgreSQL-to-PostgreSQL data federation driver, postgres_fdw, has new capabilities to execute work on remote servers. By "pushing down" sorts, joins, and batch data updates, users can distribute workload across multiple PostgreSQL servers. These features should soon be added to other FDW drivers.
  • "With the capabilities of remote JOIN, UPDATE and DELETE, Foreign Data Wrappers are now a complete solution for sharing data between other databases and PostgreSQL. For example, PostgreSQL can be used to handle data input going to two or more different kinds of databases," said Julyanto Sutandang, Director of Business Solutions at Equnix.
  • Better Text Search with Phrases:
  • PostgreSQL's full text search feature now supports "phrase search." This lets users search for exact phrases, or for words within a specified proximity to each other, using fast GIN indexes. Combined with new features for fine-tuning text search options, PostgreSQL is the superior option for "hybrid search" which puts together relational, JSON, and full text searching.
  • Smoother, Faster, and Easier to Use:
  • Thanks to feedback and testing by PostgreSQL users with high-volume production databases, the project has been able to improve many aspects of performance and usability in this release. Replication, aggregation, indexing, sorting, and stored procedures have all been made more efficient, and PostgreSQL now makes better use of resources with recent Linux kernels. Administration overhead for large tables and complex workloads was also reduced, especially through improvements to VACUUM.
  • Other Features:
  • New system views and functions: pg_stat_wal_receiver, pg_visibility, pg_config, pg_blocking_pids, pg_notification_queue_usage
  • Command progress reporting support
  • Cascade support for installing extensions
  • pg_basebackup concurrency and replication slot support
  • Wait Event support
  • View editing and crosstabs in psql
  • User-defined expiration for old snapshots
  • Index-only scans for partial indexes

New in PostgreSQL 9.6 Beta 1 (May 13, 2016)

  • Parallel sequential scans, joins and aggregates
  • Support for consistent, read-scaling clusters through multiple synchronous standbys and "remote_apply" synchronous commit.
  • Full text search for phrases
  • postgres_fdw can now execute sorts, joins, UPDATEs and DELETEs on the remote server
  • Decreased autovacuum impact on big tables by avoiding "refreezing" old data.

New in PostgreSQL 9.5.2 (Apr 1, 2016)

  • This is an update release that fixes two security issues in version 9.5 as well as several bugs in all current releases. In particular, the 9.5.2 release disables an optimization present in 9.5 and newer that can lead to corruption of text indexes in non-C locales. Users of 9.5 should update as soon as convenient and REINDEX potentially corrupt indexes.

New in PostgreSQL 9.5.1 (Feb 11, 2016)

  • Fix many issues in pg_dump with specific object types
  • Prevent over-eager pushdown of HAVING clauses for GROUPING SETS
  • Fix deparsing error with ON CONFLICT ... WHERE clauses
  • Fix tableoid errors for postgres_fdw
  • Prevent floating-point exceptions in pgbench
  • Make \det search Foreign Table names consistently
  • Fix quoting of domain constraint names in pg_dump
  • Prevent putting expanded objects into Const nodes
  • Allow compile of PL/Java on Windows
  • Fix "unresolved symbol" errors in PL/Python execution
  • Allow Python2 and Python3 to be used in the same database
  • Add support for Python 3.5 in PL/Python
  • Fix issue with subdirectory creation during initdb
  • Make pg_ctl report status correctly on Windows
  • Suppress confusing error when using pg_receivexlog with older servers
  • Multiple documentation corrections and additions
  • Fix erroneous hash calculations in gin_extract_jsonb_path()

New in PostgreSQL 9.5.0 (Jan 8, 2016)

  • UPSERT:
  • A most-requested feature by application developers for several years, "UPSERT" is shorthand for "INSERT, ON CONFLICT UPDATE", allowing new and updated rows to be treated the same. UPSERT simplifies web and mobile application development by enabling the database to handle conflicts between concurrent data changes. This feature also removes the last significant barrier to migrating legacy MySQL applications to PostgreSQL.
  • Developed over the last two years by Heroku programmer Peter Geoghegan, PostgreSQL's implementation of UPSERT is significantly more flexible and powerful than those offered by other relational databases. The new ON CONFLICT clause permits ignoring the new data, or updating different columns or relations in ways which will support complex ETL (Extract, Transform, Load) toolchains for bulk data loading. And, like all of PostgreSQL, it is designed to be absolutely concurrency-safe and to integrate with all other PostgreSQL features, including Logical Replication.
  • Row Level Security:
  • PostgreSQL continues to expand database security capabilities with its new Row Level Security (RLS) feature. RLS implements true per-row and per-column data access control which integrates with external label-based security stacks such as SE Linux. PostgreSQL is already known as "the most secure by default." RLS cements its position as the best choice for applications with strong data security requirements, such as compliance with PCI, the European Data Protection Directive, and healthcare data protection standards.
  • RLS is the culmination of five years of security features added to PostgreSQL, including extensive work by KaiGai Kohei of NEC, Stephen Frost of Crunchy Data, and Dean Rasheed. Through it, database administrators can set security "policies" which filter which rows particular users are allowed to update or view. Data security implemented this way is resistant to SQL injection exploits and other application-level security holes.
  • Big Data Features:
  • PostgreSQL 9.5 includes multiple new features for bigger databases, and for integrating with other Big Data systems. These features ensure that PostgreSQL continues to have a strong role in the rapidly growing open source Big Data marketplace. Among them are...
  • BRIN Indexing: This new type of index supports creating tiny, but effective indexes for very large, "naturally ordered" tables. For example, tables containing logging data with billions of rows could be indexed and searched in 5% of the time required by standard BTree indexes.
  • Faster Sorts: PostgreSQL now sorts text and NUMERIC data faster, using an algorithm called "abbreviated keys". This makes some queries which need to sort large amounts of data 2X to 12X faster, and can speed up index creation by 20X.
  • CUBE, ROLLUP and GROUPING SETS: These new standard SQL clauses let users produce reports with multiple levels of summarization in one query instead of requiring several. CUBE will also enable tightly integrating PostgreSQL with more Online Analytic Processing (OLAP) reporting tools such as Tableau.
  • Foreign Data Wrappers (FDWs): These already allow using PostgreSQL as a query engine for other Big Data systems such as Hadoop and Cassandra. Version 9.5 adds IMPORT FOREIGN SCHEMA and JOIN pushdown making query connections to external databases both easier to set up and more efficient.
  • TABLESAMPLE: This SQL clause allows grabbing a quick statistical sample of huge tables, without the need for expensive sorting.

New in PostgreSQL 9.4.1 (Feb 5, 2015)

  • These new releases contain security and bug fixes over previous releases. All users should plan upgrade their systems as soon as possible.

New in PostgreSQL 9.4.0 (Dec 18, 2014)

  • Flexibility:
  • With the new JSONB data type for PostgreSQL, users no longer have to choose between relational and non-relational data stores: they can have both at the same time. JSONB supports fast lookups and simple expression search queries using Generalized Inverted Indexes (GIN). Multiple new support functions enable users to extract and manipulate JSON data, with a performance which matches or surpasses the most popular document databases. With JSONB, table data can be easily integrated with document data for a fully integrated database environment.
  • "JSONB brings PostgreSQL to the JavaScript development community by allowing JSON data to be stored and queried natively. Node.js and other server-side JavaScript frameworks can benefit from the safety and robustness of PostgreSQL, while still storing data in the schema-less format that they prefer," said Matt Soldo, Product Manager, Heroku Postgres.
  • Scalability:
  • In 9.4, Logical Decoding supplies a new API for reading, filtering and manipulating the PostgreSQL replication stream. This interface is the foundation for new replication tools, such as Bi-Directional Replication, which supports the creation of multi-master PostgreSQL clusters. Other improvements in the replication system, such as replication slots and time-delayed replicas, improve management and utility of replica servers.
  • "The main reason behind our immediate adoption of PostgreSQL 9.4 in production is the new Logical Decoding feature," said Marco Favale, Cartographic Production Manager of Navionics. "The ability to write custom and flexible output plugins will allow us to transparently collect changes on selected tables and replicate changes where we like, by removing heavier - and more complex to manage - trigger based replication solutions."
  • "Zalando relies on the stability and performance of hundreds of PostgreSQL database servers to continuously serve millions of customers all around Europe," said Valentine Gogichashvili, Team Lead Database Operations at Zalando Technologies. "We are excited to run time-delayed standby servers which work out of the box, and will evaluate the new bi-directional replication tools as soon as they are released."
  • Performance:
  • Version 9.4 also introduces multiple performance improvements which will allow users to get even more out of each PostgreSQL server. These include:
  • Improvements to GIN indexes, making them up to 50% smaller and up to 3X faster.
  • Concurrently updatable Materialized Views for faster, more up-to-date reporting.
  • Rapidly reload the database cache on restart using pg_prewarm.
  • Faster parallel writing to the PostgreSQL transaction log.
  • Support for Linux Huge Pages for servers with large memory.
  • "We will definitely benefit from concurrent refresh of materialised views, delayed standby servers (which will make our disaster recovery even more robust) as well as the usual performance improvements every new release carries on," added Marco Favale.

New in PostgreSQL 9.3.5 (Sep 8, 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 PostgreSQL 9.3.3 (Feb 20, 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 PostgreSQL 9.3.2 (Dec 5, 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 PostgreSQL 9.3.1 (Oct 10, 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 PostgreSQL 9.3.0 (Sep 9, 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 PostgreSQL 9.3 Beta 1 (May 14, 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 PostgreSQL 9.2.4 (Apr 4, 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 PostgreSQL 9.2.3 (Feb 7, 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 PostgreSQL 9.2.0 (Sep 11, 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.

New in PostgreSQL 9.0.4 (Apr 20, 2011)

  • fix build issues on HP-UX and Itanium
  • update time zone files for recent time zone changes
  • change SQLSTATE for Hot Standby warnings
  • prevent bgwriter hang during recovery
  • prevent recursive composite type creation
  • disallow dropping tables whose triggers are still pending
  • allow use of "replication" as a user name
  • prevent a crash during GEQO planning
  • improve join plans for tables with missing statistics
  • fix error with SELECT FOR UPDATE in a subselect
  • close PL/python array slice memory leak
  • allow SSL connections for users with no home directory
  • allow building PostgreSQL 9.0 with 64-bit MinGW

New in PostgreSQL 9.0.3 (Jan 31, 2011)

  • Avoid failure when trying to report an impending transaction wraparound condition from outside a transaction. This oversight prevented recovery after transaction wraparound got too close, because database startup processing would fail, backpatched to all versions.
  • Fix recovery from base backup when the starting checkpoint WAL record is not in the same WAL segment as its redo point, backpatched to all supported versions.
  • Force default wal_sync_method to be fdatasync for Linux, backpatched to all supported versions.
  • Fix memory leak with ANALYZE and complex index expressions, backpatched to all supported versions.
  • Fix memory leak in contrib/xml2 functions, backpatched to all supported versions.
  • Fix for segfault in pgcrypto when attempting to access an undefined public key, backpatched to all supported versions.
  • Prevent inlining an SQL function with multiple OUT parameters, backpatched to all supported versions.
  • Fix bugs in WAL replay logic for GIN indexes, backpatched to 8.2.
  • Fix segfault when accept() or a call made right after accepting a connection fails when server is compiled with GSSAPI support, backpatched to 8.3.
  • Fix error handling in tempfile deletion with log_temp_files active, backpatched to 8.3.
  • Prevent slowing of of autovacuum workers because of leakage in cost parameter over life of worker, backpatched to 8.3.
  • Check for too many children before returning "okay to proceed", backpatched to 8.4.
  • Improve performance of internal tuplestore structures for window functions, backpatched to 8.4.

New in PostgreSQL 9.0 Beta 4 (Aug 10, 2010)

  • Report disk space error properly and do not load .psqlrc when using pg_upgrade;
  • Allow full SSL certificate verification when host and hostaddr are both specified, backpatched to 8.4;
  • Fix for pg_dump's handling of SQL/MED objects;
  • Bugfix and performance improvement for scans of lossy GIN index pointers;
  • Fix for possible page corruption in ALTER TABLE .. SET TABLESPACE;
  • Properly replay CREATE TABLESPACE during crash recovery;
  • Cleanup subtransactions in FOR loops, and avoid deep recursion when assigning XIDs to multiple levels of subtransactions, both backpatched to 8.0.

New in PostgreSQL 8.4 Beta 2 (May 28, 2009)

  • In order to improve the usability of pg_standby, we have added "fast" and "smart" mode switches to the utility. This is an API change. The "smart" mode ensures that all logs are applied for zero data loss, while the "fast" mode is similar to prior behavior. The behavior of the trigger file has also changed. Developers of PostgreSQL administration tools should modify them to support the new API.
  • Additionally, the following features and utilities were fixed or modified between Beta 1 and Beta 2
  • SQL/XML
  • PL/pgSQL
  • pg_bench
  • JOIN costing
  • Encoding and collation handling
  • SSL connections
  • psql df
  • Unicode escapes
  • Parallel restore
  • pg_dumpall

New in PostgreSQL 8.4 Beta 1 (Apr 17, 2009)

  • Windowing Functions
  • Common Table Expressions & Recursive Joins
  • Default & Variadic parameters for functions
  • Parallel Restore
  • Column Permissions
  • Per-database locale settings
  • Improved hash indexes
  • Improved join performance for EXISTS and NOT EXISTS queries
  • Easier-to-use Warm Standby
  • Free Space Map auto-tuning
  • Visibility Map (reduces vacuum overhead)
  • Version-aware psql (backslash commands)
  • Support SSL certs for user authentication
  • Per-function runtime statistics
  • Easy editing of functions in psql
  • New contrib modules: pg_stat_statements, auto_explain, citext, btree_gin