1 <!-- doc/src/sgml/release-9.2.sgml -->
2 <!-- See header comment in release.sgml about typical markup -->
4 <sect1 id="release-9-2-7">
5 <title>Release 9.2.7</title>
8 <title>Release Date</title>
9 <simpara>2014-02-20</simpara>
13 This release contains a variety of fixes from 9.2.6.
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.7</title>
22 A dump/restore is not required for those running 9.2.X.
26 However, if you are upgrading from a version earlier than 9.2.6,
27 see <xref linkend="release-9-2-6">.
33 <title>Changes</title>
39 Shore up <literal>GRANT ... WITH ADMIN OPTION</> restrictions
44 Granting a role without <literal>ADMIN OPTION</> is supposed to
45 prevent the grantee from adding or removing members from the granted
46 role, but this restriction was easily bypassed by doing <literal>SET
47 ROLE</> first. The security impact is mostly that a role member can
48 revoke the access of others, contrary to the wishes of his grantor.
49 Unapproved role member additions are a lesser concern, since an
50 uncooperative role member could provide most of his rights to others
51 anyway by creating views or <literal>SECURITY DEFINER</> functions.
58 Prevent privilege escalation via manual calls to PL validator
59 functions (Andres Freund)
63 The primary role of PL validator functions is to be called implicitly
64 during <command>CREATE FUNCTION</>, but they are also normal SQL
65 functions that a user can call explicitly. Calling a validator on
66 a function actually written in some other language was not checked
67 for and could be exploited for privilege-escalation purposes.
68 The fix involves adding a call to a privilege-checking function in
69 each validator function. Non-core procedural languages will also
70 need to make this change to their own validator functions, if any.
77 Avoid multiple name lookups during table and index DDL
78 (Robert Haas, Andres Freund)
82 If the name lookups come to different conclusions due to concurrent
83 activity, we might perform some parts of the DDL on a different table
84 than other parts. At least in the case of <command>CREATE INDEX</>,
85 this can be used to cause the permissions checks to be performed
86 against a different table than the index creation, allowing for a
87 privilege escalation attack.
94 Prevent buffer overrun with long datetime strings (Noah Misch)
98 The <literal>MAXDATELEN</> constant was too small for the longest
99 possible value of type <type>interval</>, allowing a buffer overrun
100 in <function>interval_out()</>. Although the datetime input
101 functions were more careful about avoiding buffer overrun, the limit
102 was short enough to cause them to reject some valid inputs, such as
103 input containing a very long timezone name. The <application>ecpg</>
104 library contained these vulnerabilities along with some of its own.
111 Prevent buffer overrun due to integer overflow in size calculations
112 (Noah Misch, Heikki Linnakangas)
116 Several functions, mostly type input functions, calculated an
117 allocation size without checking for overflow. If overflow did
118 occur, a too-small buffer would be allocated and then written past.
125 Prevent overruns of fixed-size buffers
126 (Peter Eisentraut, Jozef Mlich)
130 Use <function>strlcpy()</> and related functions to provide a clear
131 guarantee that fixed-size buffers are not overrun. Unlike the
132 preceding items, it is unclear whether these cases really represent
133 live issues, since in most cases there appear to be previous
134 constraints on the size of the input string. Nonetheless it seems
135 prudent to silence all Coverity warnings of this type.
142 Avoid crashing if <function>crypt()</> returns NULL (Honza Horak,
147 There are relatively few scenarios in which <function>crypt()</>
148 could return NULL, but <filename>contrib/chkpass</> would crash
149 if it did. One practical case in which this could be an issue is
150 if <application>libc</> is configured to refuse to execute unapproved
151 hashing algorithms (e.g., <quote>FIPS mode</>).
158 Document risks of <literal>make check</> in the regression testing
159 instructions (Noah Misch, Tom Lane)
163 Since the temporary server started by <literal>make check</>
164 uses <quote>trust</> authentication, another user on the same machine
165 could connect to it as database superuser, and then potentially
166 exploit the privileges of the operating-system user who started the
167 tests. A future release will probably incorporate changes in the
168 testing procedure to prevent this risk, but some public discussion is
169 needed first. So for the moment, just warn people against using
170 <literal>make check</> when there are untrusted users on the
178 Fix possible mis-replay of WAL records when some segments of a
179 relation aren't full size (Greg Stark, Tom Lane)
183 The WAL update could be applied to the wrong page, potentially many
184 pages past where it should have been. Aside from corrupting data,
185 this error has been observed to result in significant <quote>bloat</>
186 of standby servers compared to their masters, due to updates being
187 applied far beyond where the end-of-file should have been. This
188 failure mode does not appear to be a significant risk during crash
189 recovery, only when initially synchronizing a standby created from a
190 base backup taken from a quickly-changing master.
196 Fix bug in determining when recovery has reached consistency
197 (Tomonari Katsumata, Heikki Linnakangas)
201 In some cases WAL replay would mistakenly conclude that the database
202 was already consistent at the start of replay, thus possibly allowing
203 hot-standby queries before the database was really consistent. Other
204 symptoms such as <quote>PANIC: WAL contains references to invalid
205 pages</> were also possible.
211 Fix improper locking of btree index pages while replaying
212 a <literal>VACUUM</> operation in hot-standby mode (Andres Freund,
213 Heikki Linnakangas, Tom Lane)
217 This error could result in <quote>PANIC: WAL contains references to
218 invalid pages</> failures.
224 Ensure that insertions into non-leaf GIN index pages write a full-page
225 WAL record when appropriate (Heikki Linnakangas)
229 The previous coding risked index corruption in the event of a
230 partial-page write during a system crash.
236 When <literal>pause_at_recovery_target</>
237 and <literal>recovery_target_inclusive</> are both set, ensure the
238 target record is applied before pausing, not after (Heikki
245 Fix race conditions during server process exit (Robert Haas)
249 Ensure that signal handlers don't attempt to use the
250 process's <varname>MyProc</> pointer after it's no longer valid.
256 Fix race conditions in walsender shutdown logic and walreceiver
257 SIGHUP signal handler (Tom Lane)
263 Fix unsafe references to <varname>errno</> within error reporting
264 logic (Christian Kruse)
268 This would typically lead to odd behaviors such as missing or
269 inappropriate <literal>HINT</> fields.
275 Fix possible crashes from using <function>ereport()</> too early
276 during server startup (Tom Lane)
280 The principal case we've seen in the field is a crash if the server
281 is started in a directory it doesn't have permission to read.
287 Clear retry flags properly in OpenSSL socket write
288 function (Alexander Kukushkin)
292 This omission could result in a server lockup after unexpected loss
293 of an SSL-encrypted connection.
299 Fix length checking for Unicode identifiers (<literal>U&"..."</>
300 syntax) containing escapes (Tom Lane)
304 A spurious truncation warning would be printed for such identifiers
305 if the escaped form of the identifier was too long, but the
306 identifier actually didn't need truncation after de-escaping.
312 Allow keywords that are type names to be used in lists of roles
317 A previous patch allowed such keywords to be used without quoting
318 in places such as role identifiers; but it missed cases where a
319 list of role identifiers was permitted, such as <literal>DROP ROLE</>.
325 Fix parser crash for <literal>EXISTS(SELECT * FROM
326 zero_column_table)</literal> (Tom Lane)
332 Fix possible crash due to invalid plan for nested sub-selects, such
333 as <literal>WHERE (... x IN (SELECT ...) ...) IN (SELECT ...)</>
340 Fix <literal>UPDATE/DELETE</> of an inherited target table
341 that has <literal>UNION ALL</> subqueries (Tom Lane)
345 Without this fix, <literal>UNION ALL</> subqueries aren't correctly
346 inserted into the update plans for inheritance child tables after the
347 first one, typically resulting in no update happening for those child
354 Ensure that <command>ANALYZE</> creates statistics for a table column
355 even when all the values in it are <quote>too wide</> (Tom Lane)
359 <command>ANALYZE</> intentionally omits very wide values from its
360 histogram and most-common-values calculations, but it neglected to do
361 something sane in the case that all the sampled entries are too wide.
367 In <literal>ALTER TABLE ... SET TABLESPACE</>, allow the database's
368 default tablespace to be used without a permissions check
373 <literal>CREATE TABLE</> has always allowed such usage,
374 but <literal>ALTER TABLE</> didn't get the memo.
380 Fix <quote>cannot accept a set</> error when some arms of
381 a <literal>CASE</> return a set and others don't (Tom Lane)
387 Properly distinguish numbers from non-numbers when generating JSON
388 output (Andrew Dunstan)
394 Fix checks for all-zero client addresses in pgstat functions (Kevin
401 Fix possible misclassification of multibyte characters by the text
402 search parser (Tom Lane)
406 Non-ASCII characters could be misclassified when using C locale with
407 a multibyte encoding. On Cygwin, non-C locales could fail as well.
413 Fix possible misbehavior in <function>plainto_tsquery()</>
418 Use <function>memmove()</> not <function>memcpy()</> for copying
419 overlapping memory regions. There have been no field reports of
420 this actually causing trouble, but it's certainly risky.
426 Fix placement of permissions checks in <function>pg_start_backup()</>
427 and <function>pg_stop_backup()</> (Andres Freund, Magnus Hagander)
431 The previous coding might attempt to do catalog access when it
438 Accept <literal>SHIFT_JIS</> as an encoding name for locale checking
439 purposes (Tatsuo Ishii)
445 Fix <literal>*</>-qualification of named parameters in SQL-language
450 Given a composite-type parameter
451 named <literal>foo</>, <literal>$1.*</> worked fine,
452 but <literal>foo.*</> not so much.
458 Fix misbehavior of <function>PQhost()</> on Windows (Fujii Masao)
462 It should return <literal>localhost</> if no host has been specified.
468 Improve error handling in <application>libpq</> and <application>psql</>
469 for failures during <literal>COPY TO STDOUT/FROM STDIN</> (Tom Lane)
473 In particular this fixes an infinite loop that could occur in 9.2 and
474 up if the server connection was lost during <literal>COPY FROM
475 STDIN</>. Variants of that scenario might be possible in older
476 versions, or with other client applications.
482 Fix incorrect translation handling in
483 some <application>psql</> <literal>\d</> commands
484 (Peter Eisentraut, Tom Lane)
493 Ensure <application>pg_basebackup</>'s background process is killed
494 when exiting its foreground process (Magnus Hagander)
500 Fix possible incorrect printing of filenames
501 in <application>pg_basebackup</>'s verbose mode (Magnus Hagander)
507 Avoid including tablespaces inside PGDATA twice in base backups
508 (Dimitri Fontaine, Magnus Hagander)
514 Fix misaligned descriptors in <application>ecpg</> (MauMau)
520 In <application>ecpg</>, handle lack of a hostname in the connection
521 parameters properly (Michael Meskes)
527 Fix performance regression in <filename>contrib/dblink</> connection
532 Avoid an unnecessary round trip when client and server encodings match.
538 In <filename>contrib/isn</>, fix incorrect calculation of the check
539 digit for ISMN values (Fabien Coelho)
545 Fix <filename>contrib/pg_stat_statement</>'s handling
546 of <literal>CURRENT_DATE</> and related constructs (Kyotaro
553 Ensure client-code-only installation procedure works as documented
560 In Mingw and Cygwin builds, install the <application>libpq</> DLL
561 in the <filename>bin</> directory (Andrew Dunstan)
565 This duplicates what the MSVC build has long done. It should fix
566 problems with programs like <application>psql</> failing to start
567 because they can't find the DLL.
573 Avoid using the deprecated <literal>dllwrap</> tool in Cygwin builds
580 Don't generate plain-text <filename>HISTORY</>
581 and <filename>src/test/regress/README</> files anymore (Tom Lane)
585 These text files duplicated the main HTML and PDF documentation
586 formats. The trouble involved in maintaining them greatly outweighs
587 the likely audience for plain-text format. Distribution tarballs
588 will still contain files by these names, but they'll just be stubs
589 directing the reader to consult the main documentation.
590 The plain-text <filename>INSTALL</> file will still be maintained, as
591 there is arguably a use-case for that.
597 Update time zone data files to <application>tzdata</> release 2013i
598 for DST law changes in Jordan and historical changes in Cuba.
602 In addition, the zones <literal>Asia/Riyadh87</>,
603 <literal>Asia/Riyadh88</>, and <literal>Asia/Riyadh89</> have been
604 removed, as they are no longer maintained by IANA, and never
605 represented actual civil timekeeping practice.
614 <sect1 id="release-9-2-6">
615 <title>Release 9.2.6</title>
618 <title>Release Date</title>
619 <simpara>2013-12-05</simpara>
623 This release contains a variety of fixes from 9.2.5.
624 For information about new features in the 9.2 major release, see
625 <xref linkend="release-9-2">.
629 <title>Migration to Version 9.2.6</title>
632 A dump/restore is not required for those running 9.2.X.
636 However, this release corrects a number of potential data corruption
637 issues. See the first two changelog entries below to find out whether
638 your installation has been affected and what steps you can take if so.
642 Also, if you are upgrading from a version earlier than 9.2.4,
643 see <xref linkend="release-9-2-4">.
649 <title>Changes</title>
655 Fix <command>VACUUM</>'s tests to see whether it can
656 update <structfield>relfrozenxid</> (Andres Freund)
660 In some cases <command>VACUUM</> (either manual or autovacuum) could
661 incorrectly advance a table's <structfield>relfrozenxid</> value,
662 allowing tuples to escape freezing, causing those rows to become
663 invisible once 2^31 transactions have elapsed. The probability of
664 data loss is fairly low since multiple incorrect advancements would
665 need to happen before actual loss occurs, but it's not zero. In 9.2.0
666 and later, the probability of loss is higher, and it's also possible
667 to get <quote>could not access status of transaction</> errors as a
668 consequence of this bug. Users upgrading from releases 9.0.4 or 8.4.8
669 or earlier are not affected, but all later versions contain the bug.
673 The issue can be ameliorated by, after upgrading, vacuuming all tables
674 in all databases while having <link
675 linkend="guc-vacuum-freeze-table-age"><varname>vacuum_freeze_table_age</></link>
676 set to zero. This will fix any latent corruption but will not be able
677 to fix all pre-existing data errors. However, an installation can be
678 presumed safe after performing this vacuuming if it has executed fewer
679 than 2^31 update transactions in its lifetime (check this with
680 <literal>SELECT txid_current() < 2^31</>).
686 Fix initialization of <filename>pg_clog</> and <filename>pg_subtrans</>
687 during hot standby startup (Andres Freund, Heikki Linnakangas)
691 This bug can cause data loss on standby servers at the moment they
692 start to accept hot-standby queries, by marking committed transactions
693 as uncommitted. The likelihood of such corruption is small unless, at
694 the time of standby startup, the primary server has executed many
695 updating transactions since its last checkpoint. Symptoms include
696 missing rows, rows that should have been deleted being still visible,
697 and obsolete versions of updated rows being still visible alongside
698 their newer versions.
702 This bug was introduced in versions 9.3.0, 9.2.5, 9.1.10, and 9.0.14.
703 Standby servers that have only been running earlier releases are not
704 at risk. It's recommended that standby servers that have ever run any
705 of the buggy releases be re-cloned from the primary (e.g., with a new
706 base backup) after upgrading.
712 Fix dangling-pointer problem in fast-path locking (Tom Lane)
716 This could lead to corruption of the lock data structures in shared
717 memory, causing <quote>lock already held</> and other odd errors.
723 Truncate <filename>pg_multixact</> contents during WAL replay
728 This avoids ever-increasing disk space consumption in standby servers.
734 Ensure an anti-wraparound <command>VACUUM</> counts a page as scanned
735 when it's only verified that no tuples need freezing (Sergey
736 Burladyan, Jeff Janes)
740 This bug could result in failing to
741 advance <structfield>relfrozenxid</>, so that the table would still be
742 thought to need another anti-wraparound vacuum. In the worst case the
743 database might even shut down to prevent wraparound.
749 Fix race condition in GIN index posting tree page deletion (Heikki
754 This could lead to transient wrong answers or query failures.
760 Fix <quote>unexpected spgdoinsert() failure</> error during SP-GiST
761 index creation (Teodor Sigaev)
767 Avoid flattening a subquery whose <literal>SELECT</> list contains a
768 volatile function wrapped inside a sub-<literal>SELECT</> (Tom Lane)
772 This avoids unexpected results due to extra evaluations of the
779 Fix planner's processing of non-simple-variable subquery outputs
780 nested within outer joins (Tom Lane)
784 This error could lead to incorrect plans for queries involving
785 multiple levels of subqueries within <literal>JOIN</> syntax.
791 Fix incorrect planning in cases where the same non-strict expression
792 appears in multiple <literal>WHERE</> and outer <literal>JOIN</>
793 equality clauses (Tom Lane)
799 Fix planner crash with whole-row reference to a subquery (Tom Lane)
805 Fix incorrect generation of optimized MIN()/MAX() plans for
806 inheritance trees (Tom Lane)
810 The planner could fail in cases where the MIN()/MAX() argument was an
811 expression rather than a simple variable.
817 Fix premature deletion of temporary files (Andres Freund)
823 Prevent intra-transaction memory leak when printing range values
828 This fix actually cures transient memory leaks in any datatype output
829 function, but range types are the only ones known to have had a
836 Prevent incorrect display of dropped columns in NOT NULL and CHECK
837 constraint violation messages (Michael Paquier and Tom Lane)
843 Allow default arguments and named-argument notation for window
848 Previously, these cases were likely to crash.
854 Fix possible read past end of memory in rule printing (Peter Eisentraut)
860 Fix array slicing of <type>int2vector</> and <type>oidvector</> values
865 Expressions of this kind are now implicitly promoted to
866 regular <type>int2</> or <type>oid</> arrays.
872 Fix incorrect behaviors when using a SQL-standard, simple GMT offset
877 In some cases, the system would use the simple GMT offset value when
878 it should have used the regular timezone setting that had prevailed
879 before the simple offset was selected. This change also causes
880 the <function>timeofday</> function to honor the simple GMT offset
887 Prevent possible misbehavior when logging translations of Windows
888 error codes (Tom Lane)
894 Properly quote generated command lines in <application>pg_ctl</>
895 (Naoya Anzai and Tom Lane)
899 This fix applies only to Windows.
905 Fix <application>pg_dumpall</> to work when a source database
907 linkend="guc-default-transaction-read-only"><varname>default_transaction_read_only</></link>
908 via <command>ALTER DATABASE SET</> (Kevin Grittner)
912 Previously, the generated script would fail during restore.
918 Make <application>ecpg</> search for quoted cursor names
919 case-sensitively (Zoltán Böszörményi)
925 Fix <application>ecpg</>'s processing of lists of variables
926 declared <type>varchar</> (Zoltán Böszörményi)
932 Make <filename>contrib/lo</> defend against incorrect trigger definitions
939 Update time zone data files to <application>tzdata</> release 2013h
940 for DST law changes in Argentina, Brazil, Jordan, Libya,
941 Liechtenstein, Morocco, and Palestine. Also, new timezone
942 abbreviations WIB, WIT, WITA for Indonesia.
951 <sect1 id="release-9-2-5">
952 <title>Release 9.2.5</title>
955 <title>Release Date</title>
956 <simpara>2013-10-10</simpara>
960 This release contains a variety of fixes from 9.2.4.
961 For information about new features in the 9.2 major release, see
962 <xref linkend="release-9-2">.
966 <title>Migration to Version 9.2.5</title>
969 A dump/restore is not required for those running 9.2.X.
973 However, if you are upgrading from a version earlier than 9.2.4,
974 see <xref linkend="release-9-2-4">.
980 <title>Changes</title>
986 Prevent corruption of multi-byte characters when attempting to
987 case-fold identifiers (Andrew Dunstan)
991 <productname>PostgreSQL</> case-folds non-ASCII characters only
992 when using a single-byte server encoding.
998 Fix memory leak when creating B-tree indexes on range columns
1005 Fix checkpoint memory leak in background writer when <literal>wal_level =
1006 hot_standby</> (Naoya Anzai)
1012 Fix memory leak caused by <function>lo_open()</function> failure
1013 (Heikki Linnakangas)
1019 Fix memory overcommit bug when <varname>work_mem</> is using more
1020 than 24GB of memory (Stephen Frost)
1026 Serializable snapshot fixes (Kevin Grittner, Heikki Linnakangas)
1032 Fix deadlock bug in libpq when using SSL (Stephen Frost)
1038 Fix possible SSL state corruption in threaded libpq applications
1039 (Nick Phillips, Stephen Frost)
1045 Improve estimate of planner cost when choosing between generic and
1046 custom plans (Tom Lane)
1050 This change will favor generic plans when planning cost is high.
1056 Properly compute row estimates for boolean columns containing many NULL
1057 values (Andrew Gierth)
1061 Previously tests like <literal>col IS NOT TRUE</> and <literal>col IS
1062 NOT FALSE</> did not properly factor in NULL values when estimating
1069 Fix accounting for qualifier evaluation costs in <literal>UNION ALL</>
1070 and inheritance queries (Tom Lane)
1074 This fixes cases where suboptimal query plans could be chosen if
1075 some <literal>WHERE</> clauses are expensive to calculate.
1081 Prevent pushing down <literal>WHERE</> clauses into unsafe
1082 <literal>UNION/INTERSECT</> subqueries (Tom Lane)
1086 Subqueries of a <literal>UNION</> or <literal>INTERSECT</> that
1087 contain set-returning functions or volatile functions in their
1088 <literal>SELECT</> lists could be improperly optimized, leading to
1089 run-time errors or incorrect query results.
1095 Fix rare case of <quote>failed to locate grouping columns</>
1096 planner failure (Tom Lane)
1102 Fix <application>pg_dump</> of foreign tables with dropped columns (Andrew Dunstan)
1106 Previously such cases could cause a <application>pg_upgrade</> error.
1112 Reorder <application>pg_dump</> processing of extension-related
1113 rules and event triggers (Joe Conway)
1119 Force dumping of extension tables if specified by <command>pg_dump
1120 -t</> or <literal>-n</> (Joe Conway)
1126 Improve view dumping code's handling of dropped columns in referenced
1133 Fix <command>pg_restore -l</> with the directory archive to display
1134 the correct format name (Fujii Masao)
1140 Properly record index comments created using <literal>UNIQUE</>
1141 and <literal>PRIMARY KEY</> syntax (Andres Freund)
1145 This fixes a parallel <application>pg_restore</> failure.
1151 Cause <command>pg_basebackup -x</> with an empty xlog directory
1152 to throw an error rather than crashing (Magnus Hagander, Haruka
1159 Properly guarantee transmission of WAL files before clean switchover
1164 Previously, the streaming replication connection might close before all
1165 WAL files had been replayed on the standby.
1171 Fix WAL segment timeline handling during recovery (Mitsumasa Kondo,
1176 WAL file recycling during standby recovery could lead to premature
1177 recovery completion, resulting in data loss.
1183 Fix <command>REINDEX TABLE</> and <command>REINDEX DATABASE</>
1184 to properly revalidate constraints and mark invalidated indexes as
1189 <command>REINDEX INDEX</> has always worked properly.
1195 Avoid deadlocks during insertion into SP-GiST indexes (Teodor Sigaev)
1201 Fix possible deadlock during concurrent <command>CREATE INDEX
1202 CONCURRENTLY</> operations (Tom Lane)
1208 Fix GiST index lookup crash (Tom Lane)
1214 Fix <function>regexp_matches()</> handling of zero-length matches
1219 Previously, zero-length matches like '^' could return too many matches.
1225 Fix crash for overly-complex regular expressions (Heikki Linnakangas)
1231 Fix regular expression match failures for back references combined with
1232 non-greedy quantifiers (Jeevan Chalke)
1238 Prevent <command>CREATE FUNCTION</> from checking <command>SET</>
1239 variables unless function body checking is enabled (Tom Lane)
1245 Allow <command>ALTER DEFAULT PRIVILEGES</> to operate on schemas
1246 without requiring CREATE permission (Tom Lane)
1252 Loosen restriction on keywords used in queries (Tom Lane)
1256 Specifically, lessen keyword restrictions for role names, language
1257 names, <command>EXPLAIN</> and <command>COPY</> options, and
1258 <command>SET</> values. This allows <literal>COPY ... (FORMAT
1259 BINARY)</> to work as expected; previously <literal>BINARY</> needed
1266 Print proper line number during <command>COPY</> failure (Heikki
1273 Fix <function>pgp_pub_decrypt()</> so it works for secret keys with
1274 passwords (Marko Kreen)
1280 Make <application>pg_upgrade</> use <literal>pg_dump
1281 --quote-all-identifiers</> to avoid problems with keyword changes
1282 between releases (Tom Lane)
1288 Remove rare inaccurate warning during vacuum of index-less tables
1289 (Heikki Linnakangas)
1295 Ensure that <command>VACUUM ANALYZE</> still runs the ANALYZE phase
1296 if its attempt to truncate the file is cancelled due to lock conflicts
1303 Avoid possible failure when performing transaction control commands (e.g
1304 <command>ROLLBACK</>) in prepared queries (Tom Lane)
1310 Ensure that floating-point data input accepts standard spellings
1311 of <quote>infinity</> on all platforms (Tom Lane)
1315 The C99 standard says that allowable spellings are <literal>inf</>,
1316 <literal>+inf</>, <literal>-inf</>, <literal>infinity</>,
1317 <literal>+infinity</>, and <literal>-infinity</>. Make sure we
1318 recognize these even if the platform's <function>strtod</> function
1325 Avoid unnecessary reporting when <varname>track_activities</> is off
1332 Expand ability to compare rows to records and arrays (Rafal Rzepecki,
1339 Prevent crash when <application>psql</>'s <envar>PSQLRC</> variable
1340 contains a tilde (Bruce Momjian)
1346 Add spinlock support for ARM64 (Mark Salter)
1352 Update time zone data files to <application>tzdata</> release 2013d
1353 for DST law changes in Israel, Morocco, Palestine, and Paraguay.
1354 Also, historical zone data corrections for Macquarie Island.
1363 <sect1 id="release-9-2-4">
1364 <title>Release 9.2.4</title>
1367 <title>Release Date</title>
1368 <simpara>2013-04-04</simpara>
1372 This release contains a variety of fixes from 9.2.3.
1373 For information about new features in the 9.2 major release, see
1374 <xref linkend="release-9-2">.
1378 <title>Migration to Version 9.2.4</title>
1381 A dump/restore is not required for those running 9.2.X.
1385 However, this release corrects several errors in management of GiST
1386 indexes. After installing this update, it is advisable to
1387 <command>REINDEX</> any GiST indexes that meet one or more of the
1388 conditions described below.
1392 Also, if you are upgrading from a version earlier than 9.2.2,
1393 see <xref linkend="release-9-2-2">.
1399 <title>Changes</title>
1405 Fix insecure parsing of server command-line switches (Mitsumasa
1406 Kondo, Kyotaro Horiguchi)
1410 A connection request containing a database name that begins with
1411 <quote><literal>-</></quote> could be crafted to damage or destroy
1412 files within the server's data directory, even if the request is
1413 eventually rejected. (CVE-2013-1899)
1419 Reset OpenSSL randomness state in each postmaster child process
1424 This avoids a scenario wherein random numbers generated by
1425 <filename>contrib/pgcrypto</> functions might be relatively easy for
1426 another database user to guess. The risk is only significant when
1427 the postmaster is configured with <varname>ssl</> = <literal>on</>
1428 but most connections don't use SSL encryption. (CVE-2013-1900)
1434 Make REPLICATION privilege checks test current user not authenticated
1439 An unprivileged database user could exploit this mistake to call
1440 <function>pg_start_backup()</> or <function>pg_stop_backup()</>,
1441 thus possibly interfering with creation of routine backups.
1448 Fix GiST indexes to not use <quote>fuzzy</> geometric comparisons when
1449 it's not appropriate to do so (Alexander Korotkov)
1453 The core geometric types perform comparisons using <quote>fuzzy</>
1454 equality, but <function>gist_box_same</> must do exact comparisons,
1455 else GiST indexes using it might become inconsistent. After installing
1456 this update, users should <command>REINDEX</> any GiST indexes on
1457 <type>box</>, <type>polygon</>, <type>circle</>, or <type>point</>
1458 columns, since all of these use <function>gist_box_same</>.
1464 Fix erroneous range-union and penalty logic in GiST indexes that use
1465 <filename>contrib/btree_gist</> for variable-width data types, that is
1466 <type>text</>, <type>bytea</>, <type>bit</>, and <type>numeric</>
1471 These errors could result in inconsistent indexes in which some keys
1472 that are present would not be found by searches, and also in useless
1473 index bloat. Users are advised to <command>REINDEX</> such indexes
1474 after installing this update.
1480 Fix bugs in GiST page splitting code for multi-column indexes
1485 These errors could result in inconsistent indexes in which some keys
1486 that are present would not be found by searches, and also in indexes
1487 that are unnecessarily inefficient to search. Users are advised to
1488 <command>REINDEX</> multi-column GiST indexes after installing this
1495 Fix <function>gist_point_consistent</>
1496 to handle fuzziness consistently (Alexander Korotkov)
1500 Index scans on GiST indexes on <type>point</> columns would sometimes
1501 yield results different from a sequential scan, because
1502 <function>gist_point_consistent</> disagreed with the underlying
1503 operator code about whether to do comparisons exactly or fuzzily.
1509 Fix buffer leak in WAL replay (Heikki Linnakangas)
1513 This bug could result in <quote>incorrect local pin count</> errors
1514 during replay, making recovery impossible.
1520 Ensure we do crash recovery before entering archive recovery, if the
1521 database was not stopped cleanly and a <filename>recovery.conf</> file
1522 is present (Heikki Linnakangas, Kyotaro Horiguchi, Mitsumasa Kondo)
1526 This is needed to ensure that the database is consistent in certain
1527 scenarios, such as initializing a standby server with a filesystem
1528 snapshot from a running server.
1534 Avoid deleting not-yet-archived WAL files during crash recovery
1535 (Heikki Linnakangas, Fujii Masao)
1541 Fix race condition in <command>DELETE RETURNING</> (Tom Lane)
1545 Under the right circumstances, <command>DELETE RETURNING</> could
1546 attempt to fetch data from a shared buffer that the current process
1547 no longer has any pin on. If some other process changed the buffer
1548 meanwhile, this would lead to garbage <literal>RETURNING</> output, or
1555 Fix infinite-loop risk in regular expression compilation (Tom Lane,
1562 Fix potential null-pointer dereference in regular expression compilation
1569 Fix <function>to_char()</> to use ASCII-only case-folding rules where
1570 appropriate (Tom Lane)
1574 This fixes misbehavior of some template patterns that should be
1575 locale-independent, but mishandled <quote><literal>I</></quote> and
1576 <quote><literal>i</></quote> in Turkish locales.
1582 Fix unwanted rejection of timestamp <literal>1999-12-31 24:00:00</>
1589 Fix SQL-language functions to be safely usable as support
1590 functions for range types (Tom Lane)
1596 Fix logic error when a single transaction does <command>UNLISTEN</>
1597 then <command>LISTEN</> (Tom Lane)
1601 The session wound up not listening for notify events at all, though it
1602 surely should listen in this case.
1608 Fix possible planner crash after columns have been added to a view
1609 that's depended on by another view (Tom Lane)
1615 Fix performance issue in <literal>EXPLAIN (ANALYZE, TIMING OFF)</>
1622 Remove useless <quote>picksplit doesn't support secondary split</> log
1623 messages (Josh Hansen, Tom Lane)
1627 This message seems to have been added in expectation of code that was
1628 never written, and probably never will be, since GiST's default
1629 handling of secondary splits is actually pretty good. So stop nagging
1636 Remove vestigial secondary-split support in
1637 <function>gist_box_picksplit()</> (Tom Lane)
1641 Not only was this implementation of secondary-split not better than the
1642 default implementation, it's actually worse. So remove it and let the
1643 default code path handle the case.
1649 Fix possible failure to send a session's last few transaction
1650 commit/abort counts to the statistics collector (Tom Lane)
1656 Eliminate memory leaks in PL/Perl's <function>spi_prepare()</> function
1657 (Alex Hunsaker, Tom Lane)
1663 Fix <application>pg_dumpall</> to handle database names containing
1664 <quote><literal>=</></quote> correctly (Heikki Linnakangas)
1670 Avoid crash in <application>pg_dump</> when an incorrect connection
1671 string is given (Heikki Linnakangas)
1677 Ignore invalid indexes in <application>pg_dump</> and
1678 <application>pg_upgrade</> (Michael Paquier, Bruce Momjian)
1682 Dumping invalid indexes can cause problems at restore time, for example
1683 if the reason the index creation failed was because it tried to enforce
1684 a uniqueness condition not satisfied by the table's data. Also, if the
1685 index creation is in fact still in progress, it seems reasonable to
1686 consider it to be an uncommitted DDL change, which
1687 <application>pg_dump</> wouldn't be expected to dump anyway.
1688 <application>pg_upgrade</> now also skips invalid indexes rather than
1695 In <application>pg_basebackup</>, include only the current server
1696 version's subdirectory when backing up a tablespace (Heikki
1703 Add a server version check in <application>pg_basebackup</> and
1704 <application>pg_receivexlog</>, so they fail cleanly with version
1705 combinations that won't work (Heikki Linnakangas)
1711 Fix <filename>contrib/dblink</> to handle inconsistent settings of
1712 <varname>DateStyle</> or <varname>IntervalStyle</> safely (Daniel
1717 Previously, if the remote server had different settings of these
1718 parameters, ambiguous dates might be read incorrectly. This fix
1719 ensures that datetime and interval columns fetched by a
1720 <filename>dblink</> query will be interpreted correctly. Note however
1721 that inconsistent settings are still risky, since literal values
1722 appearing in SQL commands sent to the remote server might be
1723 interpreted differently than they would be locally.
1729 Fix <filename>contrib/pg_trgm</>'s <function>similarity()</> function
1730 to return zero for trigram-less strings (Tom Lane)
1734 Previously it returned <literal>NaN</> due to internal division by zero.
1740 Enable building <productname>PostgreSQL</> with Microsoft Visual
1741 Studio 2012 (Brar Piening, Noah Misch)
1747 Update time zone data files to <application>tzdata</> release 2013b
1748 for DST law changes in Chile, Haiti, Morocco, Paraguay, and some
1749 Russian areas. Also, historical zone data corrections for numerous
1754 Also, update the time zone abbreviation files for recent changes in
1755 Russia and elsewhere: <literal>CHOT</>, <literal>GET</>,
1756 <literal>IRKT</>, <literal>KGT</>, <literal>KRAT</>, <literal>MAGT</>,
1757 <literal>MAWT</>, <literal>MSK</>, <literal>NOVT</>, <literal>OMST</>,
1758 <literal>TKT</>, <literal>VLAT</>, <literal>WST</>, <literal>YAKT</>,
1759 <literal>YEKT</> now follow their current meanings, and
1760 <literal>VOLT</> (Europe/Volgograd) and <literal>MIST</>
1761 (Antarctica/Macquarie) are added to the default abbreviations list.
1770 <sect1 id="release-9-2-3">
1771 <title>Release 9.2.3</title>
1774 <title>Release Date</title>
1775 <simpara>2013-02-07</simpara>
1779 This release contains a variety of fixes from 9.2.2.
1780 For information about new features in the 9.2 major release, see
1781 <xref linkend="release-9-2">.
1785 <title>Migration to Version 9.2.3</title>
1788 A dump/restore is not required for those running 9.2.X.
1792 However, if you are upgrading from a version earlier than 9.2.2,
1793 see <xref linkend="release-9-2-2">.
1799 <title>Changes</title>
1805 Prevent execution of <function>enum_recv</> from SQL (Tom Lane)
1809 The function was misdeclared, allowing a simple SQL command to crash the
1810 server. In principle an attacker might be able to use it to examine the
1811 contents of server memory. Our thanks to Sumit Soni (via Secunia SVCRP)
1812 for reporting this issue. (CVE-2013-0255)
1818 Fix multiple problems in detection of when a consistent database
1819 state has been reached during WAL replay (Fujii Masao, Heikki
1820 Linnakangas, Simon Riggs, Andres Freund)
1826 Fix detection of end-of-backup point when no actual redo work is
1827 required (Heikki Linnakangas)
1831 This mistake could result in incorrect <quote>WAL ends before end of
1832 online backup</> errors.
1838 Update minimum recovery point when truncating a relation file (Heikki
1843 Once data has been discarded, it's no longer safe to stop recovery at
1844 an earlier point in the timeline.
1850 Fix recycling of WAL segments after changing recovery target timeline
1851 (Heikki Linnakangas)
1857 Properly restore timeline history files from archive on cascading
1858 standby servers (Heikki Linnakangas)
1864 Fix lock conflict detection on hot-standby servers (Andres Freund,
1871 Fix missing cancellations in hot standby mode (Noah Misch, Simon Riggs)
1875 The need to cancel conflicting hot-standby queries would sometimes be
1876 missed, allowing those queries to see inconsistent data.
1882 Prevent recovery pause feature from pausing before users can connect
1889 Fix SQL grammar to allow subscripting or field selection from a
1890 sub-SELECT result (Tom Lane)
1896 Fix performance problems with autovacuum truncation in busy workloads
1901 Truncation of empty pages at the end of a table requires exclusive
1902 lock, but autovacuum was coded to fail (and release the table lock)
1903 when there are conflicting lock requests. Under load, it is easily
1904 possible that truncation would never occur, resulting in table bloat.
1905 Fix by performing a partial truncation, releasing the lock, then
1906 attempting to re-acquire the lock and continue. This fix also greatly
1907 reduces the average time before autovacuum releases the lock after a
1908 conflicting request arrives.
1914 Improve performance of <function>SPI_execute</> and related
1915 functions, thereby improving PL/pgSQL's <literal>EXECUTE</>
1916 (Heikki Linnakangas, Tom Lane)
1920 Remove some data-copying overhead that was added in 9.2 as a
1921 consequence of revisions in the plan caching mechanism. This
1922 eliminates a performance regression compared to 9.1, and also saves
1923 memory, especially when the query string to be executed contains many
1928 A side benefit is that multi-statement query strings are now
1929 processed fully serially, that is we complete execution of earlier
1930 statements before running parse analysis and planning on the
1931 following ones. This eliminates a long-standing issue, in that DDL
1932 that should affect the behavior of a later statement will now behave as
1939 Restore pre-9.2 cost estimates for index usage (Tom Lane)
1943 An ill-considered change of a fudge factor led to undesirably high
1944 cost estimates for use of very large indexes.
1950 Fix intermittent crash in <literal>DROP INDEX CONCURRENTLY</> (Tom Lane)
1956 Fix potential corruption of shared-memory lock table during
1957 <command>CREATE/DROP INDEX CONCURRENTLY</> (Tom Lane)
1963 Fix <command>COPY</>'s multiple-tuple-insertion code for the case of
1964 a tuple larger than page size minus fillfactor (Heikki Linnakangas)
1968 The previous coding could get into an infinite loop.
1974 Protect against race conditions when scanning
1975 <structname>pg_tablespace</> (Stephen Frost, Tom Lane)
1979 <command>CREATE DATABASE</> and <command>DROP DATABASE</> could
1980 misbehave if there were concurrent updates of
1981 <structname>pg_tablespace</> entries.
1987 Prevent <command>DROP OWNED</> from trying to drop whole databases or
1988 tablespaces (Álvaro Herrera)
1992 For safety, ownership of these objects must be reassigned, not dropped.
1999 linkend="guc-vacuum-freeze-table-age"><varname>vacuum_freeze_table_age</></link>
2000 implementation (Andres Freund)
2004 In installations that have existed for more than <link
2005 linkend="guc-vacuum-freeze-min-age"><varname>vacuum_freeze_min_age</></link>
2006 transactions, this mistake prevented autovacuum from using partial-table
2007 scans, so that a full-table scan would always happen instead.
2013 Prevent misbehavior when a <symbol>RowExpr</> or <symbol>XmlExpr</>
2014 is parse-analyzed twice (Andres Freund, Tom Lane)
2018 This mistake could be user-visible in contexts such as
2019 <literal>CREATE TABLE LIKE INCLUDING INDEXES</>.
2025 Improve defenses against integer overflow in hashtable sizing
2026 calculations (Jeff Davis)
2032 Fix some bugs associated with privileges on datatypes (Tom Lane)
2036 There were some issues with default privileges for types, and
2037 <application>pg_dump</> failed to dump such privileges at all.
2043 Fix failure to ignore leftover temporary tables after a server crash
2050 Fix failure to rotate postmaster log files for size reasons on
2051 Windows (Jeff Janes, Heikki Linnakangas)
2057 Reject out-of-range dates in <function>to_date()</> (Hitoshi Harada)
2063 Fix <function>pg_extension_config_dump()</> to handle
2064 extension-update cases properly (Tom Lane)
2068 This function will now replace any existing entry for the target
2069 table, making it usable in extension update scripts.
2075 Fix PL/pgSQL's reporting of plan-time errors in possibly-simple
2076 expressions (Tom Lane)
2080 The previous coding resulted in sometimes omitting the first line in
2081 the <literal>CONTEXT</> traceback for the error.
2087 Fix PL/Python's handling of functions used as triggers on multiple
2088 tables (Andres Freund)
2094 Ensure that non-ASCII prompt strings are translated to the correct
2095 code page on Windows (Alexander Law, Noah Misch)
2099 This bug affected <application>psql</> and some other client programs.
2105 Fix possible crash in <application>psql</>'s <command>\?</> command
2106 when not connected to a database (Meng Qingzhong)
2112 Fix possible error if a relation file is removed while
2113 <application>pg_basebackup</> is running (Heikki Linnakangas)
2119 Tolerate timeline switches while <literal>pg_basebackup -X fetch</>
2120 is backing up a standby server (Heikki Linnakangas)
2126 Make <application>pg_dump</> exclude data of unlogged tables when
2127 running on a hot-standby server (Magnus Hagander)
2131 This would fail anyway because the data is not available on the standby
2132 server, so it seems most convenient to assume
2133 <option>--no-unlogged-table-data</> automatically.
2139 Fix <application>pg_upgrade</> to deal with invalid indexes safely
2146 Fix <application>pg_upgrade</>'s -O/-o options (Marti Raudsepp)
2152 Fix one-byte buffer overrun in <application>libpq</>'s
2153 <function>PQprintTuples</> (Xi Wang)
2157 This ancient function is not used anywhere by
2158 <productname>PostgreSQL</> itself, but it might still be used by some
2165 Make <application>ecpglib</> use translated messages properly
2172 Properly install <application>ecpg_compat</> and
2173 <application>pgtypes</> libraries on MSVC (Jiang Guiqing)
2179 Include our version of <function>isinf()</> in
2180 <application>libecpg</> if it's not provided by the system
2187 Rearrange configure's tests for supplied functions so it is not
2188 fooled by bogus exports from libedit/libreadline (Christoph Berg)
2194 Ensure Windows build number increases over time (Magnus Hagander)
2200 Make <application>pgxs</> build executables with the right
2201 <literal>.exe</> suffix when cross-compiling for Windows
2202 (Zoltan Boszormenyi)
2208 Add new timezone abbreviation <literal>FET</> (Tom Lane)
2212 This is now used in some eastern-European time zones.
2221 <sect1 id="release-9-2-2">
2222 <title>Release 9.2.2</title>
2225 <title>Release Date</title>
2226 <simpara>2012-12-06</simpara>
2230 This release contains a variety of fixes from 9.2.1.
2231 For information about new features in the 9.2 major release, see
2232 <xref linkend="release-9-2">.
2236 <title>Migration to Version 9.2.2</title>
2239 A dump/restore is not required for those running 9.2.X.
2243 However, you may need to perform <command>REINDEX</> operations to
2244 correct problems in concurrently-built indexes, as described in the first
2245 changelog item below.
2249 Also, if you are upgrading from version 9.2.0,
2250 see <xref linkend="release-9-2-1">.
2256 <title>Changes</title>
2262 Fix multiple bugs associated with <command>CREATE/DROP INDEX
2263 CONCURRENTLY</> (Andres Freund, Tom Lane, Simon Riggs, Pavan Deolasee)
2267 An error introduced while adding <command>DROP INDEX CONCURRENTLY</>
2268 allowed incorrect indexing decisions to be made during the initial
2269 phase of <command>CREATE INDEX CONCURRENTLY</>; so that indexes built
2270 by that command could be corrupt. It is recommended that indexes
2271 built in 9.2.X with <command>CREATE INDEX CONCURRENTLY</> be rebuilt
2272 after applying this update.
2276 In addition, fix <command>CREATE/DROP INDEX CONCURRENTLY</> to use
2277 in-place updates when changing the state of an index's
2278 <structname>pg_index</> row. This prevents race conditions that could
2279 cause concurrent sessions to miss updating the target index, thus
2280 again resulting in corrupt concurrently-created indexes.
2284 Also, fix various other operations to ensure that they ignore
2285 invalid indexes resulting from a failed <command>CREATE INDEX
2286 CONCURRENTLY</> command. The most important of these is
2287 <command>VACUUM</>, because an auto-vacuum could easily be launched
2288 on the table before corrective action can be taken to fix or remove
2293 Also fix <command>DROP INDEX CONCURRENTLY</> to not disable
2294 insertions into the target index until all queries using it are done.
2298 Also fix misbehavior if <command>DROP INDEX CONCURRENTLY</> is
2299 canceled: the previous coding could leave an un-droppable index behind.
2305 Correct predicate locking for <command>DROP INDEX CONCURRENTLY</>
2310 Previously, SSI predicate locks were processed at the wrong time,
2311 possibly leading to incorrect behavior of serializable transactions
2312 executing in parallel with the <command>DROP</>.
2318 Fix buffer locking during WAL replay (Tom Lane)
2322 The WAL replay code was insufficiently careful about locking buffers
2323 when replaying WAL records that affect more than one page. This could
2324 result in hot standby queries transiently seeing inconsistent states,
2325 resulting in wrong answers or unexpected failures.
2331 Fix an error in WAL generation logic for GIN indexes (Tom Lane)
2335 This could result in index corruption, if a torn-page failure occurred.
2341 Fix an error in WAL replay logic for SP-GiST indexes (Tom Lane)
2345 This could result in index corruption after a crash, or on a standby
2352 Fix incorrect detection of end-of-base-backup location during WAL
2353 recovery (Heikki Linnakangas)
2357 This mistake allowed hot standby mode to start up before the database
2358 reaches a consistent state.
2364 Properly remove startup process's virtual XID lock when promoting a
2365 hot standby server to normal running (Simon Riggs)
2369 This oversight could prevent subsequent execution of certain
2370 operations such as <command>CREATE INDEX CONCURRENTLY</>.
2376 Avoid bogus <quote>out-of-sequence timeline ID</> errors in standby
2377 mode (Heikki Linnakangas)
2383 Prevent the postmaster from launching new child processes after it's
2384 received a shutdown signal (Tom Lane)
2388 This mistake could result in shutdown taking longer than it should, or
2389 even never completing at all without additional user action.
2395 Fix the syslogger process to not fail when
2396 <varname>log_rotation_age</> exceeds 2^31 milliseconds (about 25 days)
2403 Fix <function>WaitLatch()</> to return promptly when the requested
2404 timeout expires (Jeff Janes, Tom Lane)
2408 With the previous coding, a steady stream of non-wait-terminating
2409 interrupts could delay return from <function>WaitLatch()</>
2410 indefinitely. This has been shown to be a problem for the autovacuum
2411 launcher process, and might cause trouble elsewhere as well.
2417 Avoid corruption of internal hash tables when out of memory
2424 Prevent file descriptors for dropped tables from being held open past
2425 transaction end (Tom Lane)
2429 This should reduce problems with long-since-dropped tables continuing
2430 to occupy disk space.
2436 Prevent database-wide crash and restart when a new child process is
2437 unable to create a pipe for its latch (Tom Lane)
2441 Although the new process must fail, there is no good reason to force a
2442 database-wide restart, so avoid that. This improves robustness when
2443 the kernel is nearly out of file descriptors.
2449 Avoid planner crash with joins to unflattened subqueries (Tom Lane)
2455 Fix planning of non-strict equivalence clauses above outer joins
2460 The planner could derive incorrect constraints from a clause equating
2461 a non-strict construct to something else, for example
2462 <literal>WHERE COALESCE(foo, 0) = 0</>
2463 when <literal>foo</> is coming from the nullable side of an outer join.
2464 9.2 showed this type of error in more cases than previous releases,
2465 but the basic bug has been there for a long time.
2471 Fix <command>SELECT DISTINCT</> with index-optimized
2472 <function>MIN</>/<function>MAX</> on an inheritance tree (Tom Lane)
2476 The planner would fail with <quote>failed to re-find MinMaxAggInfo
2477 record</> given this combination of factors.
2483 Make sure the planner sees implicit and explicit casts as equivalent
2484 for all purposes, except in the minority of cases where there's
2485 actually a semantic difference (Tom Lane)
2491 Include join clauses when considering whether partial indexes can be
2492 used for a query (Tom Lane)
2496 A strict join clause can be sufficient to establish an
2497 <replaceable>x</> <literal>IS NOT NULL</> predicate, for example.
2498 This fixes a planner regression in 9.2, since previous versions could
2499 make comparable deductions.
2505 Limit growth of planning time when there are many indexable join
2506 clauses for the same index (Tom Lane)
2512 Improve planner's ability to prove exclusion constraints from
2513 equivalence classes (Tom Lane)
2519 Fix partial-row matching in hashed subplans to handle cross-type cases
2520 correctly (Tom Lane)
2524 This affects multicolumn <literal>NOT IN</> subplans, such as
2525 <literal>WHERE (a, b) NOT IN (SELECT x, y FROM ...)</>
2526 when for instance <literal>b</> and <literal>y</> are <type>int4</>
2527 and <type>int8</> respectively. This mistake led to wrong answers
2528 or crashes depending on the specific datatypes involved.
2534 Fix btree mark/restore functions to handle array keys (Tom Lane)
2538 This oversight could result in wrong answers from merge joins whose
2539 inner side is an index scan using an
2540 <literal><replaceable>indexed_column</> =
2541 ANY(<replaceable>array</>)</literal> condition.
2547 Revert patch for taking fewer snapshots (Tom Lane)
2551 The 9.2 change to reduce the number of snapshots taken during query
2552 execution led to some anomalous behaviors not seen in previous
2553 releases, because execution would proceed with a snapshot acquired
2554 before locking the tables used by the query. Thus, for example,
2555 a query would not be guaranteed to see updates committed by a
2556 preceding transaction even if that transaction had exclusive lock.
2557 We'll probably revisit this in future releases, but meanwhile put it
2558 back the way it was before 9.2.
2564 Acquire buffer lock when re-fetching the old tuple for an
2565 <literal>AFTER ROW UPDATE/DELETE</> trigger (Andres Freund)
2569 In very unusual circumstances, this oversight could result in passing
2570 incorrect data to a trigger <literal>WHEN</> condition, or to the
2571 precheck logic for a foreign-key enforcement trigger. That could
2572 result in a crash, or in an incorrect decision about whether to
2579 Fix <command>ALTER COLUMN TYPE</> to handle inherited check
2580 constraints properly (Pavan Deolasee)
2584 This worked correctly in pre-8.4 releases, and now works correctly
2591 Fix <command>ALTER EXTENSION SET SCHEMA</>'s failure to move some
2592 subsidiary objects into the new schema (Álvaro Herrera, Dimitri
2599 Handle <command>CREATE TABLE AS EXECUTE</> correctly in extended query
2606 Don't modify the input parse tree in <command>DROP RULE IF NOT
2607 EXISTS</> and <command>DROP TRIGGER IF NOT EXISTS</> (Tom Lane)
2611 This mistake would cause errors if a cached statement of one of these
2612 types was re-executed.
2618 Fix <command>REASSIGN OWNED</> to handle grants on tablespaces
2619 (Álvaro Herrera)
2625 Ignore incorrect <structname>pg_attribute</> entries for system
2626 columns for views (Tom Lane)
2630 Views do not have any system columns. However, we forgot to
2631 remove such entries when converting a table to a view. That's fixed
2632 properly for 9.3 and later, but in previous branches we need to defend
2633 against existing mis-converted views.
2639 Fix rule printing to dump <literal>INSERT INTO <replaceable>table</>
2640 DEFAULT VALUES</literal> correctly (Tom Lane)
2646 Guard against stack overflow when there are too many
2647 <literal>UNION</>/<literal>INTERSECT</>/<literal>EXCEPT</> clauses
2648 in a query (Tom Lane)
2654 Prevent platform-dependent failures when dividing the minimum possible
2655 integer value by -1 (Xi Wang, Tom Lane)
2661 Fix possible access past end of string in date parsing
2668 Fix failure to advance XID epoch if XID wraparound happens during a
2669 checkpoint and <varname>wal_level</> is <literal>hot_standby</>
2670 (Tom Lane, Andres Freund)
2674 While this mistake had no particular impact on
2675 <productname>PostgreSQL</productname> itself, it was bad for
2676 applications that rely on <function>txid_current()</> and related
2677 functions: the TXID value would appear to go backwards.
2683 Fix <function>pg_terminate_backend()</> and
2684 <function>pg_cancel_backend()</> to not throw error for a non-existent
2685 target process (Josh Kupershmidt)
2689 This case already worked as intended when called by a superuser,
2690 but not so much when called by ordinary users.
2697 <structname>pg_stat_replication</>.<structfield>sync_state</> at a
2698 page boundary (Kyotaro Horiguchi)
2704 Produce an understandable error message if the length of the path name
2705 for a Unix-domain socket exceeds the platform-specific limit
2706 (Tom Lane, Andrew Dunstan)
2710 Formerly, this would result in something quite unhelpful, such as
2711 <quote>Non-recoverable failure in name resolution</>.
2717 Fix memory leaks when sending composite column values to the client
2724 Save some cycles by not searching for subtransaction locks at commit
2729 In a transaction holding many exclusive locks, this useless activity
2730 could be quite costly.
2736 Make <application>pg_ctl</> more robust about reading the
2737 <filename>postmaster.pid</> file (Heikki Linnakangas)
2741 This fixes race conditions and possible file descriptor leakage.
2747 Fix possible crash in <application>psql</> if incorrectly-encoded data
2748 is presented and the <varname>client_encoding</> setting is a
2749 client-only encoding, such as SJIS (Jiang Guiqing)
2755 Make <application>pg_dump</> dump <literal>SEQUENCE SET</> items in
2756 the data not pre-data section of the archive (Tom Lane)
2760 This fixes an undesirable inconsistency between the meanings of
2761 <option>--data-only</> and <option>--section=data</>, and also fixes
2762 dumping of sequences that are marked as extension configuration tables.
2768 Fix <application>pg_dump</>'s handling of <command>DROP DATABASE</>
2769 commands in <option>--clean</> mode (Guillaume Lelarge)
2773 Beginning in 9.2.0, <literal>pg_dump --clean</> would issue a
2774 <command>DROP DATABASE</> command, which was either useless or
2775 dangerous depending on the usage scenario. It no longer does that.
2776 This change also fixes the combination of <option>--clean</> and
2777 <option>--create</> to work sensibly, i.e., emit <command>DROP
2778 DATABASE</> then <command>CREATE DATABASE</> before reconnecting to the
2785 Fix <application>pg_dump</> for views with circular dependencies and
2786 no relation options (Tom Lane)
2790 The previous fix to dump relation options when a view is
2791 involved in a circular dependency didn't work right for the case
2792 that the view has no options; it emitted <literal>ALTER VIEW foo
2793 SET ()</> which is invalid syntax.
2799 Fix bugs in the <filename>restore.sql</> script emitted by
2800 <application>pg_dump</> in <literal>tar</> output format (Tom Lane)
2804 The script would fail outright on tables whose names include
2805 upper-case characters. Also, make the script capable of restoring
2806 data in <option>--inserts</> mode as well as the regular COPY mode.
2812 Fix <application>pg_restore</> to accept POSIX-conformant
2813 <literal>tar</> files (Brian Weaver, Tom Lane)
2817 The original coding of <application>pg_dump</>'s <literal>tar</>
2818 output mode produced files that are not fully conformant with the
2819 POSIX standard. This has been corrected for version 9.3. This
2820 patch updates previous branches so that they will accept both the
2821 incorrect and the corrected formats, in hopes of avoiding
2822 compatibility problems when 9.3 comes out.
2828 Fix <literal>tar</> files emitted by <application>pg_basebackup</> to
2829 be POSIX conformant (Brian Weaver, Tom Lane)
2835 Fix <application>pg_resetxlog</> to locate <filename>postmaster.pid</>
2836 correctly when given a relative path to the data directory (Tom Lane)
2840 This mistake could lead to <application>pg_resetxlog</> not noticing
2841 that there is an active postmaster using the data directory.
2847 Fix <application>libpq</>'s <function>lo_import()</> and
2848 <function>lo_export()</> functions to report file I/O errors properly
2855 Fix <application>ecpg</>'s processing of nested structure pointer
2856 variables (Muhammad Usama)
2862 Fix <application>ecpg</>'s <function>ecpg_get_data</> function to
2863 handle arrays properly (Michael Meskes)
2869 Prevent <application>pg_upgrade</> from trying to process TOAST tables
2870 for system catalogs (Bruce Momjian)
2874 This fixes an error seen when the <literal>information_schema</> has
2875 been dropped and recreated. Other failures were also possible.
2881 Improve <application>pg_upgrade</> performance by setting
2882 <varname>synchronous_commit</> to <literal>off</> in the new cluster
2889 Make <filename>contrib/pageinspect</>'s btree page inspection
2890 functions take buffer locks while examining pages (Tom Lane)
2896 Work around unportable behavior of <literal>malloc(0)</> and
2897 <literal>realloc(NULL, 0)</> (Tom Lane)
2901 On platforms where these calls return <literal>NULL</>, some code
2902 mistakenly thought that meant out-of-memory.
2903 This is known to have broken <application>pg_dump</> for databases
2904 containing no user-defined aggregates. There might be other cases
2911 Ensure that <literal>make install</> for an extension creates the
2912 <filename>extension</> installation directory (Cédric Villemain)
2916 Previously, this step was missed if <varname>MODULEDIR</> was set in
2917 the extension's Makefile.
2923 Fix <application>pgxs</> support for building loadable modules on AIX
2928 Building modules outside the original source tree didn't work on AIX.
2934 Update time zone data files to <application>tzdata</> release 2012j
2935 for DST law changes in Cuba, Israel, Jordan, Libya, Palestine, Western
2936 Samoa, and portions of Brazil.
2945 <sect1 id="release-9-2-1">
2946 <title>Release 9.2.1</title>
2949 <title>Release Date</title>
2950 <simpara>2012-09-24</simpara>
2954 This release contains a variety of fixes from 9.2.0.
2955 For information about new features in the 9.2 major release, see
2956 <xref linkend="release-9-2">.
2960 <title>Migration to Version 9.2.1</title>
2963 A dump/restore is not required for those running 9.2.X.
2967 However, you may need to perform <command>REINDEX</> and/or
2968 <command>VACUUM</> operations to recover from the effects of the data
2969 corruption bug described in the first changelog item below.
2975 <title>Changes</title>
2981 Fix persistence marking of shared buffers during WAL replay
2986 This mistake can result in buffers not being written out during
2987 checkpoints, resulting in data corruption if the server later crashes
2988 without ever having written those buffers. Corruption can occur on
2989 any server following crash recovery, but it is significantly more
2990 likely to occur on standby slave servers since those perform much
2991 more WAL replay. There is a low probability of corruption of btree
2992 and GIN indexes. There is a much higher probability of corruption
2993 of table <quote>visibility maps</>, which might lead to wrong answers
2994 from index-only scans. Table data proper cannot be corrupted by this
2999 While no index corruption due to this bug is known to have occurred
3000 in the field, as a precautionary measure it is recommended that
3001 production installations <command>REINDEX</> all btree and GIN
3002 indexes at a convenient time after upgrading to 9.2.1.
3006 Also, it is recommended to perform a <command>VACUUM</> of all tables
3008 linkend="guc-vacuum-freeze-table-age"><varname>vacuum_freeze_table_age</></link>
3009 set to zero. This will fix any incorrect visibility map data. <link
3010 linkend="guc-vacuum-cost-delay"><varname>vacuum_cost_delay</></link>
3011 can be adjusted to reduce the performance impact of vacuuming, while
3012 causing it to take longer to finish.
3018 Fix possible incorrect sorting of output from queries involving
3019 <literal>WHERE <replaceable>indexed_column</> IN
3020 (<replaceable>list_of_values</>)</literal> (Tom Lane)
3026 Fix planner failure for queries involving <literal>GROUP BY</>
3027 expressions along with window functions and aggregates (Tom Lane)
3033 Fix planner's assignment of executor parameters (Tom Lane)
3037 This error could result in wrong answers from queries that scan the
3038 same <literal>WITH</> subquery multiple times.
3044 Improve planner's handling of join conditions in index scans (Tom Lane)
3050 Improve selectivity estimation for text search queries involving
3051 prefixes, i.e. <replaceable>word</><literal>:*</> patterns (Tom Lane)
3060 Fix delayed recognition of permissions changes (Tom Lane)
3064 A command that needed no locks other than ones its transaction already
3065 had might fail to notice a concurrent <command>GRANT</> or
3066 <command>REVOKE</> that committed since the start of its transaction.
3072 Fix <command>ANALYZE</> to not fail when a column is a domain over an
3073 array type (Tom Lane)
3079 Prevent PL/Perl from crashing if a recursive PL/Perl function is
3080 redefined while being executed (Tom Lane)
3086 Work around possible misoptimization in PL/Perl (Tom Lane)
3090 Some Linux distributions contain an incorrect version of
3091 <filename>pthread.h</> that results in incorrect compiled code in
3092 PL/Perl, leading to crashes if a PL/Perl function calls another one
3093 that throws an error.
3099 Remove unnecessary dependency on <application>pg_config</> from
3100 <application>pg_upgrade</> (Peter Eisentraut)
3106 Update time zone data files to <application>tzdata</> release 2012f
3107 for DST law changes in Fiji
3116 <sect1 id="release-9-2">
3117 <title>Release 9.2</title>
3120 <title>Release Date</title>
3121 <simpara>2012-09-10</simpara>
3125 <title>Overview</title>
3128 This release has been largely focused on performance improvements, though
3129 new SQL features are not lacking. Work also continues in the area of
3130 replication support. Major enhancements include:
3135 <!-- This list duplicates items below, but without authors or details-->
3139 Allow queries to retrieve data only from indexes, avoiding heap
3140 access (<firstterm>index-only scans</>)
3146 Allow the planner to generate custom plans for specific parameter
3147 values even when using prepared statements
3153 Improve the planner's ability to use nested loops with inner
3160 Allow streaming replication slaves to forward data to other slaves
3161 (<link linkend="cascading-replication"><firstterm>cascading
3162 replication</></link>)
3169 linkend="app-pgbasebackup"><application>pg_basebackup</></link>
3170 to make base backups from standby servers
3177 linkend="app-pgreceivexlog"><application>pg_receivexlog</></link>
3178 tool to archive WAL file changes as they are written
3184 Add the <link linkend="SPGiST">SP-GiST</link> (Space-Partitioned
3185 GiST) index access method
3191 Add support for <link linkend="rangetypes">range data types</link>
3197 Add a <link linkend="datatype-json"><type>JSON</type></link>
3205 linkend="SQL-CREATEVIEW"><literal>security_barrier</></link>
3212 Allow <application>libpq</> connection strings to have the format of a
3213 <link linkend="libpq-connstring"><acronym>URI</acronym></link>
3219 Add a <link linkend="libpq-single-row-mode">single-row processing
3220 mode</link> to <application>libpq</> for better handling of large
3228 The above items are explained in more detail in the sections below.
3235 <title>Migration to Version 9.2</title>
3238 A dump/restore using <application>pg_dump</application>, or use of
3239 <application>pg_upgrade</application>, is required for those wishing
3240 to migrate data from any previous release.
3244 Version 9.2 contains a number of changes that may affect compatibility
3245 with previous releases. Observe the following incompatibilities:
3249 <title>System Catalogs</title>
3255 Remove the <structfield>spclocation</> field from <link
3256 linkend="catalog-pg-tablespace"><structname>pg_tablespace</></link>
3261 This field was duplicative of the symbolic links that actually define
3262 tablespace locations, and thus risked errors of omission when moving
3263 a tablespace. This change allows tablespace directories to be moved
3264 while the server is down, by manually adjusting the symbolic links.
3265 To replace this field, we have added <link
3266 linkend="functions-info-catalog-table"><function>pg_tablespace_location()</></link>
3267 to allow querying of the symbolic links.
3273 Move <type>tsvector</> most-common-element statistics to new
3274 <link linkend="view-pg-stats"><structname>pg_stats</></link> columns
3275 (Alexander Korotkov)
3279 Consult <structfield>most_common_elems</>
3280 and <structfield>most_common_elem_freqs</> for the data formerly
3281 available in <structfield>most_common_vals</>
3282 and <structfield>most_common_freqs</> for a <type>tsvector</> column.
3291 <title>Functions</title>
3297 Remove <link linkend="hstore">hstore</link>'s <literal>=></>
3298 operator (Robert Haas)
3302 Users should now use <function>hstore(text, text)</>. Since
3303 <productname>PostgreSQL</productname> 9.0, a warning message has been
3304 emitted when an operator named <literal>=></> is created because
3305 the <acronym>SQL</acronym> standard reserves that token for
3313 linkend="functions-xml-processing"><function>xpath()</></link>
3314 escapes special characters in string values (Florian Pflug)
3318 Without this it is possible for the result not to be valid
3319 <acronym>XML</acronym>.
3326 linkend="functions-admin-dbobject"><function>pg_relation_size()</></link>
3327 and friends return NULL if the object does not exist (Phil Sorber)
3331 This prevents queries that call these functions from returning
3332 errors immediately after a concurrent <command>DROP</>.
3339 linkend="functions-datetime-extract"><function>EXTRACT(EPOCH FROM
3340 <replaceable>timestamp without time zone</>)</function></link>
3341 measure the epoch from local midnight, not <acronym>UTC</acronym>
3346 This change reverts an ill-considered change made in release 7.3.
3347 Measuring from <acronym>UTC</acronym> midnight was inconsistent
3348 because it made the result dependent on the <link
3349 linkend="guc-timezone"><varname>timezone</></link> setting, which
3350 computations for <type>timestamp without time zone</> should not be.
3351 The previous behavior remains available by casting the input value
3352 to <type>timestamp with time zone</>.
3358 Properly parse time strings with trailing <literal>yesterday</>,
3359 <literal>today</>, and <literal>tomorrow</> (Dean Rasheed)
3363 Previously, <literal>SELECT '04:00:00 yesterday'::timestamp</literal>
3364 returned yesterday's date at midnight.
3371 linkend="functions-formatting"><function>to_date()</></link> and
3372 <function>to_timestamp()</> to wrap incomplete dates toward 2020
3377 Previously, supplied years and year masks of less than four digits
3378 wrapped inconsistently.
3387 <title>Object Modification</title>
3393 Prevent <link linkend="SQL-ALTERDOMAIN"><command>ALTER
3394 DOMAIN</command></link> from working on non-domain types (Peter
3399 Owner and schema changes were previously possible on non-domain
3406 No longer forcibly lowercase procedural language names in <link
3407 linkend="SQL-CREATEFUNCTION"><command>CREATE FUNCTION</></link>
3412 While unquoted language identifiers are still lowercased, strings
3413 and quoted identifiers are no longer forcibly down-cased.
3414 Thus for example <literal>CREATE FUNCTION ... LANGUAGE 'C'</>
3415 will no longer work; it must be spelled <literal>'c'</>, or better
3422 Change system-generated names of foreign key enforcement triggers
3427 This change ensures that the triggers fire in the correct order in
3428 some corner cases involving self-referential foreign key constraints.
3437 <title>Command-Line Tools</title>
3443 Provide consistent backquote, variable
3444 expansion, and quoted substring behavior in <link
3445 linkend="APP-PSQL"><application>psql</></link> meta-command
3446 arguments (Tom Lane)
3450 Previously, such references were treated oddly when not separated by
3451 whitespace from adjacent text. For example <literal>'FOO'BAR</> was
3452 output as <literal>FOO BAR</> (unexpected insertion of a space) and
3453 <literal>FOO'BAR'BAZ</> was output unchanged (not removing the quotes
3454 as most would expect).
3460 No longer treat <link
3461 linkend="APP-CLUSTERDB"><application>clusterdb</></link>
3462 table names as double-quoted; no longer treat <link
3463 linkend="APP-REINDEXDB"><application>reindexdb</></link> table
3464 and index names as double-quoted (Bruce Momjian)
3468 Users must now include double-quotes in the command arguments if
3475 <link linkend="APP-CREATEUSER"><application>createuser</></link>
3476 no longer prompts for option settings by default (Peter Eisentraut)
3480 Use <option>--interactive</> to obtain the old behavior.
3486 Disable prompting for the user name in <link
3487 linkend="APP-DROPUSER"><application>dropuser</></link> unless
3488 <option>--interactive</> is specified (Peter Eisentraut)
3497 <title>Server Settings</title>
3503 Add server parameters for specifying the <link
3504 linkend="guc-ssl-ca-file">locations of server-side
3505 <acronym>SSL</acronym> files</link> (Peter Eisentraut)
3509 This allows changing the names and locations of the files that were
3510 previously hard-coded as <filename>server.crt</>,
3511 <filename>server.key</>, <filename>root.crt</>, and
3512 <filename>root.crl</> in the data directory.
3513 <emphasis>The server will no longer examine <filename>root.crt</> or
3514 <filename>root.crl</> by default</emphasis>; to load these files, the
3515 associated parameters must be set to non-default values.
3521 Remove the <varname>silent_mode</> parameter (Heikki Linnakangas)
3525 Similar behavior can be obtained with <command>pg_ctl start
3526 -l postmaster.log</>.
3532 Remove the <varname>wal_sender_delay</> parameter,
3533 as it is no longer needed (Tom Lane)
3539 Remove the <varname>custom_variable_classes</> parameter (Tom Lane)
3543 The checking provided by this setting was dubious. Now any
3544 setting can be prefixed by any class name.
3552 <title>Monitoring</title>
3559 linkend="monitoring-stats-views-table"><structname>pg_stat_activity</></link><structfield>.procpid</>
3560 to <structfield>pid</>, to match other system tables (Magnus Hagander)
3566 Create a separate <structfield>pg_stat_activity</> column to
3567 report process state (Scott Mead, Magnus Hagander)
3571 The previous <structfield>query</> and <structfield>query_start</>
3572 values now remain available for an idle session, allowing enhanced
3579 Rename <structname>pg_stat_activity</>.<structfield>current_query</> to
3580 <structfield>query</> because it is not cleared when the query
3581 completes (Magnus Hagander)
3587 Change all <acronym>SQL</acronym>-level statistics timing values
3588 to be <type>float8</> columns measured in milliseconds (Tom Lane)
3592 This change eliminates the designed-in assumption that the values
3593 are accurate to microseconds and no more (since the <type>float8</>
3594 values can be fractional).
3595 The columns affected are
3596 <structname>pg_stat_user_functions</>.<structfield>total_time</>,
3597 <structname>pg_stat_user_functions</>.<structfield>self_time</>,
3598 <structname>pg_stat_xact_user_functions</>.<structfield>total_time</>,
3600 <structname>pg_stat_xact_user_functions</>.<structfield>self_time</>.
3601 The statistics functions underlying these columns now also return
3602 <type>float8</> milliseconds, rather than <type>bigint</>
3604 <filename>contrib/pg_stat_statements</>'
3605 <structfield>total_time</> column is now also measured in
3617 <title>Changes</title>
3620 Below you will find a detailed account of the changes between
3621 <productname>PostgreSQL</productname> 9.2 and the previous major
3626 <title>Server</title>
3629 <title>Performance</title>
3634 Allow queries to retrieve data only from indexes, avoiding heap
3635 access (Robert Haas, Ibrar Ahmed, Heikki Linnakangas, Tom Lane)
3639 This feature is often called <firstterm>index-only scans</>.
3640 Heap access can be skipped for heap pages containing only tuples that
3641 are visible to all sessions, as reported by the visibility map; so
3642 the benefit applies mainly to mostly-static data. The visibility map
3643 was made crash-safe as a necessary part of implementing this feature.
3649 Add the <link linkend="SPGiST">SP-GiST</link> (Space-Partitioned
3650 GiST) index access method (Teodor Sigaev, Oleg Bartunov, Tom
3655 SP-GiST is comparable to GiST in flexibility, but supports
3656 unbalanced partitioned search structures rather than balanced
3657 trees. For suitable problems, SP-GiST can be faster than GiST in both
3658 index build time and search time.
3664 Allow group commit to work effectively under heavy load (Peter
3665 Geoghegan, Simon Riggs, Heikki Linnakangas)
3669 Previously, batching of commits became ineffective as the write
3670 workload increased, because of internal lock contention.
3676 Allow uncontended locks to be managed using a new
3677 fast-path lock mechanism (Robert Haas)
3683 Reduce overhead of creating virtual transaction ID locks (Robert
3690 Reduce the overhead of serializable isolation level locks (Dan
3697 Improve PowerPC and Itanium spinlock performance (Manabu Ori,
3698 Robert Haas, Tom Lane)
3704 Reduce overhead for shared invalidation cache messages (Robert
3711 Move the frequently accessed members of the <structname>PGPROC</>
3712 shared memory array to a separate array (Pavan
3713 Deolasee, Heikki Linnakangas, Robert Haas)
3719 Improve <command>COPY</command> performance by adding tuples to
3720 the heap in batches (Heikki Linnakangas)
3726 Improve GiST index performance for geometric data types by producing
3727 better trees with less memory allocation overhead (Alexander Korotkov)
3733 Improve GiST index build times (Alexander Korotkov, Heikki
3740 Allow hint bits to be set sooner for temporary and unlogged tables
3747 Allow sorting to be performed by inlined,
3748 non-<acronym>SQL</acronym>-callable comparison functions (Peter
3749 Geoghegan, Robert Haas, Tom Lane)
3755 Make the number of CLOG buffers scale based on <link
3756 linkend="guc-shared-buffers"><varname>shared_buffers</></link>
3757 (Robert Haas, Simon Riggs, Tom Lane)
3763 Improve performance of buffer pool scans that occur when tables or
3764 databases are dropped (Jeff Janes, Simon Riggs)
3770 Improve performance of checkpointer's fsync-request queue
3771 when many tables are being dropped or truncated (Tom Lane)
3777 Pass the safe number of file descriptors to child processes on Windows
3778 (Heikki Linnakangas)
3782 This allows Windows sessions to use more open file descriptors than
3792 <title>Process Management</title>
3798 Create a dedicated background process to perform checkpoints (Simon
3803 Formerly the background writer did both dirty-page writing and
3804 checkpointing. Separating this into two processes allows each goal
3805 to be accomplished more predictably.
3811 Improve asynchronous commit behavior by waking the walwriter sooner
3816 Previously, only <link
3817 linkend="guc-wal-writer-delay"><varname>wal_writer_delay</></link>
3818 triggered <acronym>WAL</acronym> flushing to disk; now filling a
3819 <acronym>WAL</acronym> buffer also triggers <acronym>WAL</acronym>
3826 Allow the bgwriter, walwriter, checkpointer, statistics collector,
3827 log collector, and archiver background processes to sleep more
3828 efficiently during periods of inactivity (Peter Geoghegan, Tom Lane)
3832 This series of changes reduces the frequency of process wake-ups when
3833 there is nothing to do, dramatically reducing power consumption on
3843 <title>Optimizer</title>
3849 Allow the planner to generate custom plans for specific parameter
3850 values even when using prepared statements
3855 In the past, a prepared statement always had a single
3856 <quote>generic</> plan that was used for all parameter values, which
3857 was frequently much inferior to the plans used for non-prepared
3858 statements containing explicit constant values. Now, the planner
3859 attempts to generate custom plans for specific parameter values.
3860 A generic plan will only be used after custom plans have repeatedly
3861 proven to provide no benefit. This change should eliminate the
3862 performance penalties formerly seen from use of prepared statements
3863 (including non-dynamic statements in PL/pgSQL).
3869 Improve the planner's ability to use nested loops with inner
3870 index scans (Tom Lane)
3874 The new <quote>parameterized path</> mechanism allows inner
3875 index scans to use values from relations that are more than one join
3876 level up from the scan. This can greatly improve performance in
3877 situations where semantic restrictions (such as outer joins) limit
3878 the allowed join orderings.
3884 Improve the planning <acronym>API</acronym> for foreign data wrappers
3885 (Etsuro Fujita, Shigeru Hanada, Tom Lane)
3889 Wrappers can now provide multiple access <quote>paths</> for their
3890 tables, allowing more flexibility in join planning.
3896 Recognize self-contradictory restriction clauses for non-table
3897 relations (Tom Lane)
3901 This check is only performed when <link
3902 linkend="guc-constraint-exclusion"><varname>constraint_exclusion</></link>
3903 is <literal>on</literal>.
3909 Allow <literal>indexed_col op ANY(ARRAY[...])</> conditions to be
3910 used in plain index scans and index-only scans (Tom Lane)
3914 Formerly such conditions could only be used in bitmap index scans.
3920 Support <function>MIN</>/<function>MAX</> index optimizations on
3921 <type>boolean</type> columns (Marti Raudsepp)
3927 Account for set-returning functions in <command>SELECT</> target
3928 lists when setting row count estimates (Tom Lane)
3934 Fix planner to handle indexes with duplicated columns more reliably
3941 Collect and use element-frequency statistics for arrays (Alexander
3946 This change improves selectivity estimation for the array
3947 <literal><@</literal>, <literal>&&</literal>, and
3948 <literal>@></literal> operators (array containment and overlaps).
3954 Allow statistics to be collected for foreign tables
3961 Improve cost estimates for use of partial indexes (Tom Lane)
3967 Improve the planner's ability to use statistics for columns
3968 referenced in subqueries (Tom Lane)
3974 Improve statistical estimates for subqueries using
3975 <literal>DISTINCT</> (Tom Lane)
3984 <title>Authentication</title>
3990 Do not treat role names and <literal>samerole</> specified in <link
3991 linkend="auth-pg-hba-conf"><filename>pg_hba.conf</filename></link>
3992 as automatically including superusers (Andrew Dunstan)
3996 This makes it easier to use <literal>reject</> lines with group roles.
4002 Adjust <filename>pg_hba.conf</filename> processing to handle token
4003 parsing more consistently (Brendan Jurd, Álvaro Herrera)
4009 Disallow empty <filename>pg_hba.conf</filename> files (Tom Lane)
4013 This was done to more quickly detect misconfiguration.
4019 Make superuser privilege imply replication privilege (Noah Misch)
4023 This avoids the need to explicitly assign such privileges.
4032 <title>Monitoring</title>
4038 Attempt to log the current query string during a backend crash
4045 Make logging of autovacuum I/O activity more verbose (Greg
4050 This logging is triggered by <link
4051 linkend="guc-log-autovacuum-min-duration"><varname>log_autovacuum_min_duration</></link>.
4057 Make <acronym>WAL</acronym> replay report failures sooner
4062 There were some cases where failures were only reported once the
4063 server went into master mode.
4070 linkend="functions-admin-backup"><function>pg_xlog_location_diff()</></link>
4071 to simplify WAL location comparisons (Euler Taveira de Oliveira)
4075 This is useful for computing replication lag.
4081 Support configurable event log application names on Windows
4082 (MauMau, Magnus Hagander)
4086 This allows different instances to use the event log
4087 with different identifiers, by setting the <link
4088 linkend="guc-event-source"><varname>event_source</></link>
4089 server parameter, which is similar to how <link
4090 linkend="guc-syslog-ident"><varname>syslog_ident</></link> works.
4096 Change <quote>unexpected EOF</> messages to <literal>DEBUG1</> level,
4097 except when there is an open transaction (Magnus Hagander)
4101 This change reduces log chatter caused by applications that close
4102 database connections ungracefully.
4111 <title>Statistical Views</title>
4117 Track temporary file sizes and file counts in the <link
4118 linkend="pg-stat-database-view"><structname>pg_stat_database</></link>
4119 system view (Tomas Vondra)
4125 Add a deadlock counter to the <structname>pg_stat_database</>
4126 system view (Magnus Hagander)
4132 Add a server parameter <link
4133 linkend="guc-track-io-timing"><varname>track_io_timing</></link>
4134 to track I/O timings (Ants Aasma, Robert Haas)
4140 Report checkpoint timing information in <link
4141 linkend="pg-stat-bgwriter-view"><structname>pg_stat_bgwriter</></link>
4142 (Greg Smith, Peter Geoghegan)
4151 <title>Server Settings</title>
4157 Silently ignore nonexistent schemas specified in <link
4158 linkend="guc-search-path"><varname>search_path</></link> (Tom Lane)
4162 This makes it more convenient to use generic path settings, which
4163 might include some schemas that don't exist in all databases.
4169 Allow superusers to set <link
4170 linkend="guc-deadlock-timeout"><varname>deadlock_timeout</></link>
4171 per-session, not just per-cluster (Noah Misch)
4175 This allows <varname>deadlock_timeout</> to be reduced for
4176 transactions that are likely to be involved in a deadlock, thus
4177 detecting the failure more quickly. Alternatively, increasing the
4178 value can be used to reduce the chances of a session being chosen for
4179 cancellation due to a deadlock.
4185 Add a server parameter <link
4186 linkend="guc-temp-file-limit"><varname>temp_file_limit</></link>
4187 to constrain temporary file space usage per session (Mark Kirkwood)
4193 Allow a superuser to <command>SET</command> an extension's
4194 superuser-only custom variable before loading the associated
4195 extension (Tom Lane)
4199 The system now remembers whether a <command>SET</command> was
4200 performed by a superuser, so that proper privilege checking can be
4201 done when the extension is loaded.
4207 Add <link linkend="app-postmaster">postmaster</link> <option>-C</>
4208 option to query configuration parameters (Bruce Momjian)
4212 This allows <application>pg_ctl</> to better handle cases where
4213 <envar>PGDATA</> or <option>-D</> points to a configuration-only
4220 Replace an empty locale name with the implied value in
4221 <command>CREATE DATABASE</>
4226 This prevents cases where
4227 <structname>pg_database</>.<structfield>datcollate</> or
4228 <structfield>datctype</> could be interpreted differently after a
4236 <title><filename>postgresql.conf</filename></title>
4242 Allow multiple errors in <filename>postgresql.conf</filename>
4243 to be reported, rather than just the first one (Alexey Klyukin,
4250 Allow a reload of <filename>postgresql.conf</filename> to be
4251 processed by all sessions, even if there are some settings that
4252 are invalid for particular sessions (Alexey Klyukin)
4256 Previously, such not-valid-within-session values would cause all
4257 setting changes to be ignored by that session.
4263 Add an <literal>include_if_exists</> facility for configuration
4268 This works the same as <literal>include</>, except that an error
4269 is not thrown if the file is missing.
4275 Identify the server time zone during <application>initdb</>, and set
4276 <filename>postgresql.conf</filename> entries
4277 <link linkend="guc-timezone"><varname>timezone</></link> and
4278 <link linkend="guc-log-timezone"><varname>log_timezone</></link>
4279 accordingly (Tom Lane)
4283 This avoids expensive time zone probes during server start.
4290 linkend="view-pg-settings"><structname>pg_settings</></link> to
4291 report <filename>postgresql.conf</filename> line numbers on Windows
4305 <title>Replication and Recovery</title>
4311 Allow streaming replication slaves to forward data to other slaves
4312 (<link linkend="cascading-replication"><firstterm>cascading
4313 replication</></link>) (Fujii Masao)
4317 Previously, only the master server could supply streaming
4318 replication log files to standby servers.
4325 linkend="guc-synchronous-commit"><varname>synchronous_commit</></link>
4326 mode <literal>remote_write</> (Fujii Masao, Simon Riggs)
4330 This mode waits for the standby server to write transaction data to
4331 its own operating system, but does not wait for the data to be
4332 flushed to the standby's disk.
4339 linkend="app-pgreceivexlog"><application>pg_receivexlog</></link>
4340 tool to archive WAL file changes as they are written, rather
4341 than waiting for completed WAL files (Magnus Hagander)
4348 linkend="app-pgbasebackup"><application>pg_basebackup</></link>
4349 to make base backups from standby servers (Jun Ishizuka, Fujii Masao)
4353 This feature lets the work of making new base backups be off-loaded
4354 from the primary server.
4360 Allow streaming of WAL files while <application>pg_basebackup</>
4361 is performing a backup (Magnus Hagander)
4365 This allows passing of WAL files to the standby before they are
4366 discarded on the primary.
4375 <title>Queries</title>
4381 Cancel the running query if the client gets disconnected
4386 If the backend detects loss of client connection during a query, it
4387 will now cancel the query rather than attempting to finish it.
4393 Retain column names at run time for row expressions
4394 (Andrew Dunstan, Tom Lane)
4398 This change allows better results when a row value is converted to
4399 <type>hstore</> or <type>json</> type: the fields of the resulting
4400 value will now have the expected names.
4406 Improve column labels used for sub-<command>SELECT</> results
4411 Previously, the generic label <literal>?column?</> was used.
4417 Improve heuristics for determining the types of unknown values
4422 The longstanding rule that an unknown constant might have the
4423 same type as the value on the other side of the operator using it
4424 is now applied when considering polymorphic operators, not only
4425 for simple operator matches.
4431 Warn about creating casts to or from domain types (Robert Haas)
4435 Such casts have no effect.
4441 When a row fails a <literal>CHECK</> or <literal>NOT NULL</>
4442 constraint, show the row's contents as error detail (Jan
4447 This should make it easier to identify which row is problematic
4448 when an insert or update is processing many rows.
4457 <title>Object Manipulation</title>
4463 Provide more reliable operation during concurrent
4464 <acronym>DDL</acronym> (Robert Haas, Noah Misch)
4468 This change adds locking that should eliminate <quote>cache lookup
4469 failed</> errors in many scenarios. Also, it is no longer possible
4470 to add relations to a schema that is being concurrently dropped, a
4471 scenario that formerly led to inconsistent system catalog contents.
4477 Add <literal>CONCURRENTLY</> option to <link
4478 linkend="SQL-DROPINDEX"><command>DROP INDEX</command></link>
4483 This allows index removal without blocking other sessions.
4489 Allow foreign data wrappers to have per-column options (Shigeru Hanada)
4495 Improve pretty-printing of view definitions (Andrew Dunstan)
4502 <title>Constraints</title>
4508 Allow <link linkend="ddl-constraints"><literal>CHECK</></link>
4509 constraints to be declared <literal>NOT VALID</> (Álvaro
4514 Adding a <literal>NOT VALID</> constraint does not cause the table to
4515 be scanned to verify that existing rows meet the constraint.
4516 Subsequently, newly added or updated rows are checked.
4517 Such constraints are ignored by the planner when considering
4518 <varname>constraint_exclusion</>, since it is not certain that all
4519 rows meet the constraint.
4523 The new <command>ALTER TABLE VALIDATE</> command allows <literal>NOT
4524 VALID</> constraints to be checked for existing rows, after which
4525 they are converted into ordinary constraints.
4531 Allow <literal>CHECK</> constraints to be declared <literal>NO
4532 INHERIT</> (Nikhil Sontakke, Alex Hunsaker, Álvaro Herrera)
4536 This makes them enforceable only on the parent table, not on
4543 Add the ability to <link linkend="SQL-ALTERTABLE">rename</link>
4544 constraints (Peter Eisentraut)
4552 <title><command>ALTER</></title>
4558 Reduce need to rebuild tables and indexes for certain <link
4559 linkend="SQL-ALTERTABLE"><command>ALTER TABLE</command></link>
4560 ... <literal>ALTER COLUMN TYPE</> operations (Noah Misch)
4564 Increasing the length limit for a <type>varchar</> or <type>varbit</>
4565 column, or removing the limit altogether, no longer requires a table
4566 rewrite. Similarly, increasing the allowable precision of a
4567 <type>numeric</> column, or changing a column from constrained
4568 <type>numeric</> to unconstrained <type>numeric</>, no longer
4569 requires a table rewrite. Table rewrites are also avoided in similar
4570 cases involving the <type>interval</>, <type>timestamp</>, and
4571 <type>timestamptz</> types.
4577 Avoid having <link linkend="SQL-ALTERTABLE"><command>ALTER
4578 TABLE</command></link> revalidate foreign key constraints in some
4579 cases where it is not necessary (Noah Misch)
4585 Add <literal>IF EXISTS</> options to some <command>ALTER</command>
4586 commands (Pavel Stehule)
4590 For example, <command>ALTER FOREIGN TABLE IF EXISTS foo RENAME
4597 Add <link linkend="SQL-ALTERFOREIGNDATAWRAPPER"><command>ALTER
4598 FOREIGN DATA WRAPPER</command></link> ... <literal>RENAME</>
4599 and <link linkend="SQL-ALTERSERVER"><command>ALTER
4600 SERVER</command></link> ... <literal>RENAME</> (Peter Eisentraut)
4606 Add <link linkend="SQL-ALTERDOMAIN"><command>ALTER
4607 DOMAIN</command></link> ... <literal>RENAME</> (Peter Eisentraut)
4611 You could already rename domains using <command>ALTER
4618 Throw an error for <command>ALTER DOMAIN</command> ... <literal>DROP
4619 CONSTRAINT</> on a nonexistent constraint (Peter Eisentraut)
4623 An <literal>IF EXISTS</> option has been added to provide the
4633 <title><link linkend="SQL-CREATETABLE"><command>CREATE TABLE</></link></title>
4639 Allow <command>CREATE TABLE (LIKE ...)</command> from foreign
4640 tables, views, and composite types (Peter Eisentraut)
4644 For example, this allows a table to be created whose schema matches a
4651 Fix <command>CREATE TABLE (LIKE ...)</command> to avoid index name
4652 conflicts when copying index comments (Tom Lane)
4658 Fix <command>CREATE TABLE</command> ... <literal>AS EXECUTE</>
4659 to handle <literal>WITH NO DATA</> and column name specifications
4669 <title>Object Permissions</title>
4676 linkend="SQL-CREATEVIEW"><literal>security_barrier</></link>
4677 option for views (KaiGai Kohei, Robert Haas)
4681 This option prevents optimizations that might allow view-protected
4682 data to be exposed to users, for example pushing a clause involving
4683 an insecure function into the <literal>WHERE</> clause of the view.
4684 Such views can be expected to perform more poorly than ordinary
4692 linkend="SQL-CREATEFUNCTION"><literal>LEAKPROOF</></link> function
4693 attribute to mark functions that can safely be pushed down
4694 into <literal>security_barrier</> views (KaiGai Kohei)
4700 Add support for privileges on data types (Peter Eisentraut)
4704 This adds support for the <acronym>SQL</>-conforming
4705 <literal>USAGE</> privilege on types and domains. The intent is
4706 to be able to restrict which users can create dependencies on types,
4707 since such dependencies limit the owner's ability to alter the type.
4713 Check for <command>INSERT</command> privileges in <command>SELECT
4714 INTO</command> / <command>CREATE TABLE AS</command> (KaiGai Kohei)
4718 Because the object is being created by <command>SELECT INTO</command>
4719 or <command>CREATE TABLE AS</command>, the creator would ordinarily
4720 have insert permissions; but there are corner cases where this is not
4721 true, such as when <literal>ALTER DEFAULT PRIVILEGES</> has removed
4733 <title>Utility Operations</title>
4739 Allow <link linkend="SQL-VACUUM"><command>VACUUM</></link> to more
4740 easily skip pages that cannot be locked (Simon Riggs, Robert Haas)
4744 This change should greatly reduce the incidence of <command>VACUUM</>
4745 getting <quote>stuck</> waiting for other sessions.
4751 Make <link linkend="SQL-EXPLAIN"><command>EXPLAIN</></link>
4752 <literal>(BUFFERS)</> count blocks dirtied and written (Robert Haas)
4758 Make <command>EXPLAIN ANALYZE</command> report the number of rows
4759 rejected by filter steps (Marko Tiikkaja)
4765 Allow <command>EXPLAIN ANALYZE</command> to avoid timing overhead when
4766 time values are not wanted (Tomas Vondra)
4770 This is accomplished by setting the new <literal>TIMING</> option to
4780 <title>Data Types</title>
4786 Add support for <link linkend="rangetypes">range data types</link>
4787 (Jeff Davis, Tom Lane, Alexander Korotkov)
4791 A range data type stores a lower and upper bound belonging to its
4792 base data type. It supports operations like contains, overlaps, and
4799 Add a <link linkend="datatype-json"><type>JSON</type></link>
4800 data type (Robert Haas)
4804 This type stores <acronym>JSON</acronym> (JavaScript Object Notation)
4805 data with proper validation.
4812 linkend="functions-json"><function>array_to_json()</></link>
4813 and <function>row_to_json()</> (Andrew Dunstan)
4819 Add a <link linkend="datatype-serial"><type>SMALLSERIAL</></link>
4820 data type (Mike Pultz)
4824 This is like <type>SERIAL</>, except it stores the sequence in
4825 a two-byte integer column (<type>int2</>).
4831 Allow <link linkend="SQL-CREATEDOMAIN">domains</link> to be
4832 declared <literal>NOT VALID</> (Álvaro Herrera)
4836 This option can be set at domain creation time, or via <command>ALTER
4837 DOMAIN</command> ... <literal>ADD CONSTRAINT</> ... <literal>NOT
4838 VALID</>. <command>ALTER DOMAIN</command> ... <literal>VALIDATE
4839 CONSTRAINT</> fully validates the constraint.
4845 Support more locale-specific formatting options for the <link
4846 linkend="datatype-money"><type>money</></link> data type (Tom Lane)
4850 Specifically, honor all the POSIX options for ordering of the value,
4851 sign, and currency symbol in monetary output. Also, make sure that
4852 the thousands separator is only inserted to the left of the decimal
4853 point, as required by POSIX.
4859 Add bitwise <quote>and</>, <quote>or</>, and <quote>not</>
4860 operators for the <type>macaddr</> data type (Brendan Jurd)
4867 linkend="functions-xml-processing"><function>xpath()</></link> to
4868 return a single-element <acronym>XML</acronym> array when supplied a
4869 scalar value (Florian Pflug)
4873 Previously, it returned an empty array. This change will also
4874 cause <function>xpath_exists()</> to return true, not false,
4875 for such expressions.
4881 Improve <acronym>XML</acronym> error handling to be more robust
4891 <title>Functions</title>
4897 Allow non-superusers to use <link
4898 linkend="functions-admin-signal"><function>pg_cancel_backend()</></link>
4900 linkend="functions-admin-signal"><function>pg_terminate_backend()</></link>
4901 on other sessions belonging to the same user
4902 (Magnus Hagander, Josh Kupershmidt, Dan Farina)
4906 Previously only superusers were allowed to use these functions.
4912 Allow importing and exporting of transaction snapshots (Joachim
4917 This allows multiple transactions to share identical views of the
4919 Snapshots are exported via <link
4920 linkend="functions-snapshot-synchronization"><function>pg_export_snapshot()</></link>
4921 and imported via <link linkend="SQL-SET-TRANSACTION"><command>SET
4922 TRANSACTION SNAPSHOT</command></link>. Only snapshots from
4923 currently-running transactions can be imported.
4930 linkend="functions-info-catalog-table"><literal>COLLATION
4931 FOR</></link> on expressions (Peter Eisentraut)
4935 This returns a string representing the collation of the expression.
4942 linkend="functions-info-schema-table"><function>pg_opfamily_is_visible()</></link>
4949 Add a <type>numeric</> variant of <link
4950 linkend="functions-admin-dbsize"><function>pg_size_pretty()</></link>
4951 for use with <function>pg_xlog_location_diff()</> (Fujii Masao)
4958 linkend="functions-info-session-table"><function>pg_trigger_depth()</></link>
4959 function (Kevin Grittner)
4963 This reports the current trigger call depth.
4970 linkend="functions-aggregate-table"><function>string_agg()</></link>
4971 to process <type>bytea</> values (Pavel Stehule)
4977 Fix regular expressions in which a back-reference occurs within
4978 a larger quantified subexpression (Tom Lane)
4982 For example, <literal>^(\w+)( \1)+$</>. Previous releases did not
4983 check that the back-reference actually matched the first occurrence.
4992 <title><link linkend="information-schema">Information Schema</link></title>
4998 Add information schema views
4999 <structname>role_udt_grants</>, <structname>udt_privileges</>,
5000 and <structname>user_defined_types</> (Peter Eisentraut)
5006 Add composite-type attributes to the
5007 information schema <structname>element_types</> view
5014 Implement <structfield>interval_type</> columns in the information
5015 schema (Peter Eisentraut)
5019 Formerly these columns read as nulls.
5025 Implement collation-related columns in the information schema
5026 <structname>attributes</>, <structname>columns</>,
5027 <structname>domains</>, and <structname>element_types</>
5028 views (Peter Eisentraut)
5034 Implement the <structfield>with_hierarchy</> column in the
5035 information schema <structname>table_privileges</> view (Peter
5042 Add display of sequence <literal>USAGE</> privileges to information
5043 schema (Peter Eisentraut)
5049 Make the information schema show default privileges (Peter
5054 Previously, non-empty default permissions were not represented in the
5064 <title>Server-Side Languages</title>
5067 <title><link linkend="plpgsql">PL/pgSQL</link> Server-Side Language</title>
5073 Allow the PL/pgSQL <command>OPEN</> cursor command to supply
5074 parameters by name (Yeb Havinga)
5080 Add a <command>GET STACKED DIAGNOSTICS</command> PL/pgSQL command
5081 to retrieve exception info (Pavel Stehule)
5087 Speed up PL/pgSQL array assignment by caching type information
5094 Improve performance and memory consumption for long chains of
5095 <literal>ELSIF</> clauses (Tom Lane)
5101 Output the function signature, not just the name, in PL/pgSQL
5102 error messages (Pavel Stehule)
5111 <title><link linkend="plpython">PL/Python</link> Server-Side Language</title>
5117 Add PL/Python <acronym>SPI</acronym> cursor support (Jan
5122 This allows PL/Python to read partial result sets.
5128 Add result metadata functions to PL/Python (Peter Eisentraut)
5132 Specifically, this adds result object functions
5133 <literal>.colnames</literal>, <literal>.coltypes</literal>, and
5134 <literal>.coltypmods</literal>.
5140 Remove support for Python 2.2 (Peter Eisentraut)
5149 <title><link linkend="xfunc-sql">SQL</link> Server-Side Language</title>
5154 Allow <acronym>SQL</acronym>-language functions to reference
5155 parameters by name (Matthew Draper)
5159 To use this, simply name the function arguments and then reference
5160 the argument names in the <acronym>SQL</acronym> function body.
5170 <title>Client Applications</title>
5176 Add <link linkend="APP-INITDB"><application>initdb</></link>
5177 options <option>--auth-local</> and <option>--auth-host</>
5182 This allows separate control of <literal>local</> and
5183 <literal>host</> <filename>pg_hba.conf</filename> authentication
5184 settings. <option>--auth</> still controls both.
5190 Add <option>--replication</>/<option>--no-replication</> flags to
5191 <link linkend="APP-CREATEUSER"><application>createuser</></link>
5192 to control replication permission (Fujii Masao)
5198 Add the <option>--if-exists</> option to <link
5199 linkend="APP-DROPDB"><application>dropdb</></link> and <link
5200 linkend="APP-DROPUSER"><application>dropuser</></link> (Josh
5207 Give command-line tools the ability to specify the name of the
5208 database to connect to, and fall back to <literal>template1</>
5209 if a <literal>postgres</> database connection fails (Robert Haas)
5216 <title><link linkend="APP-PSQL"><application>psql</></link></title>
5222 Add a display mode to auto-expand output based on the
5223 display width (Peter Eisentraut)
5227 This adds the <literal>auto</> option to the <command>\x</>
5228 command, which switches to the expanded mode when the normal
5229 output would be wider than the screen.
5235 Allow inclusion of a script file that is named relative to the
5236 directory of the file from which it was invoked (Gurjeet Singh)
5240 This is done with a new command <command>\ir</>.
5246 Add support for non-<acronym>ASCII</acronym> characters in
5247 <application>psql</> variable names (Tom Lane)
5253 Add support for major-version-specific <filename>.psqlrc</> files
5258 <application>psql</> already supported minor-version-specific
5259 <filename>.psqlrc</> files.
5265 Provide environment variable overrides for <application>psql</>
5266 history and startup file locations (Andrew Dunstan)
5270 <envar>PSQL_HISTORY</envar> and <envar>PSQLRC</envar> now
5271 determine these file names if set.
5277 Add a <command>\setenv</> command to modify
5278 the environment variables passed to child processes (Andrew Dunstan)
5284 Name <application>psql</>'s temporary editor files with a
5285 <filename>.sql</> extension (Peter Eisentraut)
5289 This allows extension-sensitive editors to select the right mode.
5295 Allow <application>psql</> to use zero-byte field and record
5296 separators (Peter Eisentraut)
5300 Various shell tools use zero-byte (NUL) separators,
5301 e.g. <application>find</>.
5307 Make the <command>\timing</> option report times for
5308 failed queries (Magnus Hagander)
5312 Previously times were reported only for successful queries.
5318 Unify and tighten <application>psql</>'s treatment of <command>\copy</>
5319 and SQL <command>COPY</> (Noah Misch)
5323 This fix makes failure behavior more predictable and honors
5324 <command>\set ON_ERROR_ROLLBACK</>.
5333 <title>Informational Commands</title>
5338 Make <command>\d</> on a sequence show the
5339 table/column name owning it (Magnus Hagander)
5345 Show statistics target for columns in <command>\d+</> (Magnus
5352 Show role password expiration dates in <command>\du</>
5353 (Fabrízio de Royes Mello)
5359 Display comments for casts, conversions, domains, and languages
5364 These are included in the output of <command>\dC+</>,
5365 <command>\dc+</>, <command>\dD+</>, and <command>\dL</> respectively.
5371 Display comments for <acronym>SQL</acronym>/<acronym>MED</acronym>
5372 objects (Josh Kupershmidt)
5376 These are included in the output of <command>\des+</>,
5377 <command>\det+</>, and <command>\dew+</> for foreign servers, foreign
5378 tables, and foreign data wrappers respectively.
5384 Change <command>\dd</> to display comments only for object types
5385 without their own backslash command (Josh Kupershmidt)
5394 <title>Tab Completion</title>
5400 In <application>psql</> tab completion, complete <acronym>SQL</>
5401 keywords in either upper or lower case according to the new <link
5402 linkend="APP-PSQL-variables"><literal>COMP_KEYWORD_CASE</></link>
5403 setting (Peter Eisentraut)
5409 Add tab completion support for
5410 <command>EXECUTE</command> (Andreas Karlsson)
5416 Allow tab completion of role references in
5417 <command>GRANT</command>/<command>REVOKE</command> (Peter
5424 Allow tab completion of file names to supply quotes, when necessary
5431 Change tab completion support for
5432 <command>TABLE</command> to also include views (Magnus Hagander)
5441 <title><link linkend="APP-PGDUMP"><application>pg_dump</></link></title>
5447 Add an <option>--exclude-table-data</> option to
5448 <application>pg_dump</> (Andrew Dunstan)
5452 This allows dumping of a table's definition but not its data,
5453 on a per-table basis.
5459 Add a <option>--section</> option to <application>pg_dump</>
5460 and <application>pg_restore</> (Andrew Dunstan)
5464 Valid values are <literal>pre-data</>, <literal>data</>,
5465 and <literal>post-data</>. The option can be
5466 given more than once to select two or more sections.
5473 linkend="APP-PG-DUMPALL"><application>pg_dumpall</></link> dump all
5474 roles first, then all configuration settings on roles (Phil Sorber)
5478 This allows a role's configuration settings to mention other
5479 roles without generating an error.
5485 Allow <application>pg_dumpall</> to avoid errors if the
5486 <literal>postgres</> database is missing in the new cluster
5493 Dump foreign server user mappings in user name order (Peter
5498 This helps produce deterministic dump files.
5504 Dump operators in a predictable order (Peter Eisentraut)
5510 Tighten rules for when extension configuration tables are dumped
5511 by <application>pg_dump</> (Tom Lane)
5517 Make <application>pg_dump</> emit more useful dependency
5518 information (Tom Lane)
5522 The dependency links included in archive-format dumps were formerly
5523 of very limited use, because they frequently referenced objects that
5524 appeared nowhere in the dump. Now they represent actual dependencies
5525 (possibly indirect) among the dumped objects.
5531 Improve <application>pg_dump</>'s performance when dumping many
5532 database objects (Tom Lane)
5543 <title><link linkend="libpq"><application>libpq</></link></title>
5549 Allow <application>libpq</> connection strings to have the format of a
5550 <link linkend="libpq-connstring"><acronym>URI</acronym></link>
5555 The syntax begins with <literal>postgres://</>. This can allow
5556 applications to avoid implementing their own parser for URIs
5557 representing database connections.
5563 Add a <link linkend="libpq-connect-sslcompression">connection
5564 option</link> to disable <acronym>SSL</acronym> compression
5569 This can be used to remove the overhead of <acronym>SSL</acronym>
5570 compression on fast networks.
5576 Add a <link linkend="libpq-single-row-mode">single-row processing
5577 mode</link> for better handling of large result sets
5578 (Kyotaro Horiguchi, Marko Kreen)
5582 Previously, <application>libpq</> always collected the entire query
5583 result in memory before passing it back to the application.
5589 Add <literal>const</> qualifiers to the declarations of the functions
5590 <function>PQconnectdbParams</>, <function>PQconnectStartParams</>,
5591 and <function>PQpingParams</> (Lionel Elie Mamane)
5597 Allow the <filename>.pgpass</> file to include escaped characters
5598 in the password field (Robert Haas)
5604 Make library functions use <function>abort()</> instead of
5605 <function>exit()</> when it is necessary to terminate the process
5610 This choice does not interfere with the normal exit codes used by the
5611 program, and generates a signal that can be caught by the caller.
5620 <title>Source Code</title>
5626 Remove dead ports (Peter Eisentraut)
5630 The following platforms are no longer supported: dgux,
5631 nextstep, sunos4, svr4, ultrix4, univel, bsdi.
5637 Add support for building with <link linkend="install-windows">MS
5638 Visual Studio 2010</link> (Brar Piening)
5644 Enable compiling with the MinGW-w64 32-bit compiler (Lars Kanis)
5650 Install <filename>plpgsql.h</> into <filename>include/server</> during installation
5651 (Heikki Linnakangas)
5657 Improve the latch facility to include detection of postmaster death
5658 (Peter Geoghegan, Heikki Linnakangas, Tom Lane)
5662 This eliminates one of the main reasons that background processes
5663 formerly had to wake up to poll for events.
5669 Use C flexible array members, where supported (Peter Eisentraut)
5675 Improve the concurrent transaction regression tests
5676 (<application>isolationtester</>) (Noah Misch)
5682 Modify <application>thread_test</> to create its test files in
5683 the current directory, rather than <filename>/tmp</> (Bruce Momjian)
5689 Improve flex and bison warning and error reporting (Tom Lane)
5695 Add memory barrier support (Robert Haas)
5699 This is currently unused.
5705 Modify pgindent to use a typedef file (Bruce Momjian)
5711 Add a hook for processing messages due to be sent to the server
5718 Add object access hooks for <command>DROP</command> commands
5725 Centralize <command>DROP</command> handling for some object types
5732 Add a <application>pg_upgrade</> test suite (Peter Eisentraut)
5738 Sync regular expression code with <acronym>TCL</acronym> 8.5.11
5739 and improve internal processing (Tom Lane)
5745 Move <acronym>CRC</acronym> tables to libpgport, and provide them
5746 in a separate include file (Daniel Farina)
5752 Add options to <application>git_changelog</> for use in major
5753 release note creation (Bruce Momjian)
5759 Support Linux's <filename>/proc/self/oom_score_adj</> API (Tom Lane)
5768 <title>Additional Modules</title>
5774 Improve efficiency of <link linkend="dblink">dblink</link> by using
5775 libpq's new single-row processing mode (Kyotaro Horiguchi, Marko
5780 This improvement does not apply to
5781 <function>dblink_send_query()</>/<function>dblink_get_result()</>.
5787 Support <literal>force_not_null</> option in <link
5788 linkend="file-fdw">file_fdw</link> (Shigeru Hanada)
5794 Implement dry-run mode for <link
5795 linkend="pgarchivecleanup"><application>pg_archivecleanup</></link>
5796 (Gabriele Bartolini)
5800 This only outputs the names of files to be deleted.
5806 Add new <link linkend="pgbench">pgbench</link> switches
5807 <option>--unlogged-tables</>, <option>--tablespace</>, and
5808 <option>--index-tablespace</> (Robert Haas)
5815 linkend="pgtestfsync"><application>pg_test_fsync</></link> to test
5816 for a fixed amount of time, rather than a fixed number of cycles
5821 The <option>-o</>/cycles option was removed, and
5822 <option>-s</>/seconds added.
5829 linkend="pgtesttiming"><application>pg_test_timing</></link>
5830 utility to measure clock monotonicity and timing overhead (Ants
5837 Add a <link linkend="tcn">tcn</link> (triggered change notification)
5838 module to generate <command>NOTIFY</command> events on table changes
5846 <title><link linkend="pgupgrade"><application>pg_upgrade</></link></title>
5852 Adjust <application>pg_upgrade</> environment variables (Bruce
5857 Rename data, bin, and port environment
5858 variables to begin with <literal>PG</>, and support
5859 <envar>PGPORTOLD</envar>/<envar>PGPORTNEW</envar>, to replace
5860 <envar>PGPORT</envar>.
5866 Overhaul <application>pg_upgrade</> logging and failure reporting
5871 Create four append-only log files, and delete them on success.
5872 Add <option>-r</>/<option>--retain</> option to unconditionally
5873 retain these files. Also remove <application>pg_upgrade</> options
5874 <option>-g</>/<option>-G</>/<option>-l</> options as unnecessary,
5875 and tighten log file permissions.
5881 Make <application>pg_upgrade</> create a script to incrementally
5882 generate more accurate optimizer statistics (Bruce Momjian)
5886 This reduces the time needed to generate minimal cluster statistics
5893 Allow <application>pg_upgrade</> to upgrade an old cluster that
5894 does not have a <literal>postgres</> database (Bruce Momjian)
5900 Allow <application>pg_upgrade</> to handle cases where some
5901 old or new databases are missing, as long as they are empty
5908 Allow <application>pg_upgrade</> to handle configuration-only
5909 directory installations (Bruce Momjian)
5915 In <application>pg_upgrade</>, add <option>-o</>/<option>-O</>
5916 options to pass parameters to the servers (Bruce Momjian)
5920 This is useful for configuration-only directory installs.
5926 Change <application>pg_upgrade</> to use port 50432 by default
5931 This helps avoid unintended client connections during the upgrade.
5937 Reduce cluster locking in <application>pg_upgrade</> (Bruce
5942 Specifically, only lock the old cluster if link mode is used,
5943 and do it right after the schema is restored.
5952 <title><link linkend="pgstatstatements"><application>pg_stat_statements</></link></title>
5958 Allow <application>pg_stat_statements</> to aggregate similar
5959 queries via SQL text normalization (Peter Geoghegan, Tom Lane)
5963 Users with applications that use non-parameterized SQL will now
5964 be able to monitor query performance without detailed log analysis.
5970 Add dirtied and written block counts and read/write times to
5971 <application>pg_stat_statements</> (Robert Haas, Ants Aasma)
5977 Prevent <application>pg_stat_statements</> from double-counting
5978 <command>PREPARE</command> and <command>EXECUTE</command> commands
5988 <title><link linkend="sepgsql">sepgsql</link></title>
5993 Support <literal>SECURITY LABEL</> on global objects (KaiGai
5998 Specifically, add security labels to databases,
5999 tablespaces, and roles.
6005 Allow sepgsql to honor database labels (KaiGai Kohei)
6011 Perform sepgsql permission checks during the creation of various
6012 objects (KaiGai Kohei)
6018 Add <function>sepgsql_setcon()</> and related functions to control
6019 the sepgsql security domain (KaiGai Kohei)
6025 Add a user space access cache to sepgsql to improve performance
6036 <title>Documentation</title>
6042 Add a rule to optionally build HTML documentation using the
6043 stylesheet from the website (Magnus Hagander)
6047 Use <command>gmake STYLE=website draft</>.
6053 Improve <command>EXPLAIN</command> documentation (Tom Lane)
6059 Document that user/database names are preserved with double-quoting
6060 by command-line tools like <application>vacuumdb</> (Bruce
6067 Document the actual string returned by the client for MD5
6068 authentication (Cyan Ogilvie)
6074 Deprecate use of <literal>GLOBAL</> and <literal>LOCAL</> in
6075 <command>CREATE TEMP TABLE</> (Noah Misch)
6079 <productname>PostgreSQL</> has long treated these keyword as no-ops,
6080 and continues to do so; but in future they might mean what the SQL
6081 standard says they mean, so applications should avoid using them.