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>Locking</title>
69 Prevent non-key-field row updates from locking foreign key rows
70 (Álvaro Herrera, Marti Raudsepp, Alexander Shulgin,
71 Noah Misch and Andres Freund)
75 This improves concurrency and reduces the probability of deadlocks.
76 UPDATEs on non-key columns use the new SELECT FOR NO KEY UPDATE lock
77 type, and foreign key checks use the new SELECT FOR KEY SHARE lock mode.
83 Add cache of local locks (Jeff Janes)
87 This speeds lock release at statement completion in transactions
88 that hold many locks; it is particularly useful for pg_dump.
97 <title>Indexes</title>
103 Add SP-GiST support for range data types (Alexander Korotkov)
109 Allow unlogged GiST indexes (Jeevan Chalke)
115 Improve concurrency of hash indexes (Robert Haas)
124 <title>Optimizer</title>
130 Improve the ability to detect indexable prefixes in regular
131 expressions (Tom Lane)
137 Collect and use histograms of lower and upper bounds for range types
144 Reduce optimizer overhead by discarding plans with unneeded cheaper
145 startup costs (Tom Lane)
151 Improve optimizer cost estimation for index access (Tom Lane)
160 <title>General Performance</title>
166 Add COPY FREEZE option to avoid the overhead of later marking
167 tuples as committed (Simon Riggs, Jeff Davis)
173 Improve performance of NUMERIC calculations (Kyotaro Horiguchi)
179 Use a binary heap for Merge-Append operations (Abhijit Menon-Sen)
185 "Allow in-memory sorts to use their full memory allocation (Jeff Janes)
189 Users who have set work_mem based on the previous behavior should
190 revisit that setting.
196 Improve performance for transactions creating, rebuilding, or
197 dropping many relations (Jeff Janes, Tomas Vondra)
203 Improve performance of the CREATE TABLE ... ON COMMIT DELETE ROWS
204 clause by only issuing delete if the temporary table was accessed
211 Allow heap-only tuple updates on system tables (Andres Freund)
217 Have vacuum recheck visibility after it has removed expired tuples
222 This increases the chance of a page being marked as all-visible.
228 Split pgstat file in per-database and global files (Tomas Vondra)
232 This reduces the statistics management read and write overhead.
241 <title>Monitoring</title>
247 Add optional ability to checksum data pages and report corruption
248 (Simon Riggs, Jeff Davis, Greg Smith)
252 The checksum option can be set during initdb.
258 Allow pg_terminate_backend() to terminate other backends with
259 the same role (Dan Farina)
263 Previously, only superusers could terminate other sessions.
269 Allow the statistics collector to operate properly in cases where
270 the system clock goes backwards (Tom Lane)
274 Previously statistics collection would stop until the time again
275 reached the previously-stored latest time.
281 Add function to report the size of the GIN pending index insertion
286 The function is pgstatginindex() in pgstattuple.
295 <title>Authentication</title>
301 Improve LDAP error reporting and documentation (Peter Eisentraut)
307 Add support for LDAP authentication to be specified in URL format
314 Change the ssl_ciphers parameter to start with DEFAULT, rather
315 than ALL, then remove insecure ciphers (Magnus Hagander)
319 It is assumed DEFAULT is more appropriate cipher set.
325 Parse/load pg_ident.conf once, not during each connection (Amit
330 This is similar to how pg_hba.conf is processed.
339 <title>Server Settings</title>
345 Dramatically reduce System V shared memory usage (Robert Haas)
349 Instead, on Unix-like systems, mmap() is used for shared memory.
350 This eliminates the requirement of increasing System V shared
351 memory kernel parameters.
357 Allow the postmaster to listen on multiple Unix-domain sockets
362 This renames configuration parameter unix_socket_directory to
363 unix_socket_directories, which accepts a list of directories.
369 Add configuration variable lock_timeout to limit lock wait duration
370 (Zoltán Böszörményi)
376 Allow a directory of configuration files to be processed (Magnus
377 Hagander, Greg Smith, Selena Deckelmann)
381 The directory is specified as include_dir in server configuration file.
387 Increase the maximum initdb-configured value for shared_buffers
388 to 128MB (Robert Haas)
392 This is the maximum value initdb attempts to set in
393 postgresql.conf; the previous value was 32MB.
399 Improve grouping of sessions waiting for commit_delay (Peter
404 This improves the usefulness and behavior of commit_delay.
405 BACKWARD COMPATIBILITY
411 Require superuser privileges to set commit_delay because it
412 can now potentially delay other sessions (Simon Riggs)
413 BACKWARD COMPATIBILITY
420 Remove the external PID file on postmaster exit (Peter Eisentraut)
426 Have "session id" in log_line_prefix (%c) always output four hex
427 digits after the period (Bruce Momjian) COMPATIBILITY
438 <title>Replication and Recovery</title>
444 Allow a streaming replication standbys to follow a timeline switch (Heikki Linnakangas)
448 This allows streaming standbys to feed from newly-promoted slaves.
449 Previously slaves required access to a WAL archive directory to
456 Add SQL functions pg_backup_in_progress() and pg_backup_start_time() (Gilles Darold)
460 These functions report the status of base backups.
466 Improve performance of streaming replication log shipping and
467 standby promotion (Andres Freund, Simon Riggs)
473 Add the last checkpoint's redo location to pg_controldata's output
478 This information is useful for determining the WAL files needed for
485 Allow tooling like pg_receivexlog to run on computers with different
486 architectures (Heikki Linnakangas)
492 Have pg_basebackup --write-recovery-conf output a minimal
493 recovery.conf (Zoltan Boszormenyi, Magnus Hagander)
497 This simplifies setting up a standby server.
503 Allow pg_receivexlog and pg_basebackup -X to handle streaming
504 timeline switches (Heikki Linnakangas)
510 Add wal_receiver_timeout parameter to control the WAL receiver
511 timeout (Amit Kapila)
515 This allows more rapid detection of connection failure. No longer set
516 wal_receiver_status_interval?
522 Rename replication_timeout to wal_sender_timeout (Amit Kapila)
526 BACKWARD COMPATIBILITY
527 This setting controls the WAL sender timeout.
534 <title>Write-Ahead Log (WAL)</title>
540 Store WAL in a continuous stream, rather than skipping the last
541 16MB segment every 4GB (Heikki Linnakangas)
547 Restructure WAL files to better handle timeline changes during
548 recovery (Heikki Linnakangas)
554 Restructure WAL files to use a more compact storage format (Heikki Linnakangas)
565 <title>Queries</title>
571 Implement SQL-standard LATERAL for FROM-clause subqueries and
572 function calls (Tom Lane)
576 This is useful in allowing FROM subqueries and functions to reference
577 output from tables in the FROM clause. The LATERAL keyword is optional
584 Add support for piping COPY and psql \copy to/from an external program (Etsuro
591 Improve query string error location reporting (Tom Lane)
595 Only CREATE SCHEMA ... IF NOT EXISTS? If so, remove it.
601 Correct predicate locking for DROP INDEX CONCURRENTLY (Kevin Grittner)
611 <title>Object Manipulation</title>
617 Add support to event triggers (Dimitri Fontaine, Tom Lane)
621 This allows C functions to be called when specific commands are run.
627 Allow a multi-row VALUES clause in rules to reference OLD/NEW (Tom Lane)
633 Add CREATE SCHEMA ... IF NOT EXIST clause (FabrÃzio de Royes Mello)
639 Throw an error if expiring tuple is again updated or deleted (Kevin Grittner)
646 Have DROP OWNED only remove user-matching GRANTs on shared objects,
647 e.g. databases, tablespaces (Álvaro Herrera)
651 Previously it tried to remove such objects, leading to an error.
657 Have REASSIGN OWNED also change ownership of shared objects (Álvaro
665 <title><link linkend="SQL-CREATETABLE"><command>CREATE TABLE</></link></title>
671 No longer output messages about implicit index and sequence creation (Robert Haas)
675 These messages now appear with DEBUG1-level output.
681 Allow CREATE TABLE IF NOT EXIST to succeed for a non-existent schema
686 Previously, it threw an error if the schema did not exist.
695 <title>Constraints</title>
701 Change ON UPDATE SET NULL/SET DEFAULT foreign key actions to affect
702 all referenced columns, not just those referenced in the UPDATE
707 Previously only columns referenced in the UPDATE were set to null or DEFAULT.
708 BACKWARD COMPATIBILITY CHANGE
714 Provide clients with constraint violation details as separate fields
719 This allows clients to retrieve table, column, data type, or constraint
720 name error details. Previously such information had to be extracted from
721 error strings. Client language support is required to access these
728 Internally store default foreign key matches (non-FULL, non-PARTIAL) as "simple" (Tom Lane)
732 These were previously stored as "&unspecified&".
733 This changes the value stored in system column
734 pg_constraint.confmatchtype. BACKWARD COMPATIBILITY CHANGE
743 <title><command>ALTER</></title>
749 Add the ALTER TYPE ... IF NOT EXISTS clause when adding enumerated type labels (Andrew
754 This is useful for conditional label creation in transaction blocks.
760 Add ALTER ROLE ALL SET to add settings to all users (Peter Eisentraut)
764 This allows settings to apply to all users in all databases. ALTER
765 DATABASE SET already allowed addition of settings for all users in a
766 single database. postgresql.conf has a similar effect.
772 Add support for ALTER RULE ... RENAME (Ali Dar)
781 <title><link linkend="rules-views"><command>VIEWs</></link></title>
787 Add a materialized view relations (Kevin Grittner)
791 Unlike ordinary views, where the base tables are read on every access,
792 materialized views create physical tables at creation or refresh time.
793 Access to the materialized view reads from these materialized physical
794 tables. There is no facility for incrementally refreshing materialized
795 views or auto-accessing them via base table access.
801 Make simple views auto-updatable (Dean Rasheed)
805 INSTEAD rules are still available for complex views.
811 Allow views and rules to handle cases where referenced tables are
812 renamed, or columns are renamed, added, or dropped (Tom Lane)
813 HOW DOES IT HANDLE A DROPPED REFERENCED COLUMN?
819 Add CREATE RECURSIVE VIEW syntax (Peter Eisentraut)
823 Internally this is translated into CREATE VIEW ... WITH RECURSIVE.
829 When converting a table to a view, remove its system columns (Tom Lane)
841 <title>Data Types</title>
847 Increase the maximum length of large objects from 2GB to 4TB (Nozomi
852 This change includes new libpq and server-side 64-bit-enabled functions.
858 Allow text timezone designations, e.g. "America/Chicago" when using the
859 ISO "T" timestamptz format (Bruce Momjian)
872 Add operators and functions to extract values from JSON data strings
879 Allow JSON data strings to be converted into records (Andrew Dunstan)
885 Add functions to convert values, records, and hstore data to JSON (Andrew Dunstan)
897 <title>Functions</title>
903 Add array_remove() and array_replace() functions (Marco Nenciarini, Gabriele Bartolini)
909 Allow concat() and format() to properly expand VARIADIC-labeled
910 arguments (Pavel Stehule)
916 Improve format() to handle field width and left/right alignment
923 Have to_char(), to_date(), and to_timestamp() properly handle
924 negative century designations (CC) (Bruce Momjian)
928 Previously the behavior was either wrong or inconsistent with
929 positive/AD handling, e.g. format mask 'IYYY-IW-DY'.
935 Have to_date() and to_timestamp() return proper results when mixing
936 ISO and Gregorian week/day designations (Bruce Momjian)
942 Cause pg_get_viewdef() to default to wrapping after every SELECT
943 target list and FROM entry (Marko Tiikkaja)
947 This causes view output, like from pg_dump, to be more narrow.
953 Have map_sql_value_to_xml_value() properly expand domains (Pavel
960 Force cached functions to be replanned if the search_path changes (Tom Lane)
964 Previously functions already run in the current session ignored
975 <title>Server-Side Languages</title>
981 Allow SPI functions to access the number of rows processed by COPY (Pavel
989 <title><link linkend="plpgsql">PL/pgSQL</link> Server-Side Language</title>
995 Allow PL/pgSQL to use RETURN with a composite-type expressions (Asif
1000 Previously RETURN could only reference composite-type variables.
1006 Allow PL/pgSQL to access the number of rows processed by COPY (Pavel
1011 The command is GET DIAGNOSTICS x = ROW_COUNT.
1017 Allow greater flexibility in where keywords can be used in PL/pgSQL (Tom Lane)
1026 <title><link linkend="plpython">PL/Python</link> Server-Side Language</title>
1032 Add PL/Python result object string handler (Peter Eisentraut)
1036 This allows plpy.debug(rv) to output something reasonable.
1042 Have PL/Python convert oid values to a proper Python numeric type (Peter Eisentraut)
1048 Handle SPIErrors raised explicitly with PL/Python's RAISE the same as
1049 as internal SPI errors (Oskari Saarenmaa and Jan Urbanski)
1060 <title>Client Applications</title>
1066 Add command-line utility pg_isready to check if the server is ready
1067 to accept connections (Phil Sorber)
1073 Support multiple --table arguments for pg_restore, clusterdb,
1074 reindexdb, and vacuumdb (Josh Kupershmidt)
1078 This is similar to the pg_dump --table support.
1084 Add -d option to pg_dumpall, pg_basebackup, pg_receivexlog to
1085 specify the connection string (Amit Kapila)
1091 Allow pg_ctl --idempotent to a 'success' return code if the requested
1092 start/stop action fails, but the cluster is already in the requested
1093 state (Peter Eisentraut)
1099 Change pg_ctl to return an error code if start fails because the
1100 server is already running (Peter Eisentraut)
1106 Add libpq function PQconninfo() to return connection information
1107 (Zoltan Boszormenyi, Magnus Hagander)
1114 <title><link linkend="APP-PSQL"><application>psql</></link></title>
1120 Adjust function cost settings so psql tab completion and pattern
1121 searching is more efficient (Tom Lane)
1127 Improve psql tab completion (Jeff Janes, Peter Eisentraut)
1133 Allow the psql --single-transaction mode to work when reading from
1134 standard input (Fabien Coelho, Robert Haas)
1138 Previously this option only worked when reading from a file.
1144 Remove psql warning when connecting to an older server (Peter Eisentraut)
1148 The warning when connecting to a newer server was retained.
1155 <title>Backslash Commands</title>
1161 Add psql \watch command to repeatedly execute commands (Will
1168 Add psql command \gset to store query results in psql variables
1175 Add SSL information to psql's \conninfo command (Alastair Turner)
1181 Add "Security" label to psql \df+ output (Jon Erdman)
1187 Allow psql \l to accept a database name pattern (Peter Eisentraut)
1193 In psql, do not allow \connect to use defaults if there is no active
1194 connection (Bruce Momjian)
1198 This might be the case if the server had crashed.
1204 Properly reset state if psql's "\g file" command failed (Tom Lane)
1208 Previously failed commands discarded output from subsequent commands.
1217 <title>Output</title>
1223 Add a 'latex-longtable' output format to psql (Bruce Momjian)
1227 Longtable allows tables to span multiple pages.
1233 Add a border=3 output mode to the psql 'latex' format (Bruce Momjian)
1239 In psql tuples-only and expanded modes, no longer output "(No rows)"
1246 In psql, no longer print an empty line for unaligned, expanded
1247 output for zero rows (Peter Eisentraut)
1258 <title><link linkend="APP-PGDUMP"><application>pg_dump</></link></title>
1264 Add pg_dump --jobs to dump in parallel (Joachim Wieland)
1270 Have pg_dump output functions in a predictable order (Joel Jacobson)
1276 Fix tar files emitted by pg_dump and pg_basebackup to be POSIX
1277 conformant (Brian Weaver, Tom Lane)
1283 Add -d/--dbname option to pg_dump, for consistency with other client
1284 commands (Heikki Linnakangas)
1288 The database name could already be supplied last without a flag.
1297 <title><link linkend="APP-INITDB"><application>initdb</></link></title>
1303 Have initdb fsync the newly created data directory (Jeff Davis)
1307 This can be disabled by using --nosync.
1313 Add initdb --sync-only option to sync the data directory to durable
1314 storage (Bruce Momjian)
1318 This is used by pg_upgrade.
1324 Have initdb issue a warning about placing the data directory at the
1325 top of file system mount points (Bruce Momjian)
1336 <title>Source Code</title>
1342 Add an embedded list interface (Andres Freund)
1348 Add infrastructure to better support plug-in background worker
1349 processes (Álvaro Herrera)
1355 Create a centralized timeout API (Zoltán Böszörményi)
1361 Create libpgcommon and move pg_malloc() and other functions there
1362 (Álvaro Herrera, Andres Freund)
1366 This allows libpgport to be used solely for porting code.
1372 Standardize on naming of client-side memory allocation functions (Tom Lane)
1378 Add compiler designations to indicate some ereport() and elog()
1379 calls do not return (Peter Eisentraut, Andres Freund, Tom Lane,
1386 Allow options to be passed to the regression test output comparison
1387 utility via PG_REGRESS_DIFF_OPTS (Peter Eisentraut)
1393 Add isolation tests for CREATE INDEX CONCURRENTLY (Abhijit Menon-Sen)
1399 Remove typedefs for int2/int4 as they are better represented as int16/int32 (Peter Eisentraut)
1405 Fix install-strip on Mac OS X (Peter Eisentraut)
1411 Remove configure flag --disable-shared, as it is no longer used (Bruce Momjian)
1417 Rewrite pgindent in Perl (Andrew Dunstan)
1423 Add emacs macro to match Postgres perltidy formatting (Peter
1430 Run tool to check the keyword list when the backend grammar is changed (Tom Lane)
1436 Centralize flex and bison 'make' rules (Peter Eisentraut)
1440 This is useful for pgxs authors.
1446 Add Assert() functionality to client-side code for frontend code (Andrew Dunstan)
1452 Change many internal many backend functions to return OIDs rather
1453 than void (Dimitri Fontaine)
1457 This is useful for event triggers.
1463 Invent pre-commit/pre-prepare/pre-subcommit events for transaction
1464 callbacks (Tom Lane)
1468 Loadable modules that use transaction callbacks might need modification
1469 to handle these new event types.
1475 Add function pg_identify_object() to dump an object in
1476 machine-readable format (Álvaro Herrera)
1482 Add post-ALTER-object server hooks (KaiGai Kohei)
1488 Improve ability to detect official timezone abbreviation changes
1495 Add pkg-config support libpq and ecpg libraries (Peter Eisentraut)
1501 Remove src/tool/backend, now that the content is on the Postgres
1502 wiki (Bruce Momjian)
1508 Split out WAL reading as an independent facility (Andres Freund, Heikki
1515 Allow PL/Python to support platform-specific include directories (Peter Eisentraut)
1521 Allow PL/Python on OS X to build against custom versions of Python
1531 <title>Additional Modules</title>
1537 Add a Postgres foreign data wrapper contrib module (Shigeru
1538 Hanada, KaiGai Kohei)
1542 This foreign data wrapper allows writes; potentially other
1543 foreign data wrappers can now support writes.
1549 Add pg_xlogdump contrib program (Andres Freund)
1555 Add support for indexing of regular-expression searches in
1556 contrib/pg_trgm (Alexander Korotkov)
1562 Improve pg_trgm handling of multibyte characters (Tom Lane)
1568 Have oid2name, pgbench, and vacuumlo set fallback_application_name (Amit Kapila)
1574 Improve output of pg_test_timing (Bruce Momjian)
1580 Improve output of pg_test_fsync (Peter Geoghegan)
1586 Improve dblink option validator (Tom Lane)
1597 <title><link linkend="pgupgrade"><application>pg_upgrade</></link></title>
1603 Allow pg_upgrade --jobs to do parallelism (Bruce Momjian)
1607 This allows parallel schema dump/restore of databases, as well as
1608 parallel copy/link of data files per tablespace.
1614 Have pg_upgrade create unix-domain sockets in the current directory
1615 (Bruce Momjian, Tom Lane)
1619 This reduces the possibility that someone will accidentally connect
1626 Have pg_upgrade --check mode properly detect the location of
1627 non-default socket directories (Bruce Momjian, Tom Lane)
1633 Improve performance of pg_upgrade for databases with many tables (Bruce Momjian)
1639 Increase pg_upgrade logging content by showing executed command
1640 (Álvaro Herrera)
1646 Improve pg_upgrade's status display during copy/link (Bruce Momjian)
1655 <title><link linkend="pgbench"><application>pgbench</></link></title>
1661 Add --foreign-keys option to pgbench (Jeff Janes)
1665 This adds foreign key constraints to tables, for use in foreign
1666 key performance testing.
1672 Allow pgbench to aggregate performance statistics and produce output
1673 every --aggregate-interval specified seconds (Tomas Vondra)
1679 Add pg_bench --sampling-rate option to control the percentage of
1680 transactions logged (Tomas Vondra)
1686 Reduce and improve the status message output of pgbench's
1687 initialization mode (Robert Haas, Peter Eisentraut)
1693 Add pgbench -q mode to print one output line every five seconds (Tomas Vondra)
1697 Output pgbench elapsed and estimated remaining time during
1698 initialization logging (Tomas Vondra)
1704 Allow pgbench to use a larger scale factor (Greg Smith)
1715 <title>Documentation</title>
1721 Allow EPUB-format documentation to be created (Peter Eisentraut)
1727 Update FreeBSD kernel configuration documentation (Brad Davis)
1733 Improve WINDOW function documentation (Bruce Momjian, Tom Lane)
1739 Add instructions for setting up the documentation tool chain on
1740 Mac OS X (Peter Eisentraut)
1746 Improve commit_delay documentation (Peter Geoghegan)