== Wöchentlicher PostgreSQL Newsletter - 21. Februar 2010 ==

Der Originalartikel befindet sich unter:

http://www.postgresql.org/community/weeklynews/pwn20100221

== Wöchentlicher PostgreSQL Newsletter - 21. Februar 2010 ==

Das finale Commitfest für 9.0 nähert sich seinem Ende. Danke an alle
Reviewer ect., die teilgenommen haben.

== PostgreSQL Produkt Neuigkeiten ==

dataPro 1.6.1, ein visuelles Datenmanagement, Entwicklungs- und
Umwandlungswerkzeug, ist erschienen.
http://www.vive.net/products/datapro.htm

tail_n_mail 1.7.1, ein Monitorprogramm für PostgreSQL Logdateien,
ist erschienen.
http://bucardo.org/wiki/Tnm

boxinfo 1.1.10, ein Skript zum Analysieren und Publizieren von
Serverinformationen, ist erschienen.
http://bucardo.org/wiki/Boxinfo

split_postgres_dump 1.2.3, ein Werkzeug zum Aufteilen von PostgreSQL
Dump Dateien in Pre-Daten und Post-Daten Segmente, ist erschienen.
http://blog.endpoint.com/2010/01/splitting-postgres-pgdump-into-pre-and.html

check_postgres 2.14.2, ein Nagios Plugin zum Überwachen von PostgreSQL,
ist erschienen.
http://bucardo.org/wiki/Check_postgres

Zwartberg bietet jetzt einen Hostingservice für PostgreSQL
Webanwendungen. http://www.zwartberg.com/hosting.html

PostgreSQL Code Factory 10.2, ein GUI zum Entwickeln unter Windows,
ist erschienen.
http://www.sqlmaestro.com/products/postgresql/codefactory/

pgpool-II 2.3.2.2, ein Connection Pooler und mehr,
ist erschienen.
http://pgfoundry.org/projects/pgpool/

RHQ 3.0.0.B02 Systemmanagement- und Monotoringsoftware erschienen.
http://rhq-project.org/

== PostgreSQL 9.0 Feature der Woche ==

Du kannst jetzt das Verhalten von unterschiedlichen (distinct) Werten
pro Spalte mittels ALTER TABLE..ALTER COLUMN...SET
(attribute=value, ...) festlegen. Positive Werte geben die Anzahl
unterschiedlicher Werte vor. 0 sagt dem Planer, dass der Wert von
ANALYZE genutzt werden soll. Negative Werte (welche zwischen -1 und
0 liegen sollten) veranlassen den Planer, die Anzahl verschiedener
Werte durch Berechnung der geschätzten Zeilenzahl mal dem absoluten
Wert zu wählen. Für Version 9.0 sind als Attribute "n_distinct" oder
"n_distinct_inherited" möglich. Dies kann in zukünftigen Versionen noch
erweitert werden.

== PostgreSQL Jobs im Februar ==

http://archives.postgresql.org/pgsql-jobs/2010-02/threads.php

== PostgreSQL Lokal ==

Die Deutsche PostgreSQL User Gruppe hat einen Stand in der
"Projekt Lounge" in Halle 2 auf der Cebit 2010 vom 02. bis zum 06.
März 2010 in Hannover, Deutschland.
http://andreas.scherbaum.la/blog/archives/649-PostgreSQL-stand-at-Cebit-2010.html

Die Deutsche PostgreSQL User Gruppe hat einen Stand auf den Chemnitzer
Linuxtagen 2010 am 13. und 14. März 2010 in Chemnitz, Deutschland.
Andreas (ads) Scherbaum hält einen Vortrag über PostgreSQL 9.0 und
einen Workshop zum Thema "PostgreSQL tunen".
http://andreas.scherbaum.la/blog/archives/652-PostgreSQL-stand-at-Chemnitz-Linux-Days-2010.html
http://chemnitzer.linux-tage.de/2010/vortraege/plan.html

Linuxfest Nordwest 2010 findet in Bellingham, Washington, USA am
24. und 25. April statt. Vortragsreihen, Stände und Sponsoring
sind verfügbar.
http://linuxfestnorthwest.org/

Andreas (ads) Scherbaum hält einen Kurs "PostgreSQL im
Unternehmenseinsatz" an der VHS Magdeburg, Deutschland, vom 03. bis 07.
Mai 2010. Details unter:
http://andreas.scherbaum.la/blog/archives/650-PostgreSQL-Schulung-als-Bildungsurlaub-03.-07.05.2010-in-Magdeburg.html

PgCon 2010 findet vom 20.-21. Mai 2010 in Ottawa statt mit Tutorials
vorher am 18. und 19.
http://www.pgcon.org/2010/registration.php

Der Call for Proposals für die OSCON ist eröffnet. OSCON findet in
Portland, Oregon vom 19. bis 23. Juli 2010 statt.
http://post.oreilly.com/f2f/9z1zqmm5lhkab0uogt3avlvc4u59bro6f917re423d8

== PostgreSQL in den News ==

Planet PostgreSQL: http://planet.postgresql.org/

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

Sende Neuigkeiten und Ankündigungen bis Sonntag, 15 Uhr Pazifischer
Zeit. Bitte sende englische Beiträge an david(at)fetter(dot)org, deutsche an
pwn(at)pgug(dot)de, italienische an pwn(at)itpug(dot)org(dot)

== Angewandte Patches ==

Gregory Stark committed:

- In pgsql/src/port/copydir.c, temporarily disable fsyncing the
database directory in CREATE DATABASE until we can work out
portability issues the build farm uncovered. In passing avoid
fsyncing subdirectories twice.

- In pgsql/src/port/copydir.c, revert prior patch to fsync directories
until portability problems exposed by build farm can be sorted out.

- In pgsql/src/backend/commands/explain.c, revert to showing buffer
counts in explain (buffers)

- In pgsql/src/port/copydir.c, oops, don't forget to rewind the
directory before scanning it to fsync files in CREATE DATABASE

Alvaro Herrera committed:

- In pgsql/src/backend/commands/vacuum.c, fix typo in comment.

- Move main error message text in plperl into errmsg from errdetail,
and move the context information into errcontext instead of errmsg.
This makes them better conform to our guidelines. Also remove a few
errcode declarations that were providing the default value
ERRCODE_INTERNAL_ERROR.

Magnus Hagander committed:

- In pgsql/src/tools/msvc/Mkvcbuild.pm, fix another stupid typo...
Jan Urbanski

- Add emulation of non-blocking sockets to the win32 socket/signal
layer, and use this in pq_getbyte_if_available. It's only a limited
implementation which switches the whole emulation layer to
non-blocking mode, but that's enough as long as non-blocking is only
used during a short period of time, and only one socket is accessed
during this time.

- In pgsql/src/backend/port/win32/socket.c, another typo fix. Thom
Brown.

Peter Eisentraut committed:

- In pgsql/src/bin/pg_dump/pg_dump_sort.c, when sorting functions in
pg_dump, break ties (same name) by number of arguments.

- In pgsql/doc/src/sgml/release-9.0.sgml, preliminary release notes
for 9.0alpha4.

- Translation updates for 9.0alpha4

- In pgsql/src/bin/pg_ctl/pg_ctl.c, small corrections to message
output targets in pg_ctl. 1. The message "server stopped" should be
affected by the -s option, just like "server started" already was.
2. The message "could not start server" should consistently go to
stderr.

- Version stamp 9.0alpha4.

- Revert version stamping in wrong branch.

- Version stamp 9.0alpha4 (in the right branch).

ITAGAKI Takahiro committed:

- In pgsql/doc/src/sgml/ref/do.sgml, fix synopsis of DO statement.

- In pgsql/src/bin/psql/tab-complete.c, support new syntax and improve
handling of parentheses in psql tab-completion. Newly supported
syntax includes: ALTER {TABLE|INDEX|TABLESPACE} {SET|RESET} with
options, ALTER TABLE ALTER COLUMN {SET|RESET} with options, ALTER
TABLE ALTER COLUMN SET STORAGE, CREATE INDEX CONCURRENTLY, CREATE
INDEX ON (without name), CREATE INDEX ... USING with pg_am.amname
instead of hard-corded names, CREATE TRIGGER with events, and DROP
AGGREGATE function with arguments

- Logger should not shutdown before any other threads are alive.
Change list_concat() to free the second argument in the function.

- In pgsql/src/backend/utils/adt/date.c, date_recv should accept
infinities. Reported by James William Pye.

- Fix STOP WAL LOCATION in backup history files no to return the next
segment of XLOG_BACKUP_END record even if the the record is placed
at a segment boundary. Furthermore the previous implementation
could return nonexistent segment file name when the boundary is in
segments that has "FE" suffix. We never use segments with "FF"
suffix. Backpatch to 8.0, where hot backup was introduced.
Reported by Fujii Masao.

Michael Meskes committed:

- In ECPG, do not check NaN values for infinity. Some system are not
able to handle this. Zoltan Boszormenyi.

Bruce Momjian committed:

- Clarify documentation on the behavior of unnamed bind queries.

- In pgsql/src/backend/utils/mb/Unicode/UCS_to_GB18030.pl, remove
personal copyright now that file has been rewritten using existing
*.pl conversion script. Andreas 'ads' Scherbaum

- Have SELECT and CREATE TABLE AS queries return a row count. While
this is invisible in psql, other interfaces, like libpq, make this
value visible. Zoltan Boszormenyi.

- Prevent psql version banner from being printed by the \c command if
the versions match, per report from Peter Eisentraut.

- Honor to_char() "FM" specification in YYY, YY, and Y; it was
already honored by YYYY. Also document Oracle "toggle" FM behavior.
Per report from Guy Rouillier

- Move log_error_verbosity GUC setting to "What to log" section, and
document the behavior of terse and verbose output options.

- In pgsql/doc/src/sgml/high-availability.sgml, wordsmithing of HS and
SR documentation, with some wording improvements from Erik Rijkers
too.

- In pgsql/doc/src/sgml/high-availability.sgml, add missing close tag.

- Hot Standby documentation updates. Greg Smith.

- In pgsql/doc/src/sgml/query.sgml, remove mention that binary
distributions pre-compile the tutorial files.

- First pass over client applications documentation proofreading.
Gabrielle Roth.

- Document --version and --help options for all client applications
(they all support it). Per report from Josh Kupershmidt

- In pgsql/doc/src/sgml/wal.sgml, document that many solid-state
drives have volatile write-back caches.

- In pgsql/doc/src/sgml/client-auth.sgml, clarify documentation about
username mapping when authenticating with GSSAPI or Kerberos. Ian
Turner.

- In pgsql/doc/src/sgml/client-auth.sgml, make 'include_realm'
ordering consistent in the docs, to match recent doc change.

Andrew Dunstan committed:

- Clean up package namespace use and use of Safe in plperl. Prevent
use of another buggy version of Safe.pm. Only register the exit
handler if we have successfully created an interpreter. Change log
level of perl warnings from NOTICE to WARNING. The infrastructure
is there if in future we decide to allow DBAs to specify extra
modules that will be allowed in trusted code. However, for now the
relevant variables are declared as lexicals rather than as package
variables, so that they are not (or should not be) accessible.
Mostly code from Tim Bunce, reviewed by Alex Hunsaker, with some
tweaks by me.

- Add query text to auto_explain output. Still to be done: fix docs
and fix regression failures under auto_explain.

- In PL/Perl, use a fatal warning check with what looks like a more
portable error message. Error noticed by Tom and buildfarm member
kite.

- In pgsql/doc/src/sgml/auto-explain.sgml, adjust sample auto-explain
output to reflect query text inclusion.

Tom Lane committed:

- Replace the pg_listener-based LISTEN/NOTIFY mechanism with an
in-memory queue. In addition, add support for a "payload" string to
be passed along with each notify event. This implementation should
be significantly more efficient than the old one, and is also more
compatible with Hot Standby usage. There is not yet any facility
for Hiroshi Saito slaves to receive notifications generated on the
master, although such a thing is possible in future. Joachim
Wieland, reviewed by Jeff Davis; also hacked on by me.

- In pgsql/src/backend/commands/async.c, make NOTIFY_PAYLOAD_MAX_LENGTH
depend explicitly on BLCKSZ and NAMEDATALEN, so this code doesn't go
nuts with smaller than default BLCKSZ or larger than default
NAMEDATALEN. The standard value is still exactly 8000.

- In pgsql/doc/src/sgml/ref/notify.sgml, tweak description of payload
parameter.

- In pgsql/src/backend/utils/misc/guc.c, log_error_verbosity is now
LOGGING_WHAT category.

- In pgsql/src/pl/plpgsql/src/pl_funcs.c, prevent #option dump from
crashing on FORI statement with null step. Reported by Pavel
Stehule.

- When updating ShmemVariableCache from a checkpoint record, be sure
to set all the values derived from oldestXid, not just that field.
Brain fade in one of my patches associated with flat file removal,
exposed by a report from Fujii Masao. With this change, xidVacLimit
should always be valid, so remove a couple of bits of complexity
associated with the previous assumption that sometimes it wouldn't
get set right away.

- Stamp HEAD as 9.0devel, and update various places that were
referring to 8.5 (hope I got 'em all). Per discussion, this release
will be 9.0 not 8.5.

- In pgsql/src/backend/commands/async.c, take care to reprocess an
uncommitted notify message. Oversight in my changes to cope with
possible errors during message processing; spotted by Joachim
Wieland.

- Fix up pg_dump's treatment of large object ownership and ACLs. We
now emit a separate archive entry for each BLOB, and use pg_dump's
standard methods for dealing with its ownership, ACL if any, and
comment if any. This means that switches like --no-owner and
--no-privileges do what they're supposed to. Preliminary testing
says that performance is still reasonable even with many blobs,
though we'll have to see how that shakes out in the field. KaiGai
Kohei, revised by me.

- In pgsql/src/backend/utils/mmgr/portalmem.c, force READY portals
into FAILED state when a transaction or subtransaction is aborted,
if they were created within the failed xact. This prevents
ExecutorEnd from being run on them, which is a good idea because
they may contain references to tables or other objects that no
longer exist. In particular this is hazardous when auto_explain is
active, but it's really rather surprising that nobody has seen an
issue with this before. I'm back-patching this to 8.4, since that's
the first version that contains auto_explain or an ExecutorEnd hook,
but I wonder whether we shouldn't back-patch further.

- Fix ExecEvalArrayRef to pass down the old value of the array element
or slice being assigned to, in case the expression to be assigned is
a FieldStore that would need to modify that value. The need for
this was foreseen some time ago, but not implemented then because we
did not have arrays of composites. Now we do, but the point
evidently got overlooked in that patch. Net result is that updating
a field of an array element doesn't work right, as illustrated if
you try the new regression test on an unpatched backend. Noted
while experimenting with EXPLAIN VERBOSE, which has also got some
issues in this area. Backpatch to 8.3, where arrays of composites
were introduced.

- Provide some rather hokey ways for EXPLAIN to print FieldStore and
assignment ArrayRef expressions that are not in the immediate
context of an INSERT or UPDATE targetlist. Such cases never arise
in stored rules, so ruleutils.c hadn't tried to handle them.
However, they do occur in the targetlists of plans derived from such
statements, and now that EXPLAIN VERBOSE tries to print targetlists,
we need some way to deal with the case. I chose to represent an
assignment ArrayRef as "array[subscripts] := source", which is
fairly reasonable and doesn't omit any information. However,
FieldStore is problematic because the planner will fold multiple
assignments to fields of the same composite column into one
FieldStore, resulting in a structure that is hard to understand at
all, let alone display comprehensibly. So in that case I punted and
just made it print the source expression(s). Backpatch to 8.4 ---
the lack of functionality exists in older releases, but doesn't seem
to be important for lack of anything that would call it.

- In pgsql/src/pl/plpython/plpython.c, volatile-ize all five places
where we expect a PG_TRY block to restore old memory context in
plpython. Before only one of them was marked volatile, but per
report from Zdenek Kotala, some compilers do the wrong thing here.

- Reduce the rescan cost estimate for Materialize nodes to
cpu_operator_cost per tuple, instead of the former cpu_tuple_cost.
It is sane to charge less than cpu_tuple_cost because Materialize
never does any qual-checking or projection, so it's got less
overhead than most plan node types. In particular, we want to have
the same charge here as is charged for readout in cost_sort. That
avoids the problem recently exhibited by Teodor wherein the planner
prefers a useless sort over a materialize step in a context where a
lot of rescanning will happen. The rescan costs should be just
about the same for both node types, so make their estimates the
same. Not back-patching because all of the current logic for rescan
cost estimates is new in 9.0. The old handling of rescans is
sufficiently not-sane that changing this in that structure is a bit
pointless, and might indeed cause regressions.

- Clean up handling of XactReadOnly and RecoveryInProgress checks.
Add some checks that seem logically necessary, in particular let's
make real sure that Hiroshi Saito slave sessions cannot create temp
tables. (If they did they would think that temp tables belonging to
the master's session with the same BackendId were theirs. We *must*
not allow myTempNamespace to become set in a slave session.) Change
setval() and nextval() so that they are only allowed on temp
sequences in a read-only transaction. This seems consistent with
what we allow for table modifications in read-only transactions.
Since an Hiroshi Saito slave can't have a temp sequence, this also
provides a nicer cure for the setval PANIC reported by Erik Rijkers.
Make the error messages more uniform, and have them mention the
specific command being complained of. This seems worth the trifling
amount of extra code, since people are likely to see such messages a
lot more than before.

- In pgsql/src/port/copydir.c, fix multiple copy and paste-o's. No
wonder this code didn't work.

Heikki Linnakangas committed:

- Fix pq_getbyte_if_available() function. It was confused on what it
returns if no data is immediately available. Patch by me with
numerous fixes from Fujii Masao and Magnus Hagander.

- In pgsql/src/backend/commands/sequence.c, forbid setval() during
recovery. This prevents the PANIC reported by Erik Rijkers. Patch
by Andres Freund.

- Don't use O_DIRECT when writing WAL files if archiving or streaming
is enabled. Bypassing the kernel cache is counter-productive in
that case, because the archiver/walsender process will read from the
WAL file soon after it's written, and if it's not cached the read
will cause a physical read, eating I/O bandwidth available on the
WAL drive. Also, walreceiver process does unaligned writes, so
disable O_DIRECT in walreceiver process for that reason too.

Robert Haas committed:

- Remove incorrect statement that PostgreSQL 8.4 has no default
parameters. Tom Lane already removed this from HEAD as par of the
plpgsql variable resolution behavior patch, but this part of his
patch also applies to 8.4.

Simon Riggs committed:

- In pgsql/doc/src/sgml/high-availability.sgml, copy editing of Hot
Standby docs. Some clarifications, addition of missing items and
minor edits.

== Abgelehnte Patches (bis jetzt) ==

Fujii Masao's patch to add 3 parameters for keepalive to libpq.
Postponed to the 9.1 cycle.

== Eingesandte Patches ==

Fujii Masao sent in a document patch for pg_stop_backup() which
clarifies the usage of the WAL file name in the backup history file.

Tim Bunce sent in a patch to fix a couple of loose ends in PL/Perl,
namely: move on_proc_exit() call to after the plperl_*_init() calls,
and don't allow use of Safe version 2.21 as that's broken for PL/Perl.

Magnus Hagander sent in a patch to allow putting a RADIUS secret in a
file.

ITAGAKI Takahiro sent in a patch against autoexplain per a bug report
from Andrew Dunstan.

Fujii Masao sent in two revisions of a patch to remove O_DIRECT from
walreceiver, which fixes an issue on win32.

Tim Bunce sent in a patch to implement PostgreSQL::PLPerl::Call.

Bruce Momjian sent in a patch clarifying the handling of prepared
statements.

Fujii Masao sent in a patch to forbid starting a standby during
recovery, hence forbidding cascading standby.

Heikki Linnakangas sent in a patch to disable O_DIRECT when archiving
or streaming is enabled. This is to help with the win32 issue.

Greg Smith sent in a doc patch for Hot Standby.

Bruce Momjian sent in a patch to remove references to libxml2 on
platforms where it is not thread-safe.

Heikki Linnakangas sent in a doc patch for streaming replication.

Zoltan Boszormenyi sent in another revision of the lock_timeout GUC
patch.

--
Andreas 'ads' Scherbaum
Deutsche PostgreSQL User Group: http://www.pgug.de/
DPWN: http://andreas.scherbaum.la/blog/categories/18-PWN