129
Appendix E. Release Notes
The SQL standard does not permit an alias in these commands, but many database systems allow
one anywayfor notational convenience.
•
Allow
UPDATE
to set multiple columns with a list of values (Susanne Ebrecht)
This is basically a short-hand for assigning the columns and values in pairs. The syntax is
UPDATE
tab SET (
column
, ...) = (
val
, ...)
.
•
Make row comparisons work per standard (Tom)
The forms <, <=, >, >= now compare rows lexicographically, that is, compare the first elements,
if equal compare the second elements, and so on. Formerly they expanded to an AND condition
across all the elements, which was neither standard nor very useful.
•
Add
CASCADE
option to
TRUNCATE
(Joachim Wieland)
This causes
TRUNCATE
to automatically include all tables that reference the specified table(s) via
foreign keys. While convenient, this is a dangerous tool — use with caution!
•
Support
FOR UPDATE
and
FOR SHARE
in the same
SELECT
command (Tom)
•
Add
IS NOT DISTINCT FROM
(Pavel Stehule)
This operator is similar to equality (
=
), but evaluates to true when both left and right operands are
NULL
,and to false when just one is, rather than yielding
NULL
in these cases.
•
Improve the length output used by
UNION
/
INTERSECT
/
EXCEPT
(Tom)
When all corresponding columns are of the same defined length, that length is used for the result,
rather than a generic length.
•
Allow
ILIKE
to work for multi-byte encodings (Tom)
Internally,
ILIKE
now calls
lower()
and then uses
LIKE
.Locale-specific regular expression pat-
terns still do not workin these encodings.
•
Enable
standard_conforming_strings
to be turned
on
(Kevin Grittner)
This allows backslash escaping in strings to be disabled, making PostgreSQL more standards-
compliant. The default is
off
for backwards compatibility, but future releases will default this to
on
.
•
Do not flatten subqueries that contain
volatile
functions in their target lists (Jaime Casanova)
This prevents surprising behavior due to multiple evaluation of a
volatile
function (such as
random()
or
nextval()
). It might cause performance degradation in the presence of functions
that are unnecessarily marked as
volatile
.
•
Add system views
pg_prepared_statements
and
pg_cursors
to show prepared statements
and open cursors (Joachim Wieland, Neil)
These are very useful in pooled connection setups.
•
Support portal parameters in
EXPLAIN
and
EXECUTE
(Tom)
This allows, for example, JDBC
?
parameters to work in these commands.
•
If SQL-level
PREPARE
parameters are unspecified, infer their types from the content of the query
(Neil)
Protocol-level
PREPARE
already did this.
•
Allow
LIMIT
and
OFFSET
to exceed two billion (Dhanaraj M)
2510
114
Appendix E. Release Notes
E.155.3.4. Object Manipulation Changes
•
Add
TABLESPACE
clause to
CREATE TABLE AS
(Neil)
This allows a tablespace to be specified for the new table.
•
Add
ON COMMIT
clause to
CREATE TABLE AS
(Neil)
This allows temporary tables to be truncated or dropped ontransaction commit. The default behav-
ior is for the table to remain until the session ends.
•
Add
INCLUDING CONSTRAINTS
to
CREATE TABLE LIKE
(Greg Stark)
This allows easy copying of
CHECK
constraints to a new table.
•
Allow the creation of placeholder (shell) types (Martijn van Oosterhout)
Ashelltype declaration creates atype name, withoutspecifying any of the details of the type. Mak-
ing a shell type is useful because it allows cleaner declaration of the type’s input/output functions,
whichmust exist beforethetype can be defined “for real”. The syntaxis
CREATE TYPE
typename
.
•
Aggregate functions now support multiple input parameters (Sergey Koposov, Tom)
•
Add new aggregate creation syntax (Tom)
The new syntaxis
CREATE AGGREGATE
aggname
(
input_type
) (
parameter_list
)
.This more
naturally supports the new multi-parameter aggregate functionality. The previous syntax is still
supported.
•
Add
ALTER ROLE PASSWORD NULL
to remove a previously set role password(Peter)
•
Add
DROP
object
IF EXISTS
for many object types (Andrew)
This allows
DROP
operations on non-existent objects without generating anerror.
•
Add
DROP OWNED
to drop all objects owned by a role (Alvaro)
•
Add
REASSIGN OWNED
to reassign ownership of all objects owned by a role (Alvaro)
This, and
DROP OWNED
above, facilitate dropping roles.
•
Add
GRANT ON SEQUENCE
syntax (Bruce)
This was added for setting sequence-specific permissions.
GRANT ON TABLE
for sequences is still
supported for backward compatibility.
•
Add
USAGE
permission for sequencesthatallows only
currval()
and
nextval()
,not
setval()
(Bruce)
USAGE
permission allows more fine-grained control over sequence access. Granting
USAGE
allows
users to increment a sequence, but prevents them from setting the sequence to an arbitrary value
using
setval()
.
•
Add
ALTER TABLE [ NO ] INHERIT
(Greg Stark)
This allows inheritance to be adjusteddynamically, rather thanjust attablecreationand destruction.
This is very valuable when using inheritance to implement table partitioning.
•
Allow comments on global objects to be stored globally (Kris Jurka)
Previously, comments attachedto databases were stored in individual databases, makingthem inef-
fective, and there was noprovision at all for comments on roles or tablespaces. This change adds a
newshared catalog
pg_shdescription
and stores comments ondatabases, roles, and tablespaces
therein.
2511
106
Appendix E. Release Notes
E.155.3.5. Utility Command Changes
•
Add option to allow indexes to be created without blocking concurrent writes to the table (Greg
Stark, Tom)
The new syntax is
CREATE INDEX CONCURRENTLY
.The default behavior is still to block table
modification while an index is being created.
•
Provide advisory locking functionality (Abhijit Menon-Sen, Tom)
This is a new locking API designed to replace what used to be in /contrib/userlock. The userlock
code is now on pgfoundry.
•
Allow
COPY
to dump a
SELECT
query (Zoltan Boszormenyi, Karel Zak)
This allows
COPY
to dump arbitrary SQL queries. The syntax is
COPY (SELECT ...) TO
.
•
Make the
COPY
command return a command tag that includes the number of rows copied (Volkan
YAZICI)
•
Allow
VACUUM
to expire rows without being affected by other concurrent
VACUUM
operations
(Hannu Krossing, Alvaro, Tom)
•
Make initdb detect the operating system locale and set the default
DateStyle
accordingly (Peter)
This makes it more likely that the installed
postgresql.conf DateStyle
value will be as de-
sired.
•
Reduce number of progress messages displayed by initdb (Tom)
E.155.3.6. Date/Time Changes
•
Allow full timezone names in
timestamp
input values (Joachim Wieland)
For example,
’2006-05-24 21:11 America/New_York’::timestamptz
.
•
Support configurable timezone abbreviations (Joachim Wieland)
A desired set of timezone abbreviations can be chosen via the configuration parameter
timezone_abbreviations
.
•
Add
pg_timezone_abbrevs
and
pg_timezone_names
views to show supported timezones
(Magnus Hagander)
•
Add
clock_timestamp()
,
statement_timestamp()
, and
transaction_timestamp()
(Bruce)
clock_timestamp()
is the current wall-clock time,
statement_timestamp()
is the time the
current statement arrived at the server, and
transaction_timestamp()
is an alias for
now()
.
•
Allow
to_char()
to print localized month and day names (Euler Taveira de Oliveira)
•
Allow
to_char(time)
and
to_char(interval)
to output AM/PM specifications (Bruce)
Intervals and times are treated as 24-hour periods, e.g.
25 hours
is considered AM.
•
Add new function
justify_interval()
to adjust interval units (Mark Dilger)
•
Allow timezone offsets up to 14:59 away from GMT
Kiribati uses GMT+14, so we’d better accept that.
•
Interval computation improvements (Michael Glaesemann, Bruce)
2512
149
Appendix E. Release Notes
E.155.3.7. Other Data Type and Function Changes
•
Allow arrays to contain
NULL
elements (Tom)
•
Allow assignment to array elements not contiguous with the existing entries (Tom)
The intervening array positions will be filled with nulls. This is per SQL standard.
•
New built-in operators for array-subset comparisons (
@>
,
<@
,
&&
)(Teodor, Tom)
These operators can be indexed for many data types using GiST or GIN indexes.
•
Add convenient arithmetic operations on
INET
/
CIDR
values (Stephen R. van den Berg)
The new operators are
&
(and),
|
(or),
~
(not),
inet + int8
,
inet - int8
,and
inet - inet
.
•
Add new aggregate functions from SQL:2003 (Neil)
The new functions are
var_pop()
,
var_samp()
,
stddev_pop()
, and
stddev_samp()
.
var_samp()
and
stddev_samp()
are merely renamings of the existing aggregates
variance()
and
stddev()
.The latter names remain available for backward compatibility.
•
Add SQL:2003 statistical aggregates (Sergey Koposov)
New functions:
regr_intercept()
,
regr_slope()
,
regr_r2()
,
corr()
,
covar_samp()
,
covar_pop()
,
regr_avgx()
,
regr_avgy()
,
regr_sxy()
,
regr_sxx()
,
regr_syy()
,
regr_count()
.
•
Allow domains to be based on other domains (Tom)
•
Properly enforce domain
CHECK
constraints everywhere (Neil, Tom)
For example, the result of a user-defined function that is declared to return a domain type is now
checked against the domain’s constraints. This closes a significant hole inthe domain implementa-
tion.
•
Fix problems with dumping renamed
SERIAL
columns (Tom)
The fix is to dump a
SERIAL
column by explicitly specifying its
DEFAULT
and sequence elements,
and reconstructing the
SERIAL
column onreloadusinga new
ALTER SEQUENCE OWNED BY
com-
mand. This also allows dropping a
SERIAL
column specification.
•
Add a server-side sleep function
pg_sleep()
(Joachim Wieland)
•
Add all comparison operators for the
tid
(tuple id) data type (Mark Kirkwood, Greg Stark, Tom)
E.155.3.8. PL/pgSQL Server-Side Language Changes
•
Add
TG_table_name
and
TG_table_schema
to trigger parameters (Andrew)
TG_relname
is now deprecated. Comparable changes have been made in the trigger parameters
for the other PLs as well.
•
Allow
FOR
statements to return values to scalars as well as records and row types (Pavel Stehule)
•
Add a
BY
clause to the
FOR
loop, to control the iteration increment (Jaime Casanova)
•
Add
STRICT
to
SELECT INTO
(Matt Miller)
STRICT
mode throws an exception if more or less than one row is returned by the
SELECT
,for
Oracle PL/SQL compatibility.
2513
98
Appendix E. Release Notes
E.155.3.9. PL/Perl Server-Side Language Changes
•
Add
table_name
and
table_schema
totrigger parameters (Adam Sjøgren)
•
Add prepared queries (DmitryKarasik)
•
Make
$_TD
trigger data a global variable (Andrew)
Previously, it was lexical, which caused unexpected sharing violations.
•
Run PL/Perl and PL/PerlU in separate interpreters, for security reasons (Andrew)
In consequence, they can no longer share data nor loaded modules. Also, if Perl has not been
compiled with the requisite flags to allow multiple interpreters, only one of these languages can be
used in any given backend process.
E.155.3.10. PL/Python Server-Side Language Changes
•
Named parameters are passed as ordinaryvariables, as well as in the
args[]
array(SvenSuursoho)
•
Add
table_name
and
table_schema
totrigger parameters (Andrew)
•
Allow returning of composite types and result sets (Sven Suursoho)
•
Return result-set as
list
,
iterator
,or
generator
(Sven Suursoho)
•
Allow functions to return
void
(Neil)
•
Python 2.5 is now supported (Tom)
E.155.3.11. psql Changes
•
Add new command
\password
for changing role password with client-side password encryption
(Peter)
•
Allow
\c
to connect toa new host and port number (David, Volkan YAZICI)
•
Add tablespace display to
\l+
(Philip Yarra)
•
Improve
\df
slash command to include the argument names and modes (
OUT
or
INOUT
)of the
function (David Fetter)
•
Support binary
COPY
(Andreas Pflug)
•
Add option to run the entire session in a single transaction (Simon)
Use option
-1
or
--single-transaction
.
•
Support for automatically retrieving
SELECT
results in batches using a cursor (Chris Mair)
This is enabled using
\set FETCH_COUNT
n
.This feature allows large result sets to be retrieved
in psql without attempting to buffer the entire result set in memory.
•
Make multi-line values align in the proper column (Martijn van Oosterhout)
Field values containing newlines are now displayedin a more readable fashion.
•
Save multi-line statements as a single entry, rather than one line at a time (Sergey E. Koposov)
This makes up-arrow recall of queries easier. (This is not available on Windows, because that
platform uses the native command-line editing present in the operating system.)
2514
96
Appendix E. Release Notes
•
Make the line counter 64-bit so it can handle files with more than two billion lines (David Fetter)
•
Report both the returned data and the command status tag for
INSERT
/
UPDATE
/
DELETE
RETURNING
(Tom)
E.155.3.12. pg_dump Changes
•
Allowcomplex selectionof objects to be included or excluded by pg_dump (Greg SabinoMullane)
pg_dump now supports multiple
-n
(schema) and
-t
(table) options, and adds
-N
and
-T
options
to exclude objects. Also, the arguments of these switches can now be wild-card expressions rather
than single object names, for example
-t ’foo
*
’
,anda schema can be part of a
-t
or
-T
switch,
for example
-t schema1.table1
.
•
Add pg_restore
--no-data-for-failed-tables
option to suppress loading data if table cre-
ation failed (i.e., the table already exists) (Martin Pitt)
•
Add pg_restore option to run the entire session in a single transaction (Simon)
Use option
-1
or
--single-transaction
.
E.155.3.13. libpq Changes
•
Add
PQencryptPassword()
to encrypt passwords (Tom)
This allows passwords tobe sent pre-encrypted for commands like
ALTER ROLE ... PASSWORD
.
•
Add function
PQisthreadsafe()
(Bruce)
This allows applications toquery the thread-safety status of the library.
•
Add
PQdescribePrepared()
,
PQdescribePortal()
,and related functions to return informa-
tion about previously prepared statements and open cursors (Volkan YAZICI)
•
Allow LDAP lookups from
pg_service.conf
(Laurenz Albe)
•
Allow a hostname in
~/.pgpass
to match the default socket directory (Bruce)
Ablank hostname continues to match any Unix-socket connection, but this addition allows entries
that are specific to one of several postmasters on the machine.
E.155.3.14. ecpg Changes
•
Allow
SHOW
to put its result into a variable (Joachim Wieland)
•
Add
COPY TO STDOUT
(Joachim Wieland)
•
Add regression tests (Joachim Wieland, Michael)
•
Major source code cleanups (Joachim Wieland, Michael)
E.155.3.15. Windows Port
•
Allow MSVC to compile the PostgreSQL server (Magnus, Hiroshi Saito)
2515
97
Appendix E. Release Notes
•
Add MSVC support for utility commands and pg_dump (Hiroshi Saito)
•
Add support for Windows code pages
1253
,
1254
,
1255
,and
1257
(Kris Jurka)
•
Drop privileges on startup, so that the server can be started from an administrative account (Mag-
nus)
•
Stability fixes (Qingqing Zhou, Magnus)
•
Add native semaphore implementation (Qingqing Zhou)
The previous code mimicked SysV semaphores.
E.155.3.16. Source Code Changes
•
Add GIN (Generalized Inverted iNdex) index access method (Teodor, Oleg)
•
Remove R-tree indexing (Tom)
Rtreehas beenre-implementedusingGiST. Amongother differences, this means that rtree indexes
now have support for crash recovery via write-ahead logging (WAL).
•
Reduce libraries needlessly linked into the backend (Martijn van Oosterhout, Tom)
•
Add a configure flag toallow libedit to be preferred over GNU readline (Bruce)
Use configure
--with-libedit-preferred
.
•
Allow installation into directories containing spaces (Peter)
•
Improve ability to relocate installation directories (Tom)
•
Add support for Solaris x86_64 using the Solaris compiler (Pierre Girard, Theo Schlossnagle,
Bruce)
•
Add DTrace support (Robert Lor)
•
Add
PG_VERSION_NUM
for use by third-party applications wanting to test the backend version in
Cusing > and < comparisons (Bruce)
•
Add
XLOG_BLCKSZ
as independent from
BLCKSZ
(Mark Wong)
•
Add
LWLOCK_STATS
define to report locking activity (Tom)
•
Emit warnings for unknown configure options (Martijnvan Oosterhout)
•
Add server support for “plugin” libraries that can be used for add-on tasks such as debugging and
performance measurement (Korry Douglas)
This consists of twofeatures:a table of “rendezvous variables”thatallows separately-loadedshared
libraries to communicate, and a new configuration parameter
local_preload_libraries
that
allows libraries to be loaded into specific sessions without explicit cooperation from the client
application. This allows external add-ons to implement features such as a PL/pgSQL debugger.
•
Rename
existing
configuration
parameter
preload_libraries
to
shared_preload_libraries
(Tom)
This was done for clarity in comparison to
local_preload_libraries
.
•
Add new configuration parameter
server_version_num
(Greg Sabino Mullane)
This is like
server_version
, but is an integer, e.g.
80200
.This allows applications to make
version checks more easily.
•
Add a configuration parameter
seq_page_cost
(Tom)
2516
70
Appendix E. Release Notes
•
Re-implement the regression test script as a C program (Magnus, Tom)
•
Allow loadable modules to allocate shared memory and lightweight locks (Marc Munro)
•
Add automatic initialization and finalization of dynamically loaded libraries (Ralf Engelschall,
Tom)
Newfunctions
_PG_init()
and
_PG_fini()
are calledif the library defines suchsymbols. Hence
we no longer need to specify an initialization function in
shared_preload_libraries
;we can
assume that the library used the
_PG_init()
convention instead.
•
Add
PG_MODULE_MAGIC
header block to all shared object files (Martijn van Oosterhout)
The magic block prevents version mismatches between loadable object files and servers.
•
Add shared library support for AIX (Laurenz Albe)
•
New XML documentation section (Bruce)
E.155.3.17. Contrib Changes
•
Major tsearch2 improvements (Oleg, Teodor)
•
multibyte encoding support, including UTF8
•
query rewriting support
•
improved ranking functions
•
thesaurus dictionary support
•
Ispell dictionaries now recognize MySpell format, used by OpenOffice
•
GIN support
•
Add adminpackmodule containing Pgadmin administration functions (Dave)
Thesefunctions provide additional filesystem access routines notpresentinthe defaultPostgreSQL
server.
•
Add sslinfo module (Victor Wagner)
Reports information about the current connection’s SSL certificate.
•
Add pgrowlocks module (Tatsuo)
This shows rowlocking information for a specified table.
•
Add hstore module (Oleg, Teodor)
•
Add isn module, replacing isbn_issn (Jeremy Kronuz)
This new implementation supports EAN13, UPC, ISBN (books), ISMN (music), and ISSN (seri-
als).
•
Add index information functions to pgstattuple (ITAGAKI Takahiro, Satoshi Nagayasu)
•
Add pg_freespacemap module to display free space map information (Mark Kirkwood)
•
pgcrypto now has all planned functionality (Marko Kreen)
•
Include iMath library in pgcrypto to have the public-key encryption functions always available.
•
Add SHA224 algorithm that was missing in OpenBSD code.
2517
Documents you may be interested
Documents you may be interested
