| PostgreSQL 9.5.25 Documentation | |||
|---|---|---|---|
| Prev | Up | Appendix E. Release Notes | Next |
Release date: 2016-01-07
Major enhancements in PostgreSQL 9.5 include:
Allow INSERTs that would generate constraint conflicts to be turned into UPDATEs or ignored
Add GROUP BY analysis features GROUPING SETS, CUBE and ROLLUP
Create mechanisms for tracking the progress of replication, including methods for identifying the origin of individual changes during logical replication
Add Block Range Indexes (BRIN)
Substantial performance improvements for sorting
Substantial performance improvements for multi-CPU machines
The above items are explained in more detail in the sections below.
A dump/restore using pg_dumpall, or use of pg_upgrade, is required for those wishing to migrate data from any previous release.
Version 9.5 contains a number of changes that may affect compatibility with previous releases. Observe the following incompatibilities:
Adjust operator precedence to match the SQL standard (Tom Lane)
The precedence of <=, >= and <> has been reduced to match that of <, > and =. The precedence of IS tests (e.g., x IS NULL) has been reduced to be just below these six comparison operators. Also, multi-keyword operators beginning with NOT now have the precedence of their base operator (for example, NOT BETWEEN now has the same precedence as BETWEEN) whereas before they had inconsistent precedence, behaving like NOT with respect to their left operand but like their base operator with respect to their right operand. The new configuration parameter operator_precedence_warning can be enabled to warn about queries in which these precedence changes result in different parsing choices.
Change pg_ctl's default shutdown mode from smart to fast (Bruce Momjian)
This means the default behavior will be to forcibly cancel existing database sessions, not simply wait for them to exit.
Use assignment cast behavior for data type conversions in PL/pgSQL assignments, rather than converting to and from text (Tom Lane)
This change causes conversions of Booleans to strings to produce true or false, not t or f. Other type conversions may succeed in more cases than before; for example, assigning a numeric value 3.9 to an integer variable will now assign 4 rather than failing. If no assignment-grade cast is defined for the particular source and destination types, PL/pgSQL will fall back to its old I/O conversion behavior.
Allow characters in server command-line options to be escaped with a backslash (Andres Freund)
Formerly, spaces in the options string always separated options, so there was no way to include a space in an option value. Including a backslash in an option value now requires writing \\.
Change the default value of the GSSAPI include_realm parameter to 1, so that by default the realm is not removed from a GSS or SSPI principal name (Stephen Frost)
Replace configuration parameter checkpoint_segments with min_wal_size and max_wal_size (Heikki Linnakangas)
If you previously adjusted checkpoint_segments, the following formula will give you an approximately equivalent setting:
max_wal_size = (3 * checkpoint_segments) * 16MB
Note that the default setting for max_wal_size is much higher than the default checkpoint_segments used to be, so adjusting it might no longer be necessary.
Control the Linux OOM killer via new environment variables PG_OOM_ADJUST_FILE and PG_OOM_ADJUST_VALUE, instead of compile-time options LINUX_OOM_SCORE_ADJ and LINUX_OOM_ADJ (Gurjeet Singh)
Decommission server configuration parameter ssl_renegotiation_limit, which was deprecated in earlier releases (Andres Freund)
While SSL renegotiation is a good idea in theory, it has caused enough bugs to be considered a net negative in practice, and it is due to be removed from future versions of the relevant standards. We have therefore removed support for it from PostgreSQL. The ssl_renegotiation_limit parameter still exists, but cannot be set to anything but zero (disabled). It's not documented anymore, either.
Remove server configuration parameter autocommit, which was already deprecated and non-operational (Tom Lane)
Remove the pg_authid catalog's rolcatupdate field, as it had no usefulness (Adam Brightwell)
The pg_stat_replication system view's sent field is now NULL, not zero, when it has no valid value (Magnus Hagander)
Allow json and jsonb array extraction operators to accept negative subscripts, which count from the end of JSON arrays (Peter Geoghegan, Andrew Dunstan)
Previously, these operators returned NULL for negative subscripts.
Below you will find a detailed account of the changes between PostgreSQL 9.5 and the previous major release.
Add Block Range Indexes (BRIN) (Álvaro Herrera)
BRIN indexes store only summary data (such as minimum and maximum values) for ranges of heap blocks. They are therefore very compact and cheap to update; but if the data is naturally clustered, they can still provide substantial speedup of searches.
Allow queries to perform accurate distance filtering of bounding-box-indexed objects (polygons, circles) using GiST indexes (Alexander Korotkov, Heikki Linnakangas)
Previously, to exploit such an index a subquery had to be used to select a large number of rows ordered by bounding-box distance, and the result then had to be filtered further with a more accurate distance calculation.
Allow GiST indexes to perform index-only scans (Anastasia Lubennikova, Heikki Linnakangas, Andreas Karlsson)
Add configuration parameter gin_pending_list_limit to control the size of GIN pending lists (Fujii Masao)
This value can also be set on a per-index basis as an index storage parameter. Previously the pending-list size was controlled by work_mem, which was awkward because appropriate values for work_mem are often much too large for this purpose.
Issue a warning during the creation of hash indexes because they are not crash-safe (Bruce Momjian)
Improve the speed of sorting of varchar, text, and numeric fields via "abbreviated" keys (Peter Geoghegan, Andrew Gierth, Robert Haas)
Extend the infrastructure that allows sorting to be performed by inlined, non-SQL-callable comparison functions to cover CREATE INDEX, REINDEX, and CLUSTER (Peter Geoghegan)
Improve performance of hash joins (Tomas Vondra, Robert Haas)
Improve concurrency of shared buffer replacement (Robert Haas, Amit Kapila, Andres Freund)
Reduce the number of page locks and pins during index scans (Kevin Grittner)
The primary benefit of this is to allow index vacuums to be blocked less often.
Make per-backend tracking of buffer pins more memory-efficient (Andres Freund)
Improve lock scalability (Andres Freund)
This particularly addresses scalability problems when running on systems with multiple CPU sockets.
Allow the optimizer to remove unnecessary references to left-joined subqueries (David Rowley)
Allow pushdown of query restrictions into subqueries with window functions, where appropriate (David Rowley)
Allow a non-leakproof function to be pushed down into a security barrier view if the function does not receive any view output columns (Dean Rasheed)
Teach the planner to use statistics obtained from an expression index on a boolean-returning function, when a matching function call appears in WHERE (Tom Lane)
Make ANALYZE compute basic statistics (null fraction and average column width) even for columns whose data type lacks an equality function (Oleksandr Shulgin)
Speed up CRC (cyclic redundancy check) computations and switch to CRC-32C (Abhijit Menon-Sen, Heikki Linnakangas)
Improve bitmap index scan performance (Teodor Sigaev, Tom Lane)
Speed up CREATE INDEX by avoiding unnecessary memory copies (Robert Haas)
Increase the number of buffer mapping partitions (Amit Kapila, Andres Freund, Robert Haas)
This improves performance for highly concurrent workloads.
Add per-table autovacuum logging control via new log_autovacuum_min_duration storage parameter (Michael Paquier)
Add new configuration parameter cluster_name (Thomas Munro)
This string, typically set in postgresql.conf, allows clients to identify the cluster. This name also appears in the process title of all server processes, allowing for easier identification of processes belonging to the same cluster.
Prevent non-superusers from changing log_disconnections on connection startup (Fujii Masao)
Check "Subject Alternative Names" in SSL server certificates, if present (Alexey Klyukin)
When they are present, this replaces checks against the certificate's "Common Name".
Add system view pg_stat_ssl to report SSL connection information (Magnus Hagander)
Add libpq functions to return SSL information in an implementation-independent way (Heikki Linnakangas)
While PQgetssl() can still be used to call OpenSSL functions, it is now considered deprecated because future versions of libpq might support other SSL implementations. When possible, use the new functions PQsslAttribute(), PQsslAttributeNames(), and PQsslInUse() to obtain SSL information in an SSL-implementation-independent way.
Make libpq honor any OpenSSL thread callbacks (Jan Urbanski)
Previously they were overwritten.
Replace configuration parameter checkpoint_segments with min_wal_size and max_wal_size (Heikki Linnakangas)
This change allows the allocation of a large number of WAL files without keeping them after they are no longer needed. Therefore the default for max_wal_size has been set to 1GB, much larger than the old default for checkpoint_segments. Also note that standby servers perform restartpoints to try to limit their WAL space consumption to max_wal_size; previously they did not pay any attention to checkpoint_segments.
Control the Linux OOM killer via new environment variables PG_OOM_ADJUST_FILE and PG_OOM_ADJUST_VALUE (Gurjeet Singh)
The previous OOM control infrastructure involved compile-time options LINUX_OOM_SCORE_ADJ and LINUX_OOM_ADJ, which are no longer supported. The new behavior is available in all builds.
Allow recording of transaction commit time stamps when configuration parameter track_commit_timestamp is enabled (Álvaro Herrera, Petr Jelínek)
Time stamp information can be accessed using functions pg_xact_commit_timestamp() and pg_last_committed_xact().
Allow local_preload_libraries to be set by ALTER ROLE SET (Peter Eisentraut, Kyotaro Horiguchi)
Allow autovacuum workers to respond to configuration parameter changes during a run (Michael Paquier)
Make configuration parameter debug_assertions read-only (Andres Freund)
This means that assertions can no longer be turned off if they were enabled at compile time, allowing for more efficient code optimization. This change also removes the postgres -A option.
Allow setting effective_io_concurrency on systems where it has no effect (Peter Eisentraut)
Add system view pg_file_settings to show the contents of the server's configuration files (Sawada Masahiko)
Add pending_restart to the system view pg_settings to indicate a change has been made but will not take effect until a database restart (Peter Eisentraut)
Allow ALTER SYSTEM values to be reset with ALTER SYSTEM RESET (Vik Fearing)
This command removes the specified setting from postgresql.auto.conf.
Create mechanisms for tracking the progress of replication, including methods for identifying the origin of individual changes during logical replication (Andres Freund)
This is helpful when implementing replication solutions.
Rework truncation of the multixact commit log to be properly WAL-logged (Andres Freund)
This makes things substantially simpler and more robust.
Add recovery.conf parameter recovery_target_action to control post-recovery activity (Petr Jelínek)
This replaces the old parameter pause_at_recovery_target.
Add new archive_mode value always to allow standbys to always archive received WAL files (Fujii Masao)
Add configuration parameter wal_retrieve_retry_interval to control WAL read retry after failure (Alexey Vasiliev, Michael Paquier)
This is particularly helpful for warm standbys.
Allow compression of full-page images stored in WAL (Rahila Syed, Michael Paquier)
This feature reduces WAL volume, at the cost of more CPU time spent on WAL logging and WAL replay. It is controlled by a new configuration parameter wal_compression, which currently is off by default.
Archive WAL files with suffix .partial during standby promotion (Heikki Linnakangas)
Add configuration parameter log_replication_commands to log replication commands (Fujii Masao)
By default, replication commands, e.g., IDENTIFY_SYSTEM, are not logged, even when log_statement is set to all.
Report the processes holding replication slots in pg_replication_slots (Craig Ringer)
The new output column is active_pid.
Allow recovery.conf's primary_conninfo setting to use connection URIs, e.g., postgres:// (Alexander Shulgin)
Allow INSERTs that would generate constraint conflicts to be turned into UPDATEs or ignored (Peter Geoghegan, Heikki Linnakangas, Andres Freund)
The syntax is INSERT ... ON CONFLICT DO NOTHING/UPDATE. This is the Postgres implementation of the popular UPSERT command.
Add GROUP BY analysis features GROUPING SETS, CUBE and ROLLUP (Andrew Gierth, Atri Sharma)
Allow setting multiple target columns in an UPDATE from the result of a single sub-SELECT (Tom Lane)
This is accomplished using the syntax UPDATE tab SET (col1, col2, ...) = (SELECT ...).
Add SELECT option SKIP LOCKED to skip locked rows (Thomas Munro)
This does not throw an error for locked rows like NOWAIT does.
Add SELECT option TABLESAMPLE to return a subset of a table (Petr Jelínek)
This feature supports the SQL-standard table sampling methods. In addition, there are provisions for user-defined table sampling methods.
Suggest possible matches for mistyped column names (Peter Geoghegan, Robert Haas)
Add more details about sort ordering in EXPLAIN output (Marius Timmer, Lukas Kreft, Arne Scheffer)
Details include COLLATE, DESC, USING, and NULLS FIRST/LAST.
Make VACUUM log the number of pages skipped due to pins (Jim Nasby)
Make TRUNCATE properly update the pg_stat* tuple counters (Alexander Shulgin)
Allow REINDEX to reindex an entire schema using the SCHEMA option (Sawada Masahiko)
Add VERBOSE option to REINDEX (Sawada Masahiko)
Prevent REINDEX DATABASE and SCHEMA from outputting object names, unless VERBOSE is used (Simon Riggs)
Remove obsolete FORCE option from REINDEX (Fujii Masao)
Add row-level security control (Craig Ringer, KaiGai Kohei, Adam Brightwell, Dean Rasheed, Stephen Frost)
This feature allows row-by-row control over which users can add, modify, or even see rows in a table. This is controlled by new commands CREATE/ALTER/DROP POLICY and ALTER TABLE ... ENABLE/DISABLE ROW SECURITY.
Allow changing of the WAL logging status of a table after creation with ALTER TABLE ... SET LOGGED / UNLOGGED (Fabrízio de Royes Mello)
Add IF NOT EXISTS clause to CREATE TABLE AS, CREATE INDEX, CREATE SEQUENCE, and CREATE MATERIALIZED VIEW (Fabrízio de Royes Mello)
Add support for IF EXISTS to ALTER TABLE ... RENAME CONSTRAINT (Bruce Momjian)
Allow some DDL commands to accept CURRENT_USER or SESSION_USER, meaning the current user or session user, in place of a specific user name (Kyotaro Horiguchi, Álvaro Herrera)
This feature is now supported in ALTER USER, ALTER GROUP, ALTER ROLE, GRANT, and ALTER object OWNER TO commands.
Support comments on domain constraints (Álvaro Herrera)
Reduce lock levels of some create and alter trigger and foreign key commands (Simon Riggs, Andreas Karlsson)
Allow LOCK TABLE ... ROW EXCLUSIVE MODE for those with INSERT privileges on the target table (Stephen Frost)
Previously this command required UPDATE, DELETE, or TRUNCATE privileges.
Apply table and domain CHECK constraints in order by name (Tom Lane)
The previous ordering was indeterminate.
Allow CREATE/ALTER DATABASE to manipulate datistemplate and datallowconn (Vik Fearing)
This allows these per-database settings to be changed without manually modifying the pg_database system catalog.
Add support for IMPORT FOREIGN SCHEMA (Ronan Dunklau, Michael Paquier, Tom Lane)
This command allows automatic creation of local foreign tables that match the structure of existing tables on a remote server.
Allow CHECK constraints to be placed on foreign tables (Shigeru Hanada, Etsuro Fujita)
Such constraints are assumed to be enforced on the remote server, and are not enforced locally. However, they are assumed to hold for purposes of query optimization, such as constraint exclusion.
Allow foreign tables to participate in inheritance (Shigeru Hanada, Etsuro Fujita)
To let this work naturally, foreign tables are now allowed to have check constraints marked as not valid, and to set storage and OID characteristics, even though these operations are effectively no-ops for a foreign table.
Allow foreign data wrappers and custom scans to implement join pushdown (KaiGai Kohei)
Whenever a ddl_command_end event trigger is installed, capture details of DDL activity for it to inspect (Álvaro Herrera)
This information is available through a set-returning function pg_event_trigger_ddl_commands(), or by inspection of C data structures if that function doesn't provide enough detail.
Allow event triggers on table rewrites caused by ALTER TABLE (Dimitri Fontaine)
Add event trigger support for database-level COMMENT, SECURITY LABEL, and GRANT/REVOKE (Álvaro Herrera)
Add columns to the output of pg_event_trigger_dropped_objects (Álvaro Herrera)
This allows simpler processing of delete operations.
Allow the xml data type to accept empty or all-whitespace content values (Peter Eisentraut)
This is required by the SQL/XML specification.
Allow macaddr input using the format xxxx-xxxx-xxxx (Herwin Weststrate)
Disallow non-SQL-standard syntax for interval with both precision and field specifications (Bruce Momjian)
Per the standard, such type specifications should be written as, for example, INTERVAL MINUTE TO SECOND(2). PostgreSQL formerly allowed this to be written as INTERVAL(2) MINUTE TO SECOND, but it must now be written in the standard way.
Add selectivity estimators for inet/cidr operators and improve estimators for text search functions (Emre Hasegeli, Tom Lane)
Add data types regrole and regnamespace to simplify entering and pretty-printing the OID of a role or namespace (Kyotaro Horiguchi)
Add jsonb functions jsonb_set() and jsonb_pretty() (Dmitry Dolgov, Andrew Dunstan, Petr Jelínek)
Add jsonb generator functions to_jsonb(), jsonb_object(),