Skip to content

Wöchentlicher PostgreSQL Newsletter - 22. April 2007


Der Originalartikel befindet sich unter:
http://people.planetpostgresql.org/dfetter/

== Wöchentlicher PostgreSQL Newsletter - 22. April 2007 ==

Bugfix Versionen 8.2.4, 8.1.9, 8.0.13, 7.4.17 and 7.3.19 erscheinen demnächst. Upgrade einplanen :)

== PostgreSQL Produkt Neuigkeiten ==

PgBouncer 1.0.7 erschienen.
http://pgfoundry.org/projects/pgbouncer/

pgpool-II ist in Fedora Core und EPEL
Devrim GUNDUZ

PL/Proxy 2.0.2 erschienen.
http://pgfoundry.org/projects/plproxy/

Skytools 2.1.4 erschienen.
http://pgfoundry.org/projects/skytools/

== PostgreSQL Jobs im April ==

http://archives.postgresql.org/pgsql-jobs/2007-04/threads.php

== PostgreSQL Lokal ==


Auf gehts zum PgDay!
http://www.pgday.it/en/node/95

Registrierung für PGCon 2007 ist eröffnet
http://www.pgcon.org/2007/registration.php

== PostgreSQL in den News ==

Planet PostgreSQL: http://www.planetpostgresql.org/

General Bits Archives:
http://www.varlena.com/GeneralBits/

Dieser wöchentliche PostgreSQL Newsletter wurde erstellt von David
Fetter und Devrim GUNDUZ.

Um deine Beiträge für die nächste Ausgabe zu übermitteln, sende diese
an david@fetter.org oder in Deutsch an pwn@pgug.de bis Sonntag, 15:00 Uhr Pazifischer Zeit.

== Angewandte Patches ==

Andrew Dunstan committed:

- Document new -with-libxslt build option.

Peter Eisentraut committed:

- In pgsql/contrib/uuid-ossp/uuid-ossp.sql.in, make some functions
immutable. Thanks, Marko Kreen

- Fix typo in contrib/uuid-ossp/README.uuid-ossp.

- Joachim Wieland's patch to make configuration parameters fall back
to their default values when they are removed from the configuration
file.

- Fix alignment of help output.

- A new contrib module uuid-ossp for generating UUID values using the
OSSP UUID library. New configure option --with-ossp-uuid to
activate.

- Documentation for UUID type.

- Translation updates.

Bruce Momjian committed:

- Mark TODO as done: "Allow commenting of variables in postgresql.conf
to restore them."

- Add comment on why deadlock detection error messages only prints
numbers.

- Add script major_release_split to simplify creating release notes
for multiple releases.

- Add mention of checking <link> behavior of HISTORY.html to release
checklist.

- Remove duplicate text in FAQ, per Magnus Hagander.

- Release wording updates for releases 8.2.4, 8.1.9, 8.0.13, 7.4.17,
7.3.19.

- Update FAQ to mention most recent release for releases 8.2.4, 8.1.9,
8.0.13, 7.4.17, 7.3.19.

- Stamp releases 8.2.4, 8.1.9, 8.0.13, 7.4.17, 7.3.19.

- Update FAQ for 8.2.4.

- Release note updates for 8.2.4, 8.1.9, 8.0.13, 7.4.17, 7.3.19.

- Remove tabs from SGML source files.

- Item not done, per Pavel.

- Update docs/error message for CSV quote/escape --- must be ASCII.
Backpatch doc change to 8.2.X.

- Update error message for COPY with a multi-byte delimiter.

- Document that the COPY delimiter must be an ASCII byte, rather than
a multi-byte value. It can also be a single-byte encoded character
if the client and server versions match. Backpatch to 8.2.X.

- Mark PL/PgSQL TODO sub-item as done: "Add support for WITH HOLD and
SCROLL cursors."

- Mark PL/PgSQL TODO sub-item as done: "Add MOVE to cursors"

- Add warning about TODO item.

- Change TODO from easy to not, per Alvaro Herrera: "Set proper
permissions on non-system schemas during db creation."

Magnus Hagander committed:

- Joachim Wieland's patch to fix the build for directories with spaces
in them by quoting properly.

- Don't try to build uuid-ossp on msvc.

- Enable building of 64-bit libpq using visual studio 8 and the
win32.mak file. Enable building with kerberos support using the
win32.mak file. Patch by Hiroshi Saito and Magnus Hagander.

- Change default build to release, to be consistent with unix build.
Make it possible to set the default from builenv.bat. Per request
from Dave Page.

- Silence mingw compiler warning.

- Merlin Moncure's patch which prevents psql from writing timing
output in quiet mode.

- Enable IPV6 for all MSVC builds, including the VC6 libpq-only build.
Per request from Hiroshi Saito.

Alvaro Herrera committed:

- Silence compiler warnings, per Bruce.

- Enable configurable log of autovacuum actions. Initial patch from
Simon Riggs, additional code and docs by me. Per discussion.

- Add a multi-worker capability to autovacuum. This allows multiple
worker processes to be running simultaneously. Also, now autovacuum
processes do not count towards the max_connections limit; they are
counted separately from regular processes, and are limited by the
new GUC variable autovacuum_max_workers. The launcher now has
intelligence to launch workers on each database every
autovacuum_naptime seconds, limited only on the max amount of worker
slots available. Also, the global worker I/O utilization is limited
by the vacuum cost-based delay feature. Workers are "balanced" so
that the total I/O consumption does not exceed the established
limit. This part of the patch was contributed by ITAGAKI Takahiro.

Tom Lane committed:

- Remove some of the most blatant brain-fade in the recent guc patch
(it's so nice to have a buildfarm member that actively rejects naked
uses of strcasecmp). This coding is still pretty awful, though,
since it's going to be O(N^2) in the number of guc variables. May I
direct your attention to bsearch?

- Some further performance tweaks for planning large inheritance trees
that are mostly excluded by constraints: do the CE test a bit
earlier to save some adjust_appendrel_attrs() work on excluded
children, and arrange to use array indexing rather than rt_fetch()
to fetch RTEs in the main body of the planner. The latter is
something I'd wanted to do for awhile anyway, but seeing
list_nth_cell() as 35% of the runtime gets one's attention.

- Avoid useless work during set_plain_rel_pathlist() when the relation
will be excluded by constraint exclusion anyway. Greg Stark.

- Tweak make_inh_translation_lists() to check the common case wherein
parent and child attnums are the same, before it grovels through
each and every child column looking for a name match. Saves some
time in large inheritance trees, per example from Greg Stark.

- Improve the way in which CatalogCacheComputeHashValue combines
multiple key values: don't throw away perfectly good hash bits, and
increase the shift distances so as to provide more separation in the
common case where some of the key values are small integers (and so
their hashes are too, because hashfunc.c doesn't try all that hard).
This reduces the runtime of SearchCatCache by a factor of 4 in an
example provided by Greg Stark, in which the planner spends a whole
lot of time searching the two-key STATRELATT cache. It seems
unlikely to hurt in other cases, but maybe we could do even better?

- Adjust pgstat_initstats() to avoid repeated searches of the TabStat
arrays when a relation is opened multiple times in the same
transaction. This is particularly useful for system catalogs, which
we may heap_open or index_open many times in a transaction, and it
doesn't really cost anything extra even if the rel is touched but
once. Motivated by study of an example from Greg Stark, in which
pgstat_initstats() accounted for an unreasonably large fraction of
the runtime.

- Tweak set_rel_width() to avoid redundant executions of getrelid().
In very large queries this accounts for a noticeable fraction of
planning time. Per an example from Greg Stark.

- Sync timezone data with 2007e zic release.

- Fix missed PACKAGE_STRING.

- Fix plpgsql to avoid reference to already-freed memory when
returning a pass-by-reference data type and the RETURN statement is
within an EXCEPTION block. Bug introduced by my fix of 2007-01-28
to use per-subtransaction ExprContexts/EStates; since that wasn't
back-patched into older branches, only 8.2 and HEAD are affected.
Per report from Gary Winslow.

- Cancel pending fsync requests during WAL replay of DROP DATABASE,
per bug report from David Darville. Back-patch as far as 8.1, which
may or may not have the problem but it seems a safe change anyway.

- Fix condition for whether end_heap_rewrite must fsync, per Heikki
Linnakangas.

- Don't assume rd_smgr stays open across all of a rewriteheap
operation; doing so can result in crash if an sinval reset occurs
meanwhile. I believe this explains intermittent buildfarm failures
in cluster test.

- Rewrite choose_bitmap_and() to make it more robust in the presence
of competing alternatives for indexes to use in a bitmap scan. The
former coding took estimated selectivity as an overriding factor,
causing it to sometimes choose indexes that were much slower to scan
than ones with a slightly worse selectivity. It was also too
narrow-minded about which combinations of indexes to consider
ANDing. The rewrite makes it pay more attention to index scan cost
than selectivity; this seems sane since it's impossible to have very
bad selectivity with low cost, whereas the reverse isn't true.
Also, we now consider each index alone, as well as adding each index
to an AND-group led by each prior index, for a total of about O(N^2)
rather than O(N) combinations considered. This makes the results
much less dependent on the exact order in which the indexes are
considered. It's still a lot cheaper than an O(2^N) exhaustive
search. A prefilter step eliminates all but the cheapest of those
indexes using the same set of WHERE conditions, to keep the
effective value of N down in scenarios where the DBA has created
lots of partially-redundant indexes.

- Fix erroneous column counts in some documentation tables. Brian
Gough.

- Temporarily re-add derived files, in hopes of straightening out
their CVS status. And remove 'em again ...

- Tweak clean_encoding_name() API to avoid need to cast away const.
Kris Jurka

- Fix pg_dump to not crash if -t or a similar switch is used to select
a serial sequence for dumping without also selecting its owning
table. Make it not try to emit ALTER SEQUENCE OWNED BY in this
situation. Per report from Michael Nolan.

- Make plancache store cursor options so it can pass them to planner
during a replan. I had originally thought this was not necessary,
but the new SPI facilities create a path whereby queries planned
with non-default options can get into the cache, so it is necessary.

- Support scrollable cursors (ie, 'direction' clause in FETCH) in
plpgsql. Pavel Stehule, reworked a bit by Tom.

- Expose more cursor-related functionality in SPI: specifically, allow
access to the planner's cursor-related planning options, and provide
new FETCH/MOVE routines that allow access to the full power of those
commands. Small refactoring of planner(), pg_plan_query(), and
pg_plan_queries() APIs to make it convenient to pass the planning
options down from SPI. This is the core-code portion of Pavel
Stehule's patch for scrollable cursor support in plpgsql; I'll
review and apply the plpgsql changes separately.

== Abgelehnte Patches (bis jetzt) ==

No one was disappointed this week :-)

== Eingesandte Patches ==

ITAGAKI Takahiro sent in a patch which suppresses some warnings on
MingW.

Kris Jurka sent in a patch which removes a warning about const
qualifier from src/backend/utils/mb/encnames.c

Arul Shaji sent in another revision of his patch which implements
COPY-able csv log outputs.

ITAGAKI Takahiro sent in another revision of his load distributed
checkpoint patch.

Heikki Linnakangas sent in a patch reconciling his HOT patch with
MVCC-safe CLUSTER.

Pavel Stehule sent an updated patch to add MOVE to PL/PgSQL.

Guillaume Lelarge sent in a document patch which explains that
stats_row_level needs to be enabled if user wants to get last
vacuum/analyze execution time.

Trackbacks

No Trackbacks

Comments

Display comments as Linear | Threaded

No comments

Add Comment

Enclosing asterisks marks text as bold (*word*), underscore are made via _word_.
E-Mail addresses will not be displayed and will only be used for E-Mail notifications.
To leave a comment you must approve it via e-mail, which will be sent to your address after submission.
Form options