1 <!-- $PostgreSQL: pgsql/doc/src/sgml/release-8.5.sgml,v 1.10 2009/12/18 22:11:09 petere Exp $ -->
3 <sect1 id="release-8-5">
4 <title>Release 8.5alpha3</title>
6 <title>Overview</title>
8 PostgreSQL alpha releases are snapshots of development code. They
9 are intended to preview and test upcoming features and to provide
10 the possibility for early feedback. They should not be used in
11 production installations or active development projects. While the
12 PostgreSQL code is continuously subject to a number of automated
13 and manual tests, alpha releases might have serious bugs. Also
14 features may be changed incompatibly or removed at any time during
15 the development cycle.
18 The development cycle of a PostgreSQL major release alternates
19 between periods of development and periods of integration work,
20 called commit fests, normally one month each. Alpha releases are
21 planned to be produced at the end of every commit fest, thus every
22 two months. Since the first commit fest starts within a month from
23 the beginning of development altogether, early alpha releases are
24 not indicative of the likely feature set of the final release.
27 The release notes below highlight user visible changes and new
28 features. There are normally numerous bug fixes and performance
29 improvements in every new snapshot of PostgreSQL, and it would be
30 too bulky to attempt to list them all. Note that many bug fixes are
31 also backported to stable releases of PostgreSQL, and you should be
32 using those if you are looking for bug-fix-only upgrades for your
33 current installations.
36 The release notes are cumulative over all alpha releases. Items
37 that are new in the latest alpha release
38 are <emphasis>emphasized</emphasis>.
42 <title>Migration</title>
44 To upgrade from any release to an alpha release or from an alpha
45 release to any other release will most likely require a
46 dump/restore upgrade procedure. It may happen that this is not
47 necessary in particular cases, but that is not verified beforehand.
48 (The server will warn you in any case when a dump/restore is
49 necessary if you attempt to use it with an old data directory.)
50 Note, however, that the dump/restore upgrade procedure is expected
51 to work for alpha releases, and problems in this area should be
56 <title>Testing</title>
58 The primary reason we release alphas is to get users to test new
59 features as early as possible. If you are interested in helping
60 with organized testing, please see
61 <ulink url="http://wiki.postgresql.org/wiki/HowToBetaTest">the
62 testing information page</ulink>.
66 <title>Changes</title>
68 <title>SQL Features</title>
72 <emphasis>Remove previously deprecated configuration
73 parameter add_missing_from. The system now always behaves as
74 though add_missing_from were off.</emphasis>
79 <emphasis>Performance and behavioral improvements in UPDATE,
80 DELETE, and SELECT FOR UPDATE/SHARE queries with
81 joins. Various corner-cases could result in duplicated output
82 rows. Set-returning functions are now prohibited in the
83 target list of SELECT FOR UPDATE/SHARE. FOR UPDATE does not
84 propagate into a WITH query anymore.</emphasis>
89 <emphasis>Fixed for SELECT FOR UPDATE/SHARE in conjuction with LIMIT.
90 Previously, it could return fewer rows than the limit specified.</emphasis>
95 <emphasis>Add a WHEN clause to CREATE TRIGGER, allowing a boolean expression
96 to be checked to determine whether the trigger should be fired.</emphasis>
99 <emphasis>For BEFORE triggers this is mostly a matter of spec compliance; but
100 for AFTER triggers it can provide a noticeable performance
101 improvement, since queuing of a deferred trigger event and
102 re-fetching of the row(s) at end of statement can be
103 short-circuited if the trigger does not need to be fired.</emphasis>
108 <emphasis>Add exclusion constraints, which generalize the concept of
109 uniqueness to support any indexable commutative operator, not just
110 equality. Two rows violate the exclusion constraint if
111 "row1.col OP row2.col" is TRUE for each of the columns in
112 the constraint.</emphasis>
117 <emphasis>Support ORDER BY within aggregate function calls, at long last
118 providing a non-kluge method for controlling the order in which
119 values are fed to an aggregate function. At the same time eliminate
120 the old implementation restriction that DISTINCT was only supported
121 for single-argument aggregates.</emphasis>
124 <emphasis>Behavioral change: formerly, agg(DISTINCT x) dropped null values of
125 x unconditionally. Now, it does so only if the agg transition
126 function is strict; otherwise nulls are treated as DISTINCT
127 normally would, i.e., you get one copy.</emphasis>
132 Add SQL-compliant triggers on columns, which fire only if
133 certain columns are named in the UPDATE's SET list.
138 Add CREATE LIKE INCLUDING COMMENTS and STORAGE, and INCLUDING
139 ALL shortcut to allow users to make an exact copy of a table including
140 all options and features.
145 Define a new, more extensible syntax for COPY options
146 in order to support additional COPY options in the future.
151 Modify the definition of window-function PARTITION
152 BY and ORDER BY clauses so that their elements are always
153 taken as simple expressions over the query's input
154 columns. This fixes a bug.
159 Fix bug with nested WITH RECURSIVE statements.
164 Add surrogate pair support for U& string and identifier
170 Add Unicode escapes in E'...' strings.
175 DROP COLUMN and DROP CONSTRAINT now support an IF EXISTS clause so
176 that users can avoid fatal errors when running repeatable scripts.
181 UNIQUE constraints can now be DEFERRABLE. This is primarily useful
182 for incremental updates of numerical keys, e.g. "ID = ID +
188 Allow parentheses around the query expression that follows a WITH
194 INFORMATION_SCHEMA, a catalog of standard views of database
195 objects, has been updated to the SQL:2008 standard.
200 Change character_octet_length to more sensible values in
206 Allow * as parameter for FORCE QUOTE for COPY CSV, so that all
207 columns will be quoted.
213 <title>Performance</title>
217 <emphasis>Allow rewriting ALTER TABLE to skip WAL logging.</emphasis>
222 <emphasis>Speed up information schema privilege views.</emphasis>
227 Make TRUNCATE do truncate-in-place when processing
228 a relation that was created or previously truncated in the
229 current (sub)transaction.
234 Implement "join removal" for cases where the inner side
235 of a left join is unique and is not referenced above the join. This should
236 speed up many ORM-generated and reporting tool queries.
241 Remove the use of the flat files pg_auth and
242 pg_database in order to improve performance. (Warning: pgbouncer
243 and possibly other tools currently suggest referring to the pg_auth
244 file for its user database. Such schemes will no longer work.)
249 EXPLAIN allows output of plans in XML, JSON, or YAML format for automated
250 processing of explain plans by analysis or visualization tools.
255 EXPLAIN now supports the use of generic options in EXPLAIN ( option
256 value, ... ) format, which permits the creation of additional
262 ALTER TABLE ... ALTER COLUMN ... SET STATISTICS DISTINCT allows
263 users to manually tweak the number of distinct values estimated for
264 a column, to fix cases where ANALYZE estimates are incorrect.
269 Make GEQO's planning deterministic by having it start from a
270 predictable random number seed each time.
275 Rewrite GEQO's gimme_tree function so that it always finds a legal
276 join sequence. Previously, it could have failed to produce a plan
282 Tweak TOAST code so that columns marked with MAIN storage strategy
283 are not forced out-of-line unless that is necessary to make the row
284 fit on a page. Previously, they were forced out-of-line if needed
285 to get the row down to the default target size (1/4th page).
290 Simplify the forms foo <> true and foo <> false to foo
291 = false and foo = true during query optimization.
296 Avoid per-send() system calls to manage SIGPIPE in libpq, if the
297 platform provides either sockopt(SO_NOSIGPIPE) or the MSG_NOSIGNAL
304 <title>Administration and Monitoring</title>
308 <emphasis>Provide a parenthesized-options syntax for VACUUM, analogous to
309 that recently adopted for EXPLAIN. This will allow additional
310 options to be implemented in future without having to make them
311 fully-reserved keywords. The old syntax remains available for
312 existing options, however.</emphasis>
317 <emphasis>Add support for an application_name parameter, which is displayed
318 in pg_stat_activity and recorded in log entries.</emphasis>
323 <emphasis>Fix longstanding problems in VACUUM caused by untimely
324 interruptions.</emphasis>
329 <emphasis>Error when a specified connection service is not found in
330 pg_service.conf, instead of ignoring it.</emphasis>
335 <emphasis>Add YAML to list of EXPLAIN formats.</emphasis>
340 <emphasis>Add an EXPLAIN (BUFFERS) option to show buffer-usage statistics.</emphasis>
345 <emphasis>Add a hook to let loadable modules get control at ProcessUtility
346 execution, and use it to extend contrib/pg_stat_statements to track
347 utility commands.</emphasis>
352 Add a Boolean server configuration parameter
353 "bonjour" to control whether a Bonjour-enabled
354 build actually attempts to advertise itself via Bonjour.
359 When reloading postgresql.conf, log what parameters actually
365 Make it possibly to specify server configuration parameters
366 per user-database combination. Add a \drds command to psql to
367 display the settings.
372 Allow the collection of statistics on sequences.
377 Increase the maximum value of extra_float_digits to
378 3, and have pg_dump use that value when the backend is new
379 enough to allow it, because it is possible to need 3 extra
380 digits for float4 values (but not for float8 values).
385 Add the ability to include the SQLSTATE error code of any error
386 messages in the PostgreSQL activity log with the new
387 log_line_prefix placeholder %e.
392 Show the exact value being complained of in
393 unique-constraint-violation error messages, including unique-index
400 <title>Security</title>
404 <emphasis>When querying a table with child tables, do not check permissions
405 on the child tables, only on the parent table. The old behavior was
406 found to be useless and confusing in virtually all cases, and also
407 contrary to the SQL standard.</emphasis>
412 <emphasis>Add a hook to CREATE/ALTER ROLE to allow an external module to
413 check the strength of database passwords, and create a sample
414 implementation of such a hook as a new contrib module
415 "passwordcheck".</emphasis>
420 <emphasis>Add large object access control.</emphasis>
425 <emphasis>Allow LDAP authentication to operate in search+bind
426 mode, meaning it does a search for the user in the directory
427 first, and then binds with the DN found for this user. This
428 allows for LDAP logins in scenarios where the DN of the user
429 cannot be determined simply by prefix and suffix, such as the
430 case where different users are located in different
431 containers. The old way of authentication can be
432 significantly faster, so it's kept as an option.</emphasis>
437 Add ALTER DEFAULT PRIVILEGES command, which allows
438 users to adjust the privileges that will be applied to
439 subsequently-created objects.
444 Add GRANT/REVOKE ON ALL TABLES/SEQUENCES/FUNCTIONS IN SCHEMA.
445 This makes it easier to manage permissions on database objects.
450 Support "samehost" and "samenet" specifications
451 in pg_hba.conf. This allows users with dynamic server addresses to run
452 PostgreSQL without frequently modifying pg_hba.conf.
457 New has_sequence_privilege() functions allow you to check sequence
458 privileges for a given ROLE.
464 <title>Built-In Functions</title>
468 <emphasis>Remove configuration parameter regex_flavor. It is now always
469 "advanced".</emphasis>
474 <emphasis>Teach the regular expression functions to do case-insensitive
475 matching and locale-dependent character classification properly
476 when the database encoding is UTF8. This previously only worked
477 correct for single-byte encodings and is still broken for other
478 multibyte encodings.</emphasis>
483 Support POSIX-compatible interpretation of ? as well as {m,n}
484 and related constructs in SIMILAR TO, per SQL:2008.
489 The to_char() formatting functions now supports EEEE (scientific
495 Use floor() not rint() when reducing precision of fractional
496 seconds in timestamp_trunc, timestamptz_trunc, and interval_trunc()
497 for the float-datetime case. This improves accuracy of time
503 Fix ancient bug in handling of to_char() modifier 'TH', when used
510 <title>Data Types</title>
514 <emphasis>When doing "ARRAY[...]::domain", where domain is a domain
515 over an array type, we need to check domain constraints. Regression
516 introduced in 8.4.</emphasis>
521 <emphasis>Fix integer-to-bit-string conversions to handle the first
522 fractional byte correctly when the output bit width is wider than
523 the given integer by something other than a multiple of 8 bits.</emphasis>
528 Fix encoding handling in binary input function of xml type.
533 Tighten binary receive functions so that they reject values
534 that the text input functions don't accept either.
539 New hex-string input and output format options for type bytea. Hex
540 output format is enabled by default, which is an incompatible
541 change. See the new bytea_output parameter if you need to restore
547 Adds prefix support for text search synonym dictionary, allowing
548 creation of synonyms on partial matches.
554 <title>Server Tools</title>
558 <emphasis>initdb now selects the encoding SQL_ASCII when the locale specifies
559 US-ASCII. This case previously failed.</emphasis>
564 <emphasis>Add init[db] option to pg_ctl.</emphasis>
569 It is now reasonably safe to use pg_ctl to start
570 the postmaster from a boot-time script.
575 pg_dump/pg_restore --clean now drops large objects.
580 Modify parallel pg_restore ordering logic to avoid a potential
581 O(N^2) slowdown for some complex databases.
591 <emphasis>In psql, show view definition only with \d+, not with \d.</emphasis>
596 <emphasis>In psql, ignore UTF-8-encoded Unicode byte-order mark at the
597 beginning of a file if the client encoding is UTF-8.</emphasis>
602 <emphasis>Improve psql's tabular display of wrapped-around data by inserting
603 markers in the formerly-always-blank columns just to left and right
604 of the data. "pset linestyle old-ascii" is added to make
605 the previous behavior available if anyone really wants it.</emphasis>
610 <emphasis>Simplify psql's new linestyle behavior to default to
611 linestyle=ascii all the time, rather than hoping we can tell
612 whether the terminal supports UTF-8 characters.</emphasis>
617 Implement significantly saner behavior when two or
618 more psql sessions overlap in their use of the history file.
623 Add "pset linestyle ascii/unicode" option to psql,
624 allowing our traditional ASCII-art style of table output to
625 be upgraded to use Unicode box drawing characters if
631 Have \d show child tables that inherit from the specified parent
636 psql now shows the index methods in \di
642 <title>Procedural Languages</title>
646 <emphasis>PL/pgSQL is installed by default.</emphasis>
651 <emphasis>Remove PL/pgSQL's RENAME declaration, which has
652 bizarre and mostly nonfunctional behavior, and is so little
653 used that no one has been interested in fixing it. To ensure
654 that possible uses are covered, remove the ALIAS declaration's
655 arbitrary restriction that only $n identifiers can be
661 <emphasis>The PL/pgSQL parser and scanner were reworked to behave much more
665 <emphasis>A variable reference will only be recognized where it can validly
666 go, ie, a place where a column value or parameter would be legal,
667 instead of the former behavior that would replace any textual match
668 including table names and column aliases (leading to syntax errors
669 later on). PL/pgSQL variable names that match fully-reserved words
670 will now need to be quoted. An the other hand, a number of
671 PL/pgSQL-specific words were de-reserved.</emphasis>
674 <emphasis>The variable resolution behavior is by default not backward
675 compatible, but can be configured; see documentation.</emphasis>
678 <emphasis>Error reporting is much nicer and accurate.</emphasis>
681 <emphasis>The argument of WHERE CURRENT OF can be a PL/pgSQL cursor variable.</emphasis>
686 <emphasis>Add support for anonymous code blocks (DO blocks) to PL/Perl.</emphasis>
691 <emphasis>Support arrays as parameters and return values of PL/Python
692 functions.</emphasis>
697 <emphasis>Python 3 support in PL/Python; behaves more or less unchanged
698 compared to Python 2, but the new language variant is called
699 plpython3u.</emphasis>
704 Add DO statement to support execution of procedural language
705 code without having to create a function for it.
710 Support use of function argument names to identify which
711 actual arguments match which function parameters. The syntax
712 uses AS, for example funcname(value AS arg1, anothervalue AS
718 Fix/improve bytea and boolean support in PL/Python. Data type
719 conversion into and out of PL/Python previously went through
720 an intermediate string representation, which caused various
721 discrepancies especially with bytea and boolean data. This is
722 now fixed by converting the values directly.
727 PL/Python now accepts Unicode objects where it previously
728 only accepted string objects (for example, as return
729 value). Unicode objects are converted to the PostgreSQL
730 server encoding as necessary.
735 Improve error context reporting in PL/Perl, for
741 PL/pgSQL IN parameters now accept value assignments.
746 Convert a Perl array to a PostgreSQL array when returned by
747 set-returning functions as well as non-SRFs.
752 Allow cursor commands MOVE FORWARD n, MOVE BACKWARD n,
753 MOVE FORWARD ALL, MOVE BACKWARD ALL in PL/pgSQL.
758 PL/pgSQL functions can now better cope with row types
759 containing dropped columns.
764 Improve error context reporting in PL/Python, for easier
770 Greatly expand the regression testing for PL/Python.
776 <title>Additional Supplied Modules</title>
780 <emphasis>Add \shell and \setshell meta commands to pgbench.</emphasis>
783 <emphasis>\shell command runs an external shell command. \setshell also does
784 the same and sets the result to a variable.</emphasis>
789 Multiple improvements in contrib/hstore, including
790 raising limits on keys and values, conversions to and from
791 records and arrays, and support for GROUP BY and DISTINCT.
796 pgbench is now multi-threaded, allowing it to use multiple CPU's
797 for its client connections, and to do more realistic workload
803 Added the ability to retrieve asynchronous notifications using
804 dblink, via the addition of the function dblink_get_notify().
809 Added matchorig, matchsynonyms, and keepsynonyms options to
815 Added contrib/unaccent.
821 <title>Development</title>
825 <emphasis>Refactor ECPG grammar so that it uses the core grammar's
826 unreserved_keyword list, minus a few specific words that have to be
827 treated specially. This de-reserves a number of works in ECPG.</emphasis>
832 <emphasis>Added dynamic cursor names to ECPG.</emphasis>
837 Add ECPG function that returns the current transaction status.
842 Make ECPG more robust against applications freeing strings.
847 Make libpq reject non-numeric and out-of-range port numbers with a
848 suitable error message.
853 Functions which conflict with C++ reserved words have been renamed,
854 making backend header files now safe to use with C++ libraries.
859 Add man pages for SPI functions.
864 ECPG now includes a STRING datatype for Informix compatibility
875 <emphasis>Disable triggering failover with a signal in pg_standby on Windows.
876 It never really worked before anyway.</emphasis>
881 Change the WIN32 API version to be 5.01 (Windows XP), to
882 bring in the proper IPv6 headers in newer SDKs.
887 Write to the Windows eventlog in UTF-16, converting the
888 message encoding as necessary.
893 Replace use of the long-deprecated Bonjour API
894 DNSServiceRegistrationCreate with the not-so-deprecated
895 DNSServiceRegister. The new code will fail on Mac OS X
896 releases before 10.3.
901 Reserve the shared memory region during backend startup on Windows,
902 so that memory allocated by starting third party DLLs doesn't end
903 up conflicting with it. Hopefully this solves the long-time issue
904 with "could not reattach to shared memory" errors on
910 Add s_lock support for SuperH architecture (not well tested).
916 <title>Source Code, Build Options</title>
920 <emphasis>configure --enable-thread-safety is now the default (but can still
921 be turned off).</emphasis>
926 Fix inclusions of readline/editline header files so that we
927 only attempt to #include the version of history.h that is in
928 the same directory as the readline.h we are using. This
929 avoids problems in some scenarios where both readline and
930 editline are installed.
935 Derived files that are shipped in the distribution used to be
936 built in the source directory even for out-of-tree
937 builds. They are now also built in the build tree. This
938 should be more convenient for certain developers' workflows.
943 Translations were updated.
948 Upgrade to Autoconf 2.63 (not relevant to users of distribution
954 Minimum version of Flex is now 2.5.31, to support reentrant
955 scanners (not relevant to users of distribution tarballs).
960 Documentation build rules have been improved. The generated
961 documentation files are now shipped "loose", not in
967 New toolchain to generate man pages. From now on, the man pages
968 will be current in every release.
973 Code-coverage testing support now extends to the entire source
974 tree, not only src/backend/.
979 Make the core scanner re-entrant, along with additional fixes that
980 will let it be used directly by PL/pgSQL.