== PostgreSQL Weekly News - February 21 2010 == - Mailing list pgsql-announce

== PostgreSQL Weekly News - February 21 2010 ==

The final commitfest for 9.0 is winding down.  Thanks to all the
reviewers, etc., who are participating.

== PostgreSQL Product News ==

dataPro 1.6.1, a visual database management, development and conversion
program, released.
http://www.vive.net/products/datapro.htm

tail_n_mail 1.7.1, a monitor for PostgreSQL log files, released.
http://bucardo.org/wiki/Tnm

boxinfo 1.1.10, a script to analyze and publish information about
servers, released.
http://bucardo.org/wiki/Boxinfo

split_postgres_dump 1.2.3, a utility to split PostgreSQL dump files into
pre-data and post-data segements, released.
http://blog.endpoint.com/2010/01/splitting-postgres-pgdump-into-pre-and.html

check_postgres 2.14.2, a Nagios plugin for monitoring PostgreSQL, released.
http://bucardo.org/wiki/Check_postgres

Zwartberg now has a hosting service for PostgreSQL web applications.
http://www.zwartberg.com/hosting.html

PostgreSQL Code Factory 10.2, a GUI for development on Windows, released.
http://www.sqlmaestro.com/products/postgresql/codefactory/

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

RHQ 3.0.0.B02 Systems management and monitoring software released.
http://rhq-project.org/

== PostgreSQL 9.0 Feature of the Week ==

You can now control the behavior of distinct values per column using
ALTER TABLE...ALTER COLUMN...SET STATISTICS DISTINCT.  Positive
numbers are assumed to be the number of distinct values, 0 tells the
planner to use the results from ANALYZE, and negative numbers (which
should be between -1 and 0, cause the planner to estimate the number
of distinct values as the estimated number of rows multiplied by the
absolute value of the number.

== PostgreSQL Jobs for February ==

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

== PostgreSQL Local ==

The German Speaking PostgreSQL User Group has a stand at the Project
Lounge in hall 2 at Cebit 2010 from March 02 to March 06, 2010 in
Hannover, Germany.
http://andreas.scherbaum.la/blog/archives/649-PostgreSQL-stand-at-Cebit-2010.html

The German Language PostgreSQL User Group has a booth at Chemnitz Linuxdays
2010 on March 13 and 14, 2010 in Chemnitz, Germany.  Andreas (ads)
Scherbaum will be giving a talk on PostgreSQL 9.0 and a workshop on
tuning PostgreSQL.
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 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 Call for Proposals for OSCON is open.  OSCON will take place in
Portland, Oregon July 19-23, 2010.
http://post.oreilly.com/f2f/9z1zqmm5lhkab0uogt3avlvc4u59bro6f917re423d8

== PostgreSQL in the News ==

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

PostgreSQL Weekly News is brought to you this week by David Fetter

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.

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

== Rejected Patches (for now) ==

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

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