1 <!-- doc/src/sgml/release-9.1.sgml -->
2 <!-- See header comment in release.sgml about typical markup -->
4 <sect1 id="release-9-1-5">
5 <title>Release 9.1.5</title>
8 <title>Release Date</title>
9 <simpara>2012-08-17</simpara>
13 This release contains a variety of fixes from 9.1.4.
14 For information about new features in the 9.1 major release, see
15 <xref linkend="release-9-1">.
19 <title>Migration to Version 9.1.5</title>
22 A dump/restore is not required for those running 9.1.X.
26 However, if you are upgrading from a version earlier than 9.1.4,
27 see the release notes for 9.1.4.
33 <title>Changes</title>
39 Prevent access to external files/URLs via XML entity references
40 (Noah Misch, Tom Lane)
44 <function>xml_parse()</> would attempt to fetch external files or
45 URLs as needed to resolve DTD and entity references in an XML value,
46 thus allowing unprivileged database users to attempt to fetch data
47 with the privileges of the database server. While the external data
48 wouldn't get returned directly to the user, portions of it could be
49 exposed in error messages if the data didn't parse as valid XML; and
50 in any case the mere ability to check existence of a file might be
51 useful to an attacker. (CVE-2012-3489)
57 Prevent access to external files/URLs via <filename>contrib/xml2</>'s
58 <function>xslt_process()</> (Peter Eisentraut)
62 <application>libxslt</> offers the ability to read and write both
63 files and URLs through stylesheet commands, thus allowing
64 unprivileged database users to both read and write data with the
65 privileges of the database server. Disable that through proper use
66 of <application>libxslt</>'s security options. (CVE-2012-3488)
70 Also, remove <function>xslt_process()</>'s ability to fetch documents
71 and stylesheets from external files/URLs. While this was a
72 documented <quote>feature</>, it was long regarded as a bad idea.
73 The fix for CVE-2012-3489 broke that capability, and rather than
74 expend effort on trying to fix it, we're just going to summarily
81 Prevent too-early recycling of btree index pages (Noah Misch)
85 When we allowed read-only transactions to skip assigning XIDs, we
86 introduced the possibility that a deleted btree page could be
87 recycled while a read-only transaction was still in flight to it.
88 This would result in incorrect index search results. The probability
89 of such an error occurring in the field seems very low because of the
90 timing requirements, but nonetheless it should be fixed.
96 Fix crash-safety bug with newly-created-or-reset sequences (Tom Lane)
100 If <command>ALTER SEQUENCE</> was executed on a freshly created or
101 reset sequence, and then precisely one <function>nextval()</> call
102 was made on it, and then the server crashed, WAL replay would restore
103 the sequence to a state in which it appeared that no
104 <function>nextval()</> had been done, thus allowing the first
105 sequence value to be returned again by the next
106 <function>nextval()</> call. In particular this could manifest for
107 <type>serial</> columns, since creation of a serial column's sequence
108 includes an <command>ALTER SEQUENCE OWNED BY</> step.
114 Fix race condition in <literal>enum</>-type value comparisons (Robert
119 Comparisons could fail when encountering an enum value added since
120 the current query started.
126 Fix <function>txid_current()</> to report the correct epoch when not
127 in hot standby (Heikki Linnakangas)
131 This fixes a regression introduced in the previous minor release.
137 Prevent selection of unsuitable replication connections as
138 the synchronous standby (Fujii Masao)
142 The master might improperly choose pseudo-servers such as
143 <application>pg_receivexlog</> or <application>pg_basebackup</>
144 as the synchronous standby, and then wait indefinitely for them.
150 Fix bug in startup of Hot Standby when a master transaction has many
151 subtransactions (Andres Freund)
155 This mistake led to failures reported as <quote>out-of-order XID
156 insertion in KnownAssignedXids</>.
162 Ensure the <filename>backup_label</> file is fsync'd after
163 <function>pg_start_backup()</> (Dave Kerr)
169 Fix timeout handling in walsender processes (Tom Lane)
173 WAL sender background processes neglected to establish a
174 <systemitem>SIGALRM</> handler, meaning they would wait forever in
175 some corner cases where a timeout ought to happen.
181 Wake walsenders after each background flush by walwriter (Andres
186 This greatly reduces replication delay when the workload contains
187 only asynchronously-committed transactions.
193 Fix <literal>LISTEN</>/<literal>NOTIFY</> to cope better with I/O
194 problems, such as out of disk space (Tom Lane)
198 After a write failure, all subsequent attempts to send more
199 <literal>NOTIFY</> messages would fail with messages like
200 <quote>Could not read from file "pg_notify/<replaceable>nnnn</>" at
201 offset <replaceable>nnnnn</>: Success</quote>.
207 Only allow autovacuum to be auto-canceled by a directly blocked
212 The original coding could allow inconsistent behavior in some cases;
213 in particular, an autovacuum could get canceled after less than
214 <literal>deadlock_timeout</> grace period.
220 Improve logging of autovacuum cancels (Robert Haas)
226 Fix log collector so that <literal>log_truncate_on_rotation</> works
227 during the very first log rotation after server start (Tom Lane)
233 Fix <literal>WITH</> attached to a nested set operation
234 (<literal>UNION</>/<literal>INTERSECT</>/<literal>EXCEPT</>)
241 Ensure that a whole-row reference to a subquery doesn't include any
242 extra <literal>GROUP BY</> or <literal>ORDER BY</> columns (Tom Lane)
248 Fix dependencies generated during <literal>ALTER TABLE ... ADD
249 CONSTRAINT USING INDEX</> (Tom Lane)
253 This command left behind a redundant <structname>pg_depend</> entry
254 for the index, which could confuse later operations, notably
255 <literal>ALTER TABLE ... ALTER COLUMN TYPE</> on one of the indexed
262 Fix <command>REASSIGN OWNED</> to work on extensions (Alvaro Herrera)
268 Disallow copying whole-row references in <literal>CHECK</>
269 constraints and index definitions during <command>CREATE TABLE</>
274 This situation can arise in <command>CREATE TABLE</> with
275 <literal>LIKE</> or <literal>INHERITS</>. The copied whole-row
276 variable was incorrectly labeled with the row type of the original
277 table not the new one. Rejecting the case seems reasonable for
278 <literal>LIKE</>, since the row types might well diverge later. For
279 <literal>INHERITS</> we should ideally allow it, with an implicit
280 coercion to the parent table's row type; but that will require more
281 work than seems safe to back-patch.
287 Fix memory leak in <literal>ARRAY(SELECT ...)</> subqueries (Heikki
288 Linnakangas, Tom Lane)
294 Fix planner to pass correct collation to operator selectivity
295 estimators (Tom Lane)
299 This was not previously required by any core selectivity estimation
300 function, but third-party code might need it.
306 Fix extraction of common prefixes from regular expressions (Tom Lane)
310 The code could get confused by quantified parenthesized
311 subexpressions, such as <literal>^(foo)?bar</>. This would lead to
312 incorrect index optimization of searches for such patterns.
318 Fix bugs with parsing signed
319 <replaceable>hh</><literal>:</><replaceable>mm</> and
320 <replaceable>hh</><literal>:</><replaceable>mm</><literal>:</><replaceable>ss</>
321 fields in <type>interval</> constants (Amit Kapila, Tom Lane)
327 Fix <application>pg_dump</> to better handle views containing partial
328 <literal>GROUP BY</> lists (Tom Lane)
332 A view that lists only a primary key column in <literal>GROUP BY</>,
333 but uses other table columns as if they were grouped, gets marked as
334 depending on the primary key. Improper handling of such primary key
335 dependencies in <application>pg_dump</> resulted in poorly-ordered
336 dumps, which at best would be inefficient to restore and at worst
337 could result in outright failure of a parallel
338 <application>pg_restore</> run.
344 In PL/Perl, avoid setting UTF8 flag when in SQL_ASCII encoding
345 (Alex Hunsaker, Kyotaro Horiguchi, Alvaro Herrera)
351 Use Postgres' encoding conversion functions, not Python's, when
352 converting a Python Unicode string to the server encoding in
353 PL/Python (Jan Urbanski)
357 This avoids some corner-case problems, notably that Python doesn't
358 support all the encodings Postgres does. A notable functional change
359 is that if the server encoding is SQL_ASCII, you will get the UTF-8
360 representation of the string; formerly, any non-ASCII characters in
361 the string would result in an error.
367 Fix mapping of PostgreSQL encodings to Python encodings in PL/Python
374 Report errors properly in <filename>contrib/xml2</>'s
375 <function>xslt_process()</> (Tom Lane)
381 Update time zone data files to <application>tzdata</> release 2012e
382 for DST law changes in Morocco and Tokelau
391 <sect1 id="release-9-1-4">
392 <title>Release 9.1.4</title>
395 <title>Release Date</title>
396 <simpara>2012-06-04</simpara>
400 This release contains a variety of fixes from 9.1.3.
401 For information about new features in the 9.1 major release, see
402 <xref linkend="release-9-1">.
406 <title>Migration to Version 9.1.4</title>
409 A dump/restore is not required for those running 9.1.X.
413 However, if you use the <type>citext</> data type, and you upgraded
414 from a previous major release by running <application>pg_upgrade</>,
415 you should run <literal>CREATE EXTENSION citext FROM unpackaged</>
416 to avoid collation-related failures in <type>citext</> operations.
417 The same is necessary if you restore a dump from a pre-9.1 database
418 that contains an instance of the <type>citext</> data type.
419 If you've already run the <command>CREATE EXTENSION</> command before
420 upgrading to 9.1.4, you will instead need to do manual catalog updates
421 as explained in the third changelog item below.
425 Also, if you are upgrading from a version earlier than 9.1.2,
426 see the release notes for 9.1.2.
432 <title>Changes</title>
438 Fix incorrect password transformation in
439 <filename>contrib/pgcrypto</>'s DES <function>crypt()</> function
444 If a password string contained the byte value <literal>0x80</>, the
445 remainder of the password was ignored, causing the password to be much
446 weaker than it appeared. With this fix, the rest of the string is
447 properly included in the DES hash. Any stored password values that are
448 affected by this bug will thus no longer match, so the stored values may
449 need to be updated. (CVE-2012-2143)
455 Ignore <literal>SECURITY DEFINER</> and <literal>SET</> attributes for
456 a procedural language's call handler (Tom Lane)
460 Applying such attributes to a call handler could crash the server.
467 Make <filename>contrib/citext</>'s upgrade script fix collations of
468 <type>citext</> arrays and domains over <type>citext</>
473 Release 9.1.2 provided a fix for collations of <type>citext</> columns
474 and indexes in databases upgraded or reloaded from pre-9.1
475 installations, but that fix was incomplete: it neglected to handle arrays
476 and domains over <type>citext</>. This release extends the module's
477 upgrade script to handle these cases. As before, if you have already
478 run the upgrade script, you'll need to run the collation update
479 commands by hand instead. See the 9.1.2 release notes for more
480 information about doing this.
486 Allow numeric timezone offsets in <type>timestamp</> input to be up to
487 16 hours away from UTC (Tom Lane)
491 Some historical time zones have offsets larger than 15 hours, the
492 previous limit. This could result in dumped data values being rejected
499 Fix timestamp conversion to cope when the given time is exactly the
500 last DST transition time for the current timezone (Tom Lane)
504 This oversight has been there a long time, but was not noticed
505 previously because most DST-using zones are presumed to have an
506 indefinite sequence of future DST transitions.
512 Fix <type>text</> to <type>name</> and <type>char</> to <type>name</>
513 casts to perform string truncation correctly in multibyte encodings
520 Fix memory copying bug in <function>to_tsquery()</> (Heikki Linnakangas)
526 Ensure <function>txid_current()</> reports the correct epoch when
527 executed in hot standby (Simon Riggs)
533 Fix planner's handling of outer PlaceHolderVars within subqueries (Tom
538 This bug concerns sub-SELECTs that reference variables coming from the
539 nullable side of an outer join of the surrounding query.
540 In 9.1, queries affected by this bug would fail with <quote>ERROR:
541 Upper-level PlaceHolderVar found where not expected</>. But in 9.0 and
542 8.4, you'd silently get possibly-wrong answers, since the value
543 transmitted into the subquery wouldn't go to null when it should.
549 Fix planning of <literal>UNION ALL</> subqueries with output columns
550 that are not simple variables (Tom Lane)
554 Planning of such cases got noticeably worse in 9.1 as a result of a
555 misguided fix for <quote>MergeAppend child's targetlist doesn't match
556 MergeAppend</> errors. Revert that fix and do it another way.
562 Fix slow session startup when <structname>pg_attribute</> is very large
567 If <structname>pg_attribute</> exceeds one-fourth of
568 <varname>shared_buffers</>, cache rebuilding code that is sometimes
569 needed during session start would trigger the synchronized-scan logic,
570 causing it to take many times longer than normal. The problem was
571 particularly acute if many new sessions were starting at once.
577 Ensure sequential scans check for query cancel reasonably often (Merlin
582 A scan encountering many consecutive pages that contain no live tuples
583 would not respond to interrupts meanwhile.
589 Ensure the Windows implementation of <function>PGSemaphoreLock()</>
590 clears <varname>ImmediateInterruptOK</> before returning (Tom Lane)
594 This oversight meant that a query-cancel interrupt received later
595 in the same query could be accepted at an unsafe time, with
596 unpredictable but not good consequences.
602 Show whole-row variables safely when printing views or rules
603 (Abbas Butt, Tom Lane)
607 Corner cases involving ambiguous names (that is, the name could be
608 either a table or column name of the query) were printed in an
609 ambiguous way, risking that the view or rule would be interpreted
610 differently after dump and reload. Avoid the ambiguous case by
611 attaching a no-op cast.
617 Fix <command>COPY FROM</> to properly handle null marker strings that
618 correspond to invalid encoding (Tom Lane)
622 A null marker string such as <literal>E'\\0'</> should work, and did
623 work in the past, but the case got broken in 8.4.
629 Fix <command>EXPLAIN VERBOSE</> for writable CTEs containing
630 <literal>RETURNING</> clauses (Tom Lane)
636 Fix <command>PREPARE TRANSACTION</> to work correctly in the presence
637 of advisory locks (Tom Lane)
641 Historically, <command>PREPARE TRANSACTION</> has simply ignored any
642 session-level advisory locks the session holds, but this case was
643 accidentally broken in 9.1.
649 Fix truncation of unlogged tables (Robert Haas)
655 Ignore missing schemas during non-interactive assignments of
656 <varname>search_path</> (Tom Lane)
660 This re-aligns 9.1's behavior with that of older branches. Previously
661 9.1 would throw an error for nonexistent schemas mentioned in
662 <varname>search_path</> settings obtained from places such as
663 <command>ALTER DATABASE SET</>.
669 Fix bugs with temporary or transient tables used in extension scripts
674 This includes cases such as a rewriting <command>ALTER TABLE</> within
675 an extension update script, since that uses a transient table behind
682 Ensure autovacuum worker processes perform stack depth checking
683 properly (Heikki Linnakangas)
687 Previously, infinite recursion in a function invoked by
688 auto-<command>ANALYZE</> could crash worker processes.
694 Fix logging collector to not lose log coherency under high load (Andrew
699 The collector previously could fail to reassemble large messages if it
706 Fix logging collector to ensure it will restart file rotation
707 after receiving <systemitem>SIGHUP</> (Tom Lane)
713 Fix <quote>too many LWLocks taken</> failure in GiST indexes (Heikki
720 Fix WAL replay logic for GIN indexes to not fail if the index was
721 subsequently dropped (Tom Lane)
727 Correctly detect SSI conflicts of prepared transactions after a crash
734 Avoid synchronous replication delay when committing a transaction that
735 only modified temporary tables (Heikki Linnakangas)
739 In such a case the transaction's commit record need not be flushed to
740 standby servers, but some of the code didn't know that and waited for
747 Fix error handling in <application>pg_basebackup</>
748 (Thomas Ogrisegg, Fujii Masao)
754 Fix <application>walsender</> to not go into a busy loop if connection
755 is terminated (Fujii Masao)
761 Fix memory leak in PL/pgSQL's <command>RETURN NEXT</> command (Joe
768 Fix PL/pgSQL's <command>GET DIAGNOSTICS</> command when the target
769 is the function's first variable (Tom Lane)
775 Ensure that PL/Perl package-qualifies the <varname>_TD</> variable
780 This bug caused trigger invocations to fail when they are nested
781 within a function invocation that changes the current package.
787 Fix PL/Python functions returning composite types to accept a string
788 for their result value (Jan Urbanski)
792 This case was accidentally broken by the 9.1 additions to allow a
793 composite result value to be supplied in other formats, such as
800 Fix potential access off the end of memory in <application>psql</>'s
801 expanded display (<command>\x</>) mode (Peter Eisentraut)
807 Fix several performance problems in <application>pg_dump</> when
808 the database contains many objects (Jeff Janes, Tom Lane)
812 <application>pg_dump</> could get very slow if the database contained
813 many schemas, or if many objects are in dependency loops, or if there
814 are many owned sequences.
820 Fix memory and file descriptor leaks in <application>pg_restore</>
821 when reading a directory-format archive (Peter Eisentraut)
827 Fix <application>pg_upgrade</> for the case that a database stored in a
828 non-default tablespace contains a table in the cluster's default
829 tablespace (Bruce Momjian)
835 In <application>ecpg</>, fix rare memory leaks and possible overwrite
836 of one byte after the <structname>sqlca_t</> structure (Peter Eisentraut)
842 Fix <filename>contrib/dblink</>'s <function>dblink_exec()</> to not leak
843 temporary database connections upon error (Tom Lane)
849 Fix <filename>contrib/dblink</> to report the correct connection name in
850 error messages (Kyotaro Horiguchi)
856 Fix <filename>contrib/vacuumlo</> to use multiple transactions when
857 dropping many large objects (Tim Lewis, Robert Haas, Tom Lane)
861 This change avoids exceeding <varname>max_locks_per_transaction</> when
862 many objects need to be dropped. The behavior can be adjusted with the
863 new <literal>-l</> (limit) option.
869 Update time zone data files to <application>tzdata</> release 2012c
870 for DST law changes in Antarctica, Armenia, Chile, Cuba, Falkland
871 Islands, Gaza, Haiti, Hebron, Morocco, Syria, and Tokelau Islands;
872 also historical corrections for Canada.
881 <sect1 id="release-9-1-3">
882 <title>Release 9.1.3</title>
885 <title>Release Date</title>
886 <simpara>2012-02-27</simpara>
890 This release contains a variety of fixes from 9.1.2.
891 For information about new features in the 9.1 major release, see
892 <xref linkend="release-9-1">.
896 <title>Migration to Version 9.1.3</title>
899 A dump/restore is not required for those running 9.1.X.
903 However, if you are upgrading from a version earlier than 9.1.2,
904 see the release notes for 9.1.2.
910 <title>Changes</title>
916 Require execute permission on the trigger function for
917 <command>CREATE TRIGGER</> (Robert Haas)
921 This missing check could allow another user to execute a trigger
922 function with forged input data, by installing it on a table he owns.
923 This is only of significance for trigger functions marked
924 <literal>SECURITY DEFINER</>, since otherwise trigger functions run
925 as the table owner anyway. (CVE-2012-0866)
931 Remove arbitrary limitation on length of common name in SSL
932 certificates (Heikki Linnakangas)
936 Both <application>libpq</> and the server truncated the common name
937 extracted from an SSL certificate at 32 bytes. Normally this would
938 cause nothing worse than an unexpected verification failure, but there
939 are some rather-implausible scenarios in which it might allow one
940 certificate holder to impersonate another. The victim would have to
941 have a common name exactly 32 bytes long, and the attacker would have
942 to persuade a trusted CA to issue a certificate in which the common
943 name has that string as a prefix. Impersonating a server would also
944 require some additional exploit to redirect client connections.
951 Convert newlines to spaces in names written in <application>pg_dump</>
952 comments (Robert Haas)
956 <application>pg_dump</> was incautious about sanitizing object names
957 that are emitted within SQL comments in its output script. A name
958 containing a newline would at least render the script syntactically
959 incorrect. Maliciously crafted object names could present a SQL
960 injection risk when the script is reloaded. (CVE-2012-0868)
966 Fix btree index corruption from insertions concurrent with vacuuming
971 An index page split caused by an insertion could sometimes cause a
972 concurrently-running <command>VACUUM</> to miss removing index entries
973 that it should remove. After the corresponding table rows are removed,
974 the dangling index entries would cause errors (such as <quote>could not
975 read block N in file ...</>) or worse, silently wrong query results
976 after unrelated rows are re-inserted at the now-free table locations.
977 This bug has been present since release 8.2, but occurs so infrequently
978 that it was not diagnosed until now. If you have reason to suspect
979 that it has happened in your database, reindexing the affected index
986 Fix transient zeroing of shared buffers during WAL replay (Tom Lane)
990 The replay logic would sometimes zero and refill a shared buffer, so
991 that the contents were transiently invalid. In hot standby mode this
992 can result in a query that's executing in parallel seeing garbage data.
993 Various symptoms could result from that, but the most common one seems
994 to be <quote>invalid memory alloc request size</>.
1000 Fix handling of data-modifying <literal>WITH</> subplans in
1001 <literal>READ COMMITTED</> rechecking (Tom Lane)
1005 A <literal>WITH</> clause containing
1006 <command>INSERT</>/<command>UPDATE</>/<command>DELETE</> would crash
1007 if the parent <command>UPDATE</> or <command>DELETE</> command needed
1008 to be re-evaluated at one or more rows due to concurrent updates
1009 in <literal>READ COMMITTED</> mode.
1015 Fix corner case in SSI transaction cleanup
1020 When finishing up a read-write serializable transaction,
1021 a crash could occur if all remaining active serializable transactions
1028 Fix postmaster to attempt restart after a hot-standby crash (Tom Lane)
1032 A logic error caused the postmaster to terminate, rather than attempt
1033 to restart the cluster, if any backend process crashed while operating
1034 in hot standby mode.
1040 Fix <command>CLUSTER</>/<command>VACUUM FULL</> handling of toast
1041 values owned by recently-updated rows (Tom Lane)
1045 This oversight could lead to <quote>duplicate key value violates unique
1046 constraint</> errors being reported against the toast table's index
1047 during one of these commands.
1053 Update per-column permissions, not only per-table permissions, when
1054 changing table owner (Tom Lane)
1058 Failure to do this meant that any previously granted column permissions
1059 were still shown as having been granted by the old owner. This meant
1060 that neither the new owner nor a superuser could revoke the
1061 now-untraceable-to-table-owner permissions.
1067 Support foreign data wrappers and foreign servers in
1068 <command>REASSIGN OWNED</> (Alvaro Herrera)
1072 This command failed with <quote>unexpected classid</> errors if
1073 it needed to change the ownership of any such objects.
1079 Allow non-existent values for some settings in <command>ALTER
1080 USER/DATABASE SET</> (Heikki Linnakangas)
1084 Allow <varname>default_text_search_config</>,
1085 <varname>default_tablespace</>, and <varname>temp_tablespaces</> to be
1086 set to names that are not known. This is because they might be known
1087 in another database where the setting is intended to be used, or for the
1088 tablespace cases because the tablespace might not be created yet. The
1089 same issue was previously recognized for <varname>search_path</>, and
1090 these settings now act like that one.
1096 Fix <quote>unsupported node type</> error caused by <literal>COLLATE</>
1097 in an <command>INSERT</> expression (Tom Lane)
1103 Avoid crashing when we have problems deleting table files post-commit
1108 Dropping a table should lead to deleting the underlying disk files only
1109 after the transaction commits. In event of failure then (for instance,
1110 because of wrong file permissions) the code is supposed to just emit a
1111 warning message and go on, since it's too late to abort the
1112 transaction. This logic got broken as of release 8.4, causing such
1113 situations to result in a PANIC and an unrestartable database.
1119 Recover from errors occurring during WAL replay of <command>DROP
1120 TABLESPACE</> (Tom Lane)
1124 Replay will attempt to remove the tablespace's directories, but there
1125 are various reasons why this might fail (for example, incorrect
1126 ownership or permissions on those directories). Formerly the replay
1127 code would panic, rendering the database unrestartable without manual
1128 intervention. It seems better to log the problem and continue, since
1129 the only consequence of failure to remove the directories is some
1136 Fix race condition in logging AccessExclusiveLocks for hot standby
1141 Sometimes a lock would be logged as being held by <quote>transaction
1142 zero</>. This is at least known to produce assertion failures on
1143 slave servers, and might be the cause of more serious problems.
1149 Track the OID counter correctly during WAL replay, even when it wraps
1154 Previously the OID counter would remain stuck at a high value until the
1155 system exited replay mode. The practical consequences of that are
1156 usually nil, but there are scenarios wherein a standby server that's
1157 been promoted to master might take a long time to advance the OID
1158 counter to a reasonable value once values are needed.
1164 Prevent emitting misleading <quote>consistent recovery state reached</>
1165 log message at the beginning of crash recovery (Heikki Linnakangas)
1171 Fix initial value of
1172 <structname>pg_stat_replication</>.<structfield>replay_location</>
1177 Previously, the value shown would be wrong until at least one WAL
1178 record had been replayed.
1184 Fix regular expression back-references with <literal>*</> attached
1189 Rather than enforcing an exact string match, the code would effectively
1190 accept any string that satisfies the pattern sub-expression referenced
1191 by the back-reference symbol.
1195 A similar problem still afflicts back-references that are embedded in a
1196 larger quantified expression, rather than being the immediate subject
1197 of the quantifier. This will be addressed in a future
1198 <productname>PostgreSQL</> release.
1204 Fix recently-introduced memory leak in processing of
1205 <type>inet</>/<type>cidr</> values (Heikki Linnakangas)
1209 A patch in the December 2011 releases of <productname>PostgreSQL</>
1210 caused memory leakage in these operations, which could be significant
1211 in scenarios such as building a btree index on such a column.
1217 Fix planner's ability to push down index-expression restrictions
1218 through <literal>UNION ALL</> (Tom Lane)
1222 This type of optimization was inadvertently disabled by a fix for
1223 another problem in 9.1.2.
1229 Fix planning of <literal>WITH</> clauses referenced in
1230 <command>UPDATE</>/<command>DELETE</> on an inherited table
1235 This bug led to <quote>could not find plan for CTE</> failures.
1241 Fix GIN cost estimation to handle <literal>column IN (...)</>
1242 index conditions (Marti Raudsepp)
1246 This oversight would usually lead to crashes if such a condition could
1247 be used with a GIN index.
1253 Prevent assertion failure when exiting a session with an open, failed
1254 transaction (Tom Lane)
1258 This bug has no impact on normal builds with asserts not enabled.
1264 Fix dangling pointer after <command>CREATE TABLE AS</>/<command>SELECT
1265 INTO</> in a SQL-language function (Tom Lane)
1269 In most cases this only led to an assertion failure in assert-enabled
1270 builds, but worse consequences seem possible.
1276 Avoid double close of file handle in syslogger on Windows (MauMau)
1280 Ordinarily this error was invisible, but it would cause an exception
1281 when running on a debug version of Windows.
1287 Fix I/O-conversion-related memory leaks in plpgsql
1288 (Andres Freund, Jan Urbanski, Tom Lane)
1292 Certain operations would leak memory until the end of the current
1299 Work around bug in perl's SvPVutf8() function (Andrew Dunstan)
1303 This function crashes when handed a typeglob or certain read-only
1304 objects such as <literal>$^V</>. Make plperl avoid passing those to
1311 In <application>pg_dump</>, don't dump contents of an extension's
1312 configuration tables if the extension itself is not being dumped
1319 Improve <application>pg_dump</>'s handling of inherited table columns
1324 <application>pg_dump</> mishandled situations where a child column has
1325 a different default expression than its parent column. If the default
1326 is textually identical to the parent's default, but not actually the
1327 same (for instance, because of schema search path differences) it would
1328 not be recognized as different, so that after dump and restore the
1329 child would be allowed to inherit the parent's default. Child columns
1330 that are <literal>NOT NULL</> where their parent is not could also be
1331 restored subtly incorrectly.
1337 Fix <application>pg_restore</>'s direct-to-database mode for
1338 INSERT-style table data (Tom Lane)
1342 Direct-to-database restores from archive files made with
1343 <option>--inserts</> or <option>--column-inserts</> options fail when
1344 using <application>pg_restore</> from a release dated September or
1345 December 2011, as a result of an oversight in a fix for another
1346 problem. The archive file itself is not at fault, and text-mode
1353 Teach <application>pg_upgrade</> to handle renaming of
1354 <application>plpython</>'s shared library (Bruce Momjian)
1358 Upgrading a pre-9.1 database that included plpython would fail because
1365 Allow <application>pg_upgrade</> to process tables containing
1366 <type>regclass</> columns (Bruce Momjian)
1370 Since <application>pg_upgrade</> now takes care to preserve
1371 <structname>pg_class</> OIDs, there was no longer any reason for this
1378 Make <application>libpq</> ignore <literal>ENOTDIR</> errors
1379 when looking for an SSL client certificate file
1384 This allows SSL connections to be established, though without a
1385 certificate, even when the user's home directory is set to something
1386 like <literal>/dev/null</>.
1392 Fix some more field alignment issues in <application>ecpg</>'s SQLDA area
1393 (Zoltan Boszormenyi)
1399 Allow <literal>AT</> option in <application>ecpg</>
1400 <literal>DEALLOCATE</> statements (Michael Meskes)
1404 The infrastructure to support this has been there for awhile, but
1405 through an oversight there was still an error check rejecting the case.
1411 Do not use the variable name when defining a varchar structure in ecpg
1418 Fix <filename>contrib/auto_explain</>'s JSON output mode to produce
1419 valid JSON (Andrew Dunstan)
1423 The output used brackets at the top level, when it should have used
1430 Fix error in <filename>contrib/intarray</>'s <literal>int[] &
1431 int[]</> operator (Guillaume Lelarge)
1435 If the smallest integer the two input arrays have in common is 1,
1436 and there are smaller values in either array, then 1 would be
1437 incorrectly omitted from the result.
1443 Fix error detection in <filename>contrib/pgcrypto</>'s
1444 <function>encrypt_iv()</> and <function>decrypt_iv()</>
1449 These functions failed to report certain types of invalid-input errors,
1450 and would instead return random garbage values for incorrect input.
1456 Fix one-byte buffer overrun in <filename>contrib/test_parser</>
1461 The code would try to read one more byte than it should, which would
1462 crash in corner cases.
1463 Since <filename>contrib/test_parser</> is only example code, this is
1464 not a security issue in itself, but bad example code is still bad.
1470 Use <function>__sync_lock_test_and_set()</> for spinlocks on ARM, if
1471 available (Martin Pitt)
1475 This function replaces our previous use of the <literal>SWPB</>
1476 instruction, which is deprecated and not available on ARMv6 and later.
1477 Reports suggest that the old code doesn't fail in an obvious way on
1478 recent ARM boards, but simply doesn't interlock concurrent accesses,
1479 leading to bizarre failures in multiprocess operation.
1485 Use <option>-fexcess-precision=standard</> option when building with
1486 gcc versions that accept it (Andrew Dunstan)
1490 This prevents assorted scenarios wherein recent versions of gcc will
1491 produce creative results.
1497 Allow use of threaded Python on FreeBSD (Chris Rees)
1501 Our configure script previously believed that this combination wouldn't
1502 work; but FreeBSD fixed the problem, so remove that error check.
1508 Allow MinGW builds to use standardly-named OpenSSL libraries
1518 <sect1 id="release-9-1-2">
1519 <title>Release 9.1.2</title>
1522 <title>Release Date</title>
1523 <simpara>2011-12-05</simpara>
1527 This release contains a variety of fixes from 9.1.1.
1528 For information about new features in the 9.1 major release, see
1529 <xref linkend="release-9-1">.
1533 <title>Migration to Version 9.1.2</title>
1536 A dump/restore is not required for those running 9.1.X.
1540 However, a longstanding error was discovered in the definition of the
1541 <literal>information_schema.referential_constraints</> view. If you
1542 rely on correct results from that view, you should replace its
1543 definition as explained in the first changelog item below.
1547 Also, if you use the <type>citext</> data type, and you upgraded
1548 from a previous major release by running <application>pg_upgrade</>,
1549 you should run <literal>CREATE EXTENSION citext FROM unpackaged</>
1550 to avoid collation-related failures in <type>citext</> operations.
1551 The same is necessary if you restore a dump from a pre-9.1 database
1552 that contains an instance of the <type>citext</> data type.
1553 If you've already run the <command>CREATE EXTENSION</> command before
1554 upgrading to 9.1.2, you will instead need to do manual catalog updates
1555 as explained in the second changelog item.
1561 <title>Changes</title>
1567 Fix bugs in <literal>information_schema.referential_constraints</> view
1572 This view was being insufficiently careful about matching the
1573 foreign-key constraint to the depended-on primary or unique key
1574 constraint. That could result in failure to show a foreign key
1575 constraint at all, or showing it multiple times, or claiming that it
1576 depends on a different constraint than the one it really does.
1580 Since the view definition is installed by <application>initdb</>,
1581 merely upgrading will not fix the problem. If you need to fix this
1582 in an existing installation, you can (as a superuser) drop the
1583 <literal>information_schema</> schema then re-create it by sourcing
1584 <filename><replaceable>SHAREDIR</>/information_schema.sql</filename>.
1585 (Run <literal>pg_config --sharedir</> if you're uncertain where
1586 <replaceable>SHAREDIR</> is.) This must be repeated in each database
1593 Make <filename>contrib/citext</>'s upgrade script fix collations of
1594 <type>citext</> columns and indexes (Tom Lane)
1598 Existing <type>citext</> columns and indexes aren't correctly marked as
1599 being of a collatable data type during <application>pg_upgrade</> from
1600 a pre-9.1 server, or when a pre-9.1 dump containing the <type>citext</>
1601 type is loaded into a 9.1 server.
1602 That leads to operations on these columns failing with errors
1603 such as <quote>could not determine which collation to use for string
1604 comparison</>. This change allows them to be fixed by the same
1605 script that upgrades the <type>citext</> module into a proper 9.1
1606 extension during <literal>CREATE EXTENSION citext FROM unpackaged</>.
1610 If you have a previously-upgraded database that is suffering from this
1611 problem, and you already ran the <command>CREATE EXTENSION</> command,
1612 you can manually run (as superuser) the <command>UPDATE</> commands
1614 <filename><replaceable>SHAREDIR</>/extension/citext--unpackaged--1.0.sql</filename>.
1615 (Run <literal>pg_config --sharedir</> if you're uncertain where
1616 <replaceable>SHAREDIR</> is.)
1617 There is no harm in doing this again if unsure.
1623 Fix possible crash during <command>UPDATE</> or <command>DELETE</> that
1624 joins to the output of a scalar-returning function (Tom Lane)
1628 A crash could only occur if the target row had been concurrently
1629 updated, so this problem surfaced only intermittently.
1635 Fix incorrect replay of WAL records for GIN index updates
1640 This could result in transiently failing to find index entries after
1641 a crash, or on a hot-standby server. The problem would be repaired
1642 by the next <command>VACUUM</> of the index, however.
1648 Fix TOAST-related data corruption during <literal>CREATE TABLE dest AS
1649 SELECT * FROM src</> or <literal>INSERT INTO dest SELECT * FROM src</>
1654 If a table has been modified by <command>ALTER TABLE ADD COLUMN</>,
1655 attempts to copy its data verbatim to another table could produce
1656 corrupt results in certain corner cases.
1657 The problem can only manifest in this precise form in 8.4 and later,
1658 but we patched earlier versions as well in case there are other code
1659 paths that could trigger the same bug.
1665 Fix possible failures during hot standby startup (Simon Riggs)
1671 Start hot standby faster when initial snapshot is incomplete
1678 Fix race condition during toast table access from stale syscache entries
1683 The typical symptom was transient errors like <quote>missing chunk
1684 number 0 for toast value NNNNN in pg_toast_2619</>, where the cited
1685 toast table would always belong to a system catalog.
1691 Track dependencies of functions on items used in parameter default
1692 expressions (Tom Lane)
1696 Previously, a referenced object could be dropped without having dropped
1697 or modified the function, leading to misbehavior when the function was
1698 used. Note that merely installing this update will not fix the missing
1699 dependency entries; to do that, you'd need to <command>CREATE OR
1700 REPLACE</> each such function afterwards. If you have functions whose
1701 defaults depend on non-built-in objects, doing so is recommended.
1707 Fix incorrect management of placeholder variables in nestloop joins
1712 This bug is known to lead to <quote>variable not found in subplan target
1713 list</> planner errors, and could possibly result in wrong query output
1714 when outer joins are involved.
1720 Fix window functions that sort by expressions involving aggregates
1725 Previously these could fail with <quote>could not find pathkey item to
1726 sort</> planner errors.
1732 Fix <quote>MergeAppend child's targetlist doesn't match MergeAppend</>
1733 planner errors (Tom Lane)
1739 Fix index matching for operators with both collatable and noncollatable
1744 In 9.1.0, an indexable operator that has a non-collatable left-hand
1745 input type and a collatable right-hand input type would not be
1746 recognized as matching the left-hand column's index. An example is
1747 the <type>hstore</> <literal>?</> <type>text</> operator.
1753 Allow inlining of set-returning SQL functions with multiple OUT
1754 parameters (Tom Lane)
1760 Don't trust deferred-unique indexes for join removal (Tom Lane and Marti
1765 A deferred uniqueness constraint might not hold intra-transaction,
1766 so assuming that it does could give incorrect query results.
1772 Make <function>DatumGetInetP()</> unpack inet datums that have a 1-byte
1773 header, and add a new macro, <function>DatumGetInetPP()</>, that does
1774 not (Heikki Linnakangas)
1778 This change affects no core code, but might prevent crashes in add-on
1779 code that expects <function>DatumGetInetP()</> to produce an unpacked
1780 datum as per usual convention.
1786 Improve locale support in <type>money</> type's input and output
1791 Aside from not supporting all standard
1792 <link linkend="guc-lc-monetary"><varname>lc_monetary</></link>
1793 formatting options, the input and output functions were inconsistent,
1794 meaning there were locales in which dumped <type>money</> values could
1802 linkend="guc-transform-null-equals"><varname>transform_null_equals</></link>
1803 affect <literal>CASE foo WHEN NULL ...</> constructs
1804 (Heikki Linnakangas)
1808 <varname>transform_null_equals</> is only supposed to affect
1809 <literal>foo = NULL</> expressions written directly by the user, not
1810 equality checks generated internally by this form of <literal>CASE</>.
1816 Change foreign-key trigger creation order to better support
1817 self-referential foreign keys (Tom Lane)
1821 For a cascading foreign key that references its own table, a row update
1822 will fire both the <literal>ON UPDATE</> trigger and the
1823 <literal>CHECK</> trigger as one event. The <literal>ON UPDATE</>
1824 trigger must execute first, else the <literal>CHECK</> will check a
1825 non-final state of the row and possibly throw an inappropriate error.
1826 However, the firing order of these triggers is determined by their
1827 names, which generally sort in creation order since the triggers have
1828 auto-generated names following the convention
1829 <quote>RI_ConstraintTrigger_NNNN</>. A proper fix would require
1830 modifying that convention, which we will do in 9.2, but it seems risky
1831 to change it in existing releases. So this patch just changes the
1832 creation order of the triggers. Users encountering this type of error
1833 should drop and re-create the foreign key constraint to get its
1834 triggers into the right order.
1840 Fix <literal>IF EXISTS</> to work correctly in <command>DROP OPERATOR
1841 FAMILY</> (Robert Haas)
1847 Disallow dropping of an extension from within its own script
1852 This prevents odd behavior in case of incorrect management of extension
1859 Don't mark auto-generated types as extension members (Robert Haas)
1863 Relation rowtypes and automatically-generated array types do not need to
1864 have their own extension membership entries in <structname>pg_depend</>,
1865 and creating such entries complicates matters for extension upgrades.
1871 Cope with invalid pre-existing <varname>search_path</> settings during
1872 <command>CREATE EXTENSION</> (Tom Lane)
1878 Avoid floating-point underflow while tracking buffer allocation rate
1883 While harmless in itself, on certain platforms this would result in
1884 annoying kernel log messages.
1890 Prevent autovacuum transactions from running in serializable mode
1895 Autovacuum formerly used the cluster-wide default transaction isolation
1896 level, but there is no need for it to use anything higher than READ
1897 COMMITTED, and using SERIALIZABLE could result in unnecessary delays
1898 for other processes.
1904 Ensure walsender processes respond promptly to <systemitem>SIGTERM</>
1911 Exclude <filename>postmaster.opts</> from base backups
1918 Preserve configuration file name and line number values when starting
1919 child processes under Windows (Tom Lane)
1923 Formerly, these would not be displayed correctly in the
1924 <structname>pg_settings</> view.
1930 Fix incorrect field alignment in <application>ecpg</>'s SQLDA area
1931 (Zoltan Boszormenyi)
1937 Preserve blank lines within commands in <application>psql</>'s command
1938 history (Robert Haas)
1942 The former behavior could cause problems if an empty line was removed
1943 from within a string literal, for example.
1949 Avoid platform-specific infinite loop in <application>pg_dump</>
1956 Fix compression of plain-text output format in <application>pg_dump</>
1957 (Adrian Klaver and Tom Lane)
1961 <application>pg_dump</> has historically understood <literal>-Z</> with
1962 no <literal>-F</> switch to mean that it should emit a gzip-compressed
1963 version of its plain text output. Restore that behavior.
1969 Fix <application>pg_dump</> to dump user-defined casts between
1970 auto-generated types, such as table rowtypes (Tom Lane)
1976 Fix missed quoting of foreign server names in <application>pg_dump</>
1983 Assorted fixes for <application>pg_upgrade</> (Bruce Momjian)
1987 Handle exclusion constraints correctly, avoid failures on Windows,
1988 don't complain about mismatched toast table names in 8.4 databases.
1994 In PL/pgSQL, allow foreign tables to define row types
1995 (Alexander Soudakov)
2001 Fix up conversions of PL/Perl functions' results
2002 (Alex Hunsaker and Tom Lane)
2006 Restore the pre-9.1 behavior that PL/Perl functions returning
2007 <type>void</> ignore the result value of their last Perl statement;
2008 9.1.0 would throw an error if that statement returned a reference.
2009 Also, make sure it works to return a string value for a composite type,
2010 so long as the string meets the type's input format.
2011 In addition, throw errors for attempts to return Perl arrays or hashes
2012 when the function's declared result type is not an array or composite
2013 type, respectively. (Pre-9.1 versions rather uselessly returned
2014 strings like <literal>ARRAY(0x221a9a0)</> or
2015 <literal>HASH(0x221aa90)</> in such cases.)
2021 Ensure PL/Perl strings are always correctly UTF8-encoded
2022 (Amit Khandekar and Alex Hunsaker)
2028 Use the preferred version of <application>xsubpp</> to build PL/Perl,
2029 not necessarily the operating system's main copy
2030 (David Wheeler and Alex Hunsaker)
2036 Correctly propagate SQLSTATE in PL/Python exceptions
2037 (Mika Eloranta and Jan Urbanski)
2043 Do not install PL/Python extension files for Python major versions
2044 other than the one built against (Peter Eisentraut)
2050 Change all the <filename>contrib</> extension script files to report
2051 a useful error message if they are fed to <application>psql</>
2052 (Andrew Dunstan and Tom Lane)
2056 This should help teach people about the new method of using
2057 <command>CREATE EXTENSION</> to load these files. In most cases,
2058 sourcing the scripts directly would fail anyway, but with
2059 harder-to-interpret messages.
2065 Fix incorrect coding in <filename>contrib/dict_int</> and
2066 <filename>contrib/dict_xsyn</> (Tom Lane)
2070 Some functions incorrectly assumed that memory returned by
2071 <function>palloc()</> is guaranteed zeroed.
2077 Remove <filename>contrib/sepgsql</> tests from the regular regression
2078 test mechanism (Tom Lane)
2082 Since these tests require root privileges for setup, they're impractical
2083 to run automatically. Switch over to a manual approach instead, and
2084 provide a testing script to help with that.
2090 Fix assorted errors in <filename>contrib/unaccent</>'s configuration
2091 file parsing (Tom Lane)
2097 Honor query cancel interrupts promptly in <function>pgstatindex()</>
2104 Fix incorrect quoting of log file name in Mac OS X start script
2111 Revert unintentional enabling of <literal>WAL_DEBUG</> (Robert Haas)
2115 Fortunately, as debugging tools go, this one is pretty cheap;
2116 but it's not intended to be enabled by default, so revert.
2122 Ensure VPATH builds properly install all server header files
2129 Shorten file names reported in verbose error messages (Peter Eisentraut)
2133 Regular builds have always reported just the name of the C file
2134 containing the error message call, but VPATH builds formerly
2135 reported an absolute path name.
2141 Fix interpretation of Windows timezone names for Central America
2146 Map <quote>Central America Standard Time</> to <literal>CST6</>, not
2147 <literal>CST6CDT</>, because DST is generally not observed anywhere in
2154 Update time zone data files to <application>tzdata</> release 2011n
2155 for DST law changes in Brazil, Cuba, Fiji, Palestine, Russia, and Samoa;
2156 also historical corrections for Alaska and British East Africa.
2165 <sect1 id="release-9-1-1">
2166 <title>Release 9.1.1</title>
2169 <title>Release Date</title>
2170 <simpara>2011-09-26</simpara>
2174 This release contains a small number of fixes from 9.1.0.
2175 For information about new features in the 9.1 major release, see
2176 <xref linkend="release-9-1">.
2180 <title>Migration to Version 9.1.1</title>
2183 A dump/restore is not required for those running 9.1.X.
2189 <title>Changes</title>
2195 Make <function>pg_options_to_table</> return NULL for an option with no
2200 Previously such cases would result in a server crash.
2206 Fix memory leak at end of a GiST index scan (Tom Lane)
2210 Commands that perform many separate GiST index scans, such as
2211 verification of a new GiST-based exclusion constraint on a table
2212 already containing many rows, could transiently require large amounts of
2213 memory due to this leak.
2219 Fix explicit reference to <literal>pg_temp</> schema in <command>CREATE
2220 TEMPORARY TABLE</> (Robert Haas)
2224 This used to be allowed, but failed in 9.1.0.
2233 <sect1 id="release-9-1">
2234 <title>Release 9.1</title>
2237 <title>Release Date</title>
2238 <simpara>2011-09-12</simpara>
2242 <title>Overview</title>
2245 This release shows <productname>PostgreSQL</> moving beyond the
2246 traditional relational-database feature set with new, ground-breaking
2247 functionality that is unique to <productname>PostgreSQL</>.
2248 The streaming replication feature introduced in release 9.0 is
2249 significantly enhanced by adding a synchronous-replication option,
2250 streaming backups, and monitoring improvements.
2251 Major enhancements include:
2256 <!-- This list duplicates items below, but without authors or details-->
2260 Allow <link linkend="synchronous-replication">synchronous
2267 Add support for <link linkend="SQL-CREATEFOREIGNTABLE">foreign
2274 Add per-column <link
2275 linkend="collation">collation</link> support
2281 Add <link linkend="extend-extensions">extensions</link> which
2282 simplify packaging of additions to <productname>PostgreSQL</>
2289 linkend="xact-serializable">serializable isolation level</link>
2295 Support unlogged tables using the <literal>UNLOGGED</>
2296 option in <link linkend="SQL-CREATETABLE"><command>CREATE
2303 Allow data-modification commands
2304 (<command>INSERT</>/<command>UPDATE</>/<command>DELETE</>) in
2305 <link linkend="queries-with"><literal>WITH</></link> clauses
2311 Add nearest-neighbor (order-by-operator) searching to <link
2312 linkend="GiST"><acronym>GiST</> indexes</link>
2318 Add a <link linkend="SQL-SECURITY-LABEL"><command>SECURITY
2319 LABEL</></link> command and support for
2320 <link linkend="sepgsql"><acronym>SELinux</> permissions control</link>
2326 Update the <link linkend="plpython">PL/Python</link> server-side
2334 The above items are explained in more detail in the sections below.
2341 <title>Migration to Version 9.1</title>
2344 A dump/restore using <application>pg_dump</application>,
2345 or use of <application>pg_upgrade</application>, is required
2346 for those wishing to migrate data from any previous
2351 Version 9.1 contains a number of changes that may affect compatibility
2352 with previous releases. Observe the following incompatibilities:
2356 <title>Strings</title>
2362 Change the default value of <link
2363 linkend="guc-standard-conforming-strings"><varname>standard_conforming_strings</></link>
2368 By default, backslashes are now ordinary characters in string literals,
2369 not escape characters. This change removes a long-standing
2370 incompatibility with the SQL standard. <link
2371 linkend="guc-escape-string-warning"><varname>escape_string_warning</></link>
2372 has produced warnings about this usage for years. <literal>E''</>
2373 strings are the proper way to embed backslash escapes in strings and are
2374 unaffected by this change.
2379 This change can break applications that are not expecting it and
2380 do their own string escaping according to the old rules. The
2381 consequences could be as severe as introducing SQL-injection security
2382 holes. Be sure to test applications that are exposed to untrusted
2383 input, to ensure that they correctly handle single quotes and
2384 backslashes in text strings.
2394 <title>Casting</title>
2400 Disallow function-style and attribute-style data type casts for
2401 composite types (Tom Lane)
2405 For example, disallow
2406 <literal><replaceable>composite_value</>.text</literal> and
2407 <literal>text(<replaceable>composite_value</>)</literal>.
2408 Unintentional uses of this syntax have frequently resulted in bug
2409 reports; although it was not a bug, it seems better to go back to
2410 rejecting such expressions.
2411 The <literal>CAST</> and <literal>::</> syntaxes are still available
2412 for use when a cast of an entire composite value is actually intended.
2418 Tighten casting checks for domains based on arrays (Tom Lane)
2422 When a domain is based on an array type, it is allowed to <quote>look
2423 through</> the domain type to access the array elements, including
2424 subscripting the domain value to fetch or assign an element.
2425 Assignment to an element of such a domain value, for instance via
2426 <literal>UPDATE ... SET domaincol[5] = ...</>, will now result in
2427 rechecking the domain type's constraints, whereas before the checks
2437 <title>Arrays</title>
2444 linkend="array-functions-table"><function>string_to_array()</></link>
2445 to return an empty array for a zero-length string (Pavel
2450 Previously this returned a null value.
2457 linkend="array-functions-table"><function>string_to_array()</></link>
2458 so a <literal>NULL</> separator splits the string into characters
2463 Previously this returned a null value.
2472 <title>Object Modification</title>
2478 Fix improper checks for before/after triggers (Tom Lane)
2482 Triggers can now be fired in three cases: <literal>BEFORE</>,
2483 <literal>AFTER</>, or <literal>INSTEAD OF</> some action.
2484 Trigger function authors should verify that their logic behaves
2485 sanely in all three cases.
2491 Require superuser or <literal>CREATEROLE</> permissions in order to
2492 set comments on roles (Tom Lane)
2501 <title>Server Settings</title>
2508 linkend="functions-recovery-info-table"><function>pg_last_xlog_receive_location()</></link>
2509 so it never moves backwards (Fujii Masao)
2513 Previously, the value of <function>pg_last_xlog_receive_location()</>
2514 could move backward when streaming replication is restarted.
2520 Have logging of replication connections honor <link
2521 linkend="guc-log-connections"><varname>log_connections</></link>
2526 Previously, replication connections were always logged.
2535 <title><link linkend="plpgsql">PL/pgSQL</link> Server-Side Language</title>
2541 Change PL/pgSQL's <literal>RAISE</> command without parameters
2542 to be catchable by the attached exception block (Piyush Newe)
2546 Previously <literal>RAISE</> in a code block was always scoped to
2547 an attached exception block, so it was uncatchable at the same
2554 Adjust PL/pgSQL's error line numbering code to be consistent
2555 with other PLs (Pavel Stehule)
2559 Previously, PL/pgSQL would ignore (not count) an empty line at the
2560 start of the function body. Since this was inconsistent with all
2561 other languages, the special case was removed.
2567 Make PL/pgSQL complain about conflicting IN and OUT parameter names
2572 Formerly, the collision was not detected, and the name would just
2573 silently refer to only the OUT parameter.
2579 Type modifiers of PL/pgSQL variables are now visible to the SQL parser
2584 A type modifier (such as a varchar length limit) attached to a PL/pgSQL
2585 variable was formerly enforced during assignments, but was ignored for
2586 all other purposes. Such variables will now behave more like table
2587 columns declared with the same modifier. This is not expected to make
2588 any visible difference in most cases, but it could result in subtle
2589 changes for some SQL commands issued by PL/pgSQL functions.
2598 <title>Contrib</title>
2604 All contrib modules are now installed with <link
2605 linkend="SQL-CREATEEXTENSION"><command>CREATE EXTENSION</></link>
2606 rather than by manually invoking their SQL scripts
2607 (Dimitri Fontaine, Tom Lane)
2611 To update an existing database containing the 9.0 version of a contrib
2612 module, use <literal>CREATE EXTENSION ... FROM unpackaged</literal>
2613 to wrap the existing contrib module's objects into an extension. When
2614 updating from a pre-9.0 version, drop the contrib module's objects
2615 using its old uninstall script, then use <literal>CREATE EXTENSION</>.
2624 <title>Other Incompatibilities</title>
2631 linkend="monitoring-stats-funcs-table"><function>pg_stat_reset()</></link>
2632 reset all database-level statistics (Tomas Vondra)
2636 Some <structname>pg_stat_database</> counters were not being reset.
2643 linkend="infoschema-triggers"><structname>information_schema.triggers</></link>
2644 column names to match the new SQL-standard names (Dean Rasheed)
2650 Treat <application>ECPG</> cursor names as case-insensitive
2651 (Zoltan Boszormenyi)
2661 <title>Changes</title>
2664 Below you will find a detailed account of the changes between
2665 <productname>PostgreSQL</productname> 9.1 and the previous major
2670 <title>Server</title>
2673 <title>Performance</title>
2679 Support unlogged tables using the <literal>UNLOGGED</>
2680 option in <link linkend="SQL-CREATETABLE"><command>CREATE
2681 TABLE</></link> (Robert Haas)
2685 Such tables provide better update performance than regular tables,
2686 but are not crash-safe: their contents are automatically cleared in
2687 case of a server crash. Their contents do not propagate to
2688 replication slaves, either.
2694 Allow <literal>FULL OUTER JOIN</literal> to be implemented as a
2695 hash join, and allow either side of a <literal>LEFT OUTER JOIN</>
2696 or <literal>RIGHT OUTER JOIN</> to be hashed (Tom Lane)
2700 Previously <literal>FULL OUTER JOIN</literal> could only be
2701 implemented as a merge join, and <literal>LEFT OUTER JOIN</literal>
2702 and <literal>RIGHT OUTER JOIN</literal> could hash only the nullable
2703 side of the join. These changes provide additional query optimization
2710 Merge duplicate fsync requests (Robert Haas, Greg Smith)
2714 This greatly improves performance under heavy write loads.
2720 Improve performance of <link
2721 linkend="guc-commit-siblings"><varname>commit_siblings</></link>
2726 This allows the use of <varname>commit_siblings</varname> with
2733 Reduce the memory requirement for large ispell dictionaries
2734 (Pavel Stehule, Tom Lane)
2740 Avoid leaving data files open after <quote>blind writes</>
2745 This fixes scenarios in which backends might hold files open long
2746 after they were deleted, preventing the kernel from reclaiming
2756 <title>Optimizer</title>
2762 Allow inheritance table scans to return meaningfully-sorted
2763 results (Greg Stark, Hans-Jurgen Schonig, Robert Haas, Tom Lane)
2767 This allows better optimization of queries that use <literal>ORDER
2768 BY</>, <literal>LIMIT</>, or <literal>MIN</>/<literal>MAX</> with
2775 Improve GIN index scan cost estimation (Teodor Sigaev)
2781 Improve cost estimation for aggregates and window functions (Tom Lane)
2790 <title>Authentication</title>
2796 Support host names and host suffixes
2797 (e.g. <literal>.example.com</>) in <link
2798 linkend="auth-pg-hba-conf"><filename>pg_hba.conf</></link>
2803 Previously only host <acronym>IP</> addresses and <acronym>CIDR</>
2804 values were supported.
2810 Support the key word <literal>all</> in the host column of <link
2811 linkend="auth-pg-hba-conf"><filename>pg_hba.conf</></link>
2816 Previously people used <literal>0.0.0.0/0</> or <literal>::/0</>
2823 Reject <literal>local</> lines in <link
2824 linkend="auth-pg-hba-conf"><filename>pg_hba.conf</></link>
2825 on platforms that don't support Unix-socket connections
2830 Formerly, such lines were silently ignored, which could be surprising.
2831 This makes the behavior more like other unsupported cases.
2837 Allow <link linkend="gssapi-auth"><acronym>GSSAPI</></link>
2838 to be used to authenticate to servers via <link
2839 linkend="sspi-auth"><acronym>SSPI</></link> (Christian Ullrich)
2843 Specifically this allows Unix-based <acronym>GSSAPI</> clients
2844 to do <acronym>SSPI</> authentication with Windows servers.
2850 <link linkend="auth-ident"><literal>ident</literal></link>
2851 authentication over local sockets is now known as
2852 <link linkend="auth-peer"><literal>peer</literal></link>
2857 The old term is still accepted for backward compatibility, but since
2858 the two methods are fundamentally different, it seemed better to adopt
2859 different names for them.
2865 Rewrite <link linkend="auth-peer"><acronym>peer</></link>
2866 authentication to avoid use of credential control messages (Tom Lane)
2870 This change makes the peer authentication code simpler and
2871 better-performing. However, it requires the platform to provide the
2872 <function>getpeereid</> function or an equivalent socket operation.
2873 So far as is known, the only platform for which peer authentication
2874 worked before and now will not is pre-5.0 NetBSD.
2883 <title>Monitoring</title>
2889 Add details to the logging of restartpoints and checkpoints,
2890 which is controlled by <link
2891 linkend="guc-log-checkpoints"><varname>log_checkpoints</></link>
2892 (Fujii Masao, Greg Smith)
2896 New details include <acronym>WAL</> file and sync activity.
2903 linkend="guc-log-file-mode"><varname>log_file_mode</></link>
2904 which controls the permissions on log files created by the
2905 logging collector (Martin Pihlak)
2911 Reduce the default maximum line length for <application>syslog</>
2912 logging to 900 bytes plus prefixes (Noah Misch)
2916 This avoids truncation of long log lines on syslog implementations
2917 that have a 1KB length limit, rather than the more common 2KB.
2926 <title>Statistical Views</title>
2932 Add <structfield>client_hostname</structfield> column to <link
2933 linkend="monitoring-stats-views-table"><structname>pg_stat_activity</></link>
2938 Previously only the client address was reported.
2945 linkend="monitoring-stats-views-table"><structname>pg_stat_xact_*</></link>
2946 statistics functions and views (Joel Jacobson)
2950 These are like the database-wide statistics counter views, but
2951 reflect counts for only the current transaction.
2957 Add time of last reset in database-level and background writer
2958 statistics views (Tomas Vondra)
2964 Add columns showing the number of vacuum and analyze operations
2966 linkend="monitoring-stats-views-table"><structname>pg_stat_*_tables</></link>
2967 views (Magnus Hagander)
2973 Add <structfield>buffers_backend_fsync</> column to <link
2974 linkend="monitoring-stats-views-table"><structname>pg_stat_bgwriter</></link>
2979 This new column counts the number of times a backend fsyncs a
2989 <title>Server Settings</title>
2995 Provide auto-tuning of <link
2996 linkend="guc-wal-buffers"><varname>wal_buffers</></link> (Greg
3001 By default, the value of <varname>wal_buffers</> is now chosen
3002 automatically based on the value of <varname>shared_buffers</>.
3008 Increase the maximum values for
3009 <link linkend="guc-deadlock-timeout"><varname>deadlock_timeout</varname></link>,
3010 <link linkend="guc-log-min-duration-statement"><varname>log_min_duration_statement</varname></link>, and
3011 <link linkend="guc-log-autovacuum-min-duration"><varname>log_autovacuum_min_duration</varname></link>
3016 The maximum value for each of these parameters was previously
3017 only about 35 minutes. Much larger values are now allowed.
3028 <title>Replication and Recovery</title>
3031 <title>Streaming Replication and Continuous Archiving</title>
3037 Allow <link linkend="synchronous-replication">synchronous
3038 replication</link> (Simon Riggs, Fujii Masao)
3042 This allows the primary server to wait for a standby to write a
3043 transaction's information to disk before acknowledging the commit.
3044 One standby at a time can take the role of the synchronous standby,
3045 as controlled by the
3046 <link linkend="guc-synchronous-standby-names"><varname>synchronous_standby_names</varname></link>
3047 setting. Synchronous replication can be enabled or disabled on a
3048 per-transaction basis using the
3049 <link linkend="guc-synchronous-commit"><varname>synchronous_commit</></link>
3056 Add protocol support for sending file system backups to standby servers
3057 using the streaming replication network connection (Magnus Hagander,
3062 This avoids the requirement of manually transferring a file
3063 system backup when setting up a standby server.
3070 <link linkend="guc-replication-timeout"><varname>replication_timeout</></link>
3071 setting (Fujii Masao, Heikki Linnakangas)
3075 Replication connections that are idle for more than the
3076 <varname>replication_timeout</> interval will be terminated
3077 automatically. Formerly, a failed connection was typically not
3078 detected until the TCP timeout elapsed, which is inconveniently
3079 long in many situations.
3085 Add command-line tool <link
3086 linkend="app-pgbasebackup"><application>pg_basebackup</></link>
3087 for creating a new standby server or database backup (Magnus
3094 Add a <link linkend="SQL-CREATEROLE">replication permission</link>
3095 for roles (Magnus Hagander)
3099 This is a read-only permission used for streaming replication.
3100 It allows a non-superuser role to be used for replication connections.
3101 Previously only superusers could initiate replication
3102 connections; superusers still have this permission by default.
3111 <title>Replication Monitoring</title>
3117 Add system view <link
3118 linkend="monitoring-stats-views-table"><structname>pg_stat_replication</></link>
3119 which displays activity of <acronym>WAL</> sender processes (Itagaki
3120 Takahiro, Simon Riggs)
3124 This reports the status of all connected standby servers.
3130 Add monitoring function <link
3131 linkend="functions-recovery-info-table"><function>pg_last_xact_replay_timestamp()</></link>
3136 This returns the time at which the primary generated the most
3137 recent commit or abort record applied on the standby.
3146 <title>Hot Standby</title>
3152 Add configuration parameter <link
3153 linkend="guc-hot-standby-feedback"><varname>hot_standby_feedback</></link>
3154 to enable standbys to postpone cleanup of old row versions on the
3155 primary (Simon Riggs)
3159 This helps avoid canceling long-running queries on the standby.
3166 linkend="monitoring-stats-views-table"><structname>pg_stat_database_conflicts</></link>
3167 system view to show queries that have been canceled and the
3168 reason (Magnus Hagander)
3172 Cancellations can occur because of dropped tablespaces, lock
3173 timeouts, old snapshots, pinned buffers, and deadlocks.
3179 Add a <structfield>conflicts</> count to <link
3180 linkend="monitoring-stats-views-table"><structname>pg_stat_database</></link>
3185 This is the number of conflicts that occurred in the database.
3191 Increase the maximum values for
3192 <link linkend="guc-max-standby-archive-delay"><varname>max_standby_archive_delay</varname></link> and
3193 <link linkend="guc-max-standby-streaming-delay"><varname>max_standby_streaming_delay</varname></link>
3197 The maximum value for each of these parameters was previously
3198 only about 35 minutes. Much larger values are now allowed.
3205 linkend="errcodes-table"><literal>ERRCODE_T_R_DATABASE_DROPPED</></link>
3206 error code to report recovery conflicts due to dropped databases
3211 This is useful for connection pooling software.
3220 <title>Recovery Control</title>
3226 Add functions to control streaming replication replay (Simon Riggs)
3230 The new functions are <link
3231 linkend="functions-recovery-control-table"><function>pg_xlog_replay_pause()</></link>,
3233 linkend="functions-recovery-control-table"><function>pg_xlog_replay_resume()</></link>,
3234 and the status function <link
3235 linkend="functions-recovery-control-table"><function>pg_is_xlog_replay_paused()</></link>.
3241 Add <filename>recovery.conf</> setting <link
3242 linkend="pause-at-recovery-target"><varname>pause_at_recovery_target</></link>
3243 to pause recovery at target (Simon Riggs)
3247 This allows a recovery server to be queried to check whether
3248 the recovery point is the one desired.
3254 Add the ability to create named restore points using <link
3255 linkend="functions-admin-backup-table"><function>pg_create_restore_point()</></link>
3260 These named restore points can be specified as recovery
3261 targets using the new <filename>recovery.conf</> setting
3262 <link linkend="recovery-target-name"><varname>recovery_target_name</></link>.
3268 Allow standby recovery to switch to a new timeline automatically
3269 (Heikki Linnakangas)
3273 Now standby servers scan the archive directory for new
3274 timelines periodically.
3281 linkend="guc-restart-after-crash"><varname>restart_after_crash</></link>
3282 setting which disables automatic server restart after a backend
3287 This allows external cluster management software to control
3288 whether the database server restarts or not.
3295 linkend="recovery-config"><filename>recovery.conf</></link>
3296 to use the same quoting behavior as <filename>postgresql.conf</>
3301 Previously all values had to be quoted.
3312 <title>Queries</title>
3319 linkend="xact-serializable">serializable isolation level</link>
3320 (Kevin Grittner, Dan Ports)
3324 Previously, asking for serializable isolation guaranteed only that a
3325 single MVCC snapshot would be used for the entire transaction, which
3326 allowed certain documented anomalies. The old snapshot isolation
3327 behavior is still available by requesting the <link
3328 linkend="xact-repeatable-read"><literal>REPEATABLE READ</></link>
3335 Allow data-modification commands
3336 (<command>INSERT</>/<command>UPDATE</>/<command>DELETE</>) in
3337 <link linkend="queries-with"><literal>WITH</></link> clauses
3338 (Marko Tiikkaja, Hitoshi Harada)
3342 These commands can use <literal>RETURNING</> to pass data up to the
3349 Allow <link linkend="queries-with"><literal>WITH</></link>
3350 clauses to be attached to <command>INSERT</>, <command>UPDATE</>,
3351 <command>DELETE</> statements (Marko Tiikkaja, Hitoshi Harada)
3357 Allow non-<link linkend="queries-group"><literal>GROUP
3358 BY</></link> columns in the query target list when the primary
3359 key is specified in the <literal>GROUP BY</> clause (Peter
3364 The SQL standard allows this behavior, and
3365 because of the primary key, the result is unambiguous.
3371 Allow use of the key word <literal>DISTINCT</> in <link
3372 linkend="queries-union"><literal>UNION</>/<literal>INTERSECT</>/<literal>EXCEPT</></link>
3377 <literal>DISTINCT</> is the default behavior so use of this
3378 key word is redundant, but the SQL standard allows it.
3384 Fix ordinary queries with rules to use the same snapshot behavior
3385 as <command>EXPLAIN ANALYZE</> (Marko Tiikkaja)
3389 Previously <command>EXPLAIN ANALYZE</> used slightly different
3390 snapshot timing for queries involving rules. The
3391 <command>EXPLAIN ANALYZE</> behavior was judged to be more logical.
3398 <title>Strings</title>
3404 Add per-column <link
3405 linkend="collation">collation</link> support
3406 (Peter Eisentraut, Tom Lane)
3410 Previously collation (the sort ordering of text strings) could only be
3411 chosen at database creation.
3412 Collation can now be set per column, domain, index, or
3413 expression, via the SQL-standard <literal>COLLATE</> clause.
3424 <title>Object Manipulation</title>
3430 Add <link linkend="extend-extensions">extensions</link> which
3431 simplify packaging of additions to <productname>PostgreSQL</>
3432 (Dimitri Fontaine, Tom Lane)
3436 Extensions are controlled by the new <link
3437 linkend="SQL-CREATEEXTENSION"><command>CREATE</></link>/<link
3438 linkend="SQL-ALTEREXTENSION"><command>ALTER</></link>/<link
3439 linkend="SQL-DROPEXTENSION"><command>DROP EXTENSION</></link>
3440 commands. This replaces ad-hoc methods of grouping objects that
3441 are added to a <productname>PostgreSQL</> installation.
3447 Add support for <link linkend="SQL-CREATEFOREIGNTABLE">foreign
3448 tables</link> (Shigeru Hanada, Robert Haas, Jan Urbanski,
3453 This allows data stored outside the database to be used like
3454 native <productname>PostgreSQL</>-stored data. Foreign tables
3455 are currently read-only, however.
3461 Allow new values to be added to an existing enum type via
3462 <link linkend="SQL-ALTERTYPE"><command>ALTER TYPE</></link> (Andrew
3469 Add <link linkend="SQL-ALTERTYPE"><command>ALTER TYPE ...
3470 ADD/DROP/ALTER/RENAME ATTRIBUTE</></link> (Peter Eisentraut)
3474 This allows modification of composite types.
3481 <title><command>ALTER</> Object</title>
3487 Add <literal>RESTRICT</>/<literal>CASCADE</> to <link
3488 linkend="SQL-ALTERTYPE"><command>ALTER TYPE</></link> operations
3489 on typed tables (Peter Eisentraut)
3494 <literal>ADD</>/<literal>DROP</>/<literal>ALTER</>/<literal>RENAME
3495 ATTRIBUTE</> cascading behavior.
3501 Support <literal>ALTER TABLE <replaceable>name</> {OF | NOT OF}
3502 <replaceable>type</></literal>
3507 This syntax allows a standalone table to be made into a typed table,
3508 or a typed table to be made standalone.
3514 Add support for more object types in <command>ALTER ... SET
3515 SCHEMA</> commands (Dimitri Fontaine)
3519 This command is now supported for conversions, operators, operator
3520 classes, operator families, text search configurations, text search
3521 dictionaries, text search parsers, and text search templates.
3530 <title><link linkend="SQL-CREATETABLE"><command>CREATE/ALTER TABLE</></link></title>
3536 Add <command>ALTER TABLE ...
3537 ADD UNIQUE/PRIMARY KEY USING INDEX</command>
3542 This allows a primary key or unique constraint to be defined using an
3543 existing unique index, including a concurrently created unique index.
3549 Allow <command>ALTER TABLE</>
3550 to add foreign keys without validation (Simon Riggs)
3554 The new option is called <literal>NOT VALID</>. The constraint's
3555 state can later be modified to <literal>VALIDATED</> and validation
3556 checks performed. Together these allow you to add a foreign key
3557 with minimal impact on read and write operations.
3563 Allow <link linkend="SQL-ALTERTABLE"><command>ALTER TABLE
3564 ... SET DATA TYPE</command></link> to avoid table rewrites in
3565 appropriate cases (Noah Misch, Robert Haas)
3569 For example, converting a <type>varchar</> column to
3570 <type>text</> no longer requires a rewrite of the table.
3571 However, increasing the length constraint on a
3572 <type>varchar</> column still requires a table rewrite.
3578 Add <link linkend="SQL-CREATETABLE"><command>CREATE TABLE IF
3579 NOT EXISTS</></link> syntax (Robert Haas)
3583 This allows table creation without causing an error if the
3584 table already exists.
3590 Fix possible <quote>tuple concurrently updated</quote> error
3591 when two backends attempt to add an inheritance
3592 child to the same table at the same time (Robert Haas)
3596 <link linkend="sql-altertable"><command>ALTER TABLE</command></link>
3597 now takes a stronger lock on the parent table, so that the sessions
3598 cannot try to update it simultaneously.
3607 <title>Object Permissions</title>
3613 Add a <link linkend="SQL-SECURITY-LABEL"><command>SECURITY
3614 LABEL</></link> command (KaiGai Kohei)
3618 This allows security labels to be assigned to objects.
3629 <title>Utility Operations</title>
3635 Add transaction-level <link linkend="advisory-locks">advisory
3636 locks</link> (Marko Tiikkaja)
3640 These are similar to the existing session-level advisory locks,
3641 but such locks are automatically released at transaction end.
3647 Make <link linkend="SQL-TRUNCATE"><command>TRUNCATE ... RESTART
3648 IDENTITY</></link> restart sequences transactionally (Steve
3653 Previously the counter could have been left out of sync if a
3654 backend crashed between the on-commit truncation activity and
3662 <title><link linkend="SQL-COPY"><command>COPY</></link></title>
3668 Add <literal>ENCODING</> option to <link
3669 linkend="SQL-COPY"><command>COPY TO/FROM</></link> (Hitoshi
3670 Harada, Itagaki Takahiro)
3674 This allows the encoding of the <command>COPY</> file to be
3675 specified separately from client encoding.
3681 Add bidirectional <link linkend="SQL-COPY"><command>COPY</></link>
3682 protocol support (Fujii Masao)
3686 This is currently only used by streaming replication.
3695 <title><link linkend="SQL-EXPLAIN"><command>EXPLAIN</></link></title>
3701 Make <command>EXPLAIN VERBOSE</> show the function call expression
3702 in a <literal>FunctionScan</literal> node (Tom Lane)
3711 <title><link linkend="SQL-VACUUM"><command>VACUUM</></link></title>
3717 Add additional details to the output of <link
3718 linkend="SQL-VACUUM"><command>VACUUM FULL VERBOSE</></link>
3719 and <link linkend="SQL-CLUSTER"><command>CLUSTER VERBOSE</></link>
3724 New information includes the live and dead tuple count and
3725 whether <command>CLUSTER</> is using an index to rebuild.
3731 Prevent <link linkend="autovacuum">autovacuum</link> from
3732 waiting if it cannot acquire a table lock (Robert Haas)
3736 It will try to vacuum that table later.
3745 <title><link linkend="SQL-CLUSTER"><command>CLUSTER</></link></title>
3751 Allow <command>CLUSTER</> to sort the table rather than scanning
3752 the index when it seems likely to be cheaper (Leonardo Francalanci)
3761 <title>Indexes</title>
3767 Add nearest-neighbor (order-by-operator) searching to <link
3768 linkend="GiST"><acronym>GiST</> indexes</link> (Teodor Sigaev, Tom Lane)
3772 This allows <acronym>GiST</> indexes to quickly return the
3773 <replaceable>N</> closest values in a query with <literal>LIMIT</>.
3775 <programlisting><![CDATA[
3776 SELECT * FROM places ORDER BY location <-> point '(101,456)' LIMIT 10;
3779 finds the ten places closest to a given target point.
3785 Allow <link linkend="GIN"><acronym>GIN</> indexes</link> to index null
3786 and empty values (Tom Lane)
3790 This allows full <acronym>GIN</> index scans, and fixes various
3791 corner cases in which GIN scans would fail.
3797 Allow <link linkend="GIN"><acronym>GIN</> indexes</link> to
3798 better recognize duplicate search entries (Tom Lane)
3802 This reduces the cost of index scans, especially in cases where
3803 it avoids unnecessary full index scans.
3809 Fix <link linkend="GiST"><acronym>GiST</> indexes</link> to be fully
3810 crash-safe (Heikki Linnakangas)
3814 Previously there were rare cases where a <command>REINDEX</>
3815 would be required (you would be informed).
3826 <title>Data Types</title>
3832 Allow <type>numeric</> to use a more compact, two-byte header
3833 in common cases (Robert Haas)
3837 Previously all <type>numeric</> values had four-byte headers;
3838 this change saves on disk storage.
3844 Add support for dividing <type>money</> by <type>money</>
3851 Allow binary I/O on type <type>void</type> (Radoslaw Smogura)
3857 Improve hypotenuse calculations for geometric operators (Paul Matthews)
3861 This avoids unnecessary overflows, and may also be more accurate.
3867 Support hashing array values (Tom Lane)
3871 This provides additional query optimization possibilities.
3877 Don't treat a composite type as sortable unless all its column types
3878 are sortable (Tom Lane)
3882 This avoids possible <quote>could not identify a comparison function</>
3883 failures at runtime, if it is possible to implement the query without
3884 sorting. Also, <command>ANALYZE</> won't try to use inappropriate
3885 statistics-gathering methods for columns of such composite types.
3892 <title>Casting</title>
3898 Add support for casting between <type>money</> and <type>numeric</>
3905 Add support for casting from <type>int4</> and <type>int8</>
3906 to <type>money</> (Joey Adams)
3912 Allow casting a table's row type to the table's supertype if
3913 it's a typed table (Peter Eisentraut)
3917 This is analogous to the existing facility that allows casting a row
3918 type to a supertable's row type.
3927 <title><link linkend="functions-xml"><acronym>XML</></link></title>
3933 Add <acronym>XML</> function <link
3934 linkend="xml-exists"><literal>XMLEXISTS</></link> and <link
3935 linkend="xml-exists"><function>xpath_exists()</></link>
3936 functions (Mike Fowler)
3940 These are used for XPath matching.
3946 Add <acronym>XML</> functions <link
3947 linkend="xml-is-well-formed"><function>xml_is_well_formed()</></link>,
3949 linkend="xml-is-well-formed"><function>xml_is_well_formed_document()</></link>,
3951 linkend="xml-is-well-formed"><function>xml_is_well_formed_content()</></link>
3956 These check whether the input is properly-formed <acronym>XML</>.
3957 They provide functionality that was previously available only in
3958 the deprecated <filename>contrib/xml2</filename> module.
3969 <title>Functions</title>
3975 Add SQL function <link
3976 linkend="format"><function>format(text, ...)</></link>, which
3977 behaves analogously to C's <function>printf()</> (Pavel Stehule,
3982 It currently supports formats for strings, SQL literals, and
3989 Add string functions <link
3990 linkend="functions-string-other"><function>concat()</></link>,
3992 linkend="functions-string-other"><function>concat_ws()</></link>,
3993 <link linkend="functions-string-other"><function>left()</></link>,
3994 <link linkend="functions-string-other"><function>right()</></link>,
3996 linkend="functions-string-other"><function>reverse()</></link>
4001 These improve compatibility with other database products.
4008 linkend="functions-admin-genfile"><function>pg_read_binary_file()</></link>
4009 to read binary files (Dimitri Fontaine, Itagaki Takahiro)
4015 Add a single-parameter version of function <link
4016 linkend="functions-admin-genfile"><function>pg_read_file()</></link>
4017 to read an entire file (Dimitri Fontaine, Itagaki Takahiro)
4023 Add three-parameter forms of <link
4024 linkend="array-functions-table"><function>array_to_string()</></link>
4026 linkend="array-functions-table"><function>string_to_array()</></link>
4027 for null value processing control (Pavel Stehule)
4034 <title>Object Information Functions</title>
4041 linkend="functions-info-catalog-table"><function>pg_describe_object()</></link>
4042 function (Alvaro Herrera)
4046 This function is used to obtain a human-readable string describing
4047 an object, based on the <link
4048 linkend="catalog-pg-class"><structname>pg_class</structname></link>
4049 OID, object OID, and sub-object ID. It can be used to help
4050 interpret the contents of <link
4051 linkend="catalog-pg-depend"><structname>pg_depend</structname></link>.
4057 Update comments for built-in operators and their underlying
4058 functions (Tom Lane)
4062 Functions that are meant to be used via an associated operator
4063 are now commented as such.
4070 linkend="guc-quote-all-identifiers"><varname>quote_all_identifiers</></link>
4071 to force the quoting of all identifiers in <command>EXPLAIN</>
4072 and in system catalog functions like <link
4073 linkend="functions-info-catalog-table"><function>pg_get_viewdef()</></link>
4078 This makes exporting schemas to tools and other databases with
4079 different quoting rules easier.
4085 Add columns to the <link
4086 linkend="infoschema-sequences"><structname>information_schema.sequences</></link>
4087 system view (Peter Eisentraut)
4091 Previously, though the view existed, the columns about the
4092 sequence parameters were unimplemented.
4098 Allow <literal>public</> as a pseudo-role name in <link
4099 linkend="functions-info-access-table"><function>has_table_privilege()</></link>
4100 and related functions (Alvaro Herrera)
4104 This allows checking for public permissions.
4113 <title>Function and Trigger Creation</title>
4119 Support <link linkend="SQL-CREATETRIGGER"><literal>INSTEAD
4120 OF</></link> triggers on views (Dean Rasheed)
4124 This feature can be used to implement fully updatable views.
4135 <title>Server-Side Languages</title>
4138 <title><link linkend="plpgsql">PL/pgSQL</link> Server-Side Language</title>
4144 Add <link linkend="plpgsql-foreach-array"><command>FOREACH IN
4145 ARRAY</></link> to PL/pgSQL
4150 This is more efficient and readable than previous methods of
4151 iterating through the elements of an array value.
4157 Allow <command>RAISE</command> without parameters to be caught in
4158 the same places that could catch a <command>RAISE ERROR</command>
4159 from the same location (Piyush Newe)
4163 The previous coding threw the error
4164 from the block containing the active exception handler.
4165 The new behavior is more consistent with other DBMS products.
4174 <title><link linkend="plperl">PL/Perl</link> Server-Side Language</title>
4180 Allow generic record arguments to PL/Perl functions (Andrew
4185 PL/Perl functions can now be declared to accept type <type>record</>.
4186 The behavior is the same as for any named composite type.
4192 Convert PL/Perl array arguments to Perl arrays (Alexey Klyukin,
4197 String representations are still available.
4203 Convert PL/Perl composite-type arguments to Perl hashes
4204 (Alexey Klyukin, Alex Hunsaker)
4208 String representations are still available.
4217 <title><link linkend="plpython">PL/Python</link> Server-Side Language</title>
4223 Add table function support for PL/Python (Jan Urbanski)
4227 PL/Python can now return multiple <literal>OUT</> parameters
4234 Add a validator to PL/Python (Jan Urbanski)
4238 This allows PL/Python functions to be syntax-checked at function
4245 Allow exceptions for SQL queries in PL/Python (Jan Urbanski)
4249 This allows access to SQL-generated exception error codes from
4250 PL/Python exception blocks.
4256 Add explicit subtransactions to PL/Python (Jan Urbanski)
4262 Add PL/Python functions for quoting strings (Jan Urbanski)
4266 These functions are <link
4267 linkend="plpython-util"><literal>plpy.quote_ident</></link>,
4268 <link linkend="plpython-util"><literal>plpy.quote_literal</></link>,
4270 linkend="plpython-util"><literal>plpy.quote_nullable</></link>.
4276 Add traceback information to PL/Python errors (Jan Urbanski)
4282 Report PL/Python errors from iterators with <literal>PLy_elog</> (Jan
4289 Fix exception handling with Python 3 (Jan Urbanski)
4293 Exception classes were previously not available in
4294 <literal>plpy</> under Python 3.
4305 <title>Client Applications</title>
4312 linkend="APP-CREATELANG"><application>createlang</></link>
4313 and <link linkend="APP-DROPLANG"><application>droplang</></link>
4314 as deprecated now that they just invoke extension commands (Tom
4322 <title><link linkend="APP-PSQL"><application>psql</></link></title>
4328 Add <application>psql</> command <literal>\conninfo</>
4329 to show current connection information (David Christensen)
4335 Add <application>psql</> command <literal>\sf</> to
4336 show a function's definition (Pavel Stehule)
4342 Add <application>psql</> command <literal>\dL</> to list
4343 languages (Fernando Ike)
4349 Add the <option>S</> (<quote>system</>) option to <application>psql</>'s
4350 <literal>\dn</> (list schemas) command (Tom Lane)
4354 <literal>\dn</> without <literal>S</> now suppresses system
4361 Allow <application>psql</>'s <literal>\e</> and <literal>\ef</>
4362 commands to accept a line number to be used to position the
4363 cursor in the editor (Pavel Stehule)
4367 This is passed to the editor according to the
4368 <envar>PSQL_EDITOR_LINENUMBER_ARG</> environment variable.
4374 Have <application>psql</> set the client encoding from the
4375 operating system locale by default (Heikki Linnakangas)
4379 This only happens if the <envar>PGCLIENTENCODING</> environment
4380 variable is not set.
4386 Make <literal>\d</literal> distinguish between unique
4387 indexes and unique constraints (Josh Kupershmidt)
4393 Make <literal>\dt+</literal> report <function>pg_table_size</>
4394 instead of <function>pg_relation_size</> when talking to 9.0 or
4395 later servers (Bernd Helmle)
4399 This is a more useful measure of table size, but note that it is
4400 not identical to what was previously reported in the same display.
4406 Additional tab completion support (Itagaki Takahiro, Pavel Stehule,
4407 Andrey Popp, Christoph Berg, David Fetter, Josh Kupershmidt)
4416 <title><link linkend="APP-PGDUMP"><application>pg_dump</></link></title>
4422 Add <link linkend="APP-PGDUMP"><application>pg_dump</></link>
4424 linkend="APP-PG-DUMPALL"><application>pg_dumpall</></link>
4425 option <option>--quote-all-identifiers</> to force quoting
4426 of all identifiers (Robert Haas)
4432 Add <literal>directory</> format to <application>pg_dump</>
4433 (Joachim Wieland, Heikki Linnakangas)
4437 This is internally similar to the <literal>tar</>
4438 <application>pg_dump</> format.
4447 <title><link linkend="APP-PG-CTL"><application>pg_ctl</></link></title>
4453 Fix <application>pg_ctl</>
4454 so it no longer incorrectly reports that the server is not
4455 running (Bruce Momjian)
4459 Previously this could happen if the server was running but
4460 <application>pg_ctl</> could not authenticate.
4466 Improve <application>pg_ctl</> start's <quote>wait</quote>
4467 (<option>-w</>) option (Bruce Momjian, Tom Lane)
4471 The wait mode is now significantly more robust. It will not get
4472 confused by non-default postmaster port numbers, non-default
4473 Unix-domain socket locations, permission problems, or stale
4474 postmaster lock files.
4480 Add <literal>promote</> option to <application>pg_ctl</> to
4481 switch a standby server to primary (Fujii Masao)
4492 <title><application>Development Tools</></title>
4495 <title><link linkend="libpq"><application>libpq</></link></title>
4501 Add a libpq connection option <link
4502 linkend="libpq-connect-client-encoding"><literal>client_encoding</></link>
4503 which behaves like the <envar>PGCLIENTENCODING</> environment
4504 variable (Heikki Linnakangas)
4508 The value <literal>auto</> sets the client encoding based on
4509 the operating system locale.
4516 linkend="libpq-pqlibversion"><function>PQlibVersion()</></link>
4517 function which returns the libpq library version (Magnus
4522 libpq already had <function>PQserverVersion()</> which returns
4529 Allow libpq-using clients to
4530 check the user name of the server process
4531 when connecting via Unix-domain sockets, with the new <link
4532 linkend="libpq-connect-requirepeer"><literal>requirepeer</></link>
4538 <productname>PostgreSQL</> already allowed servers to check
4539 the client user name when connecting via Unix-domain sockets.
4545 Add <link linkend="libpq-pqping"><function>PQping()</></link>
4547 linkend="libpq-pqpingparams"><function>PQpingParams()</></link>
4548 to libpq (Bruce Momjian, Tom Lane)
4552 These functions allow detection of the server's status without
4553 trying to open a new session.
4562 <title><link linkend="ecpg"><application>ECPG</></link></title>
4568 Allow ECPG to accept dynamic cursor names even in
4569 <literal>WHERE CURRENT OF</literal> clauses
4570 (Zoltan Boszormenyi)
4576 Make <application>ecpglib</> write <type>double</> values with a
4577 precision of 15 digits, not 14 as formerly (Akira Kurosawa)
4587 <title>Build Options</title>
4593 Use <literal>+Olibmerrno</> compile flag with HP-UX C compilers
4594 that accept it (Ibrar Ahmed)
4598 This avoids possible misbehavior of math library calls on recent
4606 <title>Makefiles</title>
4612 Improved parallel make support (Peter Eisentraut)
4616 This allows for faster compiles. Also, <literal>make -k</>
4617 now works more consistently.
4623 Require <acronym>GNU</> <link
4624 linkend="install-requirements"><application>make</></link>
4625 3.80 or newer (Peter Eisentraut)
4629 This is necessary because of the parallel-make improvements.
4635 Add <literal>make maintainer-check</> target
4640 This target performs various source code checks that are not
4641 appropriate for either the build or the regression tests. Currently:
4642 duplicate_oids, SGML syntax and tabs check, NLS syntax check.
4648 Support <literal>make check</> in <filename>contrib</>
4653 Formerly only <literal>make installcheck</> worked, but now
4654 there is support for testing in a temporary installation.
4655 The top-level <literal>make check-world</> target now includes
4656 testing <filename>contrib</> this way.
4665 <title>Windows</title>
4671 On Windows, allow <link
4672 linkend="app-pg-ctl"><application>pg_ctl</></link> to register
4673 the service as auto-start or start-on-demand (Quan Zongliang)
4679 Add support for collecting <link linkend="windows-crash-dumps">crash
4680 dumps</link> on Windows (Craig Ringer, Magnus Hagander)
4684 <productname>minidumps</> can now be generated by non-debug
4685 Windows binaries and analyzed by standard debugging tools.
4691 Enable building with the MinGW64 compiler (Andrew Dunstan)
4695 This allows building 64-bit Windows binaries even on non-Windows
4696 platforms via cross-compiling.
4707 <title>Source Code</title>
4713 Revise the API for GUC variable assign hooks (Tom Lane)
4717 The previous functions of assign hooks are now split between check
4718 hooks and assign hooks, where the former can fail but the latter
4719 shouldn't. This change will impact add-on modules that define custom
4726 Add latches to the source code to support waiting for events (Heikki
4733 Centralize data modification permissions-checking logic
4740 Add missing <function>get_<replaceable>object</>_oid()</function> functions, for consistency
4747 Improve ability to use C++ compilers for <link
4748 linkend="xfunc-c">compiling add-on modules</link> by removing
4749 conflicting key words (Tom Lane)
4755 Add support for DragonFly <acronym>BSD</> (Rumko)
4761 Expose <function>quote_literal_cstr()</> for backend use
4768 Run <link linkend="build">regression tests</link> in the
4769 default encoding (Peter Eisentraut)
4773 Regression tests were previously always run with
4774 <literal>SQL_ASCII</> encoding.
4780 Add <application>src/tools/git_changelog</> to replace
4781 <application>cvs2cl</> and <application>pgcvslog</> (Robert
4788 Add <application>git-external-diff</> script to
4789 <filename>src/tools</> (Bruce Momjian)
4793 This is used to generate context diffs from git.
4799 Improve support for building with
4800 <application>Clang</application> (Peter Eisentraut)
4807 <title>Server Hooks</title>
4813 Add source code hooks to check permissions (Robert Haas,
4820 Add post-object-creation function hooks for use by security
4821 frameworks (KaiGai Kohei)
4827 Add a client authentication hook (KaiGai Kohei)
4838 <title>Contrib</title>
4844 Modify <filename>contrib</> modules and procedural
4845 languages to install via the new <link
4846 linkend="extend-extensions">extension</link> mechanism (Tom Lane,
4853 Add <link linkend="file-fdw"><filename>contrib/file_fdw</></link>
4854 foreign-data wrapper (Shigeru Hanada)
4858 Foreign tables using this foreign data wrapper can read flat files
4859 in a manner very similar to <command>COPY</>.
4865 Add nearest-neighbor search support to <link
4866 linkend="pgtrgm"><filename>contrib/pg_trgm</></link> and <link
4867 linkend="btree-gist"><filename>contrib/btree_gist</></link>
4875 linkend="btree-gist"><filename>contrib/btree_gist</></link>
4876 support for searching on not-equals (Jeff Davis)
4883 linkend="fuzzystrmatch"><filename>contrib/fuzzystrmatch</></link>'s
4884 <function>levenshtein()</> function to handle multibyte characters
4885 (Alexander Korotkov)
4891 Add <function>ssl_cipher()</> and <function>ssl_version()</>
4893 linkend="sslinfo"><filename>contrib/sslinfo</></link> (Robert
4900 Fix <link linkend="intarray"><filename>contrib/intarray</></link>
4901 and <link linkend="hstore"><filename>contrib/hstore</></link>
4902 to give consistent results with indexed empty arrays (Tom Lane)
4906 Previously an empty-array query that used an index might return
4907 different results from one that used a sequential scan.
4913 Allow <link linkend="intarray"><filename>contrib/intarray</></link>
4914 to work properly on multidimensional arrays (Tom Lane)
4921 <link linkend="intarray"><filename>contrib/intarray</></link>,
4922 avoid errors complaining about the presence of nulls in cases where no
4923 nulls are actually present (Tom Lane)
4930 <link linkend="intarray"><filename>contrib/intarray</></link>,
4931 fix behavior of containment operators with respect to empty arrays
4936 Empty arrays are now correctly considered to be contained in any other
4943 Remove <link linkend="xml2"><filename>contrib/xml2</></link>'s
4944 arbitrary limit on the number of
4945 <replaceable>parameter</>=<replaceable>value</> pairs that can be
4946 handled by <function>xslt_process()</> (Pavel Stehule)
4950 The previous limit was 10.
4956 In <link linkend="pageinspect"><filename>contrib/pageinspect</></link>,
4957 fix heap_page_item to return infomasks as 32-bit values (Alvaro Herrera)
4961 This avoids returning negative values, which was confusing. The
4962 underlying value is a 16-bit unsigned integer.
4969 <title>Security</title>
4975 Add <link linkend="sepgsql"><filename>contrib/sepgsql</></link>
4976 to interface permission checks with <acronym>SELinux</> (KaiGai Kohei)
4980 This uses the new <link
4981 linkend="SQL-SECURITY-LABEL"><command>SECURITY LABEL</></link>
4988 Add contrib module <link
4989 linkend="auth-delay"><filename>auth_delay</></link> (KaiGai
4994 This causes the server to pause before returning authentication
4995 failure; it is designed to make brute force password attacks
5002 Add <link linkend="dummy-seclabel"><filename>dummy_seclabel</></link>
5003 contrib module (KaiGai Kohei)
5007 This is used for permission regression testing.
5016 <title>Performance</title>
5022 Add support for <literal>LIKE</> and <literal>ILIKE</> index
5024 linkend="pgtrgm"><filename>contrib/pg_trgm</></link> (Alexander
5031 Add <function>levenshtein_less_equal()</> function to <link
5032 linkend="fuzzystrmatch"><filename>contrib/fuzzystrmatch</></link>,
5033 which is optimized for small distances (Alexander Korotkov)
5039 Improve performance of index lookups on <link
5040 linkend="seg"><filename>contrib/seg</></link> columns (Alexander
5047 Improve performance of <link
5048 linkend="pgupgrade"><application>pg_upgrade</></link> for
5049 databases with many relations (Bruce Momjian)
5056 linkend="pgbench"><filename>contrib/pgbench</></link> to
5057 report per-statement latencies (Florian Pflug)
5066 <title>Fsync Testing</title>
5072 Move <filename>src/tools/test_fsync</> to <link
5073 linkend="pgtestfsync"><filename>contrib/pg_test_fsync</></link>
5074 (Bruce Momjian, Tom Lane)
5080 Add <literal>O_DIRECT</> support to <link
5081 linkend="pgtestfsync"><filename>contrib/pg_test_fsync</></link>
5086 This matches the use of <literal>O_DIRECT</> by <link
5087 linkend="guc-wal-sync-method"><varname>wal_sync_method</></link>.
5093 Add new tests to <link
5094 linkend="pgtestfsync"><filename>contrib/pg_test_fsync</></link>
5106 <title>Documentation</title>
5112 Extensive <link linkend="ecpg"><application>ECPG</></link>
5113 documentation improvements (Satoshi Nagayasu)
5119 Extensive proofreading and documentation improvements
5120 (Thom Brown, Josh Kupershmidt, Susanne Ebrecht)
5126 Add documentation for <link
5127 linkend="guc-exit-on-error"><varname>exit_on_error</></link>
5132 This parameter causes sessions to exit on any error.
5138 Add documentation for <link
5139 linkend="functions-info-catalog-table"><function>pg_options_to_table()</></link>
5144 This function shows table storage options in a readable form.
5150 Document that it is possible to access all composite type
5152 linkend="field-selection"><literal>(compositeval).*</></link>
5153 syntax (Peter Eisentraut)
5160 linkend="functions-string-other"><function>translate()</></link>
5161 removes characters in <literal>from</> that don't have a
5162 corresponding <literal>to</> character (Josh Kupershmidt)
5168 Merge documentation for <command>CREATE CONSTRAINT TRIGGER</> and <link
5169 linkend="SQL-CREATETRIGGER"><command>CREATE TRIGGER</></link>
5176 Centralize <link linkend="ddl-priv">permission</link> and <link
5177 linkend="upgrading">upgrade</link> documentation (Bruce Momjian)
5183 Add <link linkend="sysvipc-parameters">kernel tuning
5184 documentation</link> for Solaris 10 (Josh Berkus)
5188 Previously only Solaris 9 kernel tuning was documented.
5194 Handle non-ASCII characters consistently in <filename>HISTORY</> file
5199 While the <filename>HISTORY</> file is in English, we do have to deal
5200 with non-ASCII letters in contributor names. These are now
5201 transliterated so that they are reasonably legible without assumptions
5202 about character set.