1 <!-- doc/src/sgml/release-9.2.sgml -->
2 <!-- See header comment in release.sgml about typical markup -->
4 <sect1 id="release-9-2-2">
5 <title>Release 9.2.2</title>
8 <title>Release Date</title>
9 <simpara>2012-12-06</simpara>
13 This release contains a variety of fixes from 9.2.1.
14 For information about new features in the 9.2 major release, see
15 <xref linkend="release-9-2">.
19 <title>Migration to Version 9.2.2</title>
22 A dump/restore is not required for those running 9.2.X.
26 However, you may need to perform <command>REINDEX</> operations to
27 correct problems in concurrently-built indexes, as described in the first
32 Also, if you are upgrading from version 9.2.0,
33 see the release notes for 9.2.1.
39 <title>Changes</title>
45 Fix multiple bugs associated with <command>CREATE/DROP INDEX
46 CONCURRENTLY</> (Andres Freund, Tom Lane, Simon Riggs, Pavan Deolasee)
50 An error introduced while adding <command>DROP INDEX CONCURRENTLY</>
51 allowed incorrect indexing decisions to be made during the initial
52 phase of <command>CREATE INDEX CONCURRENTLY</>; so that indexes built
53 by that command could be corrupt. It is recommended that indexes
54 built in 9.2.X with <command>CREATE INDEX CONCURRENTLY</> be rebuilt
55 after applying this update.
59 In addition, fix <command>CREATE/DROP INDEX CONCURRENTLY</> to use
60 in-place updates when changing the state of an index's
61 <structname>pg_index</> row. This prevents race conditions that could
62 cause concurrent sessions to miss updating the target index, thus
63 again resulting in corrupt concurrently-created indexes.
67 Also, fix various other operations to ensure that they ignore
68 invalid indexes resulting from a failed <command>CREATE INDEX
69 CONCURRENTLY</> command. The most important of these is
70 <command>VACUUM</>, because an auto-vacuum could easily be launched
71 on the table before corrective action can be taken to fix or remove
76 Also fix <command>DROP INDEX CONCURRENTLY</> to not disable
77 insertions into the target index until all queries using it are done.
81 Also fix misbehavior if <command>DROP INDEX CONCURRENTLY</> is
82 canceled: the previous coding could leave an un-droppable index behind.
88 Correct predicate locking for <command>DROP INDEX CONCURRENTLY</>
93 Previously, SSI predicate locks were processed at the wrong time,
94 possibly leading to incorrect behavior of serializable transactions
95 executing in parallel with the <command>DROP</>.
101 Fix buffer locking during WAL replay (Tom Lane)
105 The WAL replay code was insufficiently careful about locking buffers
106 when replaying WAL records that affect more than one page. This could
107 result in hot standby queries transiently seeing inconsistent states,
108 resulting in wrong answers or unexpected failures.
114 Fix an error in WAL generation logic for GIN indexes (Tom Lane)
118 This could result in index corruption, if a torn-page failure occurred.
124 Fix an error in WAL replay logic for SP-GiST indexes (Tom Lane)
128 This could result in index corruption after a crash, or on a standby
135 Fix incorrect detection of end-of-base-backup location during WAL
136 recovery (Heikki Linnakangas)
140 This mistake allowed hot standby mode to start up before the database
141 reaches a consistent state.
147 Properly remove startup process's virtual XID lock when promoting a
148 hot standby server to normal running (Simon Riggs)
152 This oversight could prevent subsequent execution of certain
153 operations such as <command>CREATE INDEX CONCURRENTLY</>.
159 Avoid bogus <quote>out-of-sequence timeline ID</> errors in standby
160 mode (Heikki Linnakangas)
166 Prevent the postmaster from launching new child processes after it's
167 received a shutdown signal (Tom Lane)
171 This mistake could result in shutdown taking longer than it should, or
172 even never completing at all without additional user action.
178 Fix the syslogger process to not fail when
179 <varname>log_rotation_age</> exceeds 2^31 milliseconds (about 25 days)
186 Fix <function>WaitLatch()</> to return promptly when the requested
187 timeout expires (Jeff Janes, Tom Lane)
191 With the previous coding, a steady stream of non-wait-terminating
192 interrupts could delay return from <function>WaitLatch()</>
193 indefinitely. This has been shown to be a problem for the autovacuum
194 launcher process, and might cause trouble elsewhere as well.
200 Avoid corruption of internal hash tables when out of memory
207 Prevent file descriptors for dropped tables from being held open past
208 transaction end (Tom Lane)
212 This should reduce problems with long-since-dropped tables continuing
213 to occupy disk space.
219 Prevent database-wide crash and restart when a new child process is
220 unable to create a pipe for its latch (Tom Lane)
224 Although the new process must fail, there is no good reason to force a
225 database-wide restart, so avoid that. This improves robustness when
226 the kernel is nearly out of file descriptors.
232 Avoid planner crash with joins to unflattened subqueries (Tom Lane)
238 Fix planning of non-strict equivalence clauses above outer joins
243 The planner could derive incorrect constraints from a clause equating
244 a non-strict construct to something else, for example
245 <literal>WHERE COALESCE(foo, 0) = 0</>
246 when <literal>foo</> is coming from the nullable side of an outer join.
247 9.2 showed this type of error in more cases than previous releases,
248 but the basic bug has been there for a long time.
254 Fix <command>SELECT DISTINCT</> with index-optimized
255 <function>MIN</>/<function>MAX</> on an inheritance tree (Tom Lane)
259 The planner would fail with <quote>failed to re-find MinMaxAggInfo
260 record</> given this combination of factors.
266 Make sure the planner sees implicit and explicit casts as equivalent
267 for all purposes, except in the minority of cases where there's
268 actually a semantic difference (Tom Lane)
274 Include join clauses when considering whether partial indexes can be
275 used for a query (Tom Lane)
279 A strict join clause can be sufficient to establish an
280 <replaceable>x</> <literal>IS NOT NULL</> predicate, for example.
281 This fixes a planner regression in 9.2, since previous versions could
282 make comparable deductions.
288 Limit growth of planning time when there are many indexable join
289 clauses for the same index (Tom Lane)
295 Improve planner's ability to prove exclusion constraints from
296 equivalence classes (Tom Lane)
302 Fix partial-row matching in hashed subplans to handle cross-type cases
307 This affects multicolumn <literal>NOT IN</> subplans, such as
308 <literal>WHERE (a, b) NOT IN (SELECT x, y FROM ...)</>
309 when for instance <literal>b</> and <literal>y</> are <type>int4</>
310 and <type>int8</> respectively. This mistake led to wrong answers
311 or crashes depending on the specific datatypes involved.
317 Fix btree mark/restore functions to handle array keys (Tom Lane)
321 This oversight could result in wrong answers from merge joins whose
322 inner side is an index scan using an
323 <literal><replaceable>indexed_column</> =
324 ANY(<replaceable>array</>)</literal> condition.
330 Revert patch for taking fewer snapshots (Tom Lane)
334 The 9.2 change to reduce the number of snapshots taken during query
335 execution led to some anomalous behaviors not seen in previous
336 releases, because execution would proceed with a snapshot acquired
337 before locking the tables used by the query. Thus, for example,
338 a query would not be guaranteed to see updates committed by a
339 preceding transaction even if that transaction had exclusive lock.
340 We'll probably revisit this in future releases, but meanwhile put it
341 back the way it was before 9.2.
347 Acquire buffer lock when re-fetching the old tuple for an
348 <literal>AFTER ROW UPDATE/DELETE</> trigger (Andres Freund)
352 In very unusual circumstances, this oversight could result in passing
353 incorrect data to a trigger <literal>WHEN</> condition, or to the
354 precheck logic for a foreign-key enforcement trigger. That could
355 result in a crash, or in an incorrect decision about whether to
362 Fix <command>ALTER COLUMN TYPE</> to handle inherited check
363 constraints properly (Pavan Deolasee)
367 This worked correctly in pre-8.4 releases, and now works correctly
374 Fix <command>ALTER EXTENSION SET SCHEMA</>'s failure to move some
375 subsidiary objects into the new schema (Álvaro Herrera, Dimitri
382 Handle <command>CREATE TABLE AS EXECUTE</> correctly in extended query
389 Don't modify the input parse tree in <command>DROP RULE IF NOT
390 EXISTS</> and <command>DROP TRIGGER IF NOT EXISTS</> (Tom Lane)
394 This mistake would cause errors if a cached statement of one of these
395 types was re-executed.
401 Fix <command>REASSIGN OWNED</> to handle grants on tablespaces
402 (Álvaro Herrera)
408 Ignore incorrect <structname>pg_attribute</> entries for system
409 columns for views (Tom Lane)
413 Views do not have any system columns. However, we forgot to
414 remove such entries when converting a table to a view. That's fixed
415 properly for 9.3 and later, but in previous branches we need to defend
416 against existing mis-converted views.
422 Fix rule printing to dump <literal>INSERT INTO <replaceable>table</>
423 DEFAULT VALUES</literal> correctly (Tom Lane)
429 Guard against stack overflow when there are too many
430 <literal>UNION</>/<literal>INTERSECT</>/<literal>EXCEPT</> clauses
431 in a query (Tom Lane)
437 Prevent platform-dependent failures when dividing the minimum possible
438 integer value by -1 (Xi Wang, Tom Lane)
444 Fix possible access past end of string in date parsing
451 Fix failure to advance XID epoch if XID wraparound happens during a
452 checkpoint and <varname>wal_level</> is <literal>hot_standby</>
453 (Tom Lane, Andres Freund)
457 While this mistake had no particular impact on
458 <productname>PostgreSQL</productname> itself, it was bad for
459 applications that rely on <function>txid_current()</> and related
460 functions: the TXID value would appear to go backwards.
466 Fix <function>pg_terminate_backend()</> and
467 <function>pg_cancel_backend()</> to not throw error for a non-existent
468 target process (Josh Kupershmidt)
472 This case already worked as intended when called by a superuser,
473 but not so much when called by ordinary users.
480 <structname>pg_stat_replication</>.<structfield>sync_state</> at a
481 page boundary (Kyotaro Horiguchi)
487 Produce an understandable error message if the length of the path name
488 for a Unix-domain socket exceeds the platform-specific limit
489 (Tom Lane, Andrew Dunstan)
493 Formerly, this would result in something quite unhelpful, such as
494 <quote>Non-recoverable failure in name resolution</>.
500 Fix memory leaks when sending composite column values to the client
507 Save some cycles by not searching for subtransaction locks at commit
512 In a transaction holding many exclusive locks, this useless activity
513 could be quite costly.
519 Make <application>pg_ctl</> more robust about reading the
520 <filename>postmaster.pid</> file (Heikki Linnakangas)
524 This fixes race conditions and possible file descriptor leakage.
530 Fix possible crash in <application>psql</> if incorrectly-encoded data
531 is presented and the <varname>client_encoding</> setting is a
532 client-only encoding, such as SJIS (Jiang Guiqing)
538 Make <application>pg_dump</> dump <literal>SEQUENCE SET</> items in
539 the data not pre-data section of the archive (Tom Lane)
543 This fixes an undesirable inconsistency between the meanings of
544 <option>--data-only</> and <option>--section=data</>, and also fixes
545 dumping of sequences that are marked as extension configuration tables.
551 Fix <application>pg_dump</>'s handling of <command>DROP DATABASE</>
552 commands in <option>--clean</> mode (Guillaume Lelarge)
556 Beginning in 9.2.0, <literal>pg_dump --clean</> would issue a
557 <command>DROP DATABASE</> command, which was either useless or
558 dangerous depending on the usage scenario. It no longer does that.
559 This change also fixes the combination of <option>--clean</> and
560 <option>--create</> to work sensibly, i.e., emit <command>DROP
561 DATABASE</> then <command>CREATE DATABASE</> before reconnecting to the
568 Fix <application>pg_dump</> for views with circular dependencies and
569 no relation options (Tom Lane)
573 The previous fix to dump relation options when a view is
574 involved in a circular dependency didn't work right for the case
575 that the view has no options; it emitted <literal>ALTER VIEW foo
576 SET ()</> which is invalid syntax.
582 Fix bugs in the <filename>restore.sql</> script emitted by
583 <application>pg_dump</> in <literal>tar</> output format (Tom Lane)
587 The script would fail outright on tables whose names include
588 upper-case characters. Also, make the script capable of restoring
589 data in <option>--inserts</> mode as well as the regular COPY mode.
595 Fix <application>pg_restore</> to accept POSIX-conformant
596 <literal>tar</> files (Brian Weaver, Tom Lane)
600 The original coding of <application>pg_dump</>'s <literal>tar</>
601 output mode produced files that are not fully conformant with the
602 POSIX standard. This has been corrected for version 9.3. This
603 patch updates previous branches so that they will accept both the
604 incorrect and the corrected formats, in hopes of avoiding
605 compatibility problems when 9.3 comes out.
611 Fix <literal>tar</> files emitted by <application>pg_basebackup</> to
612 be POSIX conformant (Brian Weaver, Tom Lane)
618 Fix <application>pg_resetxlog</> to locate <filename>postmaster.pid</>
619 correctly when given a relative path to the data directory (Tom Lane)
623 This mistake could lead to <application>pg_resetxlog</> not noticing
624 that there is an active postmaster using the data directory.
630 Fix <application>libpq</>'s <function>lo_import()</> and
631 <function>lo_export()</> functions to report file I/O errors properly
638 Fix <application>ecpg</>'s processing of nested structure pointer
639 variables (Muhammad Usama)
645 Fix <application>ecpg</>'s <function>ecpg_get_data</> function to
646 handle arrays properly (Michael Meskes)
652 Prevent <application>pg_upgrade</> from trying to process TOAST tables
653 for system catalogs (Bruce Momjian)
657 This fixes an error seen when the <literal>information_schema</> has
658 been dropped and recreated. Other failures were also possible.
664 Improve <application>pg_upgrade</> performance by setting
665 <varname>synchronous_commit</> to <literal>off</> in the new cluster
672 Make <filename>contrib/pageinspect</>'s btree page inspection
673 functions take buffer locks while examining pages (Tom Lane)
679 Work around unportable behavior of <literal>malloc(0)</> and
680 <literal>realloc(NULL, 0)</> (Tom Lane)
684 On platforms where these calls return <literal>NULL</>, some code
685 mistakenly thought that meant out-of-memory.
686 This is known to have broken <application>pg_dump</> for databases
687 containing no user-defined aggregates. There might be other cases
694 Ensure that <literal>make install</> for an extension creates the
695 <filename>extension</> installation directory (Cédric Villemain)
699 Previously, this step was missed if <varname>MODULEDIR</> was set in
700 the extension's Makefile.
706 Fix <application>pgxs</> support for building loadable modules on AIX
711 Building modules outside the original source tree didn't work on AIX.
717 Update time zone data files to <application>tzdata</> release 2012j
718 for DST law changes in Cuba, Israel, Jordan, Libya, Palestine, Western
719 Samoa, and portions of Brazil.
728 <sect1 id="release-9-2-1">
729 <title>Release 9.2.1</title>
732 <title>Release Date</title>
733 <simpara>2012-09-24</simpara>
737 This release contains a variety of fixes from 9.2.0.
738 For information about new features in the 9.2 major release, see
739 <xref linkend="release-9-2">.
743 <title>Migration to Version 9.2.1</title>
746 A dump/restore is not required for those running 9.2.X.
750 However, you may need to perform <command>REINDEX</> and/or
751 <command>VACUUM</> operations to recover from the effects of the data
752 corruption bug described in the first changelog item below.
758 <title>Changes</title>
764 Fix persistence marking of shared buffers during WAL replay
769 This mistake can result in buffers not being written out during
770 checkpoints, resulting in data corruption if the server later crashes
771 without ever having written those buffers. Corruption can occur on
772 any server following crash recovery, but it is significantly more
773 likely to occur on standby slave servers since those perform much
774 more WAL replay. There is a low probability of corruption of btree
775 and GIN indexes. There is a much higher probability of corruption
776 of table <quote>visibility maps</>, which might lead to wrong answers
777 from index-only scans. Table data proper cannot be corrupted by this
782 While no index corruption due to this bug is known to have occurred
783 in the field, as a precautionary measure it is recommended that
784 production installations <command>REINDEX</> all btree and GIN
785 indexes at a convenient time after upgrading to 9.2.1.
789 Also, it is recommended to perform a <command>VACUUM</> of all tables
791 linkend="guc-vacuum-freeze-table-age"><varname>vacuum_freeze_table_age</></link>
792 set to zero. This will fix any incorrect visibility map data. <link
793 linkend="guc-vacuum-cost-delay"><varname>vacuum_cost_delay</></link>
794 can be adjusted to reduce the performance impact of vacuuming, while
795 causing it to take longer to finish.
801 Fix possible incorrect sorting of output from queries involving
802 <literal>WHERE <replaceable>indexed_column</> IN
803 (<replaceable>list_of_values</>)</literal> (Tom Lane)
809 Fix planner failure for queries involving <literal>GROUP BY</>
810 expressions along with window functions and aggregates (Tom Lane)
816 Fix planner's assignment of executor parameters (Tom Lane)
820 This error could result in wrong answers from queries that scan the
821 same <literal>WITH</> subquery multiple times.
827 Improve planner's handling of join conditions in index scans (Tom Lane)
833 Improve selectivity estimation for text search queries involving
834 prefixes, i.e. <replaceable>word</><literal>:*</> patterns (Tom Lane)
843 Fix delayed recognition of permissions changes (Tom Lane)
847 A command that needed no locks other than ones its transaction already
848 had might fail to notice a concurrent <command>GRANT</> or
849 <command>REVOKE</> that committed since the start of its transaction.
855 Fix <command>ANALYZE</> to not fail when a column is a domain over an
856 array type (Tom Lane)
862 Prevent PL/Perl from crashing if a recursive PL/Perl function is
863 redefined while being executed (Tom Lane)
869 Work around possible misoptimization in PL/Perl (Tom Lane)
873 Some Linux distributions contain an incorrect version of
874 <filename>pthread.h</> that results in incorrect compiled code in
875 PL/Perl, leading to crashes if a PL/Perl function calls another one
876 that throws an error.
882 Remove unnecessary dependency on <application>pg_config</> from
883 <application>pg_upgrade</> (Peter Eisentraut)
889 Update time zone data files to <application>tzdata</> release 2012f
890 for DST law changes in Fiji
899 <sect1 id="release-9-2">
900 <title>Release 9.2</title>
903 <title>Release Date</title>
904 <simpara>2012-09-10</simpara>
908 <title>Overview</title>
911 This release has been largely focused on performance improvements, though
912 new SQL features are not lacking. Work also continues in the area of
913 replication support. Major enhancements include:
918 <!-- This list duplicates items below, but without authors or details-->
922 Allow queries to retrieve data only from indexes, avoiding heap
923 access (<firstterm>index-only scans</>)
929 Allow the planner to generate custom plans for specific parameter
930 values even when using prepared statements
936 Improve the planner's ability to use nested loops with inner
943 Allow streaming replication slaves to forward data to other slaves
944 (<link linkend="cascading-replication"><firstterm>cascading
945 replication</></link>)
952 linkend="app-pgbasebackup"><application>pg_basebackup</></link>
953 to make base backups from standby servers
960 linkend="app-pgreceivexlog"><application>pg_receivexlog</></link>
961 tool to archive WAL file changes as they are written
967 Add the <link linkend="SPGiST">SP-GiST</link> (Space-Partitioned
968 GiST) index access method
974 Add support for <link linkend="rangetypes">range data types</link>
980 Add a <link linkend="datatype-json"><type>JSON</type></link>
988 linkend="SQL-CREATEVIEW"><literal>security_barrier</></link>
995 Allow <application>libpq</> connection strings to have the format of a
996 <link linkend="libpq-connstring"><acronym>URI</acronym></link>
1002 Add a <link linkend="libpq-single-row-mode">single-row processing
1003 mode</link> to <application>libpq</> for better handling of large
1011 The above items are explained in more detail in the sections below.
1018 <title>Migration to Version 9.2</title>
1021 A dump/restore using <application>pg_dump</application>, or use of
1022 <application>pg_upgrade</application>, is required for those wishing
1023 to migrate data from any previous release.
1027 Version 9.2 contains a number of changes that may affect compatibility
1028 with previous releases. Observe the following incompatibilities:
1032 <title>System Catalogs</title>
1038 Remove the <structfield>spclocation</> field from <link
1039 linkend="catalog-pg-tablespace"><structname>pg_tablespace</></link>
1044 This field was duplicative of the symbolic links that actually define
1045 tablespace locations, and thus risked errors of omission when moving
1046 a tablespace. This change allows tablespace directories to be moved
1047 while the server is down, by manually adjusting the symbolic links.
1048 To replace this field, we have added <link
1049 linkend="functions-info-catalog-table"><function>pg_tablespace_location()</></link>
1050 to allow querying of the symbolic links.
1056 Move <type>tsvector</> most-common-element statistics to new
1057 <link linkend="view-pg-stats"><structname>pg_stats</></link> columns
1058 (Alexander Korotkov)
1062 Consult <structfield>most_common_elems</>
1063 and <structfield>most_common_elem_freqs</> for the data formerly
1064 available in <structfield>most_common_vals</>
1065 and <structfield>most_common_freqs</> for a <type>tsvector</> column.
1074 <title>Functions</title>
1080 Remove <link linkend="hstore">hstore</link>'s <literal>=></>
1081 operator (Robert Haas)
1085 Users should now use <function>hstore(text, text)</>. Since
1086 <productname>PostgreSQL</productname> 9.0, a warning message has been
1087 emitted when an operator named <literal>=></> is created because
1088 the <acronym>SQL</acronym> standard reserves that token for
1096 linkend="functions-xml-processing"><function>xpath()</></link>
1097 escapes special characters in string values (Florian Pflug)
1101 Without this it is possible for the result not to be valid
1102 <acronym>XML</acronym>.
1109 linkend="functions-admin-dbobject"><function>pg_relation_size()</></link>
1110 and friends return NULL if the object does not exist (Phil Sorber)
1114 This prevents queries that call these functions from returning
1115 errors immediately after a concurrent <command>DROP</>.
1122 linkend="functions-datetime-extract"><function>EXTRACT(EPOCH FROM
1123 <replaceable>timestamp without time zone</>)</function></link>
1124 measure the epoch from local midnight, not <acronym>UTC</acronym>
1129 This change reverts an ill-considered change made in release 7.3.
1130 Measuring from <acronym>UTC</acronym> midnight was inconsistent
1131 because it made the result dependent on the <link
1132 linkend="guc-timezone"><varname>timezone</></link> setting, which
1133 computations for <type>timestamp without time zone</> should not be.
1134 The previous behavior remains available by casting the input value
1135 to <type>timestamp with time zone</>.
1141 Properly parse time strings with trailing <literal>yesterday</>,
1142 <literal>today</>, and <literal>tomorrow</> (Dean Rasheed)
1146 Previously, <literal>SELECT '04:00:00 yesterday'::timestamp</literal>
1147 returned yesterday's date at midnight.
1154 linkend="functions-formatting"><function>to_date()</></link> and
1155 <function>to_timestamp()</> to wrap incomplete dates toward 2020
1160 Previously, supplied years and year masks of less than four digits
1161 wrapped inconsistently.
1170 <title>Object Modification</title>
1176 Prevent <link linkend="SQL-ALTERDOMAIN"><command>ALTER
1177 DOMAIN</command></link> from working on non-domain types (Peter
1182 Owner and schema changes were previously possible on non-domain
1189 No longer forcibly lowercase procedural language names in <link
1190 linkend="SQL-CREATEFUNCTION"><command>CREATE FUNCTION</></link>
1195 While unquoted language identifiers are still lowercased, strings
1196 and quoted identifiers are no longer forcibly down-cased.
1197 Thus for example <literal>CREATE FUNCTION ... LANGUAGE 'C'</>
1198 will no longer work; it must be spelled <literal>'c'</>, or better
1205 Change system-generated names of foreign key enforcement triggers
1210 This change ensures that the triggers fire in the correct order in
1211 some corner cases involving self-referential foreign key constraints.
1220 <title>Command-Line Tools</title>
1226 Provide consistent backquote, variable
1227 expansion, and quoted substring behavior in <link
1228 linkend="APP-PSQL"><application>psql</></link> meta-command
1229 arguments (Tom Lane)
1233 Previously, such references were treated oddly when not separated by
1234 whitespace from adjacent text. For example <literal>'FOO'BAR</> was
1235 output as <literal>FOO BAR</> (unexpected insertion of a space) and
1236 <literal>FOO'BAR'BAZ</> was output unchanged (not removing the quotes
1237 as most would expect).
1243 No longer treat <link
1244 linkend="APP-CLUSTERDB"><application>clusterdb</></link>
1245 table names as double-quoted; no longer treat <link
1246 linkend="APP-REINDEXDB"><application>reindexdb</></link> table
1247 and index names as double-quoted (Bruce Momjian)
1251 Users must now include double-quotes in the command arguments if
1258 <link linkend="APP-CREATEUSER"><application>createuser</></link>
1259 no longer prompts for option settings by default (Peter Eisentraut)
1263 Use <option>--interactive</> to obtain the old behavior.
1269 Disable prompting for the user name in <link
1270 linkend="APP-DROPUSER"><application>dropuser</></link> unless
1271 <option>--interactive</> is specified (Peter Eisentraut)
1280 <title>Server Settings</title>
1286 Add server parameters for specifying the <link
1287 linkend="guc-ssl-ca-file">locations of server-side
1288 <acronym>SSL</acronym> files</link> (Peter Eisentraut)
1292 This allows changing the names and locations of the files that were
1293 previously hard-coded as <filename>server.crt</>,
1294 <filename>server.key</>, <filename>root.crt</>, and
1295 <filename>root.crl</> in the data directory.
1296 <emphasis>The server will no longer examine <filename>root.crt</> or
1297 <filename>root.crl</> by default</emphasis>; to load these files, the
1298 associated parameters must be set to non-default values.
1304 Remove the <varname>silent_mode</> parameter (Heikki Linnakangas)
1308 Similar behavior can be obtained with <command>pg_ctl start
1309 -l postmaster.log</>.
1315 Remove the <varname>wal_sender_delay</> parameter,
1316 as it is no longer needed (Tom Lane)
1322 Remove the <varname>custom_variable_classes</> parameter (Tom Lane)
1326 The checking provided by this setting was dubious. Now any
1327 setting can be prefixed by any class name.
1335 <title>Monitoring</title>
1342 linkend="monitoring-stats-views-table"><structname>pg_stat_activity</></link><structfield>.procpid</>
1343 to <structfield>pid</>, to match other system tables (Magnus Hagander)
1349 Create a separate <structfield>pg_stat_activity</> column to
1350 report process state (Scott Mead, Magnus Hagander)
1354 The previous <structfield>query</> and <structfield>query_start</>
1355 values now remain available for an idle session, allowing enhanced
1362 Rename <structname>pg_stat_activity</>.<structfield>current_query</> to
1363 <structfield>query</> because it is not cleared when the query
1364 completes (Magnus Hagander)
1370 Change all <acronym>SQL</acronym>-level statistics timing values
1371 to be <type>float8</> columns measured in milliseconds (Tom Lane)
1375 This change eliminates the designed-in assumption that the values
1376 are accurate to microseconds and no more (since the <type>float8</>
1377 values can be fractional).
1378 The columns affected are
1379 <structname>pg_stat_user_functions</>.<structfield>total_time</>,
1380 <structname>pg_stat_user_functions</>.<structfield>self_time</>,
1381 <structname>pg_stat_xact_user_functions</>.<structfield>total_time</>,
1383 <structname>pg_stat_xact_user_functions</>.<structfield>self_time</>.
1384 The statistics functions underlying these columns now also return
1385 <type>float8</> milliseconds, rather than <type>bigint</>
1387 <filename>contrib/pg_stat_statements</>'
1388 <structfield>total_time</> column is now also measured in
1400 <title>Changes</title>
1403 Below you will find a detailed account of the changes between
1404 <productname>PostgreSQL</productname> 9.2 and the previous major
1409 <title>Server</title>
1412 <title>Performance</title>
1417 Allow queries to retrieve data only from indexes, avoiding heap
1418 access (Robert Haas, Ibrar Ahmed, Heikki Linnakangas, Tom Lane)
1422 This feature is often called <firstterm>index-only scans</>.
1423 Heap access can be skipped for heap pages containing only tuples that
1424 are visible to all sessions, as reported by the visibility map; so
1425 the benefit applies mainly to mostly-static data. The visibility map
1426 was made crash-safe as a necessary part of implementing this feature.
1432 Add the <link linkend="SPGiST">SP-GiST</link> (Space-Partitioned
1433 GiST) index access method (Teodor Sigaev, Oleg Bartunov, Tom
1438 SP-GiST is comparable to GiST in flexibility, but supports
1439 unbalanced partitioned search structures rather than balanced
1440 trees. For suitable problems, SP-GiST can be faster than GiST in both
1441 index build time and search time.
1447 Allow group commit to work effectively under heavy load (Peter
1448 Geoghegan, Simon Riggs, Heikki Linnakangas)
1452 Previously, batching of commits became ineffective as the write
1453 workload increased, because of internal lock contention.
1459 Allow uncontended locks to be managed using a new
1460 fast-path lock mechanism (Robert Haas)
1466 Reduce overhead of creating virtual transaction ID locks (Robert
1473 Reduce the overhead of serializable isolation level locks (Dan
1480 Improve PowerPC and Itanium spinlock performance (Manabu Ori,
1481 Robert Haas, Tom Lane)
1487 Reduce overhead for shared invalidation cache messages (Robert
1494 Move the frequently accessed members of the <structname>PGPROC</>
1495 shared memory array to a separate array (Pavan
1496 Deolasee, Heikki Linnakangas, Robert Haas)
1502 Improve <command>COPY</command> performance by adding tuples to
1503 the heap in batches (Heikki Linnakangas)
1509 Improve GiST index performance for geometric data types by producing
1510 better trees with less memory allocation overhead (Alexander Korotkov)
1516 Improve GiST index build times (Alexander Korotkov, Heikki
1523 Allow hint bits to be set sooner for temporary and unlogged tables
1530 Allow sorting to be performed by inlined,
1531 non-<acronym>SQL</acronym>-callable comparison functions (Peter
1532 Geoghegan, Robert Haas, Tom Lane)
1538 Make the number of CLOG buffers scale based on <link
1539 linkend="guc-shared-buffers"><varname>shared_buffers</></link>
1540 (Robert Haas, Simon Riggs, Tom Lane)
1546 Improve performance of buffer pool scans that occur when tables or
1547 databases are dropped (Jeff Janes, Simon Riggs)
1553 Improve performance of checkpointer's fsync-request queue
1554 when many tables are being dropped or truncated (Tom Lane)
1560 Pass the safe number of file descriptors to child processes on Windows
1561 (Heikki Linnakangas)
1565 This allows Windows sessions to use more open file descriptors than
1575 <title>Process Management</title>
1581 Create a dedicated background process to perform checkpoints (Simon
1586 Formerly the background writer did both dirty-page writing and
1587 checkpointing. Separating this into two processes allows each goal
1588 to be accomplished more predictably.
1594 Improve asynchronous commit behavior by waking the walwriter sooner
1599 Previously, only <link
1600 linkend="guc-wal-writer-delay"><varname>wal_writer_delay</></link>
1601 triggered <acronym>WAL</acronym> flushing to disk; now filling a
1602 <acronym>WAL</acronym> buffer also triggers <acronym>WAL</acronym>
1609 Allow the bgwriter, walwriter, checkpointer, statistics collector,
1610 log collector, and archiver background processes to sleep more
1611 efficiently during periods of inactivity (Peter Geoghegan, Tom Lane)
1615 This series of changes reduces the frequency of process wake-ups when
1616 there is nothing to do, dramatically reducing power consumption on
1626 <title>Optimizer</title>
1632 Allow the planner to generate custom plans for specific parameter
1633 values even when using prepared statements
1638 In the past, a prepared statement always had a single
1639 <quote>generic</> plan that was used for all parameter values, which
1640 was frequently much inferior to the plans used for non-prepared
1641 statements containing explicit constant values. Now, the planner
1642 attempts to generate custom plans for specific parameter values.
1643 A generic plan will only be used after custom plans have repeatedly
1644 proven to provide no benefit. This change should eliminate the
1645 performance penalties formerly seen from use of prepared statements
1646 (including non-dynamic statements in PL/pgSQL).
1652 Improve the planner's ability to use nested loops with inner
1653 index scans (Tom Lane)
1657 The new <quote>parameterized path</> mechanism allows inner
1658 index scans to use values from relations that are more than one join
1659 level up from the scan. This can greatly improve performance in
1660 situations where semantic restrictions (such as outer joins) limit
1661 the allowed join orderings.
1667 Improve the planning <acronym>API</acronym> for foreign data wrappers
1668 (Etsuro Fujita, Shigeru Hanada, Tom Lane)
1672 Wrappers can now provide multiple access <quote>paths</> for their
1673 tables, allowing more flexibility in join planning.
1679 Recognize self-contradictory restriction clauses for non-table
1680 relations (Tom Lane)
1684 This check is only performed when <link
1685 linkend="guc-constraint-exclusion"><varname>constraint_exclusion</></link>
1686 is <literal>on</literal>.
1692 Allow <literal>indexed_col op ANY(ARRAY[...])</> conditions to be
1693 used in plain index scans and index-only scans (Tom Lane)
1697 Formerly such conditions could only be used in bitmap index scans.
1703 Support <function>MIN</>/<function>MAX</> index optimizations on
1704 <type>boolean</type> columns (Marti Raudsepp)
1710 Account for set-returning functions in <command>SELECT</> target
1711 lists when setting row count estimates (Tom Lane)
1717 Fix planner to handle indexes with duplicated columns more reliably
1724 Collect and use element-frequency statistics for arrays (Alexander
1729 This change improves selectivity estimation for the array
1730 <literal><@</literal>, <literal>&&</literal>, and
1731 <literal>@></literal> operators (array containment and overlaps).
1737 Allow statistics to be collected for foreign tables
1744 Improve cost estimates for use of partial indexes (Tom Lane)
1750 Improve the planner's ability to use statistics for columns
1751 referenced in subqueries (Tom Lane)
1757 Improve statistical estimates for subqueries using
1758 <literal>DISTINCT</> (Tom Lane)
1767 <title>Authentication</title>
1773 Do not treat role names and <literal>samerole</> specified in <link
1774 linkend="auth-pg-hba-conf"><filename>pg_hba.conf</filename></link>
1775 as automatically including superusers (Andrew Dunstan)
1779 This makes it easier to use <literal>reject</> lines with group roles.
1785 Adjust <filename>pg_hba.conf</filename> processing to handle token
1786 parsing more consistently (Brendan Jurd, Álvaro Herrera)
1792 Disallow empty <filename>pg_hba.conf</filename> files (Tom Lane)
1796 This was done to more quickly detect misconfiguration.
1802 Make superuser privilege imply replication privilege (Noah Misch)
1806 This avoids the need to explicitly assign such privileges.
1815 <title>Monitoring</title>
1821 Attempt to log the current query string during a backend crash
1828 Make logging of autovacuum I/O activity more verbose (Greg
1833 This logging is triggered by <link
1834 linkend="guc-log-autovacuum-min-duration"><varname>log_autovacuum_min_duration</></link>.
1840 Make <acronym>WAL</acronym> replay report failures sooner
1845 There were some cases where failures were only reported once the
1846 server went into master mode.
1853 linkend="functions-admin-backup"><function>pg_xlog_location_diff()</></link>
1854 to simplify WAL location comparisons (Euler Taveira de Oliveira)
1858 This is useful for computing replication lag.
1864 Support configurable event log application names on Windows
1865 (MauMau, Magnus Hagander)
1869 This allows different instances to use the event log
1870 with different identifiers, by setting the <link
1871 linkend="guc-event-source"><varname>event_source</></link>
1872 server parameter, which is similar to how <link
1873 linkend="guc-syslog-ident"><varname>syslog_ident</></link> works.
1879 Change <quote>unexpected EOF</> messages to <literal>DEBUG1</> level,
1880 except when there is an open transaction (Magnus Hagander)
1884 This change reduces log chatter caused by applications that close
1885 database connections ungracefully.
1894 <title>Statistical Views</title>
1900 Track temporary file sizes and file counts in the <link
1901 linkend="pg-stat-database-view"><structname>pg_stat_database</></link>
1902 system view (Tomas Vondra)
1908 Add a deadlock counter to the <structname>pg_stat_database</>
1909 system view (Magnus Hagander)
1915 Add a server parameter <link
1916 linkend="guc-track-io-timing"><varname>track_io_timing</></link>
1917 to track I/O timings (Ants Aasma, Robert Haas)
1923 Report checkpoint timing information in <link
1924 linkend="pg-stat-bgwriter-view"><structname>pg_stat_bgwriter</></link>
1925 (Greg Smith, Peter Geoghegan)
1934 <title>Server Settings</title>
1940 Silently ignore nonexistent schemas specified in <link
1941 linkend="guc-search-path"><varname>search_path</></link> (Tom Lane)
1945 This makes it more convenient to use generic path settings, which
1946 might include some schemas that don't exist in all databases.
1952 Allow superusers to set <link
1953 linkend="guc-deadlock-timeout"><varname>deadlock_timeout</></link>
1954 per-session, not just per-cluster (Noah Misch)
1958 This allows <varname>deadlock_timeout</> to be reduced for
1959 transactions that are likely to be involved in a deadlock, thus
1960 detecting the failure more quickly. Alternatively, increasing the
1961 value can be used to reduce the chances of a session being chosen for
1962 cancellation due to a deadlock.
1968 Add a server parameter <link
1969 linkend="guc-temp-file-limit"><varname>temp_file_limit</></link>
1970 to constrain temporary file space usage per session (Mark Kirkwood)
1976 Allow a superuser to <command>SET</command> an extension's
1977 superuser-only custom variable before loading the associated
1978 extension (Tom Lane)
1982 The system now remembers whether a <command>SET</command> was
1983 performed by a superuser, so that proper privilege checking can be
1984 done when the extension is loaded.
1990 Add <link linkend="app-postmaster">postmaster</link> <option>-C</>
1991 option to query configuration parameters (Bruce Momjian)
1995 This allows <application>pg_ctl</> to better handle cases where
1996 <envar>PGDATA</> or <option>-D</> points to a configuration-only
2003 Replace an empty locale name with the implied value in
2004 <command>CREATE DATABASE</>
2009 This prevents cases where
2010 <structname>pg_database</>.<structfield>datcollate</> or
2011 <structfield>datctype</> could be interpreted differently after a
2019 <title><filename>postgresql.conf</filename></title>
2025 Allow multiple errors in <filename>postgresql.conf</filename>
2026 to be reported, rather than just the first one (Alexey Klyukin,
2033 Allow a reload of <filename>postgresql.conf</filename> to be
2034 processed by all sessions, even if there are some settings that
2035 are invalid for particular sessions (Alexey Klyukin)
2039 Previously, such not-valid-within-session values would cause all
2040 setting changes to be ignored by that session.
2046 Add an <literal>include_if_exists</> facility for configuration
2051 This works the same as <literal>include</>, except that an error
2052 is not thrown if the file is missing.
2058 Identify the server time zone during <application>initdb</>, and set
2059 <filename>postgresql.conf</filename> entries
2060 <link linkend="guc-timezone"><varname>timezone</></link> and
2061 <link linkend="guc-log-timezone"><varname>log_timezone</></link>
2062 accordingly (Tom Lane)
2066 This avoids expensive time zone probes during server start.
2073 linkend="view-pg-settings"><structname>pg_settings</></link> to
2074 report <filename>postgresql.conf</filename> line numbers on Windows
2088 <title>Replication and Recovery</title>
2094 Allow streaming replication slaves to forward data to other slaves
2095 (<link linkend="cascading-replication"><firstterm>cascading
2096 replication</></link>) (Fujii Masao)
2100 Previously, only the master server could supply streaming
2101 replication log files to standby servers.
2108 linkend="guc-synchronous-commit"><varname>synchronous_commit</></link>
2109 mode <literal>remote_write</> (Fujii Masao, Simon Riggs)
2113 This mode waits for the standby server to write transaction data to
2114 its own operating system, but does not wait for the data to be
2115 flushed to the standby's disk.
2122 linkend="app-pgreceivexlog"><application>pg_receivexlog</></link>
2123 tool to archive WAL file changes as they are written, rather
2124 than waiting for completed WAL files (Magnus Hagander)
2131 linkend="app-pgbasebackup"><application>pg_basebackup</></link>
2132 to make base backups from standby servers (Jun Ishizuka, Fujii Masao)
2136 This feature lets the work of making new base backups be off-loaded
2137 from the primary server.
2143 Allow streaming of WAL files while <application>pg_basebackup</>
2144 is performing a backup (Magnus Hagander)
2148 This allows passing of WAL files to the standby before they are
2149 discarded on the primary.
2158 <title>Queries</title>
2164 Cancel the running query if the client gets disconnected
2169 If the backend detects loss of client connection during a query, it
2170 will now cancel the query rather than attempting to finish it.
2176 Retain column names at run time for row expressions
2177 (Andrew Dunstan, Tom Lane)
2181 This change allows better results when a row value is converted to
2182 <type>hstore</> or <type>json</> type: the fields of the resulting
2183 value will now have the expected names.
2189 Improve column labels used for sub-<command>SELECT</> results
2194 Previously, the generic label <literal>?column?</> was used.
2200 Improve heuristics for determining the types of unknown values
2205 The longstanding rule that an unknown constant might have the
2206 same type as the value on the other side of the operator using it
2207 is now applied when considering polymorphic operators, not only
2208 for simple operator matches.
2214 Warn about creating casts to or from domain types (Robert Haas)
2218 Such casts have no effect.
2224 When a row fails a <literal>CHECK</> or <literal>NOT NULL</>
2225 constraint, show the row's contents as error detail (Jan
2230 This should make it easier to identify which row is problematic
2231 when an insert or update is processing many rows.
2240 <title>Object Manipulation</title>
2246 Provide more reliable operation during concurrent
2247 <acronym>DDL</acronym> (Robert Haas, Noah Misch)
2251 This change adds locking that should eliminate <quote>cache lookup
2252 failed</> errors in many scenarios. Also, it is no longer possible
2253 to add relations to a schema that is being concurrently dropped, a
2254 scenario that formerly led to inconsistent system catalog contents.
2260 Add <literal>CONCURRENTLY</> option to <link
2261 linkend="SQL-DROPINDEX"><command>DROP INDEX</command></link>
2266 This allows index removal without blocking other sessions.
2272 Allow foreign data wrappers to have per-column options (Shigeru Hanada)
2278 Improve pretty-printing of view definitions (Andrew Dunstan)
2285 <title>Constraints</title>
2291 Allow <link linkend="ddl-constraints"><literal>CHECK</></link>
2292 constraints to be declared <literal>NOT VALID</> (Álvaro
2297 Adding a <literal>NOT VALID</> constraint does not cause the table to
2298 be scanned to verify that existing rows meet the constraint.
2299 Subsequently, newly added or updated rows are checked.
2300 Such constraints are ignored by the planner when considering
2301 <varname>constraint_exclusion</>, since it is not certain that all
2302 rows meet the constraint.
2306 The new <command>ALTER TABLE VALIDATE</> command allows <literal>NOT
2307 VALID</> constraints to be checked for existing rows, after which
2308 they are converted into ordinary constraints.
2314 Allow <literal>CHECK</> constraints to be declared <literal>NO
2315 INHERIT</> (Nikhil Sontakke, Alex Hunsaker, Álvaro Herrera)
2319 This makes them enforceable only on the parent table, not on
2326 Add the ability to <link linkend="SQL-ALTERTABLE">rename</link>
2327 constraints (Peter Eisentraut)
2335 <title><command>ALTER</></title>
2341 Reduce need to rebuild tables and indexes for certain <link
2342 linkend="SQL-ALTERTABLE"><command>ALTER TABLE</command></link>
2343 ... <literal>ALTER COLUMN TYPE</> operations (Noah Misch)
2347 Increasing the length limit for a <type>varchar</> or <type>varbit</>
2348 column, or removing the limit altogether, no longer requires a table
2349 rewrite. Similarly, increasing the allowable precision of a
2350 <type>numeric</> column, or changing a column from constrained
2351 <type>numeric</> to unconstrained <type>numeric</>, no longer
2352 requires a table rewrite. Table rewrites are also avoided in similar
2353 cases involving the <type>interval</>, <type>timestamp</>, and
2354 <type>timestamptz</> types.
2360 Avoid having <link linkend="SQL-ALTERTABLE"><command>ALTER
2361 TABLE</command></link> revalidate foreign key constraints in some
2362 cases where it is not necessary (Noah Misch)
2368 Add <literal>IF EXISTS</> options to some <command>ALTER</command>
2369 commands (Pavel Stehule)
2373 For example, <command>ALTER FOREIGN TABLE IF EXISTS foo RENAME
2380 Add <link linkend="SQL-ALTERFOREIGNDATAWRAPPER"><command>ALTER
2381 FOREIGN DATA WRAPPER</command></link> ... <literal>RENAME</>
2382 and <link linkend="SQL-ALTERSERVER"><command>ALTER
2383 SERVER</command></link> ... <literal>RENAME</> (Peter Eisentraut)
2389 Add <link linkend="SQL-ALTERDOMAIN"><command>ALTER
2390 DOMAIN</command></link> ... <literal>RENAME</> (Peter Eisentraut)
2394 You could already rename domains using <command>ALTER
2401 Throw an error for <command>ALTER DOMAIN</command> ... <literal>DROP
2402 CONSTRAINT</> on a nonexistent constraint (Peter Eisentraut)
2406 An <literal>IF EXISTS</> option has been added to provide the
2416 <title><link linkend="SQL-CREATETABLE"><command>CREATE TABLE</></link></title>
2422 Allow <command>CREATE TABLE (LIKE ...)</command> from foreign
2423 tables, views, and composite types (Peter Eisentraut)
2427 For example, this allows a table to be created whose schema matches a
2434 Fix <command>CREATE TABLE (LIKE ...)</command> to avoid index name
2435 conflicts when copying index comments (Tom Lane)
2441 Fix <command>CREATE TABLE</command> ... <literal>AS EXECUTE</>
2442 to handle <literal>WITH NO DATA</> and column name specifications
2452 <title>Object Permissions</title>
2459 linkend="SQL-CREATEVIEW"><literal>security_barrier</></link>
2460 option for views (KaiGai Kohei, Robert Haas)
2464 This option prevents optimizations that might allow view-protected
2465 data to be exposed to users, for example pushing a clause involving
2466 an insecure function into the <literal>WHERE</> clause of the view.
2467 Such views can be expected to perform more poorly than ordinary
2475 linkend="SQL-CREATEFUNCTION"><literal>LEAKPROOF</></link> function
2476 attribute to mark functions that can safely be pushed down
2477 into <literal>security_barrier</> views (KaiGai Kohei)
2483 Add support for privileges on data types (Peter Eisentraut)
2487 This adds support for the <acronym>SQL</>-conforming
2488 <literal>USAGE</> privilege on types and domains. The intent is
2489 to be able to restrict which users can create dependencies on types,
2490 since such dependencies limit the owner's ability to alter the type.
2496 Check for <command>INSERT</command> privileges in <command>SELECT
2497 INTO</command> / <command>CREATE TABLE AS</command> (KaiGai Kohei)
2501 Because the object is being created by <command>SELECT INTO</command>
2502 or <command>CREATE TABLE AS</command>, the creator would ordinarily
2503 have insert permissions; but there are corner cases where this is not
2504 true, such as when <literal>ALTER DEFAULT PRIVILEGES</> has removed
2516 <title>Utility Operations</title>
2522 Allow <link linkend="SQL-VACUUM"><command>VACUUM</></link> to more
2523 easily skip pages that cannot be locked (Simon Riggs, Robert Haas)
2527 This change should greatly reduce the incidence of <command>VACUUM</>
2528 getting <quote>stuck</> waiting for other sessions.
2534 Make <link linkend="SQL-EXPLAIN"><command>EXPLAIN</></link>
2535 <literal>(BUFFERS)</> count blocks dirtied and written (Robert Haas)
2541 Make <command>EXPLAIN ANALYZE</command> report the number of rows
2542 rejected by filter steps (Marko Tiikkaja)
2548 Allow <command>EXPLAIN ANALYZE</command> to avoid timing overhead when
2549 time values are not wanted (Tomas Vondra)
2553 This is accomplished by setting the new <literal>TIMING</> option to
2563 <title>Data Types</title>
2569 Add support for <link linkend="rangetypes">range data types</link>
2570 (Jeff Davis, Tom Lane, Alexander Korotkov)
2574 A range data type stores a lower and upper bound belonging to its
2575 base data type. It supports operations like contains, overlaps, and
2582 Add a <link linkend="datatype-json"><type>JSON</type></link>
2583 data type (Robert Haas)
2587 This type stores <acronym>JSON</acronym> (JavaScript Object Notation)
2588 data with proper validation.
2595 linkend="functions-json"><function>array_to_json()</></link>
2596 and <function>row_to_json()</> (Andrew Dunstan)
2602 Add a <link linkend="datatype-serial"><type>SMALLSERIAL</></link>
2603 data type (Mike Pultz)
2607 This is like <type>SERIAL</>, except it stores the sequence in
2608 a two-byte integer column (<type>int2</>).
2614 Allow <link linkend="SQL-CREATEDOMAIN">domains</link> to be
2615 declared <literal>NOT VALID</> (Álvaro Herrera)
2619 This option can be set at domain creation time, or via <command>ALTER
2620 DOMAIN</command> ... <literal>ADD CONSTRAINT</> ... <literal>NOT
2621 VALID</>. <command>ALTER DOMAIN</command> ... <literal>VALIDATE
2622 CONSTRAINT</> fully validates the constraint.
2628 Support more locale-specific formatting options for the <link
2629 linkend="datatype-money"><type>money</></link> data type (Tom Lane)
2633 Specifically, honor all the POSIX options for ordering of the value,
2634 sign, and currency symbol in monetary output. Also, make sure that
2635 the thousands separator is only inserted to the left of the decimal
2636 point, as required by POSIX.
2642 Add bitwise <quote>and</>, <quote>or</>, and <quote>not</>
2643 operators for the <type>macaddr</> data type (Brendan Jurd)
2650 linkend="functions-xml-processing"><function>xpath()</></link> to
2651 return a single-element <acronym>XML</acronym> array when supplied a
2652 scalar value (Florian Pflug)
2656 Previously, it returned an empty array. This change will also
2657 cause <function>xpath_exists()</> to return true, not false,
2658 for such expressions.
2664 Improve <acronym>XML</acronym> error handling to be more robust
2674 <title>Functions</title>
2680 Allow non-superusers to use <link
2681 linkend="functions-admin-signal"><function>pg_cancel_backend()</></link>
2683 linkend="functions-admin-signal"><function>pg_terminate_backend()</></link>
2684 on other sessions belonging to the same user
2685 (Magnus Hagander, Josh Kupershmidt, Dan Farina)
2689 Previously only superusers were allowed to use these functions.
2695 Allow importing and exporting of transaction snapshots (Joachim
2700 This allows multiple transactions to share identical views of the
2702 Snapshots are exported via <link
2703 linkend="functions-snapshot-synchronization"><function>pg_export_snapshot()</></link>
2704 and imported via <link linkend="SQL-SET-TRANSACTION"><command>SET
2705 TRANSACTION SNAPSHOT</command></link>. Only snapshots from
2706 currently-running transactions can be imported.
2713 linkend="functions-info-catalog-table"><literal>COLLATION
2714 FOR</></link> on expressions (Peter Eisentraut)
2718 This returns a string representing the collation of the expression.
2725 linkend="functions-info-schema-table"><function>pg_opfamily_is_visible()</></link>
2732 Add a <type>numeric</> variant of <link
2733 linkend="functions-admin-dbsize"><function>pg_size_pretty()</></link>
2734 for use with <function>pg_xlog_location_diff()</> (Fujii Masao)
2741 linkend="functions-info-session-table"><function>pg_trigger_depth()</></link>
2742 function (Kevin Grittner)
2746 This reports the current trigger call depth.
2753 linkend="functions-aggregate-table"><function>string_agg()</></link>
2754 to process <type>bytea</> values (Pavel Stehule)
2760 Fix regular expressions in which a back-reference occurs within
2761 a larger quantified subexpression (Tom Lane)
2765 For example, <literal>^(\w+)( \1)+$</>. Previous releases did not
2766 check that the back-reference actually matched the first occurrence.
2775 <title><link linkend="information-schema">Information Schema</link></title>
2781 Add information schema views
2782 <structname>role_udt_grants</>, <structname>udt_privileges</>,
2783 and <structname>user_defined_types</> (Peter Eisentraut)
2789 Add composite-type attributes to the
2790 information schema <structname>element_types</> view
2797 Implement <structfield>interval_type</> columns in the information
2798 schema (Peter Eisentraut)
2802 Formerly these columns read as nulls.
2808 Implement collation-related columns in the information schema
2809 <structname>attributes</>, <structname>columns</>,
2810 <structname>domains</>, and <structname>element_types</>
2811 views (Peter Eisentraut)
2817 Implement the <structfield>with_hierarchy</> column in the
2818 information schema <structname>table_privileges</> view (Peter
2825 Add display of sequence <literal>USAGE</> privileges to information
2826 schema (Peter Eisentraut)
2832 Make the information schema show default privileges (Peter
2837 Previously, non-empty default permissions were not represented in the
2847 <title>Server-Side Languages</title>
2850 <title><link linkend="plpgsql">PL/pgSQL</link> Server-Side Language</title>
2856 Allow the PL/pgSQL <command>OPEN</> cursor command to supply
2857 parameters by name (Yeb Havinga)
2863 Add a <command>GET STACKED DIAGNOSTICS</command> PL/pgSQL command
2864 to retrieve exception info (Pavel Stehule)
2870 Speed up PL/pgSQL array assignment by caching type information
2877 Improve performance and memory consumption for long chains of
2878 <literal>ELSIF</> clauses (Tom Lane)
2884 Output the function signature, not just the name, in PL/pgSQL
2885 error messages (Pavel Stehule)
2894 <title><link linkend="plpython">PL/Python</link> Server-Side Language</title>
2900 Add PL/Python <acronym>SPI</acronym> cursor support (Jan
2905 This allows PL/Python to read partial result sets.
2911 Add result metadata functions to PL/Python (Peter Eisentraut)
2915 Specifically, this adds result object functions
2916 <literal>.colnames</literal>, <literal>.coltypes</literal>, and
2917 <literal>.coltypmods</literal>.
2923 Remove support for Python 2.2 (Peter Eisentraut)
2932 <title><link linkend="xfunc-sql">SQL</link> Server-Side Language</title>
2937 Allow <acronym>SQL</acronym>-language functions to reference
2938 parameters by name (Matthew Draper)
2942 To use this, simply name the function arguments and then reference
2943 the argument names in the <acronym>SQL</acronym> function body.
2953 <title>Client Applications</title>
2959 Add <link linkend="APP-INITDB"><application>initdb</></link>
2960 options <option>--auth-local</> and <option>--auth-host</>
2965 This allows separate control of <literal>local</> and
2966 <literal>host</> <filename>pg_hba.conf</filename> authentication
2967 settings. <option>--auth</> still controls both.
2973 Add <option>--replication</>/<option>--no-replication</> flags to
2974 <link linkend="APP-CREATEUSER"><application>createuser</></link>
2975 to control replication permission (Fujii Masao)
2981 Add the <option>--if-exists</> option to <link
2982 linkend="APP-DROPDB"><application>dropdb</></link> and <link
2983 linkend="APP-DROPUSER"><application>dropuser</></link> (Josh
2990 Give command-line tools the ability to specify the name of the
2991 database to connect to, and fall back to <literal>template1</>
2992 if a <literal>postgres</> database connection fails (Robert Haas)
2999 <title><link linkend="APP-PSQL"><application>psql</></link></title>
3005 Add a display mode to auto-expand output based on the
3006 display width (Peter Eisentraut)
3010 This adds the <literal>auto</> option to the <command>\x</>
3011 command, which switches to the expanded mode when the normal
3012 output would be wider than the screen.
3018 Allow inclusion of a script file that is named relative to the
3019 directory of the file from which it was invoked (Gurjeet Singh)
3023 This is done with a new command <command>\ir</>.
3029 Add support for non-<acronym>ASCII</acronym> characters in
3030 <application>psql</> variable names (Tom Lane)
3036 Add support for major-version-specific <filename>.psqlrc</> files
3041 <application>psql</> already supported minor-version-specific
3042 <filename>.psqlrc</> files.
3048 Provide environment variable overrides for <application>psql</>
3049 history and startup file locations (Andrew Dunstan)
3053 <envar>PSQL_HISTORY</envar> and <envar>PSQLRC</envar> now
3054 determine these file names if set.
3060 Add a <command>\setenv</> command to modify
3061 the environment variables passed to child processes (Andrew Dunstan)
3067 Name <application>psql</>'s temporary editor files with a
3068 <filename>.sql</> extension (Peter Eisentraut)
3072 This allows extension-sensitive editors to select the right mode.
3078 Allow <application>psql</> to use zero-byte field and record
3079 separators (Peter Eisentraut)
3083 Various shell tools use zero-byte (NUL) separators,
3084 e.g. <application>find</>.
3090 Make the <command>\timing</> option report times for
3091 failed queries (Magnus Hagander)
3095 Previously times were reported only for successful queries.
3101 Unify and tighten <application>psql</>'s treatment of <command>\copy</>
3102 and SQL <command>COPY</> (Noah Misch)
3106 This fix makes failure behavior more predictable and honors
3107 <command>\set ON_ERROR_ROLLBACK</>.
3116 <title>Informational Commands</title>
3121 Make <command>\d</> on a sequence show the
3122 table/column name owning it (Magnus Hagander)
3128 Show statistics target for columns in <command>\d+</> (Magnus
3135 Show role password expiration dates in <command>\du</>
3136 (Fabrízio de Royes Mello)
3142 Display comments for casts, conversions, domains, and languages
3147 These are included in the output of <command>\dC+</>,
3148 <command>\dc+</>, <command>\dD+</>, and <command>\dL</> respectively.
3154 Display comments for <acronym>SQL</acronym>/<acronym>MED</acronym>
3155 objects (Josh Kupershmidt)
3159 These are included in the output of <command>\des+</>,
3160 <command>\det+</>, and <command>\dew+</> for foreign servers, foreign
3161 tables, and foreign data wrappers respectively.
3167 Change <command>\dd</> to display comments only for object types
3168 without their own backslash command (Josh Kupershmidt)
3177 <title>Tab Completion</title>
3183 In <application>psql</> tab completion, complete <acronym>SQL</>
3184 keywords in either upper or lower case according to the new <link
3185 linkend="APP-PSQL-variables"><literal>COMP_KEYWORD_CASE</></link>
3186 setting (Peter Eisentraut)
3192 Add tab completion support for
3193 <command>EXECUTE</command> (Andreas Karlsson)
3199 Allow tab completion of role references in
3200 <command>GRANT</command>/<command>REVOKE</command> (Peter
3207 Allow tab completion of file names to supply quotes, when necessary
3214 Change tab completion support for
3215 <command>TABLE</command> to also include views (Magnus Hagander)
3224 <title><link linkend="APP-PGDUMP"><application>pg_dump</></link></title>
3230 Add an <option>--exclude-table-data</> option to
3231 <application>pg_dump</> (Andrew Dunstan)
3235 This allows dumping of a table's definition but not its data,
3236 on a per-table basis.
3242 Add a <option>--section</> option to <application>pg_dump</>
3243 and <application>pg_restore</> (Andrew Dunstan)
3247 Valid values are <literal>pre-data</>, <literal>data</>,
3248 and <literal>post-data</>. The option can be
3249 given more than once to select two or more sections.
3256 linkend="APP-PG-DUMPALL"><application>pg_dumpall</></link> dump all
3257 roles first, then all configuration settings on roles (Phil Sorber)
3261 This allows a role's configuration settings to mention other
3262 roles without generating an error.
3268 Allow <application>pg_dumpall</> to avoid errors if the
3269 <literal>postgres</> database is missing in the new cluster
3276 Dump foreign server user mappings in user name order (Peter
3281 This helps produce deterministic dump files.
3287 Dump operators in a predictable order (Peter Eisentraut)
3293 Tighten rules for when extension configuration tables are dumped
3294 by <application>pg_dump</> (Tom Lane)
3300 Make <application>pg_dump</> emit more useful dependency
3301 information (Tom Lane)
3305 The dependency links included in archive-format dumps were formerly
3306 of very limited use, because they frequently referenced objects that
3307 appeared nowhere in the dump. Now they represent actual dependencies
3308 (possibly indirect) among the dumped objects.
3314 Improve <application>pg_dump</>'s performance when dumping many
3315 database objects (Tom Lane)
3326 <title><link linkend="libpq"><application>libpq</></link></title>
3332 Allow <application>libpq</> connection strings to have the format of a
3333 <link linkend="libpq-connstring"><acronym>URI</acronym></link>
3338 The syntax begins with <literal>postgres://</>. This can allow
3339 applications to avoid implementing their own parser for URIs
3340 representing database connections.
3346 Add a <link linkend="libpq-connect-sslcompression">connection
3347 option</link> to disable <acronym>SSL</acronym> compression
3352 This can be used to remove the overhead of <acronym>SSL</acronym>
3353 compression on fast networks.
3359 Add a <link linkend="libpq-single-row-mode">single-row processing
3360 mode</link> for better handling of large result sets
3361 (Kyotaro Horiguchi, Marko Kreen)
3365 Previously, <application>libpq</> always collected the entire query
3366 result in memory before passing it back to the application.
3372 Add <literal>const</> qualifiers to the declarations of the functions
3373 <function>PQconnectdbParams</>, <function>PQconnectStartParams</>,
3374 and <function>PQpingParams</> (Lionel Elie Mamane)
3380 Allow the <filename>.pgpass</> file to include escaped characters
3381 in the password field (Robert Haas)
3387 Make library functions use <function>abort()</> instead of
3388 <function>exit()</> when it is necessary to terminate the process
3393 This choice does not interfere with the normal exit codes used by the
3394 program, and generates a signal that can be caught by the caller.
3403 <title>Source Code</title>
3409 Remove dead ports (Peter Eisentraut)
3413 The following platforms are no longer supported: dgux,
3414 nextstep, sunos4, svr4, ultrix4, univel, bsdi.
3420 Add support for building with <link linkend="install-windows">MS
3421 Visual Studio 2010</link> (Brar Piening)
3427 Enable compiling with the MinGW-w64 32-bit compiler (Lars Kanis)
3433 Install <filename>plpgsql.h</> into <filename>include/server</> during installation
3434 (Heikki Linnakangas)
3440 Improve the latch facility to include detection of postmaster death
3441 (Peter Geoghegan, Heikki Linnakangas, Tom Lane)
3445 This eliminates one of the main reasons that background processes
3446 formerly had to wake up to poll for events.
3452 Use C flexible array members, where supported (Peter Eisentraut)
3458 Improve the concurrent transaction regression tests
3459 (<application>isolationtester</>) (Noah Misch)
3465 Modify <application>thread_test</> to create its test files in
3466 the current directory, rather than <filename>/tmp</> (Bruce Momjian)
3472 Improve flex and bison warning and error reporting (Tom Lane)
3478 Add memory barrier support (Robert Haas)
3482 This is currently unused.
3488 Modify pgindent to use a typedef file (Bruce Momjian)
3494 Add a hook for processing messages due to be sent to the server
3501 Add object access hooks for <command>DROP</command> commands
3508 Centralize <command>DROP</command> handling for some object types
3515 Add a <application>pg_upgrade</> test suite (Peter Eisentraut)
3521 Sync regular expression code with <acronym>TCL</acronym> 8.5.11
3522 and improve internal processing (Tom Lane)
3528 Move <acronym>CRC</acronym> tables to libpgport, and provide them
3529 in a separate include file (Daniel Farina)
3535 Add options to <application>git_changelog</> for use in major
3536 release note creation (Bruce Momjian)
3542 Support Linux's <filename>/proc/self/oom_score_adj</> API (Tom Lane)
3551 <title>Additional Modules</title>
3557 Improve efficiency of <link linkend="dblink">dblink</link> by using
3558 libpq's new single-row processing mode (Kyotaro Horiguchi, Marko
3563 This improvement does not apply to
3564 <function>dblink_send_query()</>/<function>dblink_get_result()</>.
3570 Support <literal>force_not_null</> option in <link
3571 linkend="file-fdw">file_fdw</link> (Shigeru Hanada)
3577 Implement dry-run mode for <link
3578 linkend="pgarchivecleanup"><application>pg_archivecleanup</></link>
3579 (Gabriele Bartolini)
3583 This only outputs the names of files to be deleted.
3589 Add new <link linkend="pgbench">pgbench</link> switches
3590 <option>--unlogged-tables</>, <option>--tablespace</>, and
3591 <option>--index-tablespace</> (Robert Haas)
3598 linkend="pgtestfsync"><application>pg_test_fsync</></link> to test
3599 for a fixed amount of time, rather than a fixed number of cycles
3604 The <option>-o</>/cycles option was removed, and
3605 <option>-s</>/seconds added.
3612 linkend="pgtesttiming"><application>pg_test_timing</></link>
3613 utility to measure clock monotonicity and timing overhead (Ants
3620 Add a <link linkend="tcn">tcn</link> (triggered change notification)
3621 module to generate <command>NOTIFY</command> events on table changes
3629 <title><link linkend="pgupgrade"><application>pg_upgrade</></link></title>
3635 Adjust <application>pg_upgrade</> environment variables (Bruce
3640 Rename data, bin, and port environment
3641 variables to begin with <literal>PG</>, and support
3642 <envar>PGPORTOLD</envar>/<envar>PGPORTNEW</envar>, to replace
3643 <envar>PGPORT</envar>.
3649 Overhaul <application>pg_upgrade</> logging and failure reporting
3654 Create four append-only log files, and delete them on success.
3655 Add <option>-r</>/<option>--retain</> option to unconditionally
3656 retain these files. Also remove <application>pg_upgrade</> options
3657 <option>-g</>/<option>-G</>/<option>-l</> options as unnecessary,
3658 and tighten log file permissions.
3664 Make <application>pg_upgrade</> create a script to incrementally
3665 generate more accurate optimizer statistics (Bruce Momjian)
3669 This reduces the time needed to generate minimal cluster statistics
3676 Allow <application>pg_upgrade</> to upgrade an old cluster that
3677 does not have a <literal>postgres</> database (Bruce Momjian)
3683 Allow <application>pg_upgrade</> to handle cases where some
3684 old or new databases are missing, as long as they are empty
3691 Allow <application>pg_upgrade</> to handle configuration-only
3692 directory installations (Bruce Momjian)
3698 In <application>pg_upgrade</>, add <option>-o</>/<option>-O</>
3699 options to pass parameters to the servers (Bruce Momjian)
3703 This is useful for configuration-only directory installs.
3709 Change <application>pg_upgrade</> to use port 50432 by default
3714 This helps avoid unintended client connections during the upgrade.
3720 Reduce cluster locking in <application>pg_upgrade</> (Bruce
3725 Specifically, only lock the old cluster if link mode is used,
3726 and do it right after the schema is restored.
3735 <title><link linkend="pgstatstatements"><application>pg_stat_statements</></link></title>
3741 Allow <application>pg_stat_statements</> to aggregate similar
3742 queries via SQL text normalization (Peter Geoghegan, Tom Lane)
3746 Users with applications that use non-parameterized SQL will now
3747 be able to monitor query performance without detailed log analysis.
3753 Add dirtied and written block counts and read/write times to
3754 <application>pg_stat_statements</> (Robert Haas, Ants Aasma)
3760 Prevent <application>pg_stat_statements</> from double-counting
3761 <command>PREPARE</command> and <command>EXECUTE</command> commands
3771 <title><link linkend="sepgsql">sepgsql</link></title>
3776 Support <literal>SECURITY LABEL</> on global objects (KaiGai
3781 Specifically, add security labels to databases,
3782 tablespaces, and roles.
3788 Allow sepgsql to honor database labels (KaiGai Kohei)
3794 Perform sepgsql permission checks during the creation of various
3795 objects (KaiGai Kohei)
3801 Add <function>sepgsql_setcon()</> and related functions to control
3802 the sepgsql security domain (KaiGai Kohei)
3808 Add a user space access cache to sepgsql to improve performance
3819 <title>Documentation</title>
3825 Add a rule to optionally build HTML documentation using the
3826 stylesheet from the website (Magnus Hagander)
3830 Use <command>gmake STYLE=website draft</>.
3836 Improve <command>EXPLAIN</command> documentation (Tom Lane)
3842 Document that user/database names are preserved with double-quoting
3843 by command-line tools like <application>vacuumdb</> (Bruce
3850 Document the actual string returned by the client for MD5
3851 authentication (Cyan Ogilvie)
3857 Deprecate use of <literal>GLOBAL</> and <literal>LOCAL</> in
3858 <command>CREATE TEMP TABLE</> (Noah Misch)
3862 <productname>PostgreSQL</> has long treated these keyword as no-ops,
3863 and continues to do so; but in future they might mean what the SQL
3864 standard says they mean, so applications should avoid using them.
projects / postgresql / blob