== Wöchentlicher PostgreSQL Newsletter - 31. Januar 2010 ==

Der Originalartikel befindet sich unter:

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

== Wöchentlicher PostgreSQL Newsletter - 31. Januar 2010 ==

Google Summer of Code Anmeldungen sind bis zum 9. März möglich, aber
warte nicht so lange sondern kontaktiere Selena Deckelman unter
selena AT postgresql DOT org.

Die Seattle PostgreSQL Usergruppe wird ihr erstes Treffen am 9. Februar
2010 abhalten. Details unter:
http://www.seapug.org/talk.php?talk=1

== PostgreSQL Produkt Neuigkeiten ==

GNUmed EMR Client 0.6.0 und GNUmed EMR Server 12.0 sind erschienen.
http://wiki.gnumed.de/

== PostgreSQL 8.5 Feature der Woche ==

Unique Constraints können nun bis zum Commit verzögert werden.

== PostgreSQL Jobs im Januar ==

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

== PostgreSQL Lokal ==

PGDay Kuba 2010 findet vom 4.-6. Februar 2010 in La Havana an der
Universität für Wissenschaftliche Informatik statt. Teilnehmer aus
Kuba, der Dominikanischen Republik und Venezuela sind eingeladen.

FOSDEM 2010 findet am 6. und 7. Februar 2010 in Brüssel,
Belgien statt.
http://www.postgresql.eu/events/schedule/fosdem2010/

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. Der Call for Papers ist offen!
http://www.pgcon.org/2010/papers.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)

== Reviews ==

Jeff Davis reviewed Joachim Wieland's patch to fix the behavior of
LISTEN/NOTIFY when the queue is full.

== Applied Patches ==

Heikki Linnakangas committed:

- Add note that PREPARE TRANSACTION is for transaction managers, not
regular applications. Also add a comment pointing out that
tab-completion for PREPARE TRANSACTION is missing on purpose.

- Make standby server continuously retry restoring the next WAL
segment with restore_command, if the connection to the primary
server is lost. This ensures that the standby can recover
automatically, if the connection is lost for a long time and standby
falls behind so much that the required WAL segments have been
archived and deleted in the master. This also makes standby_mode
useful without streaming replication; the server will keep retrying
restore_command every few seconds until the trigger file is found.
That's the same basic functionality pg_standby offers, but without
the bells and whistles. To implement that, refactor the
ReadRecord/FetchRecord functions. The FetchRecord() function
introduced in the original streaming replication patch is removed,
and all the retry logic is now in a new function called
XLogReadPage(). XLogReadPage() is now responsible for executing
restore_command, launching walreceiver, and waiting for new WAL to
arrive from primary, as required. This also changes the life cycle
of walreceiver. When launched, it now only tries to connect to the
master once, and exits if the connection fails, or is lost during
streaming for any reason. The startup process detects the death,
and re-launches walreceiver if necessary.

- Fix bug in wasender's xlogid boundary handling, reported by Erik
Rijkers. LogwrtRqst.Write can be set to non-existent FF log
segment, we mustn't try to send that in XLogSend(). Also fix
similar bug in ReadRecord(), which I just introduced in the
ReadRecord() refactoring patch.

- Change a few remaining calls of XLogArchivingActive() to use
XLogIsNeeded() instead, to determine if an otherwise non-logged
operation needs to be logged in WAL for standby servers. Fujii
Masao.

- In pgsql/src/backend/access/transam/xlog.c, fix crashing bug at the
end of recovery in Streaming Replication, when restore_command is
not given. Fujii Masao.

Tom Lane committed:

- Add get_bit/set_bit functions for bit strings, paralleling those for
bytea, and implement OVERLAY() for bit strings and bytea. In
passing also convert text OVERLAY() to a true built-in, instead of
relying on a SQL function. Leonardo Francalanci, reviewed by Kevin
Grittner.

- In pgsql/src/bin/initdb/initdb.c, un-break initdb logic for
commenting out IPv6 pg_hba.conf line.

- Remove the default_do_language parameter, instead making DO use a
hardwired default of "plpgsql". This is more reasonable than it was
when the DO patch was written, because we have since decided that
plpgsql should be installed by default. Per discussion, having a
parameter for this doesn't seem useful enough to justify the risk of
application breakage if the value is changed unexpectedly.

- In pgsql/src/backend/regex/regcomp.c, fix some comments that got
mangled by pgindent.

- In pgsql/src/bin/psql/print.c, fix memory leakage introduced into
print_aligned_text by 8.4 changes (failure to free col_lineptrs[]
array elements) and exacerbated in the current devel cycle (failure
to free "wrap"). This resulted in moderate bloat of psql over long
script runs. Noted while testing bug #5302, although what the
reporter was complaining of was backend-side leakage.

- In pgsql/src/backend/tcop/dest.c, avoid performing encoding
conversion on command tag strings during EndCommand. Since all
current and foreseeable future command tags will be pure ASCII,
there is no need to do conversion on them. This saves a few cycles
and also avoids polluting otherwise-pristine subtransaction memory
contexts, which is the cause of the backend memory leak exhibited in
bug #5302. (Someday we'll probably want to have a better method of
determining whether subtransaction contexts need to be kept around,
but today is not that day.) Backpatch to 8.0. The cycle-shaving
aspect of this would work in 7.4 too, but without subtransactions
the memory-leak aspect doesn't apply, so it doesn't seem worth
touching 7.4.

- In pgsql/src/backend/libpq/auth.c, get rid of unportable use of
socklen_t --- we have a configure test for that, so use it.

- In pgsql/src/include/port.h, parenthesize this macro, just in case.

- Fix memory leak created by deferrable-index-constraints patches. We
need to free the OID list returned by ExecInsertIndexTuples to avoid
a query-lifespan memory leak. When many rows require rechecking,
this can be a significant leak --- it's even more than the space
used for the queued trigger events. Dean Rasheed.

- In pgsql/src/backend/regex/regexec.c, change regexp engine's
ccondissect/crevdissect routines to perform DFA matching before
recursing instead of after. The DFA match eliminates unworkable
midpoint choices a lot faster than the recursive check, in most
cases, so doing it first can speed things up; particularly in
pathological cases such as recently exhibited by Michael Glaesemann.
In addition, apply some cosmetic changes that were applied upstream
(in the Tcl project) at the same time, in order to sync with
upstream version 1.15 of regexec.c. Upstream apparently intends to
backpatch this, so I will too. The pathological behavior could be
unpleasant if encountered in the field, which seems to justify any
risk of introducing new bugs. Tom Lane, reviewed by Donal K.
Fellows of Tcl project

Simon Riggs committed:

- In pgsql/src/backend/access/transam/xlog.c, fix longstanding gripe
that we check for 0000000001.history at start of archive recovery,
even when we know it is never present.

- In pgsql/src/backend/storage/lmgr/lock.c, use malloc() in
GetLockConflicts() when called InHotStandby to avoid repeated palloc
calls. Current code assumed this was already true, so this is a bug
fix.

- Filter recovery conflicts based upon dboid from relfilenode of WAL
records for heap and btree. Minor change, mostly API changes to pass
through the required values. This is a simple change though also
provides the refactoring required for further enhancements to
conflict processing using the relOid. Changes only have effect
during Hot Standby.

- Augment WAL records for btree delete with GetOldestXmin() to reduce
false positives during Hot Standby conflict processing. Simple patch
to enhance conflict processing, following previous discussions.
Controlled by parameter minimize_standby_conflicts = on | off, with
default off allows measurement of performance impact to see whether
it should be set on all the time.

- In pgsql/src/backend/storage/lmgr/lock.c, adjust GetLockConflicts()
so that it uses TopMemoryContext when executed InHotStandby. Cleaner
solution than using malloc or palloc depending upon situation, as
proposed by Tom.

- Detect early deadlock in Hot Standby when Startup is already
waiting. First stage of required deadlock detection to allow
re-enabling max_standby_delay setting of -1, which is now essential
in the absence of improved relation- specific conflict resoluton.
Requested by Greg Stark et al.

Peter Eisentraut committed:

- Make the default pg_hba.conf and pg_ident.conf align at 8-character
boundaries to make it easier to use with tabs.

- Reformat the comments in pg_hba.conf and pg_ident.conf. These files
have apparently been edited over the years by a dozen people with as
many different editor settings, which made the alignment of the
paragraphs quite inconsistent and ugly. I made a pass of M-q with
Emacs to straighten it out.

- Type table feature. This adds the CREATE TABLE name OF type
command, per SQL standard.

Michael Meskes committed:

- Applied patch by Zoltan Boszormenyi to add out-of-scope cursor
support to native mode.

- In pgsql/doc/src/sgml/ecpg.sgml, finally applied patch by Zoltan
Boszormenyi to add the new features his patches added to the docs.

- Added test case that was part of Zoltan's ECPG patch but apparently
wasn't part of my commit.

- Fixed a few typos in ecpg. Two were in comments, the third made a
log output reverse yes and no.

- Changed ECPG outofscope handling to always print out statements in
the same order so regression testing is possible. Zoltan
Boszormenyi.

Bruce Momjian committed:

- In pgsql/doc/src/sgml/ecpg.sgml, remove tabs in SGML.

Andrew Dunstan committed:

- Various small improvements and cleanups for PL/Perl. 1. Allow
(ineffective) use of 'require' in plperl If the required module is
not already loaded then it dies. So "use strict;" now works in
plperl. 2. Pre-load the feature module if perl >= 5.10. So "use
feature :5.10;" now works in plperl. 3. Stored procedure subs are
now given names. The names are not visible in ordinary use, but
they make tools like Devel::NYTProf and Devel::Cover much more
useful. 4. Simplified and generalized the subroutine creation code.
Now one code path for generating sub source code, not four. Can
generate multiple 'use' statements with specific imports (which
handles plperl.use_strict currently and can easily be extended to
handle a plperl.use_feature=':5.12' in future). 5. Disallows use of
Safe version 2.20 which is broken for PL/Perl.
http://rt.perl.org/rt3/Ticket/Display.html?id=72068 6. Assorted
minor optimizations by pre-growing data structures. Patch from Tim
Bunce, reviewed by Alex Hunsaker.

- In pgsql/doc/src/sgml/plperl.sgml, fix plperl.sgml errors from
recent commit.

- In pgsql/src/pl/plperl/plperl.c, fix bug found by warning from
recent gcc. patch from Tim Bunce.

- Add new make targets "world", "install-world" and
"installcheck-world" to build, install and check just about
everything. In addition to everything built installed and tested by
all, install and installcheck targets, these build HTML Docs, build
and test contrib, and test PLs and ECPG.

- In pgsql/GNUmakefile.in, fix thinko in new installcheck-world target

- Add plperl.on_perl_init setting to provide for initializing the perl
library on load. Also, handle END blocks in plperl. Database access
is disallowed during both these operations, although it might be
allowed in END blocks in future. Patch from Tim Bunce.

Magnus Hagander committed:

- Add support for RADIUS authentication.

- In pgsql/doc/src/sgml/client-auth.sgml, fix typo. Noted by Thom
Brown.

- In pgsql/src/include/port/solaris.h, define INADDR_NONE on Solaris
when it's missing. Per a couple of buildfarm members complaining.

- Add functions to reset the statistics counter for a single
table/index or a single function.

- In pgsql/src/backend/port/win32/signal.c, fix race condition in
win32 signal handling. There was a race condition where the
receiving pipe could be closed by the child thread if the main
thread was pre-empted before it got a chance to create a new one,
and the dispatch thread ran to completion during that time. One
symptom of this is that rows in pg_listener could be dropped under
heavy load. Analysis and original patch by Radu Ilie, with some
small modifications by Magnus Hagander.

- In pgsql/src/port/kill.c, remove now unnecessary loop around
CallNamedPipe(). Radu Ilie.

- In pgsql/src/backend/postmaster/pgstat.c, make checks for invalid
pgStatSock use PGINVALID_SOCKET.

Joe Conway committed:

- Introduce two new libpq connection functions, PQconnectdbParams and
PQconnectStartParams. These are analogous to PQconnectdb and
PQconnectStart respectively. They differ from the legacy functions
in that they accept two NULL-terminated arrays, keywords and values,
rather than conninfo strings. This avoids the need to build the
conninfo string in cases where it might be inconvenient to do so.
Includes documentation. Also modify psql to utilize
PQconnectdbParams rather than PQsetdbLogin. This allows the new
config parameter application_name to be set, which in turn is
displayed in the pg_stat_activity view and included in CSV log
entries. This will also ensure both new functions get regularly
exercised. Patch by Guillaume Lelarge with review and minor
adjustments by Joe Conway.

ITAGAKI Takahiro committed:

- In pgsql/src/backend/tcop/utility.c, fix command tag for ALTER LARGE
OBJECT.

- Add string_agg aggregate functions. The one argument version
concatenates the input values into a string. The two argument
version also does the same thing, but inserts delimiters between
elements. Original patch by Pavel Stehule, reviewed by David E.
Wheeler and me.

Robert Haas committed:

- Allow psql variables to be interpolated with literal or identifier
escaping. Loosely based on a patch by Pavel Stehule.

== Abgelehnte Patches (bis jetzt) ==

No one was disappointed this week :-)

== Eingesandte Patches ==

Pavel Stehule sent in another revision of the patch to add a
string_agg() aggregate which concatenates the elements of aggregation
with an internal separator.

Pavel Stehule sent in another revision of the patch to introduce
quoting behavior to psql variables.

Selena Deckelmann sent in a patch to divide process from debugging
information in pg_standby.

Peter Eisentraut sent in another revision of the typed table patch per
review from Hitoshi Harada.

Tim Bunce sent in another revision of the patch to clean up namespaces
and Safe initialization for PL/perl.

Robert Haas sent in another revision of the patch to add red-black
trees to GIN indexes. Mark Cave-Ayland sent another revision in
response to this.

Zoltan Boszormenyi sent in three more revisions of the patch to fix
out-of-scope cursor handling in ECPG.

David Christensen sent in a patch to add a \whoami option to psql.

Marko (johto) Tiikkaja sent in another revision of the patch to add
general DML to CTEs.

KaiGai Kohei sent in three more revisions of the patch to fix the
behavior of ALTER TABLE...RENAME TO... in the case of multiple
inheritance.

KaiGai Kohei sent in another revision of the patch to add access
controls to large objects.

Tim Bunce sent in two more revisions of the patch to add
plperl.on_[un]trusted_init.

Leonardo Francalanci sent in another revision of the patch to improve
CLUSTER's performance.

Robert Haas sent in another revision of the patch to add hash
information to EXPLAIN.

Robert Haas sent in a patch to remove contrib/xml2. Mike Rylander
sent a smaller one to add the analogous functions to the core
distribution.

Simon Riggs sent in two revisions of a patch to add relation-specific
deferred conflict resolution.

Fujii Masao sent in a patch to extend the format of the string
returned by pg_last_xlog_receive/replay_location() to include the
timeline ID, and teaches pg_xlogfile_name[_offset]() to accept that
format, too.

Alexey Klyukin sent in a patch to improve the documentation of PL/perl
database access functions.

Zoltan Boszormenyi sent in another revision of the patch to fix NaN
and Inf behaviors in ECPG.

Simon Riggs sent in a patch to fix the behavior of VACUUM FULL in the
streaming replication case.

Tim Bunce sent in another revision of the patch to PL/Perl to move the
internal function from namespace main:: to namespaces
PostgreSQL::InServer[::safe], and restructure the Safe compartment.

Guillaume Lelarge sent in a patch to make the PostgreSQL binaries use
the new PQconnectdbParamss libpq functions.

Fujii Masao sent in a patch to shut down the server, including the
startup process and walreceiver, as soon as all read-only connections
have died. This fixes a corner case in streaming replication.

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