== PostgreSQL Weekly News - April 18 2010 ==

== PostgreSQL Weekly News - April 18 2010 ==

Michael Renner will be speaking at AMOOCON, which goes from June 4-6, 2010.
http://www.amoocon.de/speakers/214

== PostgreSQL Product News ==

Bricolage 2.0, a content management and publishing system built on
PostgreSQL, released:
http://bricolagecms.org/news/announce/2010/04/12/bricolage-2.0.0/

GNUmed 0.7.0 released.
http://wiki.gnumed.de/

pgpool-II 2.7, a connection pooler and more, released.
http://pgfoundry.org/projects/pgpool/

phpPgAdmin 4.2.3, a web-based administration tool for PostgreSQL, released.
http://phppgadmin.sourceforge.net/

tail_n_mail 1.8.6, a Perl program for searching log files and mailing
the results, released.
http://bucardo.org/wiki/Tail_n_mail

== PostgreSQL 9.0 Feature of the Week ==

GUCs are now adjustable both by role and by database. psql now has
\drds which displays these.

== PostgreSQL Jobs for April ==

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

== PostgreSQL Local ==

The CfP is open for CHAR(10) in Oxford, England on July 1-3 2010.
Proposals are due April 20.
http://www.char10.org

Bruce Momjian will be speaking about PostgreSQL Replication Solutions
at the NYPUG meeting on April 20th at 6:30-8:30pm at Penn Plaza.
Details and RSVP below.
http://postgresql.meetup.com/3/calendar/12788352/

Linuxfest Northwest 2010 will take place in Bellingham, Washington,
USA on April 24 and 25. Session, Booth and Sponsorship are available.
http://linuxfestnorthwest.org/

Andreas (ads) Scherbaum will be teaching a "PostgreSQL for Corporate
Use" course at the adult education center in Magdeburg, Germany May
3-7, 2010. Details below:
http://andreas.scherbaum.la/blog/archives/650-PostgreSQL-Schulung-als-Bildungsurlaub-03.-07.05.2010-in-Magdeburg.html

PgCon 2010 will be held May 20-21 2010 in Ottawa with tutorials before
on the 18th and 19th.
http://www.pgcon.org/2010/registration.php

The CfP for OSBridge is open! OSBridge will be June 1-4, 2010 in
Portland, Oregon.
http://opensourcebridge.org/events/2010/proposals/

Southeast Linuxfest 2010 will take place in Spartanburg, SC, USA on
June 12-13.  Booths and sponsorships are still available.
http://southeastlinuxfest.org/

OSCON will take place in Portland, Oregon July 19-23, 2010.
http://www.oscon.com/oscon2010

== PostgreSQL in the News ==

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

PostgreSQL Weekly News is brought to you this week by David Fetter
and Devrim GUNDUZ.

Submit news and announcements by Sunday at 3:00pm Pacific time.
Please send English language ones to david(at)fetter(dot)org, German language
to pwn(at)pgug(dot)de, Italian language to pwn(at)itpug(dot)org(dot) Spanish language
to pwn(at)arpug(dot)com(dot)ar(dot)

== Applied Patches ==

Heikki Linnakangas committed:

- Change the logic to decide when to delete old WAL segments, so that
it doesn't take into account how far the WAL senders are. This way
a hung WAL sender doesn't prevent old WAL segments from being
recycled/removed in the primary, ultimately causing the disk to fill
up. Instead add standby_keep_segments setting to control how many
old WAL segments are kept in the primary. This also makes it more
reliable to use streaming replication without WAL archiving,
assuming that you set standby_keep_segments high enough.

- In pgsql/doc/src/sgml/high-availability.sgml, adjust paragraph about
monitoring streaming replication, now that we have
standby_keep_segments.

- In pgsql/src/backend/replication/walsender.c, need to use the start
pointer of a block we read from WAL segment in the calculation, not
the end pointer, as pointed out by Fujii Masao.

- Update the location of last removed WAL segment in shared memory
only after actually removing one, so that if we can't remove
segments because WAL archiving is lagging behind, we don't
unnecessarily forbid streaming the old not-yet-archived segments
that are still perfectly valid. Per suggestion from Fujii Masao.

- Allow Hot Standby to begin from a shutdown checkpoint. Patch by
Simon Riggs & me.

- In pgsql/doc/src/sgml/config.sgml, fix typo, spotted by Erik
Rijkers.

- In pgsql/src/backend/access/transam/xlog.c, in standby mode,
suppress repeated LOG messages about a corrupt record, which just
indicates that we've reached the end of valid WAL found in the
standby.

- In pgsql/src/backend/postmaster/syslogger.c, on Windows, syslogger
runs in two threads. The main thread processes config reload and
rotation signals, and a helper thread reads messages from the pipe
and writes them to the log file. However, server code isn't
generally thread-safe, so if both try to do e.g palloc()/pfree() at
the same time, bad things will happen. To fix that, use a critical
section (which is like a mutex) to enforce that only one the threads
are active at a time.

Bruce Momjian committed:

- In pgsql/doc/src/sgml/backup.sgml, spell out full archive directory
specification in 'test -f' continuous archiving example, per
suggestion from Greg Smith.

- In pgsql/doc/src/sgml/config.sgml, remove example of archive_command
from configure section; instead have users look at the referenced
section for examples, per idea from Greg Smith.

- In pgsql/doc/src/sgml/wal.sgml, add "SSD" acronym mention for solid
state drive mention.

- In pgsql/doc/src/sgml/xtypes.sgml, doc change: anyways -> anyway;
Erik Rijkers

- In pgsql/src/backend/access/transam/xlog.c, doc change: effect ->
affect, per Robert Haas.

- Document that autovacuum cannot vacuum or analyze temporary tables.

Magnus Hagander committed:

- In pgsql/src/backend/replication/walreceiver.c, only try to do a
graceful disconnect if we've successfully loaded the shared library
with the disconnect function in it. Fixes segmentation fault
reported by Jeff Davis. Fujii Masao.

- In pgsql/doc/src/sgml/config.sgml, fix typo. Fujii Masao.

- Add script to enumerate the timezones in the Windows registry and
compare it with the list we have in pgtz.c, showing any differences.

Simon Riggs committed:

- In pgsql/src/backend/access/transam/xlog.c, fix minor typo in
comment in xlog.c.

- Tune GetSnapshotData() during Hot Standby by avoiding loop through
normal backends. Makes code clearer also, since we avoid various
Assert()s. Performance of snapshots taken during recovery no longer
depends upon number of read-only backends.

- Remove some additional changes in previous commit that belong
elsewhere.

- In pgsql/src/backend/access/transam/xlog.c, improve sequence and
sense of messages from pg_stop_backup(). Now doesn't report it is
waiting until it actually is waiting, plus message doesn't appear
until at least 5 seconds wait, so we avoid reporting the wait before
we've given the archiver a reasonable time to wake up and archive
the file we just created earlier in the function. Also add new
unconditional message to confirm safe completion. Now a normal,
healthy execution does not report waiting at all, just safe
completion.

Robert Haas committed:

- In pgsql/src/backend/access/gist/README, typo fix. Kevin Grittner.

- In pgsql/doc/src/sgml/config.sgml, provide better guidance for
adjusting shared_buffers.

- Provide better guidance for adjusting shared_buffers. This change
was previously committed to HEAD, but the consensus seems to be in
favor of back-patching it. I'm only backpatching as far as 8.3.X,
however, because it's not clear to me to what degree this advice
applies to older branches, and in any case our first advice to
anyone attempting to tune those versions is likely to be "upgrade".

- Add an 'enable_material' GUC. The logic for determining whether to
materialize has been significantly overhauled for 9.0. In case
there should be any doubt about whether materialization is a win in
any particular case, this should provide a convenient way of seeing
what happens without it; but even with enable_material turned off,
we still materialize in cases where it is required for correctness.
Thanks to Tom Lane for the review.

Tom Lane committed:

- In pgsql/src/backend/utils/cache/relcache.c, fix a problem
introduced by my patch of 2010-01-12 that revised the way relcache
reload works. In the patched code, a relcache entry in process of
being rebuilt doesn't get unhooked from the relcache hash table;
which means that if a cache flush occurs due to sinval queue overrun
while we're rebuilding it, the entry could get blown away by
RelationCacheInvalidate, resulting in crash or misbehavior. Fix by
ensuring that an entry being rebuilt has positive refcount, so it
won't be seen as a target for removal if a cache flush occurs.
(This will mean that the entry gets rebuilt twice in such a
scenario, but that's okay.) It appears that the problem can only
arise within a transaction that has previously reassigned the
relfilenode of a pre-existing table, via TRUNCATE or a similar
operation. Per bug #5412 from Rusty Conover. Back-patch to 8.2,
same as the patch that introduced the problem. I think that the
failure can't actually occur in 8.2, since it lacks the
rd_newRelfilenodeSubid optimization, but let's make it work like the
later branches anyway. Patch by Heikki Linnakangas, slightly
editorialized on by me.

- In pgsql/src/pl/plpgsql/src/pl_exec.c, fix plpgsql's
exec_eval_expr() to ensure it returns a sane type OID even when the
expression is a query that returns no rows. So far as I can tell,
the only caller that actually fails when a garbage OID is returned
is exec_stmt_case(), which is new in 8.4 --- in all other cases, we
might make a useless trip through casting logic, but we won't fail
since the isnull flag will be set. Hence, backpatch only to 8.4,
just in case there are apps out there that aren't expecting an error
to be thrown if the query returns more or less than one column.
(Which seems unlikely, since the error would be thrown if the query
ever did return a row; but it's possible there's some
never-exercised code out there.) Per report from Mario Splivalo.

- In pgsql/src/timezone/pgtz.c, improve message style for messages
associated with not being able to identify the system time zone
setting. Per recent discussion.

- In pgsql/src/bin/psql/copy.c, fix psql's \copy to not insert spaces
around dots and commas in the text of the SELECT query in \copy
(SELECT ...) commands. This is unnecessary and breaks numeric
literals, as seen in bug #5411 from Vitalii Tymchyshyn. This change
has already been made in passing in HEAD; backpatch to 8.2 through
8.4 (earlier releases don't have COPY (SELECT ...) at all).

- In pgsql/src/pl/plperl/plperl.c, fix bogus order of cleanup steps in
plperl_inline_handler. Per Alex Hunsaker.

Peter Eisentraut committed:

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

- In pgsql/doc/src/sgml/monitoring.sgml, IP port -> TCP port.
Backpatched to 8.1, where this first appeared.

- In pgsql/doc/src/sgml/monitoring.sgml, improve punctuation.

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

Fujii Masao sent in a patch which fixes an issue with smart shutdown.

Fujii Masao and Magnus Hagander traded patches to fix the issue where
walreceiver is uninterruptible on win32.

Heikki Linnakangas sent in a patch to fix an issue with error messages
in WALs for streaming replication.

Heikki Linnakangas and Simon Riggs sent one and three patches,
respectively, intended to deal with the issue where queries on a hot
standby server are much slower that ones on the origin.

Tom Lane sent in a patch to fix an issue where Windows time zone files
are not updated as frequently as they need to be, causing odd failure
cases.