PostgreSQL数据库新版本中增加的特性

今天初略的看了一下POSTGRESQL9开发版的RELEASE NOTES，较以前的版本从功能上和性能上都有较大的提升，最明显的感觉是为DBA们增加的特性相当的多。

大概的改进如下:

1.继承表的权限问题一直是比较困扰的，增加一个分区难道还要重新赋权吗，改善后DBA非常开心。

2.字符处理将遵循SQL:2008标准

3.继承表，索引被动重命名规则的改进

4.PLPGSQL中不再允许不带引号的保留字作为变量名

5.增加持续归档(类似于ORACLE的standby redo log)

6.增加了恢复并同时可查询STANDBY的支持

7.增加了STANDBY的恢复进度监控函数

8.增强了继承表的查询性能

9.增加了针对表空间设置SEQ或RANDOM COST的特性（这个适合SSD和机械盘混用的存储环境哦）

10.增强了TRUNCATE性能

11.增强了is not null的索引限制。太OK了，min(),max()性能大大提升。以前这可是PG的悲哀呀。

12.优化了《》的性能

13.针对分区表的ANALYZE做了优化

14.优化了重分析时的AUTOVACUUM

15.优化了针对大小比较时的统计信息

16.优化针对表进行统计信息参数设置，太好了。终于可以定制了。

17.增加了多项安全认证的支持如RAIUS,LDAP,SAMEHOST。做安全的同志们也有事可干了。

18.在pg_stat_activity中增加了程序名的监控列。又能更清晰的定位了应用端了

19.增加了日志中的SQL STATE记录，又给DBA多了一个分析项。

20.增加统计信息计数器清理函数，这个的话应该对监控和优化又起到一个作用吧

21.服务端配置的增强，增加了基于数据库和角色混合搭配的配置，太强大了是吧，比ORACLE的profile强大哦

22.加强了SELECT FOR UPDATE/SHARE的控制颗粒，允许使用LIMIT来限制锁行数

23.加强了窗口函数，这给数据分析师们又有了一个更好的选择。

24.加速CREATE DATABASE创建速度

25.增加了CREATE TABLE LIKE中INCLUDING COMMENT，太好了COMMENT不会成为烦恼了

26.增加唯一约束的延时确认。又是一件非常省事的好事情。以前写迁移脚本需要关心表之间的关系。现在好多了

27.增加批量权限赋予，如对某个SCHEMA下的所有表的权限操作可以批量做了。

28.增强了EXPLAIN (BUFFERS)的功能

29.VACUUM FULL做了颠覆性的改进，索引不再会变大了。

30.VACUUM也变得很有弹性了，增加了自定义参数语法

31.增加了自动命名INDEX的功能

32.REINDEX增加了对系统表的支持。

33.增强了全文扫描的功能。

34.增强和增加了多项字符处理函数

35.增加pg_table_size(),pg_index_size()。这个的话以前是pg_relation_size来做的。其实变化不大

36.增加了SEQUENCE权限确认函数

37.PLPGSQL中指针的改进

38.Psql,PG_CTL等客户端的改进

详细如下:

E.1.1. Overview

ADD HERE

The above items are explained in more detail in the sections below.

E.1.2. Migration to Version 9.0

CURRENT AS OF 2010-03-15

A dump/restore using pg_dump is required for those wishing to migrate data from any previous release.

Observe the following incompatibilities:

E.1.2.1. Server Settings

Remove server variable add_missing_from, which was defaulted to off for many years (Tom Lane)

Remove server variable regex_flavor, which was defaulted to advanced (e.g. Perl-regex compatible) for many years (Tom Lane)

E.1.2.2. Queries

When querying a parent table, do not do additional permission checks on child tables returned as part of the query (Peter Eisentraut)

The SQL standard specifies this behavior.

Have fractional seconds conversion truncate rather than round when using float-based dates/times (Tom Lane)

E.1.2.3. String Handling

Improve standards compliance of SIMILAR TO patterns and SQL-style substring() patterns (Tom Lane)

This includes treating ? and {…} as pattern metacharacters, while they were simple literal characters before; that corresponds to new features added in SQL:2008. Also, ^ and $ are now treated as simple literal characters; formerly they were treated as metacharacters, as if the pattern were following POSIX rather than SQL rules. Also, in SQL-standard substring(), use of parentheses for nesting no longer interferes with capturing of a substring. Also, processing of bracket expressions (character classes) is now more standards-compliant.

Reject negative length values in 3-parameter substring() for bit strings, per the SQL standard (Tom Lane)

E.1.2.4. Object Renaming

Tighten enforcement of column renaming when a child table inherits the renamed column from an unrelated parent (KaiGai Kohei)

No longer rename index names and index column names when table columns are renamed (Tom Lane)

Administrators still can rename such columns manually. Expression index names are also not renamed. WHY ARE EXPRESSION INDEXES SIGNIFICANT?

E.1.2.5. PL/pgSQL Variables

Have PL/pgSQL throw an error if a variable name conflicts with a column name used in a query (Tom Lane)

This behavior can be changed via the server variable plpgsql.variable_conflict, or by the per-function option #variable_conflict. The former behavior was to bind to variable names over column names.

Remove PL/pgSQL’s RENAME declaration option (Tom Lane)

Instead, use ALIAS, which can now alias any variable, not just dollar sign variables, e.g. $1.

PL/pgSQL no longer allows unquoted variables names that match SQL reserved words (Tom Lane)

Variables can be double-quoted to avoid this restriction.

E.1.3. Changes

E.1.3.1. Server

E.1.3.1.1. Continuous Archiving

Allow continuous archive standby systems to accept read-only queries (Simon, Heikki)

This feature is called Hot Standby. There are new postgresql.conf and recovery.conf settings to enable this feature, as well as extensive documentation.

Allow continuous archive (WAL) files to be streamed to a standby system (Fujii Masao, Heikki)

Previously WAL files could be sent to standby systems only as 16 megabytes files; this allows master changes to be sent to the slave with very little delay. There are new postgresql.conf and recovery.conf settings to enable this feature, as well as extensive documentation.

Add pg_last_xlog_receive_location() and pg_last_xlog_replay_location(), which can be used to monitor standby server WAL activity (Simon, Fujii Masao, Heikki)

E.1.3.1.2. Performance

Improve performance of finding inherited child tables (Tom Lane)

Allow per-tablespace sequential and random page cost variables (seq_page_cost/random_page_cost) via ALTER TABLESPACE … SET/RESET (Robert Haas)

Improve performance of TRUNCATE when used in the same transaction as table creation (Tom Lane)

E.1.3.1.3. Optimizer

Allow IS NOT NULL restrictions to use indexes (Tom Lane)

This is particularly useful for finding MAX()/MIN() values in indexes that also contain NULLs.

Remove unnecessary outer joins (Robert Haas)

Outer joins where the inner side is unique and not referenced in the query are unnecessary and are therefore now removed.

Improve optimizer equivalence detection of <> boolean tests (Tom Lane)

E.1.3.1.4. GEQO

Use the same random seed everytime GEQO plans a query (Andres Freund)

While the Genetic Query Optimizer (GEQO) still selects random plans, it now selects the same random plans for identical queries. You can modify geqo_seed to randomize the starting value of the random plan generator.

Improve GEQO plan selection (Tom Lane).

This avoids the rare error, “failed to make a valid plan”.

E.1.3.1.5. Optimizer Statistics

Improve ANALYZE to support inheritance-tree statistics (Tom Lane)

This is particularly useful for partitioned tables.

Improve autovacuum detection of when re-analyze is necessary (Tom Lane)

Improve optimizer statistics for greater/less-than comparisons (Tom Lane)

When looking up optimizer statistics for greater/less-than comparisons, if the comparison value is in the first or last histogram bucket, use an index to generate the actual statistics. This is particularly useful for columns that are always increasing, and hence often have inaccurate statistics.

Allow setting of distinct statistics using ALTER TABLE (Robert Haas)

This allows user-override of the number or percentage of distinct values for a column and optionally its child tables. This value is normally computed by ANALYZE.

E.1.3.1.6. Authentication

Add support for RADIUS (Remote Authentication Dial In User Service) authentication (Magnus Hagander)

Allow LDAP (Lightweight Directory Access Protocol) authentication to operate in “search/bind” mode (Robert Fleming, Magnus)

This allows the user to be looked up first, then the system uses the DN (Distinguished Name) returned for that user.

Add samehost and samenet designations to pg_hba.conf (Stef Walter)

These match the server’s IP address and network address respectively.

E.1.3.1.7. Monitoring

Add the ability for clients to set an application name, which is displayed in pg_stat_activity (Dave Page)

Add an SQL state option (%e) to log_line_prefix (Guillaume Smet)

Write to the Windows event log in UTF16 encoding (Itagaki Takahiro)

E.1.3.1.8. Statistics Counters

Add pg_stat_reset_shared(‘bgwriter’) to reset the cluster-wide shared statistics of the bgwriter (Greg Smith)

Add pg_stat_reset_single_table_counters() and pg_stat_reset_single_function_counters() to allow the reseting of statistics counters for individual tables and indexes (Magnus Hagander)

E.1.3.1.9. Server Settings

Allow setting of configuration variables based on database/role combinations (Alvaro Herrera)

Previously only per-database and per-role setting were possible, not combinations. All role and database settings are now stored in the new pg_db_role_setting system table. A new psql \drds command shows these settings. Backwards-compatible system views do not show this information.

Add boolean variable bonjour, which controls whether a Bonjour-enabled binary advertises itself via Bonjour (Tom Lane)

The default is off, meaning it does not advertise.

Log changed parameter values when postgresql.conf is reloaded (Peter Eisentraut)

E.1.3.2. Queries

Do SELECT FOR UPDATE/SHARE processing after applying LIMIT, so the number of rows returned is always predictable (Tom Lane)

Previously, concurrent transactions could potentially cause SELECT to return fewer rows than specified by LIMIT. FOR UPDATE can still affect ORDER BY ordering, but this can be corrected by using FOR UPDATE in a subquery.

Allow mixing of traditional and SQL-standard LIMIT/OFFSET syntax in the same query (Tom Lane)

Increase the supported frame options in window functions (Hitoshi Harada)

This allows frames (RANGE or ROWS) to start with CURRENT ROW, and to use the ROWS n PRECEDING/FOLLOWING clause.

Have SELECT and CREATE TABLE AS return row counts to the client (Boszormenyi Zoltan)

psql does not display these counts.

E.1.3.2.1. Unicode Strings

Add Unicode surrogate pair (dual 16-bit) support to U& strings and identifiers (Peter Eisentraut)

Allow Unicode escapes in E’…’ strings (Marko Kreen)

E.1.3.3. Object Manipulation

Speed up CREATE DATABASE by deferring flushes to disk (Andres Freund, Greg Stark)

Allow comments only on columns of tables, views, and composite types, not other objects like indexes and TOAST tables (Tom Lane)

Allow the creation of enumerate types with no labels (Bruce Momjian)

This is useful for supporting binary upgrades.

Have columns defined with storage type MAIN remain on the main heap page unless it cannot fit (Kevin Grittner)

Previously MAIN values were forced to TOAST tables until the row size was one-quarter of the page size.

E.1.3.3.1. ALTER

Add ALTER DEFAULT PRIVILEGES command to control privileges of all newly-created objects (Petr Jelinek)

It currently supports tables, views, sequences, and functions, and also has per-schema control.

Implement IF EXISTS for DROP COLUMN and DROP CONSTRAINT (Andres Freund)

Allow ALTER TABLE commands which rewrite tables to skip WAL logging (Itagaki Takahiro)

Such operations either complete fully or are rolled back, so WAL archiving can be skipped, unless running in continuous archiving mode.

E.1.3.3.2. CREATE TABLE

Add support for copying COMMENTS and STORAGE to the CREATE TABLE … LIKE INCLUDING command (Itagaki Takahiro)

Add support for copying all attributes to the CREATE TABLE LIKE INCLUDING command (Itagaki Takahiro)

Add the SQL-standard CREATE TABLE … OF type command (Peter Eisentraut)

This allows the creation of a table to match an existing composite type. Additional constraints and defaults can be specified in the command.

E.1.3.3.3. Constraints

Add deferrable unique constraints (Dean Rasheed)

This allows UPDATE tab SET col = col + 1 to work on columns that have a unique indexes or are marked as primary keys, but DEFERRABLE INITIALLY DEFERRED must be used to mark the constraint as deferred.

Generalize uniqueness constraints by allowing arbitrary operator comparisons, not just equality (Jeff Davis)

This is enabled with the CREATE TABLE CONSTRAINT … EXCLUDE clause. While uniqueness checks could be specified using this syntax, the real value of this feature is in using complex operators that do not have built-in constraints.

Improve the constraint violation error message to report the values causing the failure (Itagaki Takahiro)

For example, a uniqueness constraint violation now reports Key (x)=(2) already exists.

E.1.3.3.4. Object Permissions

Add the ability to make mass permission changes per schema using the new GRANT/REVOKE IN SCHEMA clause (Petr Jelinek)

Add the ability to control large object permissions with GRANT/REVOKE(KaiGai Kohei)

E.1.3.4. Utility Operations

Have LISTEN/NOTIFY store events in a memory queue, rather than a system table (Joachim Wieland)

This greatly improves performance for these operations.

Allow NOTIFY to pass an optional string to listeners (Joachim Wieland)

Allow CLUSTER on all system tables (Tom Lane)

Global system tables still cannot be clustered.

E.1.3.4.1. COPY

Allow * as a parameter in FORCE QUOTE for COPY CSV (Itagaki Takahiro)

This forces quotes for all CSV output columns.

Add new COPY syntax that allows parameters to be specified in parentheses (Robert Haas, Emmanuel Cecchet)

This allows greater flexibility for future COPY options. The old syntax is still supported.

E.1.3.4.2. EXPLAIN

Add new EXPLAIN (BUFFERS) to report query buffer activity (Itagaki Takahiro)

log_*_stats log output, e.g. log_statement_stats, no longer shows this information.

Add hash usage information to EXPLAIN output (Robert Haas)

Allow EXPLAIN output in XML, JSON, and YAML formats (Robert Haas, Greg Sabino Mullane)

Allow EXPLAIN options to be specified inside parentheses (Robert Haas)

This allows for the expansion of EXPLAIN options. The old syntax is still supported.

E.1.3.4.3. VACUUM

Change VACUUM FULL to rewrite the entire table and indexes, rather than moving around single rows to compact space (Itagaki Takahiro, Tom)

The previous method was usually slower and caused index bloat.

Add new VACUUM syntax that allows parameters to be specified in parentheses (Itagaki Takahiro)

This allows greater flexibility for future VACUUM options. The old syntax is still supported.

E.1.3.4.4. Indexes

Allow an index to be auto-named by not supplying an index name to CREATE INDEX (Tom Lane)

Allow REINDEX on system indexes (Tom Lane)

WAS THIS POSSIBLE ON ANY SYSTEM TABLE BEFORE? NON-HARDWIRED ONES?

Add point_ops opclass for GiST (Teodor Sigaev)

DETAILS?

Use red-black trees for GIN index creation (Teodor Sigaev)

Red-black trees are self-balanced so allow for faster index creation.

E.1.3.5. Data Types

Allow hex values to be specified in bytea strings (Peter Eisentraut)

The variable bytea_output controls if hex (default) or octal escapes are used for bytea output. (SWITCH DEFAULT FOR BETA? PETER) Libpq’s PQescapeByteaConn() now uses the hex format for PostgreSQL 9.0 servers.

Allow extra_float_digits to be increased to 3 (Tom Lane)

The previous maximum extra_float_digits was 2.

E.1.3.5.1. Full Text Search

Add prefix support for the full text search synonym dictionary (Teodor Sigaev)

Add full text search filtering dictionaries (Teodor Sigaev)

Filtering dictionaries allow tokens to be modified and passed to subsequent dictionaries.

Allow underscores in full text email addresses (Teodor Sigaev)

E.1.3.6. Functions

Allow case-insensitive regular expression matching with UTF-8 server encodings.

Previously, only ASCII characters and single-byte encodings worked properly. Other multi-byte, non-UTF-8 encodings are still broken for case-insensitive regular expression matching.

Allow function calls to supply parameter names and match them to named parameters in the function definition (Pavel Stehule)

For example, if a function is defined to take parameters a and b, it can be called with func(7 AS a, 12 AS b) or func(12 AS b, 7 AS a).

Add support for to_char() scientific notation output (‘EEEE’) (Pavel Stehule, Brendan Jurd)

Have to_char() honor ‘FM’ (fill mode) in ‘Y’, ‘YY’, and ‘YYY’ specifications (Bruce Momjian)

It was already honored by ‘YYYY’.

Correct calculations of “overlap” and “contains” operations over polygons (Teodor Sigaev)

E.1.3.6.1. Aggregates

Allow aggregate functions to use ORDER BY (Andrew Gierth)

For example, this is now supported, array_agg(a ORDER BY b). This is useful for aggregates where the order of values is significant.

Add the string_agg() aggregate function which aggregates values into a single string (Pavel Stehule)

An optional second argument allows specification of a delimiter.

Aggregate functions that are called with DISTINCT are now passed NULL values if the aggregate transition function is not marked as STRICT (Andrew Gierth)

For example, agg(DISTINCT x) might pass NULL x values to agg().

E.1.3.6.2. Bit Strings

Add get_bit() and set_bit() functions for bit strings, mirroring those for bytea (Leonardo F)

Implement OVERLAY() (replace) for bit strings and bytea (Leonardo F)

E.1.3.6.3. Object Information Functions

Add pg_table_size() and pg_indexes_size() to provide a more user-friendly interface to the pg_relation_size() function (Bernd Helmle)

Add has_sequence_privilege() for sequence permission checking (Abhijit Menon-Sen)

Have information schema properly display date type octet lengths (Peter Eisentraut)

The reported length is now the maximum octet length; previously, a huge value was reported.

Speed up information schema privilege views (Joachim Wieland)

E.1.3.6.4. Function and Trigger Creation

Implement anonymous functions using the DO statement (Petr Jelinek, Joshua Tolley, Hannu Valtonen)

This allows execution of server-side code without the need to create a new function and execute it.

Allow SQL-compliant per-column triggers (Itagaki Takahiro)

Such triggers are fired only if the specified columns are affected by the query, e.g. in UPDATE’s SET list. information_schema now also shows this information.

Add WHERE clause to CREATE TRIGGER to allow control over whether a trigger is fired (Takahiro Itagaki)

While a check can be performed inside the trigger, doing it in an external WHERE clause has performance benefits.

E.1.3.7. Server-Side Languages

Add the OR REPLACE clause to CREATE LANGUAGE (Tom Lane)

This is helpful to optionally install a language if it does not already exist, and is particularly helpful now that PL/pgSQL is installed by default.

E.1.3.7.1. PL/PgSQL Server-Side Language

Install server-side language PL/pgSQL by default (Bruce Momjian)

Allow PL/pgSQL to handle row types with dropped columns (Pavel Stehule)

Allow IN parameters to be assigned values within PL/pgSQL functions (Steve Prentice)

Improve error location reporting in PL/pgSQL (Tom Lane)

Have PL/pgSQL use the main lexer, rather than a custom version (Tom Lane)

E.1.3.7.2. PL/pgSQL Cursors

Add count and ALL options to MOVE FORWARD/BACKWARD in PL/pgSQL (Pavel Stehule)

Allow PL/pgSQL’s WHERE CURRENT OF to use a cursor variable (Tom Lane)

Add PL/pgSQL’s OPEN cursor FOR EXECUTE to use parameters (Pavel Stehule, Itagaki Takahiro)

This is accomplished with a new USING clause.

E.1.3.7.3. PL/Perl Server-Side Language

Add new PL/Perl functions: quote_literal(), quote_nullable(), quote_ident(), encode_bytea(), decode_bytea(), looks_like_number(), encode_array_literal(), encode_array_constructor() (Tim Bunce)

Add server variable plperl.on_init to specify a PL/Perl Perl initialization function (Tim Bunce)

plperl.on_plperl_init and plperl.on_plperlu_init are also available for trusted/untrusted-specific initialization.

Improve error context support in PL/Perl (Alexey Klyukin)

Support END blocks in PL/Perl (Tim Bunce)

END blocks do not currently allow database access.

Allow use strict in PL/Perl (Tim Bunce)

This can be enabled with the server variable plperl.use_strict.

Allow require in PL/Perl (Tim Bunce)

This basically tests to see if the module is loaded, and if not, generates an error.

Allow use feature in PL/Perl if Perl version 5.10 or later is used (Tim Bunce)

Verify that PL/Perl return values are valid in the server encoding (Andrew Dunstan)

Improve PL/Perl code structure (Tim Bunce)

PL/Perl subroutines are now given names (Tim Bunce)

This is for the use of profiling and code coverage tools. DIDN’T THEY HAVE NAMES BEFORE?

PL/Perl no longer allows Safe version 2.20 because it is unsafe (Tim Bunce)

E.1.3.7.4. PL/Python Server-Side Language

Add Unicode support in PL/Python (Peter Eisentraut)

Strings are automatically converted from/to the server encoding as necessary.

Improve bytea support in PL/Python (Caleb Welton)

Bytea values passed into PL/Python now are represented as binary, rather than the Postgres bytea text format. Null bytes are now also output properly from PL/Python. Boolean and numeric value passing in PL/Python was also improved.

Add array parameter/return support to PL/Python (Peter Eisentraut)

Improve mapping of domains to Python base types in PL/Python (Peter Eisentraut)

Add Python 3 support to PL/Python (Peter Eisentraut)

The new server-side language is called plpython3u.

Improve error location and exception reporting in PL/Python (Peter Eisentraut)

E.1.3.8. Client Applications

Add vacuumdb ?analyze-only option to only analyze (Bruce Momjian)

E.1.3.8.1. psql

Properly escape psql variables and identifiers (Pavel Stehule, Robert Haas)

For example, :’var’ will be escaped as a literal string, and :”var” will be escaped as an SQL identifier.

Ignore leading UTF-8-encoded Unicode byte-order marker in psql (Itagaki Takahiro)

This is enabled when the client encoding is UTF-8.

Fix psql ?file ? to properly honor ?single-transaction (Bruce Momjian)

Prevent overwriting of psql’s command-line history if two psql sessions are run simultaneously (Tom Lane)

Improve psql’s tab completion support (Itagaki Takahiro)

E.1.3.8.1.1. psql Display

Allow psql to use fancy Unicode line-drawing characters via \pset linestyle unicode (Roger Leigh)

Improve display of wrapped columns in psql (Roger Leigh)

The previous format is available by using \pset linestyle old-ascii.

E.1.3.8.1.2. psql \d Commands

Have \d show child tables that inherit from the specified parent (Damien Clochard)

\d shows only the number of child tables, while \d+ shows the names of all child tables.

Show definition of indexes in \d index_name (Khee Chin)

The definition is useful for expression indexes.

In psql, show the view definition only with \d+, not with \d (Peter Eisentraut)

E.1.3.8.2. pg_dump

Have pg_dump/pg_restore ?clean also remove large objects (Itagaki Takahiro)

Fix pg_dump to properly dump large objects if standard_conforming_strings is enabled (Tom Lane)

Large objects dumps now use hex format for output. (SWITCH DEFAULT FOR BETA? TOM)

Allow pg_dump to dump comments attached to columns of composite types (Taro Minowa (Higepon))

Have pg_dump ?verbose output the pg_dump and server versions in text output mode (Jim Cox, Tom)

These were already present in custom output mode.

E.1.3.8.3. pg_ctl

Allow pg_ctl to be safely used to start the postmaster at boot-time (Tom Lane)

Previously the pg_ctl process could have been mistakenly identified as a running postmaster based on a stale postmaster lock file.

Give pg_ctl the ability to initialize the database (like initdb) (Zdenek Kotala)

E.1.3.9. Development Tools

E.1.3.9.1. libpq

Add new libpq functions PQconnectdbParams() and PQconnectStartParams() (Guillaume Lelarge)

These functions are similar to PQconnectdb() and PQconnectStart() except they allow a null-terminated array of connection options, rather than requiring all options to be sent in a single string.

Add libpq functions PQescapeLiteral() and PQescapeIdentifier() (Robert Haas)

These functions return appropriately quoted and escaped literal strings and identifiers. The caller is not required to pre-allocate the string result, as is required by PQescapeStringConn().

Add checking for a per-user service file (.pg_service.conf), which is checked before the site-wide service file (Peter Eisentraut)

The file .pg_service.conf is assumed to be in the user’s home directory.

Properly report an error if the specified libpq service cannot be found (Peter Eisentraut)

Issue a warning if the .pgpass-retrieved password fails (Bruce Momjian)

E.1.3.9.2. ecpg

Add SQLDA (SQL Descriptor Area) support to ecpg (Boszormenyi Zoltan)

Add the DESCRIBE [OUTPUT] statement to ecpg (Boszormenyi Zoltan)

Add an ecpg function to return the current transaction status (Bernd Helmle)

Add the string data type in ecpg Informix-compatibility mode (Zoltan Boszormenyi)

Allow ecpg to use new and old variable names without restriction (Michael Meskes)

Have ecpg return zero for non-SQL3 data types (Michael Meskes))

Previously it returned the negative of the data type oid.

E.1.3.9.2.1. ecpg Cursors

Add ecpg out-of-scope cursor support in native mode (Boszormenyi Zoltan)

This allows DECLARE to use variables that are not in scope when OPEN is called. This facility already existed in ecpg Informix-compatibility mode.

Allow dynamic ecpg cursor names (Boszormenyi Zoltan)

Allow ecpg to use noise words FROM and IN in FETCH and MOVE (Zoltan Boszormenyi)

E.1.3.10. Build Options

Enable client thread safety by default (Bruce Momjian)

Thread-safe builds can be disabled with configure ?disable-thread-safety.

Add a compile-time option to allow the Linux out-of-memory killer to kill backends (Alex Hunsaker, Tom)

Now that /proc/self/oom_adj allows disabling of the Linux out-of-memory (OOM) killer for the postmaster and its children, the new compile-time option -DLINUX_OOM_ADJ=0 allows the killer to be enabled for postmaster children. pg_config shows if this flag was used during compilation.

Use DocBook XSL stylesheets for man page building (Peter Eisentraut)

E.1.3.10.1. Makefiles

New Makefile targets world, install-world, and installcheck-world (Andrew Dunstan)

These are similar to the existing all, install, and installcheck targets, but they build HTML documentation, build and test /contrib, and test server-side languages and ecpg.

Add data and documentation location control to PGXS Makefiles (Mark Cave-Ayland)

Restructure the HTML documentation build Makefile rules (Peter Eisentraut)

E.1.3.10.2. New Requirements

Require Autoconf 2.63 for building from source (Peter Eisentraut)

Require Flex 2.5.31 or later to build from source (Tom Lane)

Require Perl version 5.8 or greater to build the server from a CVS copy (John Naylor, Andrew)

E.1.3.10.3. Windows

Add support for compiling on 64-bit Windows and running in 64-bit mode (Tsutomu Yamada, Magnus)

This allows for large shared memory sizes on Windows.

Allow server builds using Visual Studio 2008 (Magnus Hagander)

Allow multi-processor compilation using Microsoft Visual C (Magnus Hagander)

E.1.3.11. Source Code

Distribute documentation in a proper directory tree, rather than as tar archive files inside the main distribution tarball (Peter Eisentraut)

For example, the HTML documentation is now in doc/src/sgml/html; the manual pages are packaged similarly.

Enable the server lexer to be reentrant (Tom Lane)

This was needed for use of the lexer by PL/pgSQL.

Improve speed of memory allocation (Tom, Greg Stark)

Add system columns to better document the use of indexes for constraint enforcement (Tom Lane)

Allow multiple actions to be communicated using the same operating system signal (Fujii Masao)

This allows improved backend communication as new features are added.

Improve source code test coverage, including /contrib, PL/Python, and PL/Perl (Peter, Andrew)

Remove the use of flat files for system table bootstrapping (Tom, Alvaro)

This also improves performance when using millions of users and databases.

Improve the ability to translate psql strings (Peter Eisentraut)

Reduce the length of some file names so file paths are less than 100 characters (Tom Lane)

Some decompression programs have problems with long file names.

Tighten input requirements for int2 vector input (Caleb Welton)

Add a new ERRCODE_INVALID_PASSWORD SQLSTATE error code (Bruce Momjian)

Properly remove the few remaining personal source code copyright entries (Bruce Momjian)

The personal copyright notices were insignificant but the community occasionally had to answer questions about them.

E.1.3.11.1. Feature Support

Use a more modern API for Bonjour (Tom Lane)

Bonjour now requires OS X 10.3 or later.

Add processor test-and-test lock support for the SuperH architecture (Nobuhiro Iwamatsu)

Allow non-GCC compilers to use inline functions if they support them (Kurt Harriman)

Remove support for platforms that don’t have a working 64-bit integer data types (Tom Lane)

It is believed all supported platforms have working 64-bit integer data types.

E.1.3.11.2. Server Programming

Allow use of C++ functions in backend code (Kurt Harriman, Peter)

This removes keyword conflicts that previously made C++ usage difficult in backend code. extern “C” { } might still be necessary.

Add AggCheckCallContext() for use in detecting if a C function is being called as an aggregate (Hitoshi Harada)

Require fastgetattr() and heap_getattr() backend macros to use a non-NULL fourth argument (Robert Haas) KEEP?

E.1.3.11.3. Server Hooks

Add parser hooks to access column and parameter references in queries (Tom Lane)

Add a hook so loadable modules can control utility commands (Itagaki Takahiro)

Allow the calling of parser hooks from SPI and cached plans (Tom Lane)

E.1.3.11.4. Binary Upgrade Support

Add support for preservation of all relfilenodes, for use during binary upgrades (Bruce Momjian)

Add support for binary upgrades to preserve pg_type and pg_enum oids (Bruce Momjian)

This is needed to allow binary upgrades of user-defined composite types, arrays, and enums (enumerated types).

Move tablespace data directories into their own PostgreSQL version-specific subdirectory (Bruce Momjian)

This simplifies binary upgrades.

E.1.3.12. Contrib

Add multi-threaded option (-j) to /contrib/pgbench (Itagaki Takahiro)

This allows multiple CPUs to be used for pgbench tests.

Add \shell and \setshell meta commands to /contrib/pgbench (Michael Paquier)

New features for /contrib/dict_xsyn (Sergey Karpov)

The new options are matchorig, matchsynonyms, and keepsynonyms.

Add full text dictionary /contrib/unaccent (Teodor Sigaev)

This filter dictionary removes accents from tokens.

Add dblink_get_notify() to /contrib/dblink (Marcus Kempe)

This allows async notifications in dblink.

Greatly increase /contrib/hstore’s length limit and add btree and hash abilities so GROUP BY and DISTINCT operations are possible (Andrew Gierth)

New functions and operators were also added.

Add /contrib/passwordcheck which can check the strength of assigned passwords (Laurenz Albe)

The source code of this module should be modified to implement site-specific password policies.

Add query text to /contrib/auto_explain output (Andrew Dunstan)

Add buffer access counters to /contrib/pg_stat_statements (Itagaki Takahiro)

Update /contrib/start-scripts/linux to use /proc/self/oom_adj to disable the Linux out-of-memory (OOM) killer (Alex Hunsaker, Tom)