1 <!-- doc/src/sgml/release-9.1.sgml -->
2 <!-- See header comment in release.sgml about typical markup -->
4 <sect1 id="release-9-1-4">
5 <title>Release 9.1.4</title>
8 <title>Release Date</title>
9 <simpara>2012-06-04</simpara>
13 This release contains a variety of fixes from 9.1.3.
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.4</title>
22 A dump/restore is not required for those running 9.1.X.
26 However, if you use the <type>citext</> data type, and you upgraded
27 from a previous major release by running <application>pg_upgrade</>,
28 you should run <literal>CREATE EXTENSION citext FROM unpackaged</>
29 to avoid collation-related failures in <type>citext</> operations.
30 The same is necessary if you restore a dump from a pre-9.1 database
31 that contains an instance of the <type>citext</> data type.
32 If you've already run the <command>CREATE EXTENSION</> command before
33 upgrading to 9.1.4, you will instead need to do manual catalog updates
34 as explained in the third changelog item below.
38 Also, if you are upgrading from a version earlier than 9.1.2,
39 see the release notes for 9.1.2.
45 <title>Changes</title>
51 Fix incorrect password transformation in
52 <filename>contrib/pgcrypto</>'s DES <function>crypt()</> function
57 If a password string contained the byte value <literal>0x80</>, the
58 remainder of the password was ignored, causing the password to be much
59 weaker than it appeared. With this fix, the rest of the string is
60 properly included in the DES hash. Any stored password values that are
61 affected by this bug will thus no longer match, so the stored values may
62 need to be updated. (CVE-2012-2143)
68 Ignore <literal>SECURITY DEFINER</> and <literal>SET</> attributes for
69 a procedural language's call handler (Tom Lane)
73 Applying such attributes to a call handler could crash the server.
80 Make <filename>contrib/citext</>'s upgrade script fix collations of
81 <type>citext</> arrays and domains over <type>citext</>
86 Release 9.1.2 provided a fix for collations of <type>citext</> columns
87 and indexes in databases upgraded or reloaded from pre-9.1
88 installations, but that fix was incomplete: it neglected to handle arrays
89 and domains over <type>citext</>. This release extends the module's
90 upgrade script to handle these cases. As before, if you have already
91 run the upgrade script, you'll need to run the collation update
92 commands by hand instead. See the 9.1.2 release notes for more
93 information about doing this.
99 Allow numeric timezone offsets in <type>timestamp</> input to be up to
100 16 hours away from UTC (Tom Lane)
104 Some historical time zones have offsets larger than 15 hours, the
105 previous limit. This could result in dumped data values being rejected
112 Fix timestamp conversion to cope when the given time is exactly the
113 last DST transition time for the current timezone (Tom Lane)
117 This oversight has been there a long time, but was not noticed
118 previously because most DST-using zones are presumed to have an
119 indefinite sequence of future DST transitions.
125 Fix <type>text</> to <type>name</> and <type>char</> to <type>name</>
126 casts to perform string truncation correctly in multibyte encodings
133 Fix memory copying bug in <function>to_tsquery()</> (Heikki Linnakangas)
139 Ensure <function>txid_current()</> reports the correct epoch when
140 executed in hot standby (Simon Riggs)
146 Fix planner's handling of outer PlaceHolderVars within subqueries (Tom
151 This bug concerns sub-SELECTs that reference variables coming from the
152 nullable side of an outer join of the surrounding query.
153 In 9.1, queries affected by this bug would fail with <quote>ERROR:
154 Upper-level PlaceHolderVar found where not expected</>. But in 9.0 and
155 8.4, you'd silently get possibly-wrong answers, since the value
156 transmitted into the subquery wouldn't go to null when it should.
162 Fix planning of <literal>UNION ALL</> subqueries with output columns
163 that are not simple variables (Tom Lane)
167 Planning of such cases got noticeably worse in 9.1 as a result of a
168 misguided fix for <quote>MergeAppend child's targetlist doesn't match
169 MergeAppend</> errors. Revert that fix and do it another way.
175 Fix slow session startup when <structname>pg_attribute</> is very large
180 If <structname>pg_attribute</> exceeds one-fourth of
181 <varname>shared_buffers</>, cache rebuilding code that is sometimes
182 needed during session start would trigger the synchronized-scan logic,
183 causing it to take many times longer than normal. The problem was
184 particularly acute if many new sessions were starting at once.
190 Ensure sequential scans check for query cancel reasonably often (Merlin
195 A scan encountering many consecutive pages that contain no live tuples
196 would not respond to interrupts meanwhile.
202 Ensure the Windows implementation of <function>PGSemaphoreLock()</>
203 clears <varname>ImmediateInterruptOK</> before returning (Tom Lane)
207 This oversight meant that a query-cancel interrupt received later
208 in the same query could be accepted at an unsafe time, with
209 unpredictable but not good consequences.
215 Show whole-row variables safely when printing views or rules
216 (Abbas Butt, Tom Lane)
220 Corner cases involving ambiguous names (that is, the name could be
221 either a table or column name of the query) were printed in an
222 ambiguous way, risking that the view or rule would be interpreted
223 differently after dump and reload. Avoid the ambiguous case by
224 attaching a no-op cast.
230 Fix <command>COPY FROM</> to properly handle null marker strings that
231 correspond to invalid encoding (Tom Lane)
235 A null marker string such as <literal>E'\\0'</> should work, and did
236 work in the past, but the case got broken in 8.4.
242 Fix <command>EXPLAIN VERBOSE</> for writable CTEs containing
243 <literal>RETURNING</> clauses (Tom Lane)
249 Fix <command>PREPARE TRANSACTION</> to work correctly in the presence
250 of advisory locks (Tom Lane)
254 Historically, <command>PREPARE TRANSACTION</> has simply ignored any
255 session-level advisory locks the session holds, but this case was
256 accidentally broken in 9.1.
262 Fix truncation of unlogged tables (Robert Haas)
268 Ignore missing schemas during non-interactive assignments of
269 <varname>search_path</> (Tom Lane)
273 This re-aligns 9.1's behavior with that of older branches. Previously
274 9.1 would throw an error for nonexistent schemas mentioned in
275 <varname>search_path</> settings obtained from places such as
276 <command>ALTER DATABASE SET</>.
282 Fix bugs with temporary or transient tables used in extension scripts
287 This includes cases such as a rewriting <command>ALTER TABLE</> within
288 an extension update script, since that uses a transient table behind
295 Ensure autovacuum worker processes perform stack depth checking
296 properly (Heikki Linnakangas)
300 Previously, infinite recursion in a function invoked by
301 auto-<command>ANALYZE</> could crash worker processes.
307 Fix logging collector to not lose log coherency under high load (Andrew
312 The collector previously could fail to reassemble large messages if it
319 Fix logging collector to ensure it will restart file rotation
320 after receiving <systemitem>SIGHUP</> (Tom Lane)
326 Fix <quote>too many LWLocks taken</> failure in GiST indexes (Heikki
333 Fix WAL replay logic for GIN indexes to not fail if the index was
334 subsequently dropped (Tom Lane)
340 Correctly detect SSI conflicts of prepared transactions after a crash
347 Avoid synchronous replication delay when committing a transaction that
348 only modified temporary tables (Heikki Linnakangas)
352 In such a case the transaction's commit record need not be flushed to
353 standby servers, but some of the code didn't know that and waited for
360 Fix error handling in <application>pg_basebackup</>
361 (Thomas Ogrisegg, Fujii Masao)
367 Fix <application>walsender</> to not go into a busy loop if connection
368 is terminated (Fujii Masao)
374 Fix memory leak in PL/pgSQL's <command>RETURN NEXT</> command (Joe
381 Fix PL/pgSQL's <command>GET DIAGNOSTICS</> command when the target
382 is the function's first variable (Tom Lane)
388 Ensure that PL/Perl package-qualifies the <varname>_TD</> variable
393 This bug caused trigger invocations to fail when they are nested
394 within a function invocation that changes the current package.
400 Fix PL/Python functions returning composite types to accept a string
401 for their result value (Jan Urbanski)
405 This case was accidentally broken by the 9.1 additions to allow a
406 composite result value to be supplied in other formats, such as
413 Fix potential access off the end of memory in <application>psql</>'s
414 expanded display (<command>\x</>) mode (Peter Eisentraut)
420 Fix several performance problems in <application>pg_dump</> when
421 the database contains many objects (Jeff Janes, Tom Lane)
425 <application>pg_dump</> could get very slow if the database contained
426 many schemas, or if many objects are in dependency loops, or if there
427 are many owned sequences.
433 Fix memory and file descriptor leaks in <application>pg_restore</>
434 when reading a directory-format archive (Peter Eisentraut)
440 Fix <application>pg_upgrade</> for the case that a database stored in a
441 non-default tablespace contains a table in the cluster's default
442 tablespace (Bruce Momjian)
448 In <application>ecpg</>, fix rare memory leaks and possible overwrite
449 of one byte after the <structname>sqlca_t</> structure (Peter Eisentraut)
455 Fix <filename>contrib/dblink</>'s <function>dblink_exec()</> to not leak
456 temporary database connections upon error (Tom Lane)
462 Fix <filename>contrib/dblink</> to report the correct connection name in
463 error messages (Kyotaro Horiguchi)
469 Fix <filename>contrib/vacuumlo</> to use multiple transactions when
470 dropping many large objects (Tim Lewis, Robert Haas, Tom Lane)
474 This change avoids exceeding <varname>max_locks_per_transaction</> when
475 many objects need to be dropped. The behavior can be adjusted with the
476 new <literal>-l</> (limit) option.
482 Update time zone data files to <application>tzdata</> release 2012c
483 for DST law changes in Antarctica, Armenia, Chile, Cuba, Falkland
484 Islands, Gaza, Haiti, Hebron, Morocco, Syria, and Tokelau Islands;
485 also historical corrections for Canada.
494 <sect1 id="release-9-1-3">
495 <title>Release 9.1.3</title>
498 <title>Release Date</title>
499 <simpara>2012-02-27</simpara>
503 This release contains a variety of fixes from 9.1.2.
504 For information about new features in the 9.1 major release, see
505 <xref linkend="release-9-1">.
509 <title>Migration to Version 9.1.3</title>
512 A dump/restore is not required for those running 9.1.X.
516 However, if you are upgrading from a version earlier than 9.1.2,
517 see the release notes for 9.1.2.
523 <title>Changes</title>
529 Require execute permission on the trigger function for
530 <command>CREATE TRIGGER</> (Robert Haas)
534 This missing check could allow another user to execute a trigger
535 function with forged input data, by installing it on a table he owns.
536 This is only of significance for trigger functions marked
537 <literal>SECURITY DEFINER</>, since otherwise trigger functions run
538 as the table owner anyway. (CVE-2012-0866)
544 Remove arbitrary limitation on length of common name in SSL
545 certificates (Heikki Linnakangas)
549 Both <application>libpq</> and the server truncated the common name
550 extracted from an SSL certificate at 32 bytes. Normally this would
551 cause nothing worse than an unexpected verification failure, but there
552 are some rather-implausible scenarios in which it might allow one
553 certificate holder to impersonate another. The victim would have to
554 have a common name exactly 32 bytes long, and the attacker would have
555 to persuade a trusted CA to issue a certificate in which the common
556 name has that string as a prefix. Impersonating a server would also
557 require some additional exploit to redirect client connections.
564 Convert newlines to spaces in names written in <application>pg_dump</>
565 comments (Robert Haas)
569 <application>pg_dump</> was incautious about sanitizing object names
570 that are emitted within SQL comments in its output script. A name
571 containing a newline would at least render the script syntactically
572 incorrect. Maliciously crafted object names could present a SQL
573 injection risk when the script is reloaded. (CVE-2012-0868)
579 Fix btree index corruption from insertions concurrent with vacuuming
584 An index page split caused by an insertion could sometimes cause a
585 concurrently-running <command>VACUUM</> to miss removing index entries
586 that it should remove. After the corresponding table rows are removed,
587 the dangling index entries would cause errors (such as <quote>could not
588 read block N in file ...</>) or worse, silently wrong query results
589 after unrelated rows are re-inserted at the now-free table locations.
590 This bug has been present since release 8.2, but occurs so infrequently
591 that it was not diagnosed until now. If you have reason to suspect
592 that it has happened in your database, reindexing the affected index
599 Fix transient zeroing of shared buffers during WAL replay (Tom Lane)
603 The replay logic would sometimes zero and refill a shared buffer, so
604 that the contents were transiently invalid. In hot standby mode this
605 can result in a query that's executing in parallel seeing garbage data.
606 Various symptoms could result from that, but the most common one seems
607 to be <quote>invalid memory alloc request size</>.
613 Fix handling of data-modifying <literal>WITH</> subplans in
614 <literal>READ COMMITTED</> rechecking (Tom Lane)
618 A <literal>WITH</> clause containing
619 <command>INSERT</>/<command>UPDATE</>/<command>DELETE</> would crash
620 if the parent <command>UPDATE</> or <command>DELETE</> command needed
621 to be re-evaluated at one or more rows due to concurrent updates
622 in <literal>READ COMMITTED</> mode.
628 Fix corner case in SSI transaction cleanup
633 When finishing up a read-write serializable transaction,
634 a crash could occur if all remaining active serializable transactions
641 Fix postmaster to attempt restart after a hot-standby crash (Tom Lane)
645 A logic error caused the postmaster to terminate, rather than attempt
646 to restart the cluster, if any backend process crashed while operating
653 Fix <command>CLUSTER</>/<command>VACUUM FULL</> handling of toast
654 values owned by recently-updated rows (Tom Lane)
658 This oversight could lead to <quote>duplicate key value violates unique
659 constraint</> errors being reported against the toast table's index
660 during one of these commands.
666 Update per-column permissions, not only per-table permissions, when
667 changing table owner (Tom Lane)
671 Failure to do this meant that any previously granted column permissions
672 were still shown as having been granted by the old owner. This meant
673 that neither the new owner nor a superuser could revoke the
674 now-untraceable-to-table-owner permissions.
680 Support foreign data wrappers and foreign servers in
681 <command>REASSIGN OWNED</> (Alvaro Herrera)
685 This command failed with <quote>unexpected classid</> errors if
686 it needed to change the ownership of any such objects.
692 Allow non-existent values for some settings in <command>ALTER
693 USER/DATABASE SET</> (Heikki Linnakangas)
697 Allow <varname>default_text_search_config</>,
698 <varname>default_tablespace</>, and <varname>temp_tablespaces</> to be
699 set to names that are not known. This is because they might be known
700 in another database where the setting is intended to be used, or for the
701 tablespace cases because the tablespace might not be created yet. The
702 same issue was previously recognized for <varname>search_path</>, and
703 these settings now act like that one.
709 Fix <quote>unsupported node type</> error caused by <literal>COLLATE</>
710 in an <command>INSERT</> expression (Tom Lane)
716 Avoid crashing when we have problems deleting table files post-commit
721 Dropping a table should lead to deleting the underlying disk files only
722 after the transaction commits. In event of failure then (for instance,
723 because of wrong file permissions) the code is supposed to just emit a
724 warning message and go on, since it's too late to abort the
725 transaction. This logic got broken as of release 8.4, causing such
726 situations to result in a PANIC and an unrestartable database.
732 Recover from errors occurring during WAL replay of <command>DROP
733 TABLESPACE</> (Tom Lane)
737 Replay will attempt to remove the tablespace's directories, but there
738 are various reasons why this might fail (for example, incorrect
739 ownership or permissions on those directories). Formerly the replay
740 code would panic, rendering the database unrestartable without manual
741 intervention. It seems better to log the problem and continue, since
742 the only consequence of failure to remove the directories is some
749 Fix race condition in logging AccessExclusiveLocks for hot standby
754 Sometimes a lock would be logged as being held by <quote>transaction
755 zero</>. This is at least known to produce assertion failures on
756 slave servers, and might be the cause of more serious problems.
762 Track the OID counter correctly during WAL replay, even when it wraps
767 Previously the OID counter would remain stuck at a high value until the
768 system exited replay mode. The practical consequences of that are
769 usually nil, but there are scenarios wherein a standby server that's
770 been promoted to master might take a long time to advance the OID
771 counter to a reasonable value once values are needed.
777 Prevent emitting misleading <quote>consistent recovery state reached</>
778 log message at the beginning of crash recovery (Heikki Linnakangas)
785 <structname>pg_stat_replication</>.<structfield>replay_location</>
790 Previously, the value shown would be wrong until at least one WAL
791 record had been replayed.
797 Fix regular expression back-references with <literal>*</> attached
802 Rather than enforcing an exact string match, the code would effectively
803 accept any string that satisfies the pattern sub-expression referenced
804 by the back-reference symbol.
808 A similar problem still afflicts back-references that are embedded in a
809 larger quantified expression, rather than being the immediate subject
810 of the quantifier. This will be addressed in a future
811 <productname>PostgreSQL</> release.
817 Fix recently-introduced memory leak in processing of
818 <type>inet</>/<type>cidr</> values (Heikki Linnakangas)
822 A patch in the December 2011 releases of <productname>PostgreSQL</>
823 caused memory leakage in these operations, which could be significant
824 in scenarios such as building a btree index on such a column.
830 Fix planner's ability to push down index-expression restrictions
831 through <literal>UNION ALL</> (Tom Lane)
835 This type of optimization was inadvertently disabled by a fix for
836 another problem in 9.1.2.
842 Fix planning of <literal>WITH</> clauses referenced in
843 <command>UPDATE</>/<command>DELETE</> on an inherited table
848 This bug led to <quote>could not find plan for CTE</> failures.
854 Fix GIN cost estimation to handle <literal>column IN (...)</>
855 index conditions (Marti Raudsepp)
859 This oversight would usually lead to crashes if such a condition could
860 be used with a GIN index.
866 Prevent assertion failure when exiting a session with an open, failed
867 transaction (Tom Lane)
871 This bug has no impact on normal builds with asserts not enabled.
877 Fix dangling pointer after <command>CREATE TABLE AS</>/<command>SELECT
878 INTO</> in a SQL-language function (Tom Lane)
882 In most cases this only led to an assertion failure in assert-enabled
883 builds, but worse consequences seem possible.
889 Avoid double close of file handle in syslogger on Windows (MauMau)
893 Ordinarily this error was invisible, but it would cause an exception
894 when running on a debug version of Windows.
900 Fix I/O-conversion-related memory leaks in plpgsql
901 (Andres Freund, Jan Urbanski, Tom Lane)
905 Certain operations would leak memory until the end of the current
912 Work around bug in perl's SvPVutf8() function (Andrew Dunstan)
916 This function crashes when handed a typeglob or certain read-only
917 objects such as <literal>$^V</>. Make plperl avoid passing those to
924 In <application>pg_dump</>, don't dump contents of an extension's
925 configuration tables if the extension itself is not being dumped
932 Improve <application>pg_dump</>'s handling of inherited table columns
937 <application>pg_dump</> mishandled situations where a child column has
938 a different default expression than its parent column. If the default
939 is textually identical to the parent's default, but not actually the
940 same (for instance, because of schema search path differences) it would
941 not be recognized as different, so that after dump and restore the
942 child would be allowed to inherit the parent's default. Child columns
943 that are <literal>NOT NULL</> where their parent is not could also be
944 restored subtly incorrectly.
950 Fix <application>pg_restore</>'s direct-to-database mode for
951 INSERT-style table data (Tom Lane)
955 Direct-to-database restores from archive files made with
956 <option>--inserts</> or <option>--column-inserts</> options fail when
957 using <application>pg_restore</> from a release dated September or
958 December 2011, as a result of an oversight in a fix for another
959 problem. The archive file itself is not at fault, and text-mode
966 Teach <application>pg_upgrade</> to handle renaming of
967 <application>plpython</>'s shared library (Bruce Momjian)
971 Upgrading a pre-9.1 database that included plpython would fail because
978 Allow <application>pg_upgrade</> to process tables containing
979 <type>regclass</> columns (Bruce Momjian)
983 Since <application>pg_upgrade</> now takes care to preserve
984 <structname>pg_class</> OIDs, there was no longer any reason for this
991 Make <application>libpq</> ignore <literal>ENOTDIR</> errors
992 when looking for an SSL client certificate file
997 This allows SSL connections to be established, though without a
998 certificate, even when the user's home directory is set to something
999 like <literal>/dev/null</>.
1005 Fix some more field alignment issues in <application>ecpg</>'s SQLDA area
1006 (Zoltan Boszormenyi)
1012 Allow <literal>AT</> option in <application>ecpg</>
1013 <literal>DEALLOCATE</> statements (Michael Meskes)
1017 The infrastructure to support this has been there for awhile, but
1018 through an oversight there was still an error check rejecting the case.
1024 Do not use the variable name when defining a varchar structure in ecpg
1031 Fix <filename>contrib/auto_explain</>'s JSON output mode to produce
1032 valid JSON (Andrew Dunstan)
1036 The output used brackets at the top level, when it should have used
1043 Fix error in <filename>contrib/intarray</>'s <literal>int[] &
1044 int[]</> operator (Guillaume Lelarge)
1048 If the smallest integer the two input arrays have in common is 1,
1049 and there are smaller values in either array, then 1 would be
1050 incorrectly omitted from the result.
1056 Fix error detection in <filename>contrib/pgcrypto</>'s
1057 <function>encrypt_iv()</> and <function>decrypt_iv()</>
1062 These functions failed to report certain types of invalid-input errors,
1063 and would instead return random garbage values for incorrect input.
1069 Fix one-byte buffer overrun in <filename>contrib/test_parser</>
1074 The code would try to read one more byte than it should, which would
1075 crash in corner cases.
1076 Since <filename>contrib/test_parser</> is only example code, this is
1077 not a security issue in itself, but bad example code is still bad.
1083 Use <function>__sync_lock_test_and_set()</> for spinlocks on ARM, if
1084 available (Martin Pitt)
1088 This function replaces our previous use of the <literal>SWPB</>
1089 instruction, which is deprecated and not available on ARMv6 and later.
1090 Reports suggest that the old code doesn't fail in an obvious way on
1091 recent ARM boards, but simply doesn't interlock concurrent accesses,
1092 leading to bizarre failures in multiprocess operation.
1098 Use <option>-fexcess-precision=standard</> option when building with
1099 gcc versions that accept it (Andrew Dunstan)
1103 This prevents assorted scenarios wherein recent versions of gcc will
1104 produce creative results.
1110 Allow use of threaded Python on FreeBSD (Chris Rees)
1114 Our configure script previously believed that this combination wouldn't
1115 work; but FreeBSD fixed the problem, so remove that error check.
1121 Allow MinGW builds to use standardly-named OpenSSL libraries
1131 <sect1 id="release-9-1-2">
1132 <title>Release 9.1.2</title>
1135 <title>Release Date</title>
1136 <simpara>2011-12-05</simpara>
1140 This release contains a variety of fixes from 9.1.1.
1141 For information about new features in the 9.1 major release, see
1142 <xref linkend="release-9-1">.
1146 <title>Migration to Version 9.1.2</title>
1149 A dump/restore is not required for those running 9.1.X.
1153 However, a longstanding error was discovered in the definition of the
1154 <literal>information_schema.referential_constraints</> view. If you
1155 rely on correct results from that view, you should replace its
1156 definition as explained in the first changelog item below.
1160 Also, if you use the <type>citext</> data type, and you upgraded
1161 from a previous major release by running <application>pg_upgrade</>,
1162 you should run <literal>CREATE EXTENSION citext FROM unpackaged</>
1163 to avoid collation-related failures in <type>citext</> operations.
1164 The same is necessary if you restore a dump from a pre-9.1 database
1165 that contains an instance of the <type>citext</> data type.
1166 If you've already run the <command>CREATE EXTENSION</> command before
1167 upgrading to 9.1.2, you will instead need to do manual catalog updates
1168 as explained in the second changelog item.
1174 <title>Changes</title>
1180 Fix bugs in <literal>information_schema.referential_constraints</> view
1185 This view was being insufficiently careful about matching the
1186 foreign-key constraint to the depended-on primary or unique key
1187 constraint. That could result in failure to show a foreign key
1188 constraint at all, or showing it multiple times, or claiming that it
1189 depends on a different constraint than the one it really does.
1193 Since the view definition is installed by <application>initdb</>,
1194 merely upgrading will not fix the problem. If you need to fix this
1195 in an existing installation, you can (as a superuser) drop the
1196 <literal>information_schema</> schema then re-create it by sourcing
1197 <filename><replaceable>SHAREDIR</>/information_schema.sql</filename>.
1198 (Run <literal>pg_config --sharedir</> if you're uncertain where
1199 <replaceable>SHAREDIR</> is.) This must be repeated in each database
1206 Make <filename>contrib/citext</>'s upgrade script fix collations of
1207 <type>citext</> columns and indexes (Tom Lane)
1211 Existing <type>citext</> columns and indexes aren't correctly marked as
1212 being of a collatable data type during <application>pg_upgrade</> from
1213 a pre-9.1 server, or when a pre-9.1 dump containing the <type>citext</>
1214 type is loaded into a 9.1 server.
1215 That leads to operations on these columns failing with errors
1216 such as <quote>could not determine which collation to use for string
1217 comparison</>. This change allows them to be fixed by the same
1218 script that upgrades the <type>citext</> module into a proper 9.1
1219 extension during <literal>CREATE EXTENSION citext FROM unpackaged</>.
1223 If you have a previously-upgraded database that is suffering from this
1224 problem, and you already ran the <command>CREATE EXTENSION</> command,
1225 you can manually run (as superuser) the <command>UPDATE</> commands
1227 <filename><replaceable>SHAREDIR</>/extension/citext--unpackaged--1.0.sql</filename>.
1228 (Run <literal>pg_config --sharedir</> if you're uncertain where
1229 <replaceable>SHAREDIR</> is.)
1230 There is no harm in doing this again if unsure.
1236 Fix possible crash during <command>UPDATE</> or <command>DELETE</> that
1237 joins to the output of a scalar-returning function (Tom Lane)
1241 A crash could only occur if the target row had been concurrently
1242 updated, so this problem surfaced only intermittently.
1248 Fix incorrect replay of WAL records for GIN index updates
1253 This could result in transiently failing to find index entries after
1254 a crash, or on a hot-standby server. The problem would be repaired
1255 by the next <command>VACUUM</> of the index, however.
1261 Fix TOAST-related data corruption during <literal>CREATE TABLE dest AS
1262 SELECT * FROM src</> or <literal>INSERT INTO dest SELECT * FROM src</>
1267 If a table has been modified by <command>ALTER TABLE ADD COLUMN</>,
1268 attempts to copy its data verbatim to another table could produce
1269 corrupt results in certain corner cases.
1270 The problem can only manifest in this precise form in 8.4 and later,
1271 but we patched earlier versions as well in case there are other code
1272 paths that could trigger the same bug.
1278 Fix possible failures during hot standby startup (Simon Riggs)
1284 Start hot standby faster when initial snapshot is incomplete
1291 Fix race condition during toast table access from stale syscache entries
1296 The typical symptom was transient errors like <quote>missing chunk
1297 number 0 for toast value NNNNN in pg_toast_2619</>, where the cited
1298 toast table would always belong to a system catalog.
1304 Track dependencies of functions on items used in parameter default
1305 expressions (Tom Lane)
1309 Previously, a referenced object could be dropped without having dropped
1310 or modified the function, leading to misbehavior when the function was
1311 used. Note that merely installing this update will not fix the missing
1312 dependency entries; to do that, you'd need to <command>CREATE OR
1313 REPLACE</> each such function afterwards. If you have functions whose
1314 defaults depend on non-built-in objects, doing so is recommended.
1320 Fix incorrect management of placeholder variables in nestloop joins
1325 This bug is known to lead to <quote>variable not found in subplan target
1326 list</> planner errors, and could possibly result in wrong query output
1327 when outer joins are involved.
1333 Fix window functions that sort by expressions involving aggregates
1338 Previously these could fail with <quote>could not find pathkey item to
1339 sort</> planner errors.
1345 Fix <quote>MergeAppend child's targetlist doesn't match MergeAppend</>
1346 planner errors (Tom Lane)
1352 Fix index matching for operators with both collatable and noncollatable
1357 In 9.1.0, an indexable operator that has a non-collatable left-hand
1358 input type and a collatable right-hand input type would not be
1359 recognized as matching the left-hand column's index. An example is
1360 the <type>hstore</> <literal>?</> <type>text</> operator.
1366 Allow inlining of set-returning SQL functions with multiple OUT
1367 parameters (Tom Lane)
1373 Don't trust deferred-unique indexes for join removal (Tom Lane and Marti
1378 A deferred uniqueness constraint might not hold intra-transaction,
1379 so assuming that it does could give incorrect query results.
1385 Make <function>DatumGetInetP()</> unpack inet datums that have a 1-byte
1386 header, and add a new macro, <function>DatumGetInetPP()</>, that does
1387 not (Heikki Linnakangas)
1391 This change affects no core code, but might prevent crashes in add-on
1392 code that expects <function>DatumGetInetP()</> to produce an unpacked
1393 datum as per usual convention.
1399 Improve locale support in <type>money</> type's input and output
1404 Aside from not supporting all standard
1405 <link linkend="guc-lc-monetary"><varname>lc_monetary</></link>
1406 formatting options, the input and output functions were inconsistent,
1407 meaning there were locales in which dumped <type>money</> values could
1415 linkend="guc-transform-null-equals"><varname>transform_null_equals</></link>
1416 affect <literal>CASE foo WHEN NULL ...</> constructs
1417 (Heikki Linnakangas)
1421 <varname>transform_null_equals</> is only supposed to affect
1422 <literal>foo = NULL</> expressions written directly by the user, not
1423 equality checks generated internally by this form of <literal>CASE</>.
1429 Change foreign-key trigger creation order to better support
1430 self-referential foreign keys (Tom Lane)
1434 For a cascading foreign key that references its own table, a row update
1435 will fire both the <literal>ON UPDATE</> trigger and the
1436 <literal>CHECK</> trigger as one event. The <literal>ON UPDATE</>
1437 trigger must execute first, else the <literal>CHECK</> will check a
1438 non-final state of the row and possibly throw an inappropriate error.
1439 However, the firing order of these triggers is determined by their
1440 names, which generally sort in creation order since the triggers have
1441 auto-generated names following the convention
1442 <quote>RI_ConstraintTrigger_NNNN</>. A proper fix would require
1443 modifying that convention, which we will do in 9.2, but it seems risky
1444 to change it in existing releases. So this patch just changes the
1445 creation order of the triggers. Users encountering this type of error
1446 should drop and re-create the foreign key constraint to get its
1447 triggers into the right order.
1453 Fix <literal>IF EXISTS</> to work correctly in <command>DROP OPERATOR
1454 FAMILY</> (Robert Haas)
1460 Disallow dropping of an extension from within its own script
1465 This prevents odd behavior in case of incorrect management of extension
1472 Don't mark auto-generated types as extension members (Robert Haas)
1476 Relation rowtypes and automatically-generated array types do not need to
1477 have their own extension membership entries in <structname>pg_depend</>,
1478 and creating such entries complicates matters for extension upgrades.
1484 Cope with invalid pre-existing <varname>search_path</> settings during
1485 <command>CREATE EXTENSION</> (Tom Lane)
1491 Avoid floating-point underflow while tracking buffer allocation rate
1496 While harmless in itself, on certain platforms this would result in
1497 annoying kernel log messages.
1503 Prevent autovacuum transactions from running in serializable mode
1508 Autovacuum formerly used the cluster-wide default transaction isolation
1509 level, but there is no need for it to use anything higher than READ
1510 COMMITTED, and using SERIALIZABLE could result in unnecessary delays
1511 for other processes.
1517 Ensure walsender processes respond promptly to <systemitem>SIGTERM</>
1524 Exclude <filename>postmaster.opts</> from base backups
1531 Preserve configuration file name and line number values when starting
1532 child processes under Windows (Tom Lane)
1536 Formerly, these would not be displayed correctly in the
1537 <structname>pg_settings</> view.
1543 Fix incorrect field alignment in <application>ecpg</>'s SQLDA area
1544 (Zoltan Boszormenyi)
1550 Preserve blank lines within commands in <application>psql</>'s command
1551 history (Robert Haas)
1555 The former behavior could cause problems if an empty line was removed
1556 from within a string literal, for example.
1562 Avoid platform-specific infinite loop in <application>pg_dump</>
1569 Fix compression of plain-text output format in <application>pg_dump</>
1570 (Adrian Klaver and Tom Lane)
1574 <application>pg_dump</> has historically understood <literal>-Z</> with
1575 no <literal>-F</> switch to mean that it should emit a gzip-compressed
1576 version of its plain text output. Restore that behavior.
1582 Fix <application>pg_dump</> to dump user-defined casts between
1583 auto-generated types, such as table rowtypes (Tom Lane)
1589 Fix missed quoting of foreign server names in <application>pg_dump</>
1596 Assorted fixes for <application>pg_upgrade</> (Bruce Momjian)
1600 Handle exclusion constraints correctly, avoid failures on Windows,
1601 don't complain about mismatched toast table names in 8.4 databases.
1607 In PL/pgSQL, allow foreign tables to define row types
1608 (Alexander Soudakov)
1614 Fix up conversions of PL/Perl functions' results
1615 (Alex Hunsaker and Tom Lane)
1619 Restore the pre-9.1 behavior that PL/Perl functions returning
1620 <type>void</> ignore the result value of their last Perl statement;
1621 9.1.0 would throw an error if that statement returned a reference.
1622 Also, make sure it works to return a string value for a composite type,
1623 so long as the string meets the type's input format.
1624 In addition, throw errors for attempts to return Perl arrays or hashes
1625 when the function's declared result type is not an array or composite
1626 type, respectively. (Pre-9.1 versions rather uselessly returned
1627 strings like <literal>ARRAY(0x221a9a0)</> or
1628 <literal>HASH(0x221aa90)</> in such cases.)
1634 Ensure PL/Perl strings are always correctly UTF8-encoded
1635 (Amit Khandekar and Alex Hunsaker)
1641 Use the preferred version of <application>xsubpp</> to build PL/Perl,
1642 not necessarily the operating system's main copy
1643 (David Wheeler and Alex Hunsaker)
1649 Correctly propagate SQLSTATE in PL/Python exceptions
1650 (Mika Eloranta and Jan Urbanski)
1656 Do not install PL/Python extension files for Python major versions
1657 other than the one built against (Peter Eisentraut)
1663 Change all the <filename>contrib</> extension script files to report
1664 a useful error message if they are fed to <application>psql</>
1665 (Andrew Dunstan and Tom Lane)
1669 This should help teach people about the new method of using
1670 <command>CREATE EXTENSION</> to load these files. In most cases,
1671 sourcing the scripts directly would fail anyway, but with
1672 harder-to-interpret messages.
1678 Fix incorrect coding in <filename>contrib/dict_int</> and
1679 <filename>contrib/dict_xsyn</> (Tom Lane)
1683 Some functions incorrectly assumed that memory returned by
1684 <function>palloc()</> is guaranteed zeroed.
1690 Remove <filename>contrib/sepgsql</> tests from the regular regression
1691 test mechanism (Tom Lane)
1695 Since these tests require root privileges for setup, they're impractical
1696 to run automatically. Switch over to a manual approach instead, and
1697 provide a testing script to help with that.
1703 Fix assorted errors in <filename>contrib/unaccent</>'s configuration
1704 file parsing (Tom Lane)
1710 Honor query cancel interrupts promptly in <function>pgstatindex()</>
1717 Fix incorrect quoting of log file name in Mac OS X start script
1724 Revert unintentional enabling of <literal>WAL_DEBUG</> (Robert Haas)
1728 Fortunately, as debugging tools go, this one is pretty cheap;
1729 but it's not intended to be enabled by default, so revert.
1735 Ensure VPATH builds properly install all server header files
1742 Shorten file names reported in verbose error messages (Peter Eisentraut)
1746 Regular builds have always reported just the name of the C file
1747 containing the error message call, but VPATH builds formerly
1748 reported an absolute path name.
1754 Fix interpretation of Windows timezone names for Central America
1759 Map <quote>Central America Standard Time</> to <literal>CST6</>, not
1760 <literal>CST6CDT</>, because DST is generally not observed anywhere in
1767 Update time zone data files to <application>tzdata</> release 2011n
1768 for DST law changes in Brazil, Cuba, Fiji, Palestine, Russia, and Samoa;
1769 also historical corrections for Alaska and British East Africa.
1778 <sect1 id="release-9-1-1">
1779 <title>Release 9.1.1</title>
1782 <title>Release Date</title>
1783 <simpara>2011-09-26</simpara>
1787 This release contains a small number of fixes from 9.1.0.
1788 For information about new features in the 9.1 major release, see
1789 <xref linkend="release-9-1">.
1793 <title>Migration to Version 9.1.1</title>
1796 A dump/restore is not required for those running 9.1.X.
1802 <title>Changes</title>
1808 Make <function>pg_options_to_table</> return NULL for an option with no
1813 Previously such cases would result in a server crash.
1819 Fix memory leak at end of a GiST index scan (Tom Lane)
1823 Commands that perform many separate GiST index scans, such as
1824 verification of a new GiST-based exclusion constraint on a table
1825 already containing many rows, could transiently require large amounts of
1826 memory due to this leak.
1832 Fix explicit reference to <literal>pg_temp</> schema in <command>CREATE
1833 TEMPORARY TABLE</> (Robert Haas)
1837 This used to be allowed, but failed in 9.1.0.
1846 <sect1 id="release-9-1">
1847 <title>Release 9.1</title>
1850 <title>Release Date</title>
1851 <simpara>2011-09-12</simpara>
1855 <title>Overview</title>
1858 This release shows <productname>PostgreSQL</> moving beyond the
1859 traditional relational-database feature set with new, ground-breaking
1860 functionality that is unique to <productname>PostgreSQL</>.
1861 The streaming replication feature introduced in release 9.0 is
1862 significantly enhanced by adding a synchronous-replication option,
1863 streaming backups, and monitoring improvements.
1864 Major enhancements include:
1869 <!-- This list duplicates items below, but without authors or details-->
1873 Allow <link linkend="synchronous-replication">synchronous
1880 Add support for <link linkend="SQL-CREATEFOREIGNTABLE">foreign
1887 Add per-column <link
1888 linkend="collation">collation</link> support
1894 Add <link linkend="extend-extensions">extensions</link> which
1895 simplify packaging of additions to <productname>PostgreSQL</>
1902 linkend="xact-serializable">serializable isolation level</link>
1908 Support unlogged tables using the <literal>UNLOGGED</>
1909 option in <link linkend="SQL-CREATETABLE"><command>CREATE
1916 Allow data-modification commands
1917 (<command>INSERT</>/<command>UPDATE</>/<command>DELETE</>) in
1918 <link linkend="queries-with"><literal>WITH</></link> clauses
1924 Add nearest-neighbor (order-by-operator) searching to <link
1925 linkend="GiST"><acronym>GiST</> indexes</link>
1931 Add a <link linkend="SQL-SECURITY-LABEL"><command>SECURITY
1932 LABEL</></link> command and support for
1933 <link linkend="sepgsql"><acronym>SELinux</> permissions control</link>
1939 Update the <link linkend="plpython">PL/Python</link> server-side
1947 The above items are explained in more detail in the sections below.
1954 <title>Migration to Version 9.1</title>
1957 A dump/restore using <application>pg_dump</application>,
1958 or use of <application>pg_upgrade</application>, is required
1959 for those wishing to migrate data from any previous
1964 Version 9.1 contains a number of changes that may affect compatibility
1965 with previous releases. Observe the following incompatibilities:
1969 <title>Strings</title>
1975 Change the default value of <link
1976 linkend="guc-standard-conforming-strings"><varname>standard_conforming_strings</></link>
1981 By default, backslashes are now ordinary characters in string literals,
1982 not escape characters. This change removes a long-standing
1983 incompatibility with the SQL standard. <link
1984 linkend="guc-escape-string-warning"><varname>escape_string_warning</></link>
1985 has produced warnings about this usage for years. <literal>E''</>
1986 strings are the proper way to embed backslash escapes in strings and are
1987 unaffected by this change.
1992 This change can break applications that are not expecting it and
1993 do their own string escaping according to the old rules. The
1994 consequences could be as severe as introducing SQL-injection security
1995 holes. Be sure to test applications that are exposed to untrusted
1996 input, to ensure that they correctly handle single quotes and
1997 backslashes in text strings.
2007 <title>Casting</title>
2013 Disallow function-style and attribute-style data type casts for
2014 composite types (Tom Lane)
2018 For example, disallow
2019 <literal><replaceable>composite_value</>.text</literal> and
2020 <literal>text(<replaceable>composite_value</>)</literal>.
2021 Unintentional uses of this syntax have frequently resulted in bug
2022 reports; although it was not a bug, it seems better to go back to
2023 rejecting such expressions.
2024 The <literal>CAST</> and <literal>::</> syntaxes are still available
2025 for use when a cast of an entire composite value is actually intended.
2031 Tighten casting checks for domains based on arrays (Tom Lane)
2035 When a domain is based on an array type, it is allowed to <quote>look
2036 through</> the domain type to access the array elements, including
2037 subscripting the domain value to fetch or assign an element.
2038 Assignment to an element of such a domain value, for instance via
2039 <literal>UPDATE ... SET domaincol[5] = ...</>, will now result in
2040 rechecking the domain type's constraints, whereas before the checks
2050 <title>Arrays</title>
2057 linkend="array-functions-table"><function>string_to_array()</></link>
2058 to return an empty array for a zero-length string (Pavel
2063 Previously this returned a null value.
2070 linkend="array-functions-table"><function>string_to_array()</></link>
2071 so a <literal>NULL</> separator splits the string into characters
2076 Previously this returned a null value.
2085 <title>Object Modification</title>
2091 Fix improper checks for before/after triggers (Tom Lane)
2095 Triggers can now be fired in three cases: <literal>BEFORE</>,
2096 <literal>AFTER</>, or <literal>INSTEAD OF</> some action.
2097 Trigger function authors should verify that their logic behaves
2098 sanely in all three cases.
2104 Require superuser or <literal>CREATEROLE</> permissions in order to
2105 set comments on roles (Tom Lane)
2114 <title>Server Settings</title>
2121 linkend="functions-recovery-info-table"><function>pg_last_xlog_receive_location()</></link>
2122 so it never moves backwards (Fujii Masao)
2126 Previously, the value of <function>pg_last_xlog_receive_location()</>
2127 could move backward when streaming replication is restarted.
2133 Have logging of replication connections honor <link
2134 linkend="guc-log-connections"><varname>log_connections</></link>
2139 Previously, replication connections were always logged.
2148 <title><link linkend="plpgsql">PL/pgSQL</link> Server-Side Language</title>
2154 Change PL/pgSQL's <literal>RAISE</> command without parameters
2155 to be catchable by the attached exception block (Piyush Newe)
2159 Previously <literal>RAISE</> in a code block was always scoped to
2160 an attached exception block, so it was uncatchable at the same
2167 Adjust PL/pgSQL's error line numbering code to be consistent
2168 with other PLs (Pavel Stehule)
2172 Previously, PL/pgSQL would ignore (not count) an empty line at the
2173 start of the function body. Since this was inconsistent with all
2174 other languages, the special case was removed.
2180 Make PL/pgSQL complain about conflicting IN and OUT parameter names
2185 Formerly, the collision was not detected, and the name would just
2186 silently refer to only the OUT parameter.
2192 Type modifiers of PL/pgSQL variables are now visible to the SQL parser
2197 A type modifier (such as a varchar length limit) attached to a PL/pgSQL
2198 variable was formerly enforced during assignments, but was ignored for
2199 all other purposes. Such variables will now behave more like table
2200 columns declared with the same modifier. This is not expected to make
2201 any visible difference in most cases, but it could result in subtle
2202 changes for some SQL commands issued by PL/pgSQL functions.
2211 <title>Contrib</title>
2217 All contrib modules are now installed with <link
2218 linkend="SQL-CREATEEXTENSION"><command>CREATE EXTENSION</></link>
2219 rather than by manually invoking their SQL scripts
2220 (Dimitri Fontaine, Tom Lane)
2224 To update an existing database containing the 9.0 version of a contrib
2225 module, use <literal>CREATE EXTENSION ... FROM unpackaged</literal>
2226 to wrap the existing contrib module's objects into an extension. When
2227 updating from a pre-9.0 version, drop the contrib module's objects
2228 using its old uninstall script, then use <literal>CREATE EXTENSION</>.
2237 <title>Other Incompatibilities</title>
2244 linkend="monitoring-stats-funcs-table"><function>pg_stat_reset()</></link>
2245 reset all database-level statistics (Tomas Vondra)
2249 Some <structname>pg_stat_database</> counters were not being reset.
2256 linkend="infoschema-triggers"><structname>information_schema.triggers</></link>
2257 column names to match the new SQL-standard names (Dean Rasheed)
2263 Treat <application>ECPG</> cursor names as case-insensitive
2264 (Zoltan Boszormenyi)
2274 <title>Changes</title>
2277 Below you will find a detailed account of the changes between
2278 <productname>PostgreSQL</productname> 9.1 and the previous major
2283 <title>Server</title>
2286 <title>Performance</title>
2292 Support unlogged tables using the <literal>UNLOGGED</>
2293 option in <link linkend="SQL-CREATETABLE"><command>CREATE
2294 TABLE</></link> (Robert Haas)
2298 Such tables provide better update performance than regular tables,
2299 but are not crash-safe: their contents are automatically cleared in
2300 case of a server crash. Their contents do not propagate to
2301 replication slaves, either.
2307 Allow <literal>FULL OUTER JOIN</literal> to be implemented as a
2308 hash join, and allow either side of a <literal>LEFT OUTER JOIN</>
2309 or <literal>RIGHT OUTER JOIN</> to be hashed (Tom Lane)
2313 Previously <literal>FULL OUTER JOIN</literal> could only be
2314 implemented as a merge join, and <literal>LEFT OUTER JOIN</literal>
2315 and <literal>RIGHT OUTER JOIN</literal> could hash only the nullable
2316 side of the join. These changes provide additional query optimization
2323 Merge duplicate fsync requests (Robert Haas, Greg Smith)
2327 This greatly improves performance under heavy write loads.
2333 Improve performance of <link
2334 linkend="guc-commit-siblings"><varname>commit_siblings</></link>
2339 This allows the use of <varname>commit_siblings</varname> with
2346 Reduce the memory requirement for large ispell dictionaries
2347 (Pavel Stehule, Tom Lane)
2353 Avoid leaving data files open after <quote>blind writes</>
2358 This fixes scenarios in which backends might hold files open long
2359 after they were deleted, preventing the kernel from reclaiming
2369 <title>Optimizer</title>
2375 Allow inheritance table scans to return meaningfully-sorted
2376 results (Greg Stark, Hans-Jurgen Schonig, Robert Haas, Tom Lane)
2380 This allows better optimization of queries that use <literal>ORDER
2381 BY</>, <literal>LIMIT</>, or <literal>MIN</>/<literal>MAX</> with
2388 Improve GIN index scan cost estimation (Teodor Sigaev)
2394 Improve cost estimation for aggregates and window functions (Tom Lane)
2403 <title>Authentication</title>
2409 Support host names and host suffixes
2410 (e.g. <literal>.example.com</>) in <link
2411 linkend="auth-pg-hba-conf"><filename>pg_hba.conf</></link>
2416 Previously only host <acronym>IP</> addresses and <acronym>CIDR</>
2417 values were supported.
2423 Support the key word <literal>all</> in the host column of <link
2424 linkend="auth-pg-hba-conf"><filename>pg_hba.conf</></link>
2429 Previously people used <literal>0.0.0.0/0</> or <literal>::/0</>
2436 Reject <literal>local</> lines in <link
2437 linkend="auth-pg-hba-conf"><filename>pg_hba.conf</></link>
2438 on platforms that don't support Unix-socket connections
2443 Formerly, such lines were silently ignored, which could be surprising.
2444 This makes the behavior more like other unsupported cases.
2450 Allow <link linkend="gssapi-auth"><acronym>GSSAPI</></link>
2451 to be used to authenticate to servers via <link
2452 linkend="sspi-auth"><acronym>SSPI</></link> (Christian Ullrich)
2456 Specifically this allows Unix-based <acronym>GSSAPI</> clients
2457 to do <acronym>SSPI</> authentication with Windows servers.
2463 <link linkend="auth-ident"><literal>ident</literal></link>
2464 authentication over local sockets is now known as
2465 <link linkend="auth-peer"><literal>peer</literal></link>
2470 The old term is still accepted for backward compatibility, but since
2471 the two methods are fundamentally different, it seemed better to adopt
2472 different names for them.
2478 Rewrite <link linkend="auth-peer"><acronym>peer</></link>
2479 authentication to avoid use of credential control messages (Tom Lane)
2483 This change makes the peer authentication code simpler and
2484 better-performing. However, it requires the platform to provide the
2485 <function>getpeereid</> function or an equivalent socket operation.
2486 So far as is known, the only platform for which peer authentication
2487 worked before and now will not is pre-5.0 NetBSD.
2496 <title>Monitoring</title>
2502 Add details to the logging of restartpoints and checkpoints,
2503 which is controlled by <link
2504 linkend="guc-log-checkpoints"><varname>log_checkpoints</></link>
2505 (Fujii Masao, Greg Smith)
2509 New details include <acronym>WAL</> file and sync activity.
2516 linkend="guc-log-file-mode"><varname>log_file_mode</></link>
2517 which controls the permissions on log files created by the
2518 logging collector (Martin Pihlak)
2524 Reduce the default maximum line length for <application>syslog</>
2525 logging to 900 bytes plus prefixes (Noah Misch)
2529 This avoids truncation of long log lines on syslog implementations
2530 that have a 1KB length limit, rather than the more common 2KB.
2539 <title>Statistical Views</title>
2545 Add <structfield>client_hostname</structfield> column to <link
2546 linkend="monitoring-stats-views-table"><structname>pg_stat_activity</></link>
2551 Previously only the client address was reported.
2558 linkend="monitoring-stats-views-table"><structname>pg_stat_xact_*</></link>
2559 statistics functions and views (Joel Jacobson)
2563 These are like the database-wide statistics counter views, but
2564 reflect counts for only the current transaction.
2570 Add time of last reset in database-level and background writer
2571 statistics views (Tomas Vondra)
2577 Add columns showing the number of vacuum and analyze operations
2579 linkend="monitoring-stats-views-table"><structname>pg_stat_*_tables</></link>
2580 views (Magnus Hagander)
2586 Add <structfield>buffers_backend_fsync</> column to <link
2587 linkend="monitoring-stats-views-table"><structname>pg_stat_bgwriter</></link>
2592 This new column counts the number of times a backend fsyncs a
2602 <title>Server Settings</title>
2608 Provide auto-tuning of <link
2609 linkend="guc-wal-buffers"><varname>wal_buffers</></link> (Greg
2614 By default, the value of <varname>wal_buffers</> is now chosen
2615 automatically based on the value of <varname>shared_buffers</>.
2621 Increase the maximum values for
2622 <link linkend="guc-deadlock-timeout"><varname>deadlock_timeout</varname></link>,
2623 <link linkend="guc-log-min-duration-statement"><varname>log_min_duration_statement</varname></link>, and
2624 <link linkend="guc-log-autovacuum-min-duration"><varname>log_autovacuum_min_duration</varname></link>
2629 The maximum value for each of these parameters was previously
2630 only about 35 minutes. Much larger values are now allowed.
2641 <title>Replication and Recovery</title>
2644 <title>Streaming Replication and Continuous Archiving</title>
2650 Allow <link linkend="synchronous-replication">synchronous
2651 replication</link> (Simon Riggs, Fujii Masao)
2655 This allows the primary server to wait for a standby to write a
2656 transaction's information to disk before acknowledging the commit.
2657 One standby at a time can take the role of the synchronous standby,
2658 as controlled by the
2659 <link linkend="guc-synchronous-standby-names"><varname>synchronous_standby_names</varname></link>
2660 setting. Synchronous replication can be enabled or disabled on a
2661 per-transaction basis using the
2662 <link linkend="guc-synchronous-commit"><varname>synchronous_commit</></link>
2669 Add protocol support for sending file system backups to standby servers
2670 using the streaming replication network connection (Magnus Hagander,
2675 This avoids the requirement of manually transferring a file
2676 system backup when setting up a standby server.
2683 <link linkend="guc-replication-timeout"><varname>replication_timeout</></link>
2684 setting (Fujii Masao, Heikki Linnakangas)
2688 Replication connections that are idle for more than the
2689 <varname>replication_timeout</> interval will be terminated
2690 automatically. Formerly, a failed connection was typically not
2691 detected until the TCP timeout elapsed, which is inconveniently
2692 long in many situations.
2698 Add command-line tool <link
2699 linkend="app-pgbasebackup"><application>pg_basebackup</></link>
2700 for creating a new standby server or database backup (Magnus
2707 Add a <link linkend="SQL-CREATEROLE">replication permission</link>
2708 for roles (Magnus Hagander)
2712 This is a read-only permission used for streaming replication.
2713 It allows a non-superuser role to be used for replication connections.
2714 Previously only superusers could initiate replication
2715 connections; superusers still have this permission by default.
2724 <title>Replication Monitoring</title>
2730 Add system view <link
2731 linkend="monitoring-stats-views-table"><structname>pg_stat_replication</></link>
2732 which displays activity of <acronym>WAL</> sender processes (Itagaki
2733 Takahiro, Simon Riggs)
2737 This reports the status of all connected standby servers.
2743 Add monitoring function <link
2744 linkend="functions-recovery-info-table"><function>pg_last_xact_replay_timestamp()</></link>
2749 This returns the time at which the primary generated the most
2750 recent commit or abort record applied on the standby.
2759 <title>Hot Standby</title>
2765 Add configuration parameter <link
2766 linkend="guc-hot-standby-feedback"><varname>hot_standby_feedback</></link>
2767 to enable standbys to postpone cleanup of old row versions on the
2768 primary (Simon Riggs)
2772 This helps avoid canceling long-running queries on the standby.
2779 linkend="monitoring-stats-views-table"><structname>pg_stat_database_conflicts</></link>
2780 system view to show queries that have been canceled and the
2781 reason (Magnus Hagander)
2785 Cancellations can occur because of dropped tablespaces, lock
2786 timeouts, old snapshots, pinned buffers, and deadlocks.
2792 Add a <structfield>conflicts</> count to <link
2793 linkend="monitoring-stats-views-table"><structname>pg_stat_database</></link>
2798 This is the number of conflicts that occurred in the database.
2804 Increase the maximum values for
2805 <link linkend="guc-max-standby-archive-delay"><varname>max_standby_archive_delay</varname></link> and
2806 <link linkend="guc-max-standby-streaming-delay"><varname>max_standby_streaming_delay</varname></link>
2810 The maximum value for each of these parameters was previously
2811 only about 35 minutes. Much larger values are now allowed.
2818 linkend="errcodes-table"><literal>ERRCODE_T_R_DATABASE_DROPPED</></link>
2819 error code to report recovery conflicts due to dropped databases
2824 This is useful for connection pooling software.
2833 <title>Recovery Control</title>
2839 Add functions to control streaming replication replay (Simon Riggs)
2843 The new functions are <link
2844 linkend="functions-recovery-control-table"><function>pg_xlog_replay_pause()</></link>,
2846 linkend="functions-recovery-control-table"><function>pg_xlog_replay_resume()</></link>,
2847 and the status function <link
2848 linkend="functions-recovery-control-table"><function>pg_is_xlog_replay_paused()</></link>.
2854 Add <filename>recovery.conf</> setting <link
2855 linkend="pause-at-recovery-target"><varname>pause_at_recovery_target</></link>
2856 to pause recovery at target (Simon Riggs)
2860 This allows a recovery server to be queried to check whether
2861 the recovery point is the one desired.
2867 Add the ability to create named restore points using <link
2868 linkend="functions-admin-backup-table"><function>pg_create_restore_point()</></link>
2873 These named restore points can be specified as recovery
2874 targets using the new <filename>recovery.conf</> setting
2875 <link linkend="recovery-target-name"><varname>recovery_target_name</></link>.
2881 Allow standby recovery to switch to a new timeline automatically
2882 (Heikki Linnakangas)
2886 Now standby servers scan the archive directory for new
2887 timelines periodically.
2894 linkend="guc-restart-after-crash"><varname>restart_after_crash</></link>
2895 setting which disables automatic server restart after a backend
2900 This allows external cluster management software to control
2901 whether the database server restarts or not.
2908 linkend="recovery-config"><filename>recovery.conf</></link>
2909 to use the same quoting behavior as <filename>postgresql.conf</>
2914 Previously all values had to be quoted.
2925 <title>Queries</title>
2932 linkend="xact-serializable">serializable isolation level</link>
2933 (Kevin Grittner, Dan Ports)
2937 Previously, asking for serializable isolation guaranteed only that a
2938 single MVCC snapshot would be used for the entire transaction, which
2939 allowed certain documented anomalies. The old snapshot isolation
2940 behavior is still available by requesting the <link
2941 linkend="xact-repeatable-read"><literal>REPEATABLE READ</></link>
2948 Allow data-modification commands
2949 (<command>INSERT</>/<command>UPDATE</>/<command>DELETE</>) in
2950 <link linkend="queries-with"><literal>WITH</></link> clauses
2951 (Marko Tiikkaja, Hitoshi Harada)
2955 These commands can use <literal>RETURNING</> to pass data up to the
2962 Allow <link linkend="queries-with"><literal>WITH</></link>
2963 clauses to be attached to <command>INSERT</>, <command>UPDATE</>,
2964 <command>DELETE</> statements (Marko Tiikkaja, Hitoshi Harada)
2970 Allow non-<link linkend="queries-group"><literal>GROUP
2971 BY</></link> columns in the query target list when the primary
2972 key is specified in the <literal>GROUP BY</> clause (Peter
2977 The SQL standard allows this behavior, and
2978 because of the primary key, the result is unambiguous.
2984 Allow use of the key word <literal>DISTINCT</> in <link
2985 linkend="queries-union"><literal>UNION</>/<literal>INTERSECT</>/<literal>EXCEPT</></link>
2990 <literal>DISTINCT</> is the default behavior so use of this
2991 key word is redundant, but the SQL standard allows it.
2997 Fix ordinary queries with rules to use the same snapshot behavior
2998 as <command>EXPLAIN ANALYZE</> (Marko Tiikkaja)
3002 Previously <command>EXPLAIN ANALYZE</> used slightly different
3003 snapshot timing for queries involving rules. The
3004 <command>EXPLAIN ANALYZE</> behavior was judged to be more logical.
3011 <title>Strings</title>
3017 Add per-column <link
3018 linkend="collation">collation</link> support
3019 (Peter Eisentraut, Tom Lane)
3023 Previously collation (the sort ordering of text strings) could only be
3024 chosen at database creation.
3025 Collation can now be set per column, domain, index, or
3026 expression, via the SQL-standard <literal>COLLATE</> clause.
3037 <title>Object Manipulation</title>
3043 Add <link linkend="extend-extensions">extensions</link> which
3044 simplify packaging of additions to <productname>PostgreSQL</>
3045 (Dimitri Fontaine, Tom Lane)
3049 Extensions are controlled by the new <link
3050 linkend="SQL-CREATEEXTENSION"><command>CREATE</></link>/<link
3051 linkend="SQL-ALTEREXTENSION"><command>ALTER</></link>/<link
3052 linkend="SQL-DROPEXTENSION"><command>DROP EXTENSION</></link>
3053 commands. This replaces ad-hoc methods of grouping objects that
3054 are added to a <productname>PostgreSQL</> installation.
3060 Add support for <link linkend="SQL-CREATEFOREIGNTABLE">foreign
3061 tables</link> (Shigeru Hanada, Robert Haas, Jan Urbanski,
3066 This allows data stored outside the database to be used like
3067 native <productname>PostgreSQL</>-stored data. Foreign tables
3068 are currently read-only, however.
3074 Allow new values to be added to an existing enum type via
3075 <link linkend="SQL-ALTERTYPE"><command>ALTER TYPE</></link> (Andrew
3082 Add <link linkend="SQL-ALTERTYPE"><command>ALTER TYPE ...
3083 ADD/DROP/ALTER/RENAME ATTRIBUTE</></link> (Peter Eisentraut)
3087 This allows modification of composite types.
3094 <title><command>ALTER</> Object</title>
3100 Add <literal>RESTRICT</>/<literal>CASCADE</> to <link
3101 linkend="SQL-ALTERTYPE"><command>ALTER TYPE</></link> operations
3102 on typed tables (Peter Eisentraut)
3107 <literal>ADD</>/<literal>DROP</>/<literal>ALTER</>/<literal>RENAME
3108 ATTRIBUTE</> cascading behavior.
3114 Support <literal>ALTER TABLE <replaceable>name</> {OF | NOT OF}
3115 <replaceable>type</></literal>
3120 This syntax allows a standalone table to be made into a typed table,
3121 or a typed table to be made standalone.
3127 Add support for more object types in <command>ALTER ... SET
3128 SCHEMA</> commands (Dimitri Fontaine)
3132 This command is now supported for conversions, operators, operator
3133 classes, operator families, text search configurations, text search
3134 dictionaries, text search parsers, and text search templates.
3143 <title><link linkend="SQL-CREATETABLE"><command>CREATE/ALTER TABLE</></link></title>
3149 Add <command>ALTER TABLE ...
3150 ADD UNIQUE/PRIMARY KEY USING INDEX</command>
3155 This allows a primary key or unique constraint to be defined using an
3156 existing unique index, including a concurrently created unique index.
3162 Allow <command>ALTER TABLE</>
3163 to add foreign keys without validation (Simon Riggs)
3167 The new option is called <literal>NOT VALID</>. The constraint's
3168 state can later be modified to <literal>VALIDATED</> and validation
3169 checks performed. Together these allow you to add a foreign key
3170 with minimal impact on read and write operations.
3176 Allow <link linkend="SQL-ALTERTABLE"><command>ALTER TABLE
3177 ... SET DATA TYPE</command></link> to avoid table rewrites in
3178 appropriate cases (Noah Misch, Robert Haas)
3182 For example, converting a <type>varchar</> column to
3183 <type>text</> no longer requires a rewrite of the table.
3184 However, increasing the length constraint on a
3185 <type>varchar</> column still requires a table rewrite.
3191 Add <link linkend="SQL-CREATETABLE"><command>CREATE TABLE IF
3192 NOT EXISTS</></link> syntax (Robert Haas)
3196 This allows table creation without causing an error if the
3197 table already exists.
3203 Fix possible <quote>tuple concurrently updated</quote> error
3204 when two backends attempt to add an inheritance
3205 child to the same table at the same time (Robert Haas)
3209 <link linkend="sql-altertable"><command>ALTER TABLE</command></link>
3210 now takes a stronger lock on the parent table, so that the sessions
3211 cannot try to update it simultaneously.
3220 <title>Object Permissions</title>
3226 Add a <link linkend="SQL-SECURITY-LABEL"><command>SECURITY
3227 LABEL</></link> command (KaiGai Kohei)
3231 This allows security labels to be assigned to objects.
3242 <title>Utility Operations</title>
3248 Add transaction-level <link linkend="advisory-locks">advisory
3249 locks</link> (Marko Tiikkaja)
3253 These are similar to the existing session-level advisory locks,
3254 but such locks are automatically released at transaction end.
3260 Make <link linkend="SQL-TRUNCATE"><command>TRUNCATE ... RESTART
3261 IDENTITY</></link> restart sequences transactionally (Steve
3266 Previously the counter could have been left out of sync if a
3267 backend crashed between the on-commit truncation activity and
3275 <title><link linkend="SQL-COPY"><command>COPY</></link></title>
3281 Add <literal>ENCODING</> option to <link
3282 linkend="SQL-COPY"><command>COPY TO/FROM</></link> (Hitoshi
3283 Harada, Itagaki Takahiro)
3287 This allows the encoding of the <command>COPY</> file to be
3288 specified separately from client encoding.
3294 Add bidirectional <link linkend="SQL-COPY"><command>COPY</></link>
3295 protocol support (Fujii Masao)
3299 This is currently only used by streaming replication.
3308 <title><link linkend="SQL-EXPLAIN"><command>EXPLAIN</></link></title>
3314 Make <command>EXPLAIN VERBOSE</> show the function call expression
3315 in a <literal>FunctionScan</literal> node (Tom Lane)
3324 <title><link linkend="SQL-VACUUM"><command>VACUUM</></link></title>
3330 Add additional details to the output of <link
3331 linkend="SQL-VACUUM"><command>VACUUM FULL VERBOSE</></link>
3332 and <link linkend="SQL-CLUSTER"><command>CLUSTER VERBOSE</></link>
3337 New information includes the live and dead tuple count and
3338 whether <command>CLUSTER</> is using an index to rebuild.
3344 Prevent <link linkend="autovacuum">autovacuum</link> from
3345 waiting if it cannot acquire a table lock (Robert Haas)
3349 It will try to vacuum that table later.
3358 <title><link linkend="SQL-CLUSTER"><command>CLUSTER</></link></title>
3364 Allow <command>CLUSTER</> to sort the table rather than scanning
3365 the index when it seems likely to be cheaper (Leonardo Francalanci)
3374 <title>Indexes</title>
3380 Add nearest-neighbor (order-by-operator) searching to <link
3381 linkend="GiST"><acronym>GiST</> indexes</link> (Teodor Sigaev, Tom Lane)
3385 This allows <acronym>GiST</> indexes to quickly return the
3386 <replaceable>N</> closest values in a query with <literal>LIMIT</>.
3388 <programlisting><
projects / postgresql / blob