== PostgreSQL Weekly News - November 01 2009 == - Mailing list pgsql-announce

== PostgreSQL Weekly News - November 01 2009 ==

PostgreSQL 8.5alpha2 released.  Start testing those apps in your test
environment!
http://www.postgresql.org/developer/alpha

== PostgreSQL Product News ==

pgtune 0.9.2, a system for tuning the base configuration file for
PostgreSQL, released.
http://pgfoundry.org/projects/pgtune/

== PostgreSQL Local ==

LISA 09, the Large Installation and Systems Administration conference,
will be held November 1-6, 2009. There will be a Postgres booth
Wednesday and Thursday.
http://www.usenix.org/event/lisa09/

PGDay.EU 2009 will be at Telecom ParisTech in Paris, France on
November 6-7, 2009.
http://www.pgday.eu/

OpenSQL Camp in Portland is looking for sponsors.  Make your travel plans now! :)
http://www.chesnok.com/daily/2009/07/29/opensql-camp-comes-to-portland-november-14-15-2009/

JPUG 10th Anniversary Conference is November 20-21, 2009 in Tokyo, Japan.
http://archives.postgresql.org/pgsql-announce/2009-05/msg00018.php

FOSDEM 2010 will be in Brussels, Belgium on February 6-7, 2010.
http://www.fosdem.org/

Chemnitzer Linuxtage will be in Chemnitz, Germany on March 13-14, 2010.
http://chemnitzer.linux-tage.de/

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

Heikki Linnakangas committed:

- Fix range check in date_recv that tried to limit accepted values to
  only those accepted by date_in().  I confused Julian day numbers and
  number of days since the postgres epoch 2000-01-01 in the original
  patch.  I just noticed that it's still easy to get such out-of-range
  values into the database using to_date or +- operators, but this
  patch doesn't do anything about those functions.  Per report from
  James Pye.

Peter Eisentraut committed:

- In pgsql/src/test/regress/GNUmakefile, check errors in for loop.

- Simplify a few makefile rules since install-sh can now install
  multiple files in one run.

- In pgsql/GNUmakefile.in, remove some leftovers of split tarball
  support.

Alvaro Herrera committed:

- In pgsql/doc/src/sgml/ref/create_table.sgml, fix documentation on
  the toast.fillfactor reloption: it doesn't exist.  Per note from
  Zoltan Boszormenyi.

Tom Lane committed:

- Make FOR UPDATE/SHARE in the primary query not propagate into WITH
  queries; for example in WITH w AS (SELECT * FROM foo) SELECT * FROM
  w, bar ... FOR UPDATE the FOR UPDATE will now affect bar but not
  foo.  This is more useful and consistent than the original 8.4
  behavior, which tried to propagate FOR UPDATE into the WITH query
  but always failed due to assorted implementation restrictions.  Even
  though we are in process of removing those restrictions, it seems
  correct on philosophical grounds to not let the outer query's FOR
  UPDATE affect the WITH query.  In passing, fix isLockedRel which
  frequently got things wrong in nested-subquery cases: "FOR UPDATE OF
  foo" applies to an alias foo in the current query level, not
  subqueries.  This has been broken for a long time, but it doesn't
  seem worth back-patching further than 8.4 because the actual
  consequences are minimal.  At worst the parser would sometimes get
  RowShareLock on a relation when it should be AccessShareLock or vice
  versa.  That would only make a difference if someone were using
  ExclusiveLock concurrently, which no standard operation does, and
  anyway FOR UPDATE doesn't result in visible changes so it's not
  clear that the someone would notice any problem.  Between that and
  the fact that FOR UPDATE barely works with subqueries at all in
  existing releases, I'm not excited about worrying about it.

- In pgsql/src/backend/commands/trigger.c, fix AfterTriggerSaveEvent
  to use a test and elog, not just Assert, to check that it's called
  within an AfterTriggerBeginQuery/AfterTriggerEndQuery pair.  The RI
  cascade triggers suppress that overhead on the assumption that they
  are always run non-deferred, so it's possible to violate the
  condition if someone mistakenly changes pg_trigger to mark such a
  trigger deferred.  We don't really care about supporting that, but
  throwing an error instead of crashing seems desirable.  Per report
  from Marcelo Costa.

- When FOR UPDATE/SHARE is used with LIMIT, put the LockRows plan node
  underneath the Limit node, not atop it.  This fixes the old problem
  that such a query might unexpectedly return fewer rows than the
  LIMIT says, due to LockRows discarding updated rows.  There is a
  related problem that LockRows might destroy the sort ordering
  produced by earlier steps; but fixing that by pushing LockRows below
  Sort would create serious performance problems that are unjustified
  in many real-world applications, as well as potential deadlock
  problems from locking many more rows than expected.  Instead, keep
  the present semantics of applying FOR UPDATE after ORDER BY within a
  single query level; but allow the user to specify the other way by
  writing FOR UPDATE in a sub-select.  To make that work, track
  whether FOR UPDATE appeared explicitly in sub-selects or got pushed
  down from the parent, and don't flatten a sub-select that contained
  an explicit FOR UPDATE.

- Fix AcquireRewriteLocks to be sure that it acquires the right lock
  strength when FOR UPDATE is propagated down into a sub-select
  expanded from a view.  Similar bug to parser's isLockedRel issue
  that I fixed yesterday; likewise seems not quite worth the effort to
  back-patch.

- In pgsql/src/bin/psql/describe.c, fix \df to re-allow regexp special
  characters in the function name pattern.  This has always worked, up
  until somebody's thinko here:
  http://archives.postgresql.org/pgsql-committers/2009-04/msg00233.php
  Per bug #5143 from Piotr Wolinski.

- In pgsql/src/backend/utils/adt/ruleutils.c, un-break EXPLAIN for
  Append plans.  I messed this up a few days ago while adding the
  ModifyTable node type --- I had been thinking ModifyTable should
  replace Append as a special case in push_plan(), but actually both
  of them have to be special-cased.

- In pgsql/src/backend/executor/nodeHash.c, make the overflow guards
  in ExecChooseHashTableSize be more protective.  The original coding
  ensured nbuckets and nbatch didn't exceed INT_MAX, which while not
  insane on its own terms did nothing to protect subsequent code like
  "palloc(nbatch * sizeof(BufFile *))".  Since enormous join size
  estimates might well be planner error rather than reality, it seems
  best to constrain the initial sizes to be not more than
  work_mem/sizeof(pointer), thus ensuring the allocated arrays don't
  exceed work_mem.  We will allow nbatch to get bigger than that
  during subsequent ExecHashIncreaseNumBatches calls, but we should
  still guard against integer overflow in those palloc requests.  Per
  bug #5145 from Bernt Marius Johnsen.  Although the given test case
  only seems to fail back to 8.2, previous releases have variants of
  this issue, so patch all supported branches.

- Implement parser hooks for processing ColumnRef and ParamRef nodes,
  as per my recent proposal.  As proof of concept, remove knowledge of
  Params from the core parser, arranging for them to be handled
  entirely by parser hook functions.  It turns out we need an
  additional hook for that --- I had forgotten about the code that
  handles inferring a parameter's type from context.  This is a
  preliminary step towards letting plpgsql handle its variables
  through parser hooks.  Additional work remains to be done to expose
  the facility through SPI, but I think this is all the changes needed
  in the core parser.

- In pgsql/src/pl/plperl/plperl.c, ensure the previous Perl
  interpreter selection is restored upon exit from
  plperl_call_handler, in both the normal and error-exit paths.  Per
  report from Alexey Klyukin.

- Fix two serious bugs introduced into hash indexes by the 8.4 patch
  that made hash indexes keep entries sorted by hash value.  First,
  the original plans for concurrency assumed that insertions would
  happen only at the end of a page, which is no longer true; this
  could cause scans to transiently fail to find index entries in the
  presence of concurrent insertions.  We can compensate by teaching
  scans to re-find their position after re-acquiring read locks.
  Second, neither the bucket split nor the bucket compaction logic had
  been fixed to preserve hashvalue ordering, so application of either
  of those processes could lead to permanent corruption of an index,
  in the sense that searches might fail to find entries that are
  present.  This patch fixes the split and compaction logic to
  preserve hashvalue ordering, but it cannot do anything about
  pre-existing corruption.  We will need to recommend reindexing all
  hash indexes in the 8.4.2 release notes.  To buy back the
  performance loss hereby induced in split and compaction, fix them to
  use PageIndexMultiDelete instead of retail PageIndexDelete
  operations.  We might later want to do something with qsort'ing the
  page contents rather than doing a binary search for each insertion,
  but that seemed more invasive than I cared to risk in a back-patch.
  Per bug #5157 from Jeff Janes and subsequent investigation.

- Department of second thoughts: after studying index_getnext() a bit
  more I realize that it can scribble on scan->xs_ctup.t_self while
  following HOT chains, so we can't rely on that to stay valid between
  hashgettuple() calls.  Introduce a private variable in
  HashScanOpaque, instead.

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

Michael Paquier sent in another revision of the patch to allow pgbench
to issue shell commands.

ITAGAKI Takahiro sent in another patch to add VACUUM ( FULL [ INPLACE
| REPLACE ] ).

Jeff Davis sent in two more revisions of the operator exclusion
constraints patch.

Peter Eisentraut sent in a patch to make \d+ work better for views in
psql.

Shrish Purohit sent in a patch intended to automate partitioning.

Peter Eisentraut sent in a patch to fix an issue where PL/PythonU can
segfault.

Peter Eisentraut sent in a patch to fix an issue with PL/PythonU's
elog output.

Roger Leigh sent in another patch to make an "ascii clean" format for
psql output.

Hitoshi Harada sent in a patch which implements PL/Javascript using
Google's V8 Javascript engine.

ITAGAKI Takahiro sent in a WIP patch to implement table partitioning
in SQL.

Andreas Freund sent in a patch which makes certain tsearch operations
more efficient.