PostgreSQL 2010年1月第1周新闻综述 -- 2010/01/03

From PostgreSQL 中文维基, PostgreSQL 中文站, PostgreSQL 中国社区, PostgreSQL Chinese community

PostgreSQL 8.5 alph3版本的RPM格式安装包已制作完成，感谢Devrim的努力工作，请各位测试，下载网址：http://yum.pgsqlrpms.org/news-8.5alpha-packages-ready-for-testing.php

[编辑] PostgreSQL相关软件新闻

Benetl 3.3版本发布，这是一个用于PostgreSQL的ETL工具，相关网址：http://www.benetl.net

Dataware Quick Application 套件1.1版本发布，这是一个支持PostgreSQL的ＲＡＤ（快速应用程序开发）工具，相关网址：http://www.datawarestudio.com

Npgsql 2.0.8版本发布，这是一个访问PostgreSQL数据库的.NET数据驱动，相关网址：http://www.npgsql.org

psqlODBC 08.04.0200版本发布，这是用于PostgreSQL的ODBC数据驱动，下载网址：http://psqlodbc.projects.postgresql.org/release.html

[编辑] PostgreSQL 8.5 本周特性介绍

在psql中，执行 \d指令现在可以显示一个父表有多少个子表继承了它，使用\d+指令则可以显示这些子表。

[编辑] PostgreSQL一月相关工作机会

详情可浏览：http://archives.postgresql.org/pgsql-jobs/2010-01/threads.php

[编辑] PostgreSQL各地新闻

纽约首都地区的PostgreSQL用户协会第一次会议将于2010/1/7举行，相关网址：http://nycdpug.x10hosting.com/

Stefan Keller将发布一个关于“Introducion to PostGIS/PostgreSQL（PostGIS/PostgreSQL概述）“的培训，地点是在瑞士苏黎世的Rapperswil应用科学院 (HSR) ，时间为2010/1/14，相关网址：http://www.gis.hsr.ch/wiki/Agenda

2009/01/23将于澳大利亚的惠灵顿举行Linux开放日，现寻求展位志愿人员，志愿人员请通过selena -at- postgresql.org 联系。

PostgreSQL东部大会将于2010/01/30举行，现正在征集论文中，详情可浏览：http://www.postgresqlconference.org/

古巴PostgreSQL活动日将于2010/02/4--6在哈瓦那的科技大学举行，参加人员主要来自古巴国内，而多米尼加和委内瑞拉等国人员也欢迎参加。

2010年开源软件大会FOSDEM将于2010/2/6--7在比利时的布鲁塞尔举行，相关网址：http://www.fosdem.org/

德国PostgreSQL用户协会从2010/03/02--06在汉诺威的2010电子大展上（Cebit）的开源广场上申请了一个展位，相关信息：http://www.cebit.de/

Chemnitzer Linuxtage大展将于2010/3/13--14在德国的开姆尼茨举行，相关网址：http://chemnitzer.linux-tage.de/

PgCon 2010大展将于2010/5/20--21在渥太华举行，在之前的18-19号将会有一些培训，论文征集活动已开始，相关网址：http://www.pgcon.org/2010/papers.php

2010年度的开源软件大会开始征集论文，大会将于2010/07/19--23在波特兰举行，相关网址：http://post.oreilly.com/f2f/9z1zqmm5lhkab0uogt3avlvc4u59bro6f917re423d8

[编辑] PostgreSQL其他信息

PostgreSQL日志站点： http://planet.postgresql.org/

PostgreSQL本周新闻综述是由David Fetter带给我们大家的，谢谢他的工作。

如果您想提交一些新闻或是软件发布稿等内容可在每周星期天的下午3:00（太平洋时区）前，英文版的内容可发送至 david@fetter.org，德文版本的内容可发送至pwn@pgug.de，意大利语版本的内容可发至pwn@itpug.org 。

[编辑] 本周审核的补丁

Robert Haas reviewed Pavel Stehule's patch to enable single- and double-quoting behavior for psql variables.

Robert Haas reviewed ITAGAKI Takahiro's patch to add buffer usage columns to contrib/pg_stat_statements.

Robert Haas reviewed the patch implementing red-black tree for GIN byTeodor Sigaev.

Jaime Casanova reviewed Robert Haas's patch to add per-tablespace random_page_cost and seq_page_cost settings.

[编辑] 本周打入系统的补丁

Bruce Momjian committed:

- In pgsql/src/backend/port/win32/mingwcompat.c, remove non-ascii characters from source code.

- Add PGDLLIMPORT for binary_upgrade global variables so shared object libraries can access them.

- Remove PGDLLIMPORT used for binary upgrade; must be on the externs, per Tom.

Peter Eisentraut committed:

- In pgsql/doc/src/sgml/plpgsql.sgml, small wording improvement and

clarification in PL/pgSQL trigger documentation

- In pgsql/doc/src/sgml/Makefile, revert makefile refactoring (version

1.123) because it doesn't work when building several files at once
(e.g., gmake postgres-A4.pdf postgres-US.pdf).

- In pgsql/src/backend/catalog/sql_features.txt, update SQL

conformance: search conditions on triggers are supported

- Fill in information schema column for trigger WHEN condition.

- Add information_schema.triggered_update_columns. This reflects the

recently added support for triggers on columns.

- In pgsql/src/backend/catalog/sql_features.txt, update SQL features

list for aggregate ORDER BY support

- In pgsql/src/backend/catalog/sql_features.txt, update SQL features

supported list.

Heikki Linnakangas committed:

- Previous fix for temporary file management broke returning a set

from PL/pgSQL function within an exception handler.  Make sure we
use the right resource owner when we create the tuplestore to hold
returned tuples.  Simplify tuplestore API so that the caller doesn't
need to be in the right memory context when calling tuplestore_put*
functions.  tuplestore.c automatically switches to the memory
context used when the tuplestore was created.  Tuplesort was already
modified like this earlier.  This patch also removes the now useless
MemoryContextSwitch calls from callers.  Report by Aleksei on
pgsql-bugs on December 22, 2009.  Backpatch to 8.1, like the
previous patch that broke this.

- In, pgsql/contrib/tablefunc/tablefunc.c, oops, previous backpatch

applied incorrectly.

- In pgsql/contrib/tablefunc/tablefunc.c, remove a now unused local

variable.

Tom Lane committed:

- Add the ability to store inheritance-tree statistics in

pg_statistic, and teach ANALYZE to compute such stats for tables
that have subclasses.  Per my proposal of yesterday.  autovacuum
still needs to be taught about running ANALYZE on parent tables when
their subclasses change, but the feature is useful even without
that.

- Add an index on pg_inherits.inhparent, and use it to avoid seqscans

in find_inheritance_children().  This is a complete no-op in
databases without any inheritance.  In databases where there are
just a few entries in pg_inherits, it could conceivably be a small
loss.  However, in databases with many inheritance parents, it can
be a big win.

- Set errno to zero before invoking SSL_read or SSL_write. It appears

that at least in some Windows versions, these functions are capable
of returning a failure indication without setting errno.  That puts
us into an infinite loop if the previous value happened to be EINTR.
Per report from Brendan Hill.  Back-patch to 8.2.  We could take it
further back, but since this is only known to be an issue on Windows
and we don't support Windows before 8.2, it does not seem worth the
trouble.

- Revise pgstat's tracking of tuple changes to improve the reliability

of decisions about when to auto-analyze.  The previous code depended
on n_live_tuples + n_dead_tuples - last_anl_tuples, where all three
of these numbers could be bad estimates from ANALYZE itself.  Even
worse, in the presence of a steady flow of HOT updates and matching
HOT-tuple reclamations, auto-analyze might never trigger at all,
even if all three numbers are exactly right, because n_dead_tuples
could hold steady.  To fix, replace last_anl_tuples with an
accurately tracked count of the total number of committed tuple
inserts + updates + deletes since the last ANALYZE on the table.
This can still be compared to the same threshold as before, but it's
much more trustworthy than the old computation.  Tracking this
requires one more intra-transaction counter per modified table
within backends, but no additional memory space in the stats
collector.  There probably isn't any measurable speed difference; if
anything it might be a bit faster than before, since I was able to
eliminate some per-tuple arithmetic operations in favor of adding
sums once per (sub)transaction.  Also, simplify the logic around
pgstat vacuum and analyze reporting messages by not trying to fold
VACUUM ANALYZE into a single pgstat message.  The original thought
behind this patch was to allow scheduling of analyzes on parent
tables by artificially inflating their changes_since_analyze count.
I've left that for a separate patch since this change seems to stand
on its own merit.

- In pgsql/src/backend/commands/analyze.c, dept of second thoughts:

recursive case in ANALYZE shouldn't emit a pgstats message.  This
might need to be done differently later, but with the current logic
that's what should happen.

- Redefine Datum as uintptr_t, instead of unsigned long. This is more

in keeping with modern practice, and is a first step towards porting
to Win64 (which has sizeof(pointer) > sizeof(long)).  Tsutomu
Yamada, Magnus Hagander, Tom Lane.

- In pgsql/src/backend/storage/ipc/procarray.c, add missing 'static'

tag.

- In pgsql/src/backend/storage/ipc/standby.c, suppress compiler

warning (pid_t isn't int everywhere).

- In pgsql/src/backend/libpq/auth.c, suppress compiler warning, per

buildfarm member narwhal.

- Support "x IS NOT NULL" clauses as indexscan conditions. This turns

out to be just a minor extension of the previous patch that made "x
IS NULL" indexable, because we can treat the IS NOT NULL condition
as if it were "x < NULL" or "x > NULL" (depending on the index's
NULLS FIRST/LAST option), just like IS NULL is treated like "x =
NULL".  Aside from any possible usefulness in its own right, this is
an important improvement for index-optimized MAX/MIN aggregates: it
is now reliably possible to get a column's min or max value cheaply,
even when there are a lot of nulls cluttering the interesting end of
the index.

- Add an "argisrow" field to NullTest nodes, following a plan made way

back in 8.2beta but never carried out.  This avoids repetitive tests
of whether the argument is of scalar or composite type.  Also, be a
bit more paranoid about composite arguments in some places where we
previously weren't checking.

- check_exclusion_constraint didn't actually work correctly for index

expressions: FormIndexDatum requires the estate's scantuple to
already point at the tuple the values are supposedly being extracted
from.  Adjust test case so that this type of confusion will be
exposed.  Per report from hubert depesz lubaczewski.

- In pgsql/src/backend/utils/adt/regexp.c, fix similar_escape() to

convert parentheses to non-capturing style.  This is needed to avoid
unwanted interference with SUBSTRING behavior, as per bug #5257 from
Roman Kononov.  Also, add some basic intelligence about character
classes (bracket expressions) since we now have several behaviors
that aren't appropriate inside a character class.  As with the
previous patch in this area, I'm reluctant to back-patch since it
might affect applications that are relying on the prior behavior.

- In pgsql/src/bin/psql/tab-complete.c, add missing

schema-qualification in tab completion query.

- In pgsql/src/backend/access/nbtree/nbtutils.c, dept of second

thoughts: my first cut at supporting "x IS NOT NULL" btree
indexscans would do the wrong thing if index_rescan() was called
with a NULL instead of a new set of scankeys and the index was DESC
order, because sk_strategy would not get flipped a second time.  I
think that those provisions for a NULL argument are dead code now as
far as the core backend goes, but possibly somebody somewhere is
still using it.  In any case, this refactoring seems clearer, and
it's definitely shorter.

Robert Haas committed:

- Reject invalid input in int2vectorin. Since the int2vector type is

intended only for internal use, this patch doesn't worry about
prettifying the error messages, which has the fringe benefit of
avoiding creating additional translatable strings.  For a type
intended to be used by end-users, we would want to do better, but
the approach taken here seems like the correct trade-off for this
case.  Caleb Welton.

Heikki Linnakangas committed:

- In pgsql/src/backend/access/transam/xlog.c, reset minRecoveryPoint

at checkpoints, so that we don't uselessly update it in the control
file at crash recovery following an archive recovery.  Per Fujii
Masao and subsequent discussion.

Magnus Hagander committed:

- In pgsql/src/tools/msvc/Solution.pm, exclude part of the product

name string that is localized in Japanese versions of MSVC when
detecting MSVC version.  Hiroshi Inoue.

- In pgsql/src/port/win32env.c, make the win32 putenv() override

update *all* present versions of the MSVCRxx runtime, not just the
current + Visual Studio 6 (MSVCRT).  Clearly there can be an almost
unlimited number of runtimes loaded at the same time.  Per report
from Hiroshi Inoue.

- In pgsql/src/tools/msvc/clean.bat, delete solution cache file on

clean, if it exists.

- Detect a 64-bit build environment on Windows, and generate the

appropriate project files.  Based on the work of Tsutomu Yamada, but
much refactored.

- In pgsql/src/backend/utils/mmgr/aset.c, silence compiler warning on

64-bit windows build

- In pgsql/src/port/open.c, fix cast for _open_osfhandle(). Tsutomu

Yamada.

- Fix one more cast for _open_osfhandle(). Tsutomu Yamada.

- Support 64-bit shared memory when building on 64-bit Windows.

Tsutomu Yamada

- In pgsql/src/include/pg_config.h.win32, set proper sizes for size_t

and void* on 64-bit Windows builds.  Tsutomu Yamada.

- In pgsql/src/tools/msvc/Project.pm, silence compiler warning about

size of size_t being larger than the result variable it's stored in.
We know this can never happen. Per discussion.

- In pgsql/src/include/port/win32.h, make ssize_t 64-bit on Win64, for

compatibility with for example plpython.

Andrew Dunstan committed:

- Enable examples to compile on Windows. Patch from Hiroshi Saito.

ITAGAKI Takahiro committed:

- In pgsql/src/backend/port/win32_shmem.c, silence compiler warning

about printf format for HANDLE.

[编辑] Rejected Patches (for now)

No one was disappointed this week :-)

[编辑] Pending Patches

ITAGAKI Takahiro sent in a patch to make pg_bench error out better on bad variable names.

Robert Haas sent in another revision of the patch to enable setting random_page_cost and seq_page_cost at the tablespace level.

Zoltan Boszormenyi sent in a patch to add row counts to SELECT INTO and CREATE TABLE AS [query].

Andreas Freund sent in a patch to delay fsync in database creation, speeding up that operation.

Mark Cave-Ayland sent in two revisions of a patch to add subdirectory support to DATA and DOCS in PGXS.

Hitoshi Harada sent in a patch to move the declaration of IntArray from c.h to execQual.c, the only place it is currently used.

Pavel Stehule sent in a patch to add quote_ident and quote_literal, using :"foo" and :'foo' respectively, to psql's variables.

Tatsuo Ishii sent in two patches to correct an issue where pgpool was crashing PostgreSQL under certain conditions.

Heikki Linnakangas sent in a patch in response to Fujii Masao's patch for streaming replication.

Teodor Sigaev sent in another revision of the k-nearest-neighbor patch.

Teodor Sigaev sent in an updated patch to add point operations to GiST. Robert Haas reviewed and revised this patch.

Hitoshi Harada sent in a patch to add ROWS|RANGE n PRECEDING|FOLLOWING to windowing functions.

Simon Riggs sent in a patch which expands pg_cancel_backend()'s functionality to allow it to cancel and idle transaction.

Gurjeet Singh sent in a patch to re-allow SET ROLE in SECURITY DEFINER functions.

Simon Riggs sent in a patch to implement recovery conflict signalling using SIGUSR1 multiplexing, then uses a SessionCancelPending mode similar to Joachim Wieland's TransactionCancelPending patch.

John Naylor sent in another revision of the patch to refactor BKI.

Magnus Hagander sent in a patch to fix an issue in win64 where socket definitions are not declared correctly.

KaiGai Kohei sent in another revision of the patch to disallow ALTER COLUMN...RENAME TO... in the case where a column name is inherited from multiple relations.

Magnus Hagander sent in a patch to change to config.pl processing in the msvc build environment.

David Fetter sent in a patch to add tab completion for DO blocks in psql.

ITAGAKI Takahiro sent in another revision of the patch to check correctness of variable names in pgbench.