== PostgreSQL Weekly News - October 31 2010 ==

== PostgreSQL Weekly News - October 31 2010 ==

Happy Hallowe'en/All Hallows Eve/Samhain from the PostgreSQL Weekly
News!

PGDay is scheduled at this year's Southern California Linux Exposition
(SCALE) held in the LAX Hilton Hotel in the city of Los Angeles,
California, on Friday February 25th, 2010.  Post your talk submission
to pgday-submissions AT googlegroups DOT com.

Andreas Scherbaum did a talk in German on PostgreSQL.  Podcast is at
http://andreas.scherbaum.la/blog/archives/743-POFACS-PostgreSQL.html

== PostgreSQL Product News ==

psqlODBC 09.00.0200, an ODBC driver for PostgreSQL, released.
http://psqlodbc.projects.postgresql.org/release.html

Postgres-XC 0.9.3, a clustering system for PostgreSQL, released.
http://postgres-xc.sourceforge.net/

== PostgreSQL Jobs for October ==

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

== PostgreSQL Local ==

The German PostgreSQL User Group is running a booth at OpenRheinRuhr
2010 on november 13rd and 14th in Oberhausen, Germany.  Andreas
Scherbaum is giving a talk about new features in PostgreSQL 9.0.
http://andreas.scherbaum.la/blog/archives/744-PostgreSQL-OpenRheinRuhr-2010.html

The German PostgreSQL User Group is present with a booth, a talk and a
workshop at Brandenburger Linux-Infotag 2010 in Potsdam, Germany on
November 6th.
http://andreas.scherbaum.la/blog/archives/746-PostgreSQL-Brandenburger-Linux-Infotag-2010.html

Registration for PGDay.EU 2010 held on December 6-8 in Stuttgart,
Germany is open.
http://2010.pgday.eu/register

PgDay.IT 2010 will be in Rome this year December 10th.  The Call for
Papers is now open.
http://www.pgday.it/


== 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.  Spanish language
to pwn@arpug.com.ar.

== Reviews ==

== Applied Patches ==

Tom Lane pushed:

- Work around rounding misbehavior exposed by buildfarm.
  http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=4ba61a487e39eaf859cd9face9520b30127e6a91

- Fix inline_set_returning_function() to preserve the invalItems list
  properly.  This avoids a possible crash when inlining a SRF whose
  argument list contains a reference to an inline-able user function.
  The crash is quite reproducible with CLOBBER_FREED_MEMORY enabled,
  but would be less certain in a production build.  Problem introduced
  in 9.0 by the named-arguments patch, which requires invoking
  eval_const_expressions() before we can try to inline a SRF.  Per
  report from Brendan Jurd.
  http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=ef55e294e66725c412d55b9689328235141d816d

- Fix overly-enthusiastic Assert in printing of Param reference
  expressions.  A NestLoopParam's value can only be a Var or Aggref,
  but this isn't the case in general for SubPlan parameters, so
  print_parameter_expr had better be prepared to cope.  Brain fade in
  my recent patch to print the referenced expression instead of just
  printing $N for PARAM_EXEC Params.  Per report from Pavel Stehule.
  http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=c6873eac4c33720140240cdbd1a663fecc922c57

- Fix up some oversights in psql's Unicode-escape support.  Original
  patch failed to include new exclusive states in a switch that needed
  to include them; and also was guilty of very fuzzy thinking about
  how to handle error cases.  Per bug #5729 from Alan Choi.
  http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=35d89401525677441824c6f2116b9f7d0cc3caac

- Previous patch had no detectable virtue other than being a
  one-liner.  Try to make the code look self-consistent again, so it
  doesn't confuse future developers.
  http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=e6721c6e1617a0fc8b4bce8eacba8b5a381f1f21

- Fix plpgsql's handling of "simple" expression evaluation.  In
  general, expression execution state trees aren't re-entrantly
  usable, since functions can store private state information in them.
  For efficiency reasons, plpgsql tries to cache and reuse state trees
  for "simple" expressions.  It can get away with that most of the
  time, but it can fail if the state tree is dirty from a previous
  failed execution (as in an example from Alvaro) or is being used
  recursively (as noted by me).  Fix by tracking whether a state tree
  is in use, and falling back to the "non-simple" code path if so.
  This results in a pretty considerable speed hit when the non-simple
  path is taken, but the available alternatives seem even more
  unpleasant because they add overhead in the simple path.  Per idea
  from Heikki Linnakangas.  Back-patch to all supported branches.
  http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=8ce22dd4c51b6e8155889653c0116cad13877512

- Save a few cycles in plpgsql simple-expression initialization.
  Instead of using ExecPrepareExpr, call ExecInitExpr.  The net change
  here is that we don't apply expression_planner() to the expression
  tree.  There is no need to do so, because that tree is extracted
  from a fully planned plancache entry, so all the needed work is
  already done.  This reduces the setup costs by about a factor of 2
  according to some simple tests.  Oversight noted while fooling
  around with the simple-expression code for previous fix.
  http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=37e0a016547abb3526b685d7aacbd0a4d57f6937

- In psql, make \? output of \dg and \du the same.  The previous
  wording might have suggested that \du only showed login roles and
  \dg only group roles, but that is no longer the case.  Proposed by
  Josh Kupershmidt.
  http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=299591d1a293e737d72d57aa8545c6c00d19db1d

- In psql, add tab completion for psql \dg and \z.  Josh Kupershmidt.
  http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=a3d40e9fb51f8aab9cde5d5018be5e46332275fd

- Avoid creation of useless EquivalenceClasses during planning.
  Zoltan Boszormenyi exhibited a test case in which planning time was
  dominated by construction of EquivalenceClasses and PathKeys that
  had no actual relevance to the query (and in fact got discarded
  immediately).  This happened because we generated PathKeys
  describing the sort ordering of every index on every table in the
  query, and only after that checked to see if the sort ordering was
  relevant.  The Emmanuel Cecchet/PK construction code is O(N^2) in
  the number of ECs, which is all right for the intended number of
  such objects, but it gets out of hand if there are ECs for lots of
  irrelevant indexes.  To fix, twiddle the handling of mergeclauses a
  little bit to ensure that every interesting Emmanuel Cecchet is
  created before we begin path generation.  (This doesn't cost
  anything --- in fact I think it's a bit cheaper than before ---
  since we always eventually created those ECs anyway.)  Then, if an
  index column can't be found in any pre-existing Emmanuel Cecchet, we
  know that that sort ordering is irrelevant for the query.  Instead
  of creating a useless Emmanuel Cecchet, we can just not build a
  pathkey for the index column in the first place.  The index will
  still be considered if it's useful for non-order-related reasons,
  but we will think of its output as unsorted.
  http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=14231a41a94b2345cc679ff67dbee1bf7dac7029

- Oops, missed one fix for EquivalenceClass rearrangement.  Now that
  we're expecting a mergeclause's left_ec/right_ec to persist from the
  initial assignments, we can't just blithely zero these out when
  transforming such a clause in adjust_appendrel_attrs.  But really it
  should be okay to keep the parent's values, since a child table's
  derived Var ought to be equivalent to the parent Var for all
  EquivalenceClass purposes.  (Indeed, I'm wondering whether we
  couldn't find a way to dispense with add_child_rel_equivalences
  altogether.  But this is wrong in any case.)
  http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=48a1fb23900d73e7d9cb2dc0408c745cd03597a7

- Fix comparisons of pointers with zero to compare with NULL instead.
  Per C standard, these are semantically the same thing; but saying
  NULL when you mean NULL is good for readability.  Marti Raudsepp,
  per results of INRIA's Coccinelle.
  http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=bfd3f37be309c3647844aed937e6a66aad5fd3cb

- Provide hashing support for arrays.  The core of this patch is
  hash_array() and associated typcache infrastructure, which works
  just about exactly like the existing support for array comparison.
  In addition I did some work to ensure that the planner won't think
  that an array type is hashable unless its element type is hashable,
  and similarly for sorting.  This includes adding a datatype
  parameter to op_hashjoinable and op_mergejoinable, and adding an
  explicit "hashable" flag to SortGroupClause.  The lack of a
  cross-check on the element type was a pre-existing bug in mergejoin
  support --- but it didn't matter so much before, because if you
  couldn't sort the element type there wasn't any good alternative to
  failing anyhow.  Now that we have the alternative of hashing the
  array type, there are cases where we can avoid a failure by being
  picky at the planner stage, so it's time to be picky.  The issue of
  exactly how to combine the per-element hash values to produce an
  array hash is still open for discussion, but the rest of this is
  pretty solid, so I'll commit it as-is.
  http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=186cbbda8f8dc5e42f68fc7892f206a76d56a20f

- Revert removal of trigger flag from plperl function hash key.  As
  noted by Jan Urbanski, this flag is in fact needed to ensure that
  the function's input/result conversion functions are set up as
  expected.  Add a regression test to discourage anyone from making
  same mistake in future.
  http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=76b12e0af765b2ca2eeddcabdd5e7c22eda164da

Alvaro Herrera pushed:

- find -path is not portable, so use grep -v instead.  Per previous
  failure of buildfarm member koi (which is no longer failing, alas).
  http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=9350824e708678f6af9657979ba656cf1951b408

Peter Eisentraut pushed:

- Refactor typenameTypeId() Split the old typenameTypeId() into two
  functions: A new typenameTypeId() that returns only a type OID, and
  typenameTypeIdAndMod() that returns type OID and typmod.  This
  isolates call sites better that actually care about the typmod.
  http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=35670340f57d78d6ab023b1fb42fd81339f85d4c

- Add missing newlines at end of files.
  http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=a87d21263600a978ff85142c53375ab25eb30bce

- Remove obsolete release-alpha.sgml.  This was only used while the
  final release notes for 9.0 were being prepared.  The alpha release
  notes are now in release-9.1.sgml.
  http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=18d6437885d2cf50bb16b09676ff054054591290

- Release notes for 9.1alpha2.
  http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=e9eb4f401333209139af1acee2cd4251b05e0397

- Remove tabs from SGML.
  http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=2999f4ef354a72f00ab0033ed9c0463d89212b99

- Last-minute updates to 9.1alpha2 release notes.
  http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=71165685b265a8e5fdd615485917affdb0e8fa32

- Change version number in release notes to 9.1alpha2
  http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=bd1ff9713369c2f54391112b92e0c22ab5c99180

ITAGAKI Takahiro pushed:

- Add .gitignore for contrib/uuid-ossp.
  http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=07ca728f699694781ce62eae9e2fa61cd4ffa542

- Fix typos "are are".
  http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=bf76ad07fe59affc64f1554d4ff20ddf4d5e8e59

Heikki Linnakangas pushed:

- Before removing backup_label and irrevocably changing pg_control
  file, check that WAL file containing the checkpoint redo-location
  can be found.  This avoids making the cluster irrecoverable if the
  redo location is in an earlie WAL file than the checkpoint record.
  Report, analysis and patch by Jeff Davis, with small changes by me.
  http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=0c6293dd0361a0d3c72c94ced389c509019713a7

- Note explicitly that hash indexes are also not replicated because
  they're not WAL-logged.  Make the notice about the lack of
  WAL-logging more visible by making it a <caution>.  Also remove the
  false statement from hot standby caveats section that hash indexes
  are not used during hot standby.
  http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=5c38782cc8b3219d43ac2ccaf4254fd590bde758

- Fix long-standing segfault when accept() or one of the calls made
  right after accepting a connection fails, and the server is compiled
  with GSSAPI support. Report and patch by Alexander V. Chernikov, bug
  #5731.
  http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=869af50fcfdd4448b2dc637d905f1afda689741d

- Give a more specific error message if you try to COMMIT, ROLLBACK or
  COPY FROM STDIN in PL/pgSQL. We alread did this for dynamic EXECUTE
  statements, ie. "EXECUTE 'COMMIT'", but not otherwise.
  http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=f184de351d6a35355aa8f1c1b655c3f6a5087205

Robert Haas pushed:

- Fix dumb typo in SECURITY LABEL error message.  Report by Peter
  Eisentraut.
  http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=3579a94d6acd9374fbc3b45d0be593331ffd414d

- Minor fixups for psql's process_file() function.  Avoid closing
  stdin, since we didn't open it.  Previously multiple inclusions of
  stdin would be terminated with a single quit, now a separate quit is
  needed for each invocation. Previous behavior also accessed stdin
  after it was fclose()d, which is undefined behavior per ANSI C.
  Properly restore pset.inputfile, since the caller expects to be able
  to free that memory.  Marti Raudsepp.
  http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=1fea0c05eb4ac4a21d79471b9a7fe96163306b88

- Add a client authentication hook.  KaiGai Kohei, with minor cleanup
  of the comments by me.
  http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=20709f813601976076a346c0b0f8e92006e3b3fa

- Note that effective_io_concurrency only affects bitmap heap scans.
  Josh Kupershmidt.
  http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=5a12c808cf223c7a5498217142cc9cb45efe033a

- Reorganize OS-specific details about write caching into a list.
  Along the way, clarify that sdparm can be used on Linux as well as
  FreeBSD.
  http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=0d5deebe11818a54f79ca66e1a1d41dae0e0b52b

- Revert "Correct WAL space calculation formula in docs." This reverts
  commit 915116bc62db2aaec7001bde6610128f4cbd29f9.  Per discussion,
  the previous formula was in fact correct.
  http://archives.postgresql.org/pgsql-docs/2010-10/msg00038.php
  http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=2cae0aeb9c61aef48b6de3fb5c4f7785a06bca0b

Andrew Dunstan pushed:

- Allow generic record arguments to plperl functions.
  http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=6c3c7b533e4b73bef99996f92da26c7c4124edd3

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

Shigeru HANADA sent in another revision of the patch to add SQL/MED
with simple wrappers.

ITAGAKI Takahiro sent in two revisions of a patch to add executor
nodes, which will help with, among other things, SQL/MED.

Alvaro Herrera sent in a patch atop the patch to add EXTENSIONs.

David Fetter sent in two revisions of a patch to add tab completion in
psql for views made writeable by triggers.

Dimitri Fontaine sent in another patch for EXTENSIONs.

Zoltan Boszormenyi, Heikki Linnakangas and Tom Lane traded patches to
speed up planning times for queries on tables with large numbers of
partitions.

Alvaro Herrera sent in a patch to add "ALTER TYPE...ADD ELEMENT" to
enums.

Steve Singer sent in a patch to modify TRUNCATE ... RESTART IDENTITY
so that when the transaction rolls back, the restart of the sequence
also rolls back.

Robert Haas sent in a patch to help explain about sdparm's effects on
WAL durability.

Alvaro Herrera sent in a patch to change ALTER TYPE...ENUM... to
include ADD LABEL [BEFORE|AFTER].  The LABEL is new.

Zoltan Boszormenyi sent in a patch for ECPG to get WHERE CURRENT OF to
accept dynamic cursor names.

Heikki Linnakangas sent in a patch to allow tracking the latest
timeline in standby mode.

Marti Raudsepp sent in a patch to change places where pointers were
compared to 0 to compare them to NULL.

Alexander Korotkov sent in a patch to fix the GiST picksplit method
implementation in contrib/cube.

Andres Freund sent in another revision of the patch to cancel IIT
backends.

Marti Raudsepp sent in some more fixes based on Coccinelli runs.

Dimitri Fontaine sent in a patch to add ALTER...SET SCHEMA... to those
object types that don't already have it.

Peter Eisentraut sent in a patch to avoid passing around type OID +
typmod (+ collation) separately all over the place.  Instead, there is
a new struct TypeInfo that contains these fields, and only a pointer
is passed around.

Alex Hunsaker sent in a patch to fix another issue with PL/Perl
trigger functions.

Andres Freund sent in a patch to improve the performance of int32- and
int64-to-text conversions by creating a custom converter rather than
using the standard itoa functions.