banzengphp1.php,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)