== 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@fetter.org, German language
to pwn@pgug.de, Italian language to pwn@itpug.org.  Spanish language
to pwn@arpug.com.ar.

== 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.