1 <!-- doc/src/sgml/release-9.3.sgml -->
2 <!-- See header comment in release.sgml about typical markup -->
4 <sect1 id="release-9-3">
5 <title>Release 9.3</title>
8 <title>Release Date</title>
9 <simpara>2013-XX-XX, CURRENT AS OF 2013-04-20</simpara>
13 <title>Overview</title>
16 ADD HERE Major enhancements include:
19 <!-- This list duplicates items below, but without authors or details-->
22 The above items are explained in more detail in the sections below.
29 <title>Migration to Version 9.3</title>
32 A dump/restore using <link
33 linkend="APP-PG-DUMPALL"><application>pg_dumpall</></link>, or use
34 of <link linkend="pgupgrade"><application>pg_upgrade</></link>, is
35 required for those wishing to migrate data from any previous release.
39 Version 9.3 contains a number of changes that may affect compatibility
40 with previous releases. Observe the following incompatibilities:
44 <title>ADD HERE</title>
51 <title>Changes</title>
54 Below you will find a detailed account of the changes between
55 <productname>PostgreSQL</productname> 9.3 and the previous major
63 <title>Performance</title>
69 Improve performance of NUMERIC calculations (Kyotaro Horiguchi)
75 Use a binary heap for Merge-Append operations (Abhijit Menon-Sen)
81 Add COPY FREEZE option to avoid the overhead of later marking
82 tuples as committed (Simon Riggs, Jeff Davis)
88 Improve memory usage for in-memory sorts (Jeff Janes)
92 Users who have set work_mem based on the previous behavior should
99 Improve performance for transactions creating, rebuilding, or
100 dropping many relations (Jeff Janes, Tomas Vondra)
106 Improve performance of the CREATE TABLE ... ON COMMIT DELETE ROWS
107 clause by only issuing delete if the temporary table was accessed
114 Allow heap-only tuple updates on system tables (Andres Freund)
120 Have vacuum recheck visibility after it has removed expired tuples
125 This increases the chance of a page being marked as all-visible.
131 Split pgstat file in per-database and global files (Tomas Vondra)
135 This reduces the statistics management read and write overhead.
144 <title>Optimizer</title>
150 Reduce optimizer overhead by discarding plans with unneeded cheaper
151 startup costs (Tom Lane)
157 Collect and use histograms of lower and upper bounds for range types
164 Improve the ability to detect indexable prefixes in regular
165 expressions (Tom Lane)
171 Improve optimizer cost estimation for index access (Tom Lane)
180 <title>Indexes</title>
186 Reduce use of heavyweight locking inside hash AM.
192 Improve concurrency of hash indexes (Robert Haas)
198 Add SP-GiST support for range data types (Alexander Korotkov)
204 Allow unlogged GiST indexes (Jeevan Chalke)
210 Allow SP-GiST to support of the range adjacent operator (Alexander
220 <title>Locking</title>
226 Prevent non-key-field row updates from locking foreign key rows
227 (AlvaroAacute;lvaro Herrera, Marti Raudsepp, Alexander Shulgin,
228 Noah Misch and Andres Freund)
232 This improves concurrency and reduces the probability of deadlocks.
233 UPDATEs on non-key columns use the new SELECT FOR NO KEY UPDATE lock
234 type, and foreign key checks use the new SELECT FOR KEY SHARE lock mode.
240 Add cache of local locks (Jeff Janes)
244 This speeds lock release at statement completion in transactions
245 that hold many locks; it is particularly useful for pg_dump.
254 <title>Authentication</title>
260 Improve LDAP error reporting and documentation (Peter Eisentraut)
266 Add support for LDAP authentication to be specified in URL format
273 Change the ssl_ciphers parameter to start with DEFAULT, rather
274 than ALL, then remove insecure ciphers (Magnus Hagander)
278 It is assumed DEFAULT is more appropriate cipher set.
284 Parse/load pg_ident.conf once, not during each connection (Amit
289 This is similar to how pg_hba.conf is processed.
298 <title>Monitoring</title>
304 Allow the statistics collector to operate properly in cases where
305 the system clock goes backwards (Tom Lane)
309 Previously statistics collection would stop until the time again
310 reached the previously-stored latest time.
316 Add optional ability to checksum data pages and report corruption
317 (Simon Riggs, Jeff Davis, Greg Smith)
321 The checksum option can be set during initdb.
327 Add function to report the size of the GIN pending index insertion
332 The function is pgstatginindex() in pgstattuple.
338 Allow pg_terminate_backend() to terminate other backends with
339 the same role (Dan Farina)
343 Previously, only superusers could terminate other sessions.
352 <title>Server Settings</title>
358 Dramatically reduce System V shared memory usage (Robert Haas)
362 Instead, on Unix-like systems, mmap() is used for shared memory.
363 This eliminates the requirement of increasing System V shared
364 memory kernel parameters.
370 Increase the maximum initdb-configured value for shared_buffers
371 to 128MB (Robert Haas)
375 This is the maximum value initdb attempts to set in
376 postgresql.conf; the previous value was 32MB.
382 Allow a directory of configuration files to be processed (Magnus
383 Hagander, Greg Smith, Selena Deckelmann)
387 The directory is specified as include_dir in server configuration file.
393 Have "session id" in log_line_prefix (%c) always output four hex
394 digits after the period (Bruce Momjian)
400 Add configuration variable lock_timeout to limit lock wait duration
401 (Zoltán Böszörményi)
407 Remove the external PID file on postmaster exit (Peter Eisentraut)
413 Improve grouping of sessions waiting for commit_delay (Peter
418 This improves the usefulness of commit_delay.
424 Require superuser privileges to set commit_delay because it
425 can now potentially delay other sessions (Simon Riggs)
426 BACKWARD COMPATIBILITY
432 Allow the postmaster to listen on multiple Unix-domain sockets
437 This renames configuration parameter unix_socket_directory to
438 unix_socket_directories, which accepts a list of directories.
449 <title>Replication and Recovery</title>
455 Add SQL functions pg_backup_in_progress() and pg_backup_start_time() (Darold Gilles)
459 These functions report the status of base backups.
465 Improve performance of streaming replication log shipping (Andres Freund)
471 Add the last checkpoint's redo location to pg_controldata's output
476 This information is useful for determining the WAL files needed for
483 Allow tooling like pg_receivexlog to run on computers with different
484 architectures (Heikki Linnakangas)
490 Allow a streaming replication standbys to follow a timeline switch (Heikki Linnakangas)
494 This allows streaming standbys to feed from newly-promoted slaves.
495 Previously slaves required access to a WAL archive directory to
502 Have pg_basebackup --write-recovery-conf output a minimal
503 recovery.conf (Zoltan Boszormenyi, Magnus Hagander)
507 This simplifies setting up a standby server.
513 Allow pg_receivexlog and pg_basebackup -X to handle streaming
514 timeline switches (Heikki Linnakangas)
520 Add wal_receiver_timeout parameter to control the WAL receiver
521 timeout (Amit Kapila)
525 This allows more rapid detection of connection failure. No longer set
526 wal_receiver_status_interval?
532 Rename replication_timeout to wal_sender_timeout (Amit Kapila)
536 BACKWARD COMPATIBILITY
537 This setting controls the WAL sender timeout.
543 Allow recovery.conf to be relocated using configuration variable
544 recovery_config_directory (Simon Riggs)
557 Store WAL in a continuous stream, rather than skipping the last
558 16MB segment every 4GB (Heikki Linnakangas)
564 Restructure WAL files to better handle timeline changes during
565 recovery (Heikki Linnakangas)
571 Restructure WAL files to use a more compact storage format (Heikki Linnakangas)
582 <title>Queries</title>
588 Implement SQL-standard LATERAL for FROM-clause subqueries and
589 function calls (Tom Lane)
593 This is useful in allowing FROM subqueries and functions to reference
594 output from tables in the FROM clause. The LATERAL keyword is optional
601 Improve query string error location reporting (Tom Lane)
605 Only CREATE SCHEMA IF NOT EXISTS? If so, remove it.
611 Correct predicate locking for DROP INDEX CONCURRENTLY (Kevin Grittner)
618 Add support for piping COPY and psql \copy to/from an external program (Etsuro
628 <title>Object Manipulation</title>
634 Add support to event triggers (Dimitri Fontaine, Tom Lane)
638 This allows C functions to be called when specific commands are run.
644 Allow a multi-row VALUES clause in rules to reference OLD/NEW (Tom Lane)
650 Add IF NOT EXISTS clause to CREATE SCHEMA (FabrÃzio de Royes Mello)
656 Throw an error if expiring tuple is again updated or deleted (Kevin Grittner)
663 Have DROP OWNED only remove user-matching GRANTs on shared objects,
664 e.g. databases, tablespaces (AlvaroAacute;lvaro Herrera)
668 Previously it tried to remove such objects, leading to an error.
674 Have REASSIGN OWNED also change ownership of shared objects (AlvaroAacute;lvaro
682 <title>Constraints</title>
688 Internally store default foreign key matches (non-FULL, non-PARTIAL) as "simple" (Tom Lane)
692 These were previously stored as "&unspecified&".
693 This changes the value stored in system column
694 pg_constraint.confmatchtype. BACKWARD COMPATIBILITY CHANGE
700 Change ON UPDATE SET NULL/SET DEFAULT foreign key actions to affect
701 all referenced columns, not just those referenced in the UPDATE
706 Previously only columns referenced in the UPDATE were set to null or DEFAULT.
707 BACKWARD COMPATIBILITY CHANGE
713 Provide clients with constraint violation details as separate fields
718 This allows clients to retrieve table, column, data type, or constraint
719 name error details. Previously such information had to be extracted from
720 error strings. Client language support is required to access these
730 <title><command>ALTER</></title>
736 Add support for ALTER RULE ... RENAME (Ali Dar)
742 Add ALTER ROLE ALL SET to add settings to all users (Peter Eisentraut)
746 This allows settings to apply to all users in all databases. ALTER
747 DATABASE SET already allowed addition of settings for all users in a
748 single database. postgresql.conf has a similar effect.
754 Add the ALTER TYPE ... IF NOT EXISTS clause when adding enumerated type labels (Andrew
759 This is useful for conditional label creation in transaction blocks.
768 <title><link linkend="rules-views"><command>VIEWs</></link></title>
774 When converting a table to a view, remove its system columns (Tom Lane)
781 Make simple views auto-updatable (Dean Rasheed)
785 INSTEAD rules are still available for complex views.
791 Allow views and rules to handle cases where referenced tables are
792 renamed, or columns are renamed, added, or dropped (Tom Lane)
793 HOW DOES IT HANDLE A DROPPED REFERENCED COLUMN?
799 Add CREATE RECURSIVE VIEW syntax (Peter Eisentraut)
803 Internally this is translated into CREATE VIEW ... WITH RECURSIVE.
809 Add a materialized view relations (Kevin Grittner)
813 Unlink ordinary views, where the base tables are read on every access,
814 materialized views create physical tables at creation or refresh time.
815 Access to the materialized view reads from these materialized physical
816 tables. There is no facility for incrementally refreshing materialized
817 views or auto-accessing them via base table access.
826 <title><link linkend="SQL-CREATETABLE"><command>CREATE TABLE</></link></title>
832 No longer output messages about implicit index and sequence creation (Robert Haas)
836 These messages now appear with DEBUG1-level output.
842 Allow CREATE TABLE to succeed for a non-existent schema
847 Previously, CREATE TABLE IF EXIST threw an error if the schema was
859 <title>Data Types</title>
865 Allow text timezone designations, e.g. "America/Chicago" when using the
866 ISO "T" timestamptz format (Bruce Momjian)
872 Increase the maximum length of large objects from 2GB to 4TB (Nozomi
877 This change includes new libpq and server-side 64-bit-enabled functions.
883 Add functions to convert values, records, and hstore data to JSON (Andrew Dunstan)
889 Add operators and functions to extract values from JSON data strings
896 Allow JSON data strings to be converted into records (Andrew Dunstan)
905 <title>Functions</title>
911 Add array_remove() and array_replace() functions (Marco Nenciarini, Gabriele Bartolini)
917 Have to_char(), to_date(), and to_timestamp() properly handle
918 negative century designations (CC) (Bruce Momjian)
922 Previously the behavior was either wrong or inconsistent with
923 positive/AD handling, e.g. format mask 'IYYY-IW-DY'.
929 In to_date() and to_timestamp(), return proper results when mixing
930 ISO and Gregorian week/day designations (Bruce Momjian)
936 Allow concat() and format() to properly expand VARIADIC-labeled
937 arguments (Pavel Stehule)
943 Cause pg_get_viewdef() to default to wrapping after every SELECT
944 target list and FROM entry (Marko Tiikkaja)
948 This causes view output, like from pg_dump, to be more narrow.
954 Have map_sql_value_to_xml_value() properly expand domains (Pavel
961 Improve format() to handle field width and left/right alignment
968 Force cached functions to be replanned if the search_path changes (Tom Lane)
972 Previously functions already run in the current session ignored changes
982 <title>Server-Side Languages</title>
988 Allow SPI functions to access the number of rows processed by COPY (Pavel
996 <title><link linkend="plpgsql">PL/pgSQL</link> Server-Side Language</title>
1002 Allow greater flexibility in where keywords can be used in PL/pgSQL (Tom Lane)
1008 Allow PL/pgSQL to access the number of rows processed by COPY (Pavel
1013 The command is GET DIAGNOSTICS x = ROW_COUNT.
1019 Allow PL/pgSQL to use RETURN with a composite-type expressions (Asif
1024 Previously RETURN could only reference composite-type variables.
1033 <title><link linkend="plpython">PL/Python</link> Server-Side Language</title>
1039 Allow PL/Python to support platform-specific include directories (Peter Eisentraut)
1045 Allow PL/Python on OS X to build against custom versions of Python
1052 Handle SPIErrors raised explicitly with PL/Python's RAISE the same as
1053 as internal SPI errors (Oskari Saarenmaa and Jan Urbanski)
1059 Add PL/Python result object string handler (Peter Eisentraut)
1063 This allows plpy.debug(rv) to output something reasonable.
1069 Have PL/Python convert oid values to a proper Python numeric type (Peter Eisentraut)
1080 <title>Client Applications</title>
1086 Have initdb fsync the newly created data directory (Jeff Davis)
1090 This can be disabled by using --nosync.
1096 Add initdb --sync-only option to sync the data directory to durable
1097 storage (Bruce Momjian)
1101 This is used by pg_upgrade.
1107 Support multiple --table arguments for pg_restore, clusterdb,
1108 reindexdb, and vacuumdb (Josh Kupershmidt)
1112 This is similar to the pg_dump --table support.
1118 Add command-line utility pg_isready to check if the server is ready
1119 to accept connections (Phil Sorber)
1125 Have initdb issue a warning about placing the data directory at the
1126 top of file system mount points (Bruce Momjian)
1132 Add -d option to pg_dumpall, pg_basebackup, pg_receivexlog to
1133 specify the connection string (Amit Kapila)
1139 Allow pg_ctl --idempotent to a 'success' return code if the requested
1140 start/stop action fails, but the cluster is already in the requested
1141 state (Peter Eisentraut)
1147 Change pg_ctl to return an error code if start fails because the
1148 server is already running (Peter Eisentraut)
1154 Add libpq function PQconninfo() to return connection information
1155 (Zoltan Boszormenyi, Magnus Hagander)
1162 <title><link linkend="APP-PSQL"><application>psql</></link></title>
1168 Allow the psql --single-transaction mode to work when reading from
1169 standard input (Fabien Coelho, Robert Haas)
1173 Previously this option only worked when reading from a file.
1179 Improve psql tab completion (Jeff Janes, Peter Eisentraut)
1185 Remove psql warning when connecting to an older server (Peter Eisentraut)
1189 The warning when connecting to a newer server was retained.
1195 Adjust function cost settings so psql tab completion and pattern
1196 searching is more efficient (Tom Lane)
1203 <title>Backslash Commands</title>
1209 Add SSL information to psql's \conninfo command (Alastair Turner)
1215 In psql, do not allow \connect to use defaults if there is no active
1216 connection (Bruce Momjian)
1220 This might be the case if the server had crashed.
1226 Add "Security" label to psql \df+ output (Jon Erdman)
1232 Properly reset state if psql's "\g file" command failed (Tom Lane)
1236 Previously failed commands discarded output from subsequent commands.
1242 Add psql command \gset to store query results in psql variables
1249 Add psql \watch command to repeatedly execute commands (Will
1256 Allow psql \l to accept a database name pattern (Peter Eisentraut)
1265 <title>Output</title>
1271 Add a 'latex-longtable' output format to psql (Bruce Momjian)
1275 Longtable allows tables to span multiple pages.
1281 Add a border=3 output mode to the psql 'latex' format (Bruce Momjian)
1287 In psql tuples-only and expanded modes, no longer output "(No rows)"
1294 In psql, no longer print an empty line for unaligned, expanded
1295 output for zero rows (Peter Eisentraut)
1306 <title><link linkend="APP-PGDUMP"><application>pg_dump</></link></title>
1312 Fix tar files emitted by pg_dump and pg_basebackup to be POSIX
1313 conformant (Brian Weaver, Tom Lane)
1320 Have pg_dump output functions in a predictable order (Joel Jacobson)
1326 Add -d/--dbname option to pg_dump, for consistency with other client
1327 commands (Heikki Linnakangas)
1331 The database name could already be supplied last without a flag.
1337 Add pg_dump --jobs to dump in parallel (Joachim Wieland)
1348 <title>Source Code</title>
1354 Remove typedefs for int2/int4 as they are better represented as int16/int32 (Peter Eisentraut)
1360 Create a centralized timeout API (Zoltán Böszörményi)
1366 Rewrite pgindent in Perl (Andrew Dunstan)
1372 Add compiler designations to indicate some ereport() and elog()
1373 calls do not return (Peter Eisentraut, Andres Freund, Tom Lane,
1380 Fix install-strip on Mac OS X (Peter Eisentraut)
1386 Remove configure flag --disable-shared, as it is no longer used (Bruce Momjian)
1392 Add emacs macro to match Postgres perltidy formatting (Peter
1399 Run tool to check the keyword list when the backend grammar is changed (Tom Lane)
1405 Standardize on naming of client-side memory allocation functions (Tom Lane)
1411 Centralize flex and bison 'make' rules (Peter Eisentraut)
1415 This is useful for pgxs authors.
1421 Add an embedded list interface (Andres Freund)
1427 Add infrastructure to better support plug-in background worker
1428 processes (AlvaroAacute;lvaro Herrera)
1434 Add Assert() functionality to client-side code for frontend code (Andrew Dunstan)
1440 Change many internal many backend functions to return OIDs rather
1441 than void (Dimitri Fontaine)
1445 This is useful for event triggers.
1451 Allow options to be passed to the regression test output comparison
1452 utility via PG_REGRESS_DIFF_OPTS (Peter Eisentraut)
1458 Create libpgcommon and move pg_malloc() and other functions there
1459 (AlvaroAacute;lvaro Herrera, Andres Freund)
1463 This allows libpgport to be used solely for porting code.
1469 Invent pre-commit/pre-prepare/pre-subcommit events for transaction
1470 callbacks (Tom Lane)
1474 Loadable modules that use transaction callbacks might need modification
1475 to handle these new event types.
1481 Add function pg_identify_object() to dump an object in
1482 machine-readable format (AlvaroAacute;lvaro Herrera)
1488 Improve ability to detect official timezone abbreviation changes
1495 Add pkg-config support libpq and ecpg libraries (Peter Eisentraut)
1501 Add post-ALTER-object server hooks (KaiGai Kohei)
1507 Remove src/tool/backend, now that the content is on the Postgres
1508 wiki (Bruce Momjian)
1514 Split out WAL reading as an independent facility (Andres Freund, Heikki
1524 <title>Additional Modules</title>
1530 Have oid2name, pgbench, and vacuumlo set fallback_application_name (Amit Kapila)
1536 Improve output of pg_test_timing (Bruce Momjian)
1542 Improve pg_test_fsync output (Peter Geoghegan)
1548 Improve contrib/dblink option validator (Tom Lane)
1558 Add a Postgres foreign data wrapper contrib module (Shigeru Hanada)
1564 Add pg_xlogdump contrib program (Andres Freund)
1570 Allow write to Postgres foreign tables (KaiGai Kohei)
1574 Other foreign data wrappers can now also potentially support write
1581 Improve pg_trgm handling of multibyte characters (Tom Lane)
1587 Add support for indexing of regular-expression searches in
1588 contrib/pg_trgm (Alexander Korotkov)
1595 <title><link linkend="pgupgrade"><application>pg_upgrade</></link></title>
1601 Increase pg_upgrade logging content by showing executed command
1602 (AlvaroAacute;lvaro Herrera)
1608 Have pg_upgrade create unix-domain sockets in the current directory
1609 (Bruce Momjian, Tom Lane)
1613 This reduces the possibility that someone will accidentally connect
1620 Have pg_upgrade --check mode properly detect the location of
1621 non-default socket directories (Bruce Momjian, Tom Lane)
1627 Improve performance of pg_upgrade for databases with many tables (Bruce Momjian)
1633 Improve pg_upgrade's status display during copy/link (Bruce Momjian)
1639 Allow pg_upgrade --jobs to do parallelism (Bruce Momjian)
1643 This allows parallel schema dump/restore of databases, as well as
1644 parallel copy/link of data files per tablespace.
1653 <title><link linkend="pgbench"><application>pgbench</></link></title>
1658 Add --foreign-keys option to pgbench (Jeff Janes)
1662 This adds foreign key constraints to tables, for use in foreign
1663 key performance testing.
1669 Reduce and improve the status message output of pgbench's
1670 initialization mode (Robert Haas, Peter Eisentraut)
1676 Add pg_bench --sampling-rate option to control the percentage of
1677 transactions logged (Tomas Vondra)
1683 Add pgbench -q mode to print one output line every five seconds (Tomas Vondra)
1687 Output pgbench elapsed and estimated remaining time during
1688 initialization logging (Tomas Vondra)
1694 Allow pgbench to aggregate performance statistics and produce output
1695 every --aggregate-interval specified seconds (Tomas Vondra)
1701 Allow pgbench to use scale values larger than 21474 (Greg Smith)
1712 <title>Documentation</title>
1718 Improve WINDOW function documentation (Bruce Momjian, Tom Lane)
1724 Add instructions for setting up the documentation tool chain on
1725 Mac OS X (Peter Eisentraut)
1731 Update FreeBSD kernel configuration documentation (Brad Davis)
1737 Allow EPUB-format documentation to be created (Peter Eisentraut)
1743 Improve commit_delay documentation (Peter Geoghegan)