E.247. Release 8.1
Release date: 2005-11-08
E.247.1. Overview
Major changes in this release:
- Improve concurrent access to the shared buffer cache (Tom)
-
Access to the shared buffer cache was identified as a significant scalability problem, particularly on multi-CPU systems. In this release, the way that locking is done in the buffer manager has been overhauled to reduce lock contention and improve scalability. The buffer manager has also been changed to use a " clock sweep " replacement policy.
- Allow index scans to use an intermediate in-memory bitmap (Tom)
-
In previous releases, only a single index could be used to do lookups on a table. With this feature, if a query has
WHERE tab.col1 = 4 and tab.col2 = 9
, and there is no multicolumn index oncol1
andcol2
, but there is an index oncol1
and another oncol2
, it is possible to search both indexes and combine the results in memory, then do heap fetches for only the rows matching both thecol1
andcol2
restrictions. This is very useful in environments that have a lot of unstructured queries where it is impossible to create indexes that match all possible access conditions. Bitmap scans are useful even with a single index, as they reduce the amount of random access needed; a bitmap index scan is efficient for retrieving fairly large fractions of the complete table, whereas plain index scans are not. - Add two-phase commit (Heikki Linnakangas, Alvaro, Tom)
-
Two-phase commit allows transactions to be "prepared" on several computers, and once all computers have successfully prepared their transactions (none failed), all transactions can be committed. Even if a machine crashes after a prepare, the prepared transaction can be committed after the machine is restarted. New syntax includes
PREPARE TRANSACTION
andCOMMIT/ROLLBACK PREPARED
. A new system viewpg_prepared_xacts
has also been added. - Create a new role system that replaces users and groups (Stephen Frost)
-
Roles are a combination of users and groups. Like users, they can have login capability, and like groups, a role can have other roles as members. Roles basically remove the distinction between users and groups. For example, a role can:
-
Have login capability (optionally)
-
Own objects
-
Hold access permissions for database objects
-
Inherit permissions from other roles it is a member of
Once a user logs into a role, she obtains capabilities of the login role plus any inherited roles, and can use
SET ROLE
to switch to other roles she is a member of. This feature is a generalization of the SQL standard's concept of roles. This change also replacespg_shadow
andpg_group
by new role-capable catalogspg_authid
andpg_auth_members
. The old tables are redefined as read-only views on the new role tables. -
-
Automatically use indexes for
MIN()
andMAX()
(Tom) -
In previous releases, the only way to use an index for
MIN()
orMAX()
was to rewrite the query asSELECT col FROM tab ORDER BY col LIMIT 1
. Index usage now happens automatically. -
Move
/contrib/pg_autovacuum
into the main server (Alvaro) -
Integrating autovacuum into the server allows it to be automatically started and stopped in sync with the database server, and allows autovacuum to be configured from
postgresql.conf
. -
Add shared row level locks using
SELECT ... FOR SHARE
(Alvaro) -
While PostgreSQL 's MVCC locking allows
SELECT
to never be blocked by writers and therefore does not need shared row locks for typical operations, shared locks are useful for applications that require shared row locking. In particular this reduces the locking requirements imposed by referential integrity checks. - Add dependencies on shared objects, specifically roles (Alvaro)
-
This extension of the dependency mechanism prevents roles from being dropped while there are still database objects they own. Formerly it was possible to accidentally " orphan " objects by deleting their owner. While this could be recovered from, it was messy and unpleasant.
- Improve performance for partitioned tables (Simon)
-
The new
constraint_exclusion
configuration parameter avoids lookups on child tables where constraints indicate that no matching rows exist in the child table.This allows for a basic type of table partitioning. If child tables store separate key ranges and this is enforced using appropriate
CHECK
constraints, the optimizer will skip child table accesses when the constraint guarantees no matching rows exist in the child table.
E.247.2. Migration to Version 8.1
A dump/restore using pg_dump is required for those wishing to migrate data from any previous release.
The 8.0 release announced that the
to_char()
function
for intervals would be removed in 8.1. However, since no better API
has been suggested,
to_char(interval)
has been enhanced in
8.1 and will remain in the server.
Observe the following incompatibilities:
-
add_missing_from
is now false by default (Neil)By default, we now generate an error if a table is used in a query without a
FROM
reference. The old behavior is still available, but the parameter must be set to 'true' to obtain it.It might be necessary to set
add_missing_from
to true in order to load an existing dump file, if the dump contains any views or rules created using the implicit-FROM
syntax. This should be a one-time annoyance, because PostgreSQL 8.1 will convert such views and rules to standard explicit-FROM
syntax. Subsequent dumps will therefore not have the problem. -
Cause input of a zero-length string (
''
) forfloat4
/float8
/oid
to throw an error, rather than treating it as a zero (Neil)This change is consistent with the current handling of zero-length strings for integers. The schedule for this change was announced in 8.0.
-
default_with_oids
is now false by default (Neil)With this option set to false, user-created tables no longer have an OID column unless
WITH OIDS
is specified inCREATE TABLE
. Though OIDs have existed in all releases of PostgreSQL , their use is limited because they are only four bytes long and the counter is shared across all installed databases. The preferred way of uniquely identifying rows is via sequences and theSERIAL
type, which have been supported since PostgreSQL 6.4. -
Add
E''
syntax so eventually ordinary strings can treat backslashes literally (Bruce)Currently PostgreSQL processes a backslash in a string literal as introducing a special escape sequence, e.g.
\n
or\010
. While this allows easy entry of special values, it is nonstandard and makes porting of applications from other databases more difficult. For this reason, the PostgreSQL project is planning to remove the special meaning of backslashes in strings. For backward compatibility and for users who want special backslash processing, a new string syntax has been created. This new string syntax is formed by writing anE
immediately preceding the single quote that starts the string, e.g.E'hi\n'
. While this release does not change the handling of backslashes in strings, it does add new configuration parameters to help users migrate applications for future releases:-
standard_conforming_strings
- does this release treat backslashes literally in ordinary strings? -
escape_string_warning
- warn about backslashes in ordinary (non-E) strings
The
standard_conforming_strings
value is read-only. Applications can retrieve the value to know how backslashes are processed. (Presence of the parameter can also be taken as an indication thatE''
string syntax is supported.) In a future release,standard_conforming_strings
will be true, meaning backslashes will be treated literally in non-E strings. To prepare for this change, useE''
strings in places that need special backslash processing, and turn onescape_string_warning
to find additional strings that need to be converted to useE''
. Also, use two single-quotes (''
) to embed a literal single-quote in a string, rather than the PostgreSQL -supported syntax of backslash single-quote (\'
). The former is standards-conforming and does not require the use of theE''
string syntax. You can also use the$$
string syntax, which does not treat backslashes specially. -
-
Make
REINDEX DATABASE
reindex all indexes in the database (Tom)Formerly,
REINDEX DATABASE
reindexed only system tables. This new behavior seems more intuitive. A new commandREINDEX SYSTEM
provides the old functionality of reindexing just the system tables. -
Read-only large object descriptors now obey MVCC snapshot semantics
When a large object is opened with
INV_READ
(and notINV_WRITE
), the data read from the descriptor will now reflect a " snapshot " of the large object's state at the time of the transaction snapshot in use by the query that calledlo_open()
. To obtain the old behavior of always returning the latest committed data, includeINV_WRITE
in the mode flags forlo_open()
. -
Add proper dependencies for arguments of sequence functions (Tom)
In previous releases, sequence names passed to
nextval()
,currval()
, andsetval()
were stored as simple text strings, meaning that renaming or dropping a sequence used in aDEFAULT
clause made the clause invalid. This release stores all newly-created sequence function arguments as internal OIDs, allowing them to track sequence renaming, and adding dependency information that prevents improper sequence removal. It also makes suchDEFAULT
clauses immune to schema renaming and search path changes.Some applications might rely on the old behavior of run-time lookup for sequence names. This can still be done by explicitly casting the argument to
text
, for examplenextval('myseq'::text)
.Pre-8.1 database dumps loaded into 8.1 will use the old text-based representation and therefore will not have the features of OID-stored arguments. However, it is possible to update a database containing text-based
DEFAULT
clauses. First, save this query into a file, such asfixseq.sql
:SELECT 'ALTER TABLE ' || pg_catalog.quote_ident(n.nspname) || '.' || pg_catalog.quote_ident(c.relname) || ' ALTER COLUMN ' || pg_catalog.quote_ident(a.attname) || ' SET DEFAULT ' || regexp_replace(d.adsrc, $$val\(\(('[^']*')::text\)::regclass$$, $$val(\1$$, 'g') || ';' FROM pg_namespace n, pg_class c, pg_attribute a, pg_attrdef d WHERE n.oid = c.relnamespace AND c.oid = a.attrelid AND a.attrelid = d.adrelid AND a.attnum = d.adnum AND d.adsrc ~ $$val\(\('[^']*'::text\)::regclass$$;
Next, run the query against a database to find what adjustments are required, like this for database
db1
:psql -t -f fixseq.sql db1
This will show the
ALTER TABLE
commands needed to convert the database to the newer OID-based representation. If the commands look reasonable, run this to update the database:psql -t -f fixseq.sql db1 | psql -e db1
This process must be repeated in each database to be updated.
-
In psql , treat unquoted
\{digit}+
sequences as octal (Bruce)In previous releases,
\{digit}+
sequences were treated as decimal, and only\0{digit}+
were treated as octal. This change was made for consistency. -
Remove grammar productions for prefix and postfix
%
and^
operators (Tom)These have never been documented and complicated the use of the modulus operator (
%
) with negative numbers. -
Make
&<
and&>
for polygons consistent with the box "over" operators (Tom) -
CREATE LANGUAGE
can ignore the provided arguments in favor of information frompg_pltemplate
(Tom)A new system catalog
pg_pltemplate
has been defined to carry information about the preferred definitions of procedural languages (such as whether they have validator functions). When an entry exists in this catalog for the language being created,CREATE LANGUAGE
will ignore all its parameters except the language name and instead use the catalog information. This measure was taken because of increasing problems with obsolete language definitions being loaded by old dump files. As of 8.1, pg_dump will dump procedural language definitions as justCREATE LANGUAGE
, relying on a template entry to exist at load time. We expect this will be a more future-proof representation.name
-
Make
pg_cancel_backend(int)
return aboolean
rather than aninteger
(Neil) -
Some users are having problems loading UTF-8 data into 8.1.X. This is because previous versions allowed invalid UTF-8 byte sequences to be entered into the database, and this release properly accepts only valid UTF-8 sequences. One way to correct a dumpfile is to run the command
iconv -c -f UTF-8 -t UTF-8 -o cleanfile.sql dumpfile.sql
. The-c
option removes invalid character sequences. A diff of the two files will show the sequences that are invalid.iconv
reads the entire input file into memory so it might be necessary to use split to break up the dump into multiple smaller files for processing.
E.247.3. Additional Changes
Below you will find a detailed account of the additional changes between PostgreSQL 8.1 and the previous major release.
E.247.3.1. Performance Improvements
-
Improve GiST and R-tree index performance (Neil)
-
Improve the optimizer, including auto-resizing of hash joins (Tom)
-
Overhaul internal API in several areas
-
Change WAL record CRCs from 64-bit to 32-bit (Tom)
We determined that the extra cost of computing 64-bit CRCs was significant, and the gain in reliability too marginal to justify it.
-
Prevent writing large empty gaps in WAL pages (Tom)
-
Improve spinlock behavior on SMP machines, particularly Opterons (Tom)
-
Allow nonconsecutive index columns to be used in a multicolumn index (Tom)
For example, this allows an index on columns a,b,c to be used in a query with
WHERE a = 4 and c = 10
. -
Skip WAL logging for
CREATE TABLE AS
/SELECT INTO
(Simon)Since a crash during
CREATE TABLE AS
would cause the table to be dropped during recovery, there is no reason to WAL log as the table is loaded. (Logging still happens if WAL archiving is enabled, however.) -
Allow concurrent GiST index access (Teodor, Oleg)
-
Add configuration parameter
full_page_writes
to control writing full pages to WAL (Bruce)To prevent partial disk writes from corrupting the database, PostgreSQL writes a complete copy of each database disk page to WAL the first time it is modified after a checkpoint. This option turns off that functionality for more speed. This is safe to use with battery-backed disk caches where partial page writes cannot happen.
-
Use
O_DIRECT
if available when usingO_SYNC
forwal_sync_method
(Itagaki Takahiro)O_DIRECT
causes disk writes to bypass the kernel cache, and for WAL writes, this improves performance. -
Improve
COPY FROM
performance (Alon Goldshuv)This was accomplished by reading
COPY
input in larger chunks, rather than character by character. -
Improve the performance of
COUNT()
,SUM
,AVG()
,STDDEV()
, andVARIANCE()
(Neil, Tom)
E.247.3.2. Server Changes
-
Prevent problems due to transaction ID (XID) wraparound (Tom)
The server will now warn when the transaction counter approaches the wraparound point. If the counter becomes too close to wraparound, the server will stop accepting queries. This ensures that data is not lost before needed vacuuming is performed.
-
Fix problems with object IDs (OIDs) conflicting with existing system objects after the OID counter has wrapped around (Tom)
-
Add warning about the need to increase
max_fsm_relations
andmax_fsm_pages
duringVACUUM
(Ron Mayer) -
Add
temp_buffers
configuration parameter to allow users to determine the size of the local buffer area for temporary table access (Tom) -
Add session start time and client IP address to
pg_stat_activity
(Magnus) -
Adjust
pg_stat
views for bitmap scans (Tom)The meanings of some of the fields have changed slightly.
-
Enhance
pg_locks
view (Tom) -
Log queries for client-side
PREPARE
andEXECUTE
(Simon) -
Allow Kerberos name and user name case sensitivity to be specified in
postgresql.conf
(Magnus) -
Add configuration parameter
krb_server_hostname
so that the server host name can be specified as part of service principal (Todd Kover)If not set, any service principal matching an entry in the keytab can be used. This is new Kerberos matching behavior in this release.
-
Add
log_line_prefix
options for millisecond timestamps (%m
) and remote host (%h
) (Ed L.) -
Add WAL logging for GiST indexes (Teodor, Oleg)
GiST indexes are now safe for crash and point-in-time recovery.
-
Remove old
*.backup
files when we dopg_stop_backup()
(Bruce)This prevents a large number of
*.backup
files from existing inpg_xlog/
. -
Add configuration parameters to control TCP/IP keep-alive times for idle, interval, and count (Oliver Jowett)
These values can be changed to allow more rapid detection of lost client connections.
-
Add per-user and per-database connection limits (Petr Jelinek)
Using
ALTER USER
andALTER DATABASE
, limits can now be enforced on the maximum number of sessions that can concurrently connect as a specific user or to a specific database. Setting the limit to zero disables user or database connections. -
Allow more than two gigabytes of shared memory and per-backend work memory on 64-bit machines (Koichi Suzuki)
-
New system catalog
pg_pltemplate
allows overriding obsolete procedural-language definitions in dump files (Tom)
E.247.3.3. Query Changes
-
Add temporary views (Koju Iijima, Neil)
-
Fix
HAVING
without any aggregate functions orGROUP BY
so that the query returns a single group (Tom)Previously, such a case would treat the
HAVING
clause the same as aWHERE
clause. This was not per spec. -
Add
USING
clause to allow additional tables to be specified toDELETE
(Euler Taveira de Oliveira, Neil)In prior releases, there was no clear method for specifying additional tables to be used for joins in a
DELETE
statement.UPDATE
already has aFROM
clause for this purpose. -
Add support for
\x
hex escapes in backend and ecpg strings (Bruce)This is just like the standard C
\x
escape syntax. Octal escapes were already supported. -
Add
BETWEEN SYMMETRIC
query syntax (Pavel Stehule)This feature allows
BETWEEN
comparisons without requiring the first value to be less than the second. For example,2 BETWEEN [ASYMMETRIC] 3 AND 1
returns false, while2 BETWEEN SYMMETRIC 3 AND 1
returns true.BETWEEN ASYMMETRIC
was already supported. -
Add
NOWAIT
option toSELECT ... FOR UPDATE/SHARE
(Hans-Juergen Schoenig)While the
statement_timeout
configuration parameter allows a query taking more than a certain amount of time to be canceled, theNOWAIT
option allows a query to be canceled as soon as aSELECT ... FOR UPDATE/SHARE
command cannot immediately acquire a row lock.
E.247.3.4. Object Manipulation Changes
-
Track dependencies of shared objects (Alvaro)
PostgreSQL allows global tables (users, databases, tablespaces) to reference information in multiple databases. This addition adds dependency information for global tables, so, for example, user ownership can be tracked across databases, so a user who owns something in any database can no longer be removed. Dependency tracking already existed for database-local objects.
-
Allow limited
ALTER OWNER
commands to be performed by the object owner (Stephen Frost)Prior releases allowed only superusers to change object owners. Now, ownership can be transferred if the user executing the command owns the object and would be able to create it as the new owner (that is, the user is a member of the new owning role and that role has the CREATE permission that would be needed to create the object afresh).
-
Add
ALTER
objectSET SCHEMA
capability for some object types (tables, functions, types) (Bernd Helmle)This allows objects to be moved to different schemas.
-
Add
ALTER TABLE ENABLE/DISABLE TRIGGER
to disable triggers (Satoshi Nagayasu)
E.247.3.5. Utility Command Changes
-
Allow
TRUNCATE
to truncate multiple tables in a single command (Alvaro)Because of referential integrity checks, it is not allowed to truncate a table that is part of a referential integrity constraint. Using this new functionality,
TRUNCATE
can be used to truncate such tables, if both tables involved in a referential integrity constraint are truncated in a singleTRUNCATE
command. -
Properly process carriage returns and line feeds in
COPY CSV
mode (Andrew)In release 8.0, carriage returns and line feeds in
CSV COPY TO
were processed in an inconsistent manner. (This was documented on the TODO list.) -
Add
COPY WITH CSV HEADER
to allow a header line as the first line inCOPY
(Andrew)This allows handling of the common
CSV
usage of placing the column names on the first line of the data file. ForCOPY TO
, the first line contains the column names, and forCOPY FROM
, the first line is ignored. -
On Windows, display better sub-second precision in
EXPLAIN ANALYZE
(Magnus) -
Add trigger duration display to
EXPLAIN ANALYZE
(Tom)Prior releases included trigger execution time as part of the total execution time, but did not show it separately. It is now possible to see how much time is spent in each trigger.
-
Add support for
\x
hex escapes inCOPY
(Sergey Ten)Previous releases only supported octal escapes.
-
Make
SHOW ALL
include variable descriptions (Matthias Schmidt)SHOW
varname still only displays the variable's value and does not include the description. -
Make initdb create a new standard database called
postgres
, and convert utilities to usepostgres
rather thantemplate1
for standard lookups (Dave)In prior releases,
template1
was used both as a default connection for utilities like createuser , and as a template for new databases. This causedCREATE DATABASE
to sometimes fail, because a new database cannot be created if anyone else is in the template database. With this change, the default connection database is nowpostgres
, meaning it is much less likely someone will be usingtemplate1
duringCREATE DATABASE
. -
Create new reindexdb command-line utility by moving
/contrib/reindexdb
into the server (Euler Taveira de Oliveira)
E.247.3.6. Data Type and Function Changes
-
Add
MAX()
andMIN()
aggregates for array types (Koju Iijima) -
Fix
to_date()
andto_timestamp()
to behave reasonably whenCC
andYY
fields are both used (Karel Zak)If the format specification contains
CC
and a year specification isYYY
or longer, ignore theCC
. If the year specification isYY
or shorter, interpretCC
as the previous century. -
Add
md5(bytea)
(Abhijit Menon-Sen)md5(text)
already existed. -
Add support for
numeric ^ numeric
based onpower(numeric, numeric)
The function already existed, but there was no operator assigned to it.
-
Fix
NUMERIC
modulus by properly truncating the quotient during computation (Bruce)In previous releases, modulus for large values sometimes returned negative results due to rounding of the quotient.
-
Add a function
lastval()
(Dennis Björklund)lastval()
is a simplified version ofcurrval()
. It automatically determines the proper sequence name based on the most recentnextval()
orsetval()
call performed by the current session. -
Add
to_timestamp(DOUBLE PRECISION) (Michael Glaesemann)
Converts Unix seconds since 1970 to a
TIMESTAMP WITH TIMEZONE
. -
Add
pg_postmaster_start_time()
function (Euler Taveira de Oliveira, Matthias Schmidt) -
Allow the full use of time zone names in
AT TIME ZONE
, not just the short list previously available (Magnus)Previously, only a predefined list of time zone names were supported by
AT TIME ZONE
. Now any supported time zone name can be used, e.g.:SELECT CURRENT_TIMESTAMP AT TIME ZONE 'Europe/London';
In the above query, the time zone used is adjusted based on the daylight saving time rules that were in effect on the supplied date.
-
Add
GREATEST()
andLEAST()
variadic functions (Pavel Stehule)These functions take a variable number of arguments and return the greatest or least value among the arguments.
-
Add
pg_column_size()
(Mark Kirkwood)This returns storage size of a column, which might be compressed.
-
Add
regexp_replace()
(Atsushi Ogawa)This allows regular expression replacement, like sed. An optional flag argument allows selection of global (replace all) and case-insensitive modes.
-
Fix interval division and multiplication (Bruce)
Previous versions sometimes returned unjustified results, like
'4 months'::interval / 5
returning'1 mon -6 days'
. -
Fix roundoff behavior in timestamp, time, and interval output (Tom)
This fixes some cases in which the seconds field would be shown as
60
instead of incrementing the higher-order fields. -
Add a separate day field to type
interval
so a one day interval can be distinguished from a 24 hour interval (Michael Glaesemann)Days that contain a daylight saving time adjustment are not 24 hours long, but typically 23 or 25 hours. This change creates a conceptual distinction between intervals of " so many days " and intervals of " so many hours " . Adding
1 day
to a timestamp now gives the same local time on the next day even if a daylight saving time adjustment occurs between, whereas adding24 hours
will give a different local time when this happens. For example, under US DST rules:'2005-04-03 00:00:00-05' + '1 day' = '2005-04-04 00:00:00-04' '2005-04-03 00:00:00-05' + '24 hours' = '2005-04-04 01:00:00-04'
-
Add
justify_days()
andjustify_hours()
(Michael Glaesemann)These functions, respectively, adjust days to an appropriate number of full months and days, and adjust hours to an appropriate number of full days and hours.
-
Move
/contrib/dbsize
into the backend, and rename some of the functions (Dave Page, Andreas Pflug)-
pg_tablespace_size()
-
pg_database_size()
-
pg_relation_size()
-
pg_total_relation_size()
-
pg_size_pretty()
pg_total_relation_size()
includes indexes and TOAST tables. -
-
Add functions for read-only file access to the cluster directory (Dave Page, Andreas Pflug)
-
pg_stat_file()
-
pg_read_file()
-
pg_ls_dir()
-
-
Add
pg_reload_conf()
to force reloading of the configuration files (Dave Page, Andreas Pflug) -
Add
pg_rotate_logfile()
to force rotation of the server log file (Dave Page, Andreas Pflug) -
Change
pg_stat_*
views to include TOAST tables (Tom)
E.247.3.7. Encoding and Locale Changes
-
Rename some encodings to be more consistent and to follow international standards (Bruce)
-
UNICODE
is nowUTF8
-
ALT
is nowWIN866
-
WIN
is nowWIN1251
-
TCVN
is nowWIN1258
The original names still work.
-
-
Add support for
WIN1252
encoding (Roland Volkmann) -
Add support for four-byte
UTF8
characters (John Hansen)Previously only one, two, and three-byte
UTF8
characters were supported. This is particularly important for support for some Chinese character sets. -
Allow direct conversion between
EUC_JP
andSJIS
to improve performance (Atsushi Ogawa) -
Allow the UTF8 encoding to work on Windows (Magnus)
This is done by mapping UTF8 to the Windows-native UTF16 implementation.
E.247.3.8. General Server-Side Language Changes
-
Fix
ALTER LANGUAGE RENAME
(Sergey Yatskevich) -
Allow function characteristics, like strictness and volatility, to be modified via
ALTER FUNCTION
(Neil) -
Increase the maximum number of function arguments to 100 (Tom)
-
Allow SQL and PL/pgSQL functions to use
OUT
andINOUT
parameters (Tom)OUT
is an alternate way for a function to return values. Instead of usingRETURN
, values can be returned by assigning to parameters declared asOUT
orINOUT
. This is notationally simpler in some cases, particularly so when multiple values need to be returned. While returning multiple values from a function was possible in previous releases, this greatly simplifies the process. (The feature will be extended to other server-side languages in future releases.) -
Move language handler functions into the
pg_catalog
schemaThis makes it easier to drop the public schema if desired.
-
Add
SPI_getnspname()
to SPI (Neil)
E.247.3.9. PL/pgSQL Server-Side Language Changes
-
Overhaul the memory management of PL/pgSQL functions (Neil)
The parsetree of each function is now stored in a separate memory context. This allows this memory to be easily reclaimed when it is no longer needed.
-
Check function syntax at
CREATE FUNCTION
time, rather than at runtime (Neil)Previously, most syntax errors were reported only when the function was executed.
-
Allow
OPEN
to open non-SELECT
queries likeEXPLAIN
andSHOW
(Tom) -
No longer require functions to issue a
RETURN
statement (Tom)This is a byproduct of the newly added
OUT
andINOUT
functionality.RETURN
can be omitted when it is not needed to provide the function's return value. -
Add support for an optional
INTO
clause to PL/pgSQL'sEXECUTE
statement (Pavel Stehule, Neil) -
Make
CREATE TABLE AS
setROW_COUNT
(Tom) -
Define
SQLSTATE
andSQLERRM
to return theSQLSTATE
and error message of the current exception (Pavel Stehule, Neil)These variables are only defined inside exception blocks.
-
Allow the parameters to the
RAISE
statement to be expressions (Pavel Stehule, Neil) -
Add a loop
CONTINUE
statement (Pavel Stehule, Neil) -
Allow block and loop labels (Pavel Stehule)
E.247.3.10. PL/Perl Server-Side Language Changes
-
Allow large result sets to be returned efficiently (Abhijit Menon-Sen)
This allows functions to use
return_next()
to avoid building the entire result set in memory. -
Allow one-row-at-a-time retrieval of query results (Abhijit Menon-Sen)
This allows functions to use
spi_query()
andspi_fetchrow()
to avoid accumulating the entire result set in memory. -
Force PL/Perl to handle strings as
UTF8
if the server encoding isUTF8
(David Kamholz) -
Add a validator function for PL/Perl (Andrew)
This allows syntax errors to be reported at definition time, rather than execution time.
-
Allow PL/Perl to return a Perl array when the function returns an array type (Andrew)
This basically maps PostgreSQL arrays to Perl arrays.
-
Allow Perl nonfatal warnings to generate
NOTICE
messages (Andrew) -
Allow Perl's
strict
mode to be enabled (Andrew)
E.247.3.11. psql Changes
-
Add
\set ON_ERROR_ROLLBACK
to allow statements in a transaction to error without affecting the rest of the transaction (Greg Sabino Mullane)This is basically implemented by wrapping every statement in a sub-transaction.
-
Add support for
\x
hex strings in psql variables (Bruce)Octal escapes were already supported.
-
Add support for
troff -ms
output format (Roger Leigh) -
Allow the history file location to be controlled by
HISTFILE
(Andreas Seltenreich)This allows configuration of per-database history storage.
-
Prevent
\x
(expanded mode) from affecting the output of\d tablename
(Neil) -
Add
-L
option to psql to log sessions (Lorne Sunley)This option was added because some operating systems do not have simple command-line activity logging functionality.
-
Make
\d
show the tablespaces of indexes (Qingqing Zhou) -
Allow psql help (
\h
) to make a best guess on the proper help information (Greg Sabino Mullane)This allows the user to just add
\h
to the front of the syntax error query and get help on the supported syntax. Previously any additional query text beyond the command name had to be removed to use\h
. -
Add
\pset numericlocale
to allow numbers to be output in a locale-aware format (Eugen Nedelcu)For example, using
C
locale100000
would be output as100,000.0
while a European locale might output this value as100.000,0
. -
Make startup banner show both server version number and psql 's version number, when they are different (Bruce)
Also, a warning will be shown if the server and psql are from different major releases.
E.247.3.12. pg_dump Changes
-
Add
-n
/--schema
switch to pg_restore (Richard van den Berg)This allows just the objects in a specified schema to be restored.
-
Allow pg_dump to dump large objects even in text mode (Tom)
With this change, large objects are now always dumped; the former
-b
switch is a no-op. -
Allow pg_dump to dump a consistent snapshot of large objects (Tom)
-
Dump comments for large objects (Tom)
-
Add
--encoding
to pg_dump (Magnus Hagander)This allows a database to be dumped in an encoding that is different from the server's encoding. This is valuable when transferring the dump to a machine with a different encoding.
-
Rely on
pg_pltemplate
for procedural languages (Tom)If the call handler for a procedural language is in the
pg_catalog
schema, pg_dump does not dump the handler. Instead, it dumps the language using justCREATE LANGUAGE
, relying on thename
pg_pltemplate
catalog to provide the language's creation parameters at load time.
E.247.3.13. libpq Changes
-
Add a
PGPASSFILE
environment variable to specify the password file's filename (Andrew) -
Add
lo_create()
, that is similar tolo_creat()
but allows the OID of the large object to be specified (Tom) -
Make libpq consistently return an error to the client application on
malloc()
failure (Neil)
E.247.3.14. Source Code Changes
-
Fix pgxs to support building against a relocated installation
-
Add spinlock support for the Itanium processor using Intel compiler (Vikram Kalsi)
-
Add Kerberos 5 support for Windows (Magnus)
-
Add Chinese FAQ (laser@pgsqldb.com)
-
Rename Rendezvous to Bonjour to match OS/X feature renaming (Bruce)
-
Add support for
fsync_writethrough
on macOS (Chris Campbell) -
Streamline the passing of information within the server, the optimizer, and the lock system (Tom)
-
Allow pg_config to be compiled using MSVC (Andrew)
This is required to build DBD::Pg using MSVC .
-
Remove support for Kerberos V4 (Magnus)
Kerberos 4 had security vulnerabilities and is no longer maintained.
-
Code cleanups (Coverity static analysis performed by EnterpriseDB)
-
Modify
postgresql.conf
to use documentation defaultson
/off
rather thantrue
/false
(Bruce) -
Enhance pg_config to be able to report more build-time values (Tom)
-
Allow libpq to be built thread-safe on Windows (Dave Page)
-
Allow IPv6 connections to be used on Windows (Andrew)
-
Add Server Administration documentation about I/O subsystem reliability (Bruce)
-
Move private declarations from
gist.h
togist_private.h
(Neil)In previous releases,
gist.h
contained both the public GiST API (intended for use by authors of GiST index implementations) as well as some private declarations used by the implementation of GiST itself. The latter have been moved to a separate file,gist_private.h
. Most GiST index implementations should be unaffected. -
Overhaul GiST memory management (Neil)
GiST methods are now always invoked in a short-lived memory context. Therefore, memory allocated via
palloc()
will be reclaimed automatically, so GiST index implementations do not need to manually release allocated memory viapfree()
.
E.247.3.15. Contrib Changes
-
Add
/contrib/pg_buffercache
contrib module (Mark Kirkwood)This displays the contents of the buffer cache, for debugging and performance tuning purposes.
-
Remove
/contrib/array
because it is obsolete (Tom) -
Clean up the
/contrib/lo
module (Tom) -
Move
/contrib/findoidjoins
to/src/tools
(Tom) -
Remove the
<<
,>>
,&<
, and&>
operators from/contrib/cube
These operators were not useful.
-
Improve
/contrib/btree_gist
(Janko Richter) -
Improve
/contrib/pgbench
(Tomoaki Sato, Tatsuo)There is now a facility for testing with SQL command scripts given by the user, instead of only a hard-wired command sequence.
-
Improve
/contrib/pgcrypto
(Marko Kreen)-
Implementation of OpenPGP symmetric-key and public-key encryption
Both RSA and Elgamal public-key algorithms are supported.
-
Stand alone build: include SHA256/384/512 hashes, Fortuna PRNG
-
OpenSSL build: support 3DES, use internal AES with OpenSSL < 0.9.7
-
Take build parameters (OpenSSL, zlib) from
configure
resultThere is no need to edit the
Makefile
anymore. -
Remove support for
libmhash
andlibmcrypt
-