== PostgreSQL Weekly News - March 25 2012 ==

== PostgreSQL Weekly News - March 25 2012 ==

== PostgreSQL Product News ==

devart dotConnect 5.80 for PostgreSQL, an ADO.NET provider, released.
http://www.devart.com/dotconnect/postgresql/

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

== PostgreSQL Jobs for March ==

http://archives.postgresql.org/pgsql-jobs/2012-03/threads.php

== PostgreSQL Local ==

PGDay Austin 2012 will be held March 28.
http://www.postgresql.org/about/event/1379/

Initial meeting of Arizona PostgreSQL Users Group at 5:00 PM on
Thursday, March 29, 2012.  Included will be a presentation by Ken
Rosensteel that is an update of his 2011 PGEast presentation on Bull's
experiences with Large Customer Usage of PostgreSQL.  You can join the
mailing list, see the agenda, and RSVP at:
https://www.bigtent.com/groups/azpug

PGDay DC 2012 will be held on March 30.
http://pgday.bwpug.org

PGDay NYC will be held April 2, 2012 at Lighthouse International in
New York City.
http://pgday.nycpug.org

London PostgreSQL Evening Meetup, 17 April 2012
http://www.meetup.com/London-PostgreSQL-Meetup-Group/

PGCon 2012 will be held 17-18 May 2012, in Ottawa at the University of
Ottawa.  It will be preceded by two days of tutorials on 15-16 May 2012.
http://www.pgcon.org/2012/

PGDay France will be in Lyon on June 7, 2012.
http://www.pgday.fr

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

== Applied Patches ==

Bruce Momjian pushed:

- In pg_upgrade, remove dependency on pg_config, as that might not be
  in the non-development install.  Instead, use the LOAD mechanism to
  check for the pg_upgrade_support shared object, like we do for other
  shared object checks.  Backpatch to 9.1.  Report from Alvaro Herrera
  http://git.postgresql.org/pg/commitdiff/02b183acb42831bbeba819fde568263f19f2acdc

Alvaro Herrera pushed:

- plperl: Package-qualify _TD.  Failing to do so causes trigger
  invocation to fail when they are nested within a function invocation
  that changes the current package.  Backpatch to 9.1; previous
  releases used a different method to obtain _TD.  Per bug report from
  Mark Murawski (bug #6511) Author: Alex Hunsaker
  http://git.postgresql.org/pg/commitdiff/814e08e895a037e3f5485000db5964cd68dbfa01

- pg_dump: fix double free of query results.  This bug was introduced
  while refactoring in commit 1631598e --- no need to back-patch.  Bug
  report and fix from Joachim Wieland.
  http://git.postgresql.org/pg/commitdiff/77503a7638a35eedd9cb08d9ca4c54deb203521d

- Update struct Trigger in docs
  http://git.postgresql.org/pg/commitdiff/438269eb104446ff0c36d893cd6f01be8405d327

- Fix trigger example code to match header changes.  I should have
  done this in b93f5a5673b4bb09e14eb80fe28aa21fc20a6271 but didn't
  notice the problem at the time.  Per report from Marco Nenciarini
  http://git.postgresql.org/pg/commitdiff/b251cf31936b4507ad8ba78f34a14f592c664f43

- pg_dump: get rid of die_horribly.  The old code was using
  exit_horribly or die_horribly other depending on whether it had an
  ArchiveHandle on which to close the connection or not; but there
  were places that were passing a NULL ArchiveHandle to die_horribly,
  and other places that used exit_horribly while having an Alvaro
  Herrera available.  So there wasn't all that much consistency.
  Improve the situation by keeping only one of the routines, and
  instead of having to pass the Alvaro Herrera down from the caller,
  arrange for it to be present for an on_exit_nicely callback to
  operate on.  Author: Joachim Wieland.  Some tweaks by me Per a
  suggestion from Robert Haas, in the ongoing "parallel pg_dump" saga.
  http://git.postgresql.org/pg/commitdiff/9d23a70d513aa1312135d6cedd444e7e3e933edb

Tom Lane pushed:

- Restructure SELECT INTO's parsetree representation into
  CreateTableAsStmt.  Making this operation look like a utility
  statement seems generally a good idea, and particularly so in light
  of the desire to provide command triggers for utility statements.
  The original choice of representing it as SELECT with an IntoClause
  appendage had metastasized into rather a lot of places,
  unfortunately, so that this patch is a great deal more complicated
  than one might at first expect.  In particular, keeping EXPLAIN
  working for SELECT INTO and CREATE TABLE Alexander Shulgin
  subcommands required restructuring some EXPLAIN-related APIs.
  Add-on code that calls ExplainOnePlan or ExplainOneUtility, or uses
  ExplainOneQuery_hook, will need adjustment.  Also, the cases PREPARE
  ... SELECT INTO and CREATE RULE ... SELECT INTO, which formerly were
  accepted though undocumented, are no longer accepted.  The PREPARE
  case can be replaced with use of CREATE TABLE Alexander Shulgin
  EXECUTE.  The CREATE RULE case doesn't seem to have much real-world
  use (since the rule would work only once before failing with "table
  already exists"), so we'll not bother with that one.  Both SELECT
  INTO and CREATE TABLE Alexander Shulgin still return a command tag
  of "SELECT nnnn".  There was some discussion of returning "CREATE
  TABLE nnnn", but for the moment backwards compatibility wins the
  day.  Andres Freund and Tom Lane
  http://git.postgresql.org/pg/commitdiff/9dbf2b7d75de5af38d087cbe2b1147dd0fd10f0a

- Improve the -l (limit) option recently added to contrib/vacuumlo.
  Instead of just stopping after removing an arbitrary subset of
  orphaned large objects, commit and start a new transaction after
  each -l objects.  This is just as effective as the original patch at
  limiting the number of locks used, and it doesn't require doing the
  OID collection process repeatedly to get everything.  Since the
  option no longer changes the fundamental behavior of vacuumlo, and
  it avoids a known server-side limitation, enable it by default (with
  a default limit of 1000 LOs per transaction).  In passing, be more
  careful about properly quoting the names of tables and fields, and
  do some other cosmetic cleanup.
  http://git.postgresql.org/pg/commitdiff/64c604898e812aa93c124c666e8709fff1b8dd26

- Back-patch contrib/vacuumlo's new -l (limit) option into 9.0 and
  9.1.  Since 9.0, removing lots of large objects in a single
  transaction risks exceeding max_locks_per_transaction, because we
  merged large object removal into the generic object-drop mechanism,
  which takes out an exclusive lock on each object to be dropped.
  This creates a hazard for contrib/vacuumlo, which has historically
  tried to drop all unreferenced large objects in one transaction.
  There doesn't seem to be any correctness requirement to do it that
  way, though; we only need to drop enough large objects per
  transaction to amortize the commit costs.  To prevent a regression
  from pre-9.0 releases wherein vacuumlo worked just fine, back-patch
  commits b69f2e36402aaa222ed03c1769b3de6d5be5f302 and
  64c604898e812aa93c124c666e8709fff1b8dd26, which break vacuumlo's
  deletions into multiple transactions with a user-controllable upper
  limit on the number of objects dropped per transaction.  Tim Lewis,
  Robert Haas, Tom Lane
  http://git.postgresql.org/pg/commitdiff/5bd06e619c82c3b2e29fed40aae5fc39a9620908

- Allow new relmapper entries when allow_system_table_mods is true.
  This restores the pre-9.0 situation that it's possible to add new
  indexes on pg_class and other mapped-but-not-shared catalogs, so
  long as you broke the glass and flipped the big red Dont-Touch-Me
  switch.  As before, there are a lot of gotchas, and you'd have to be
  pretty desperate to try this on a production database; but there
  doesn't seem to be a reason for relmapper.c to be preventing such
  things all by itself.  Per experimentation with a case suggested by
  Cody Cutrer.
  http://git.postgresql.org/pg/commitdiff/f70f095c9096d5e2689e8d79172b37b57a84e51b

- Fix configure's search for collateindex.pl.  PGAC_PATH_COLLATEINDEX
  supposed that it could use AC_PATH_PROGS to search for
  collateindex.pl, but that macro will only accept files that are
  marked executable, and at least some DocBook installations don't
  mark the script executable (a case the docs Makefile was already
  prepared for).  Accept the script if it's present and readable in
  $DOCBOOKSTYLE/bin, and otherwise search the PATH as before.  Having
  fixed that up, we don't need the fallback case that was in the docs
  Makefile, and instead can throw an understandable error if configure
  didn't find the script.  Per recent trouble report from John Lumby.
  http://git.postgresql.org/pg/commitdiff/f2386d7136dacbb8bf59ebbe8e5c5b73db202813

- If a role has a password expiration date, show that in psql's \du
  output.  Per a suggestion from Euler Taveira, it seems like a good
  idea to include this information in \du (and \dg) output.  This
  costs nothing for people who are not using the VALID UNTIL feature,
  while for those who are, it's rather critical information.  Fabrízio
  de Royes Mello
  http://git.postgresql.org/pg/commitdiff/04dfc8774966c60e4031ab5d8eeb3263d9568ad3

- Refactor to eliminate duplicate copies of conninfo default-finding
  code.  Alexander Shulgin, lightly edited by me.
  http://git.postgresql.org/pg/commitdiff/e9ce658b6203e9052bff5c89ed6265fba69cdb36

- Fix GET DIAGNOSTICS for case of assignment to function's first
  variable.  An incorrect and entirely unnecessary "safety check" in
  exec_stmt_getdiag() caused the code to treat an assignment to a
  variable with dno zero as a no-op.  Unfortunately, that's a
  perfectly valid dno.  This has been broken since GET DIAGNOSTICS was
  invented.  It's not terribly surprising that the bug went unnoticed
  for so long, since in most cases you probably wouldn't use the
  function's first-created variable (normally its first parameter) as
  a GET DIAGNOSTICS target.  Nonetheless, it's broken.  Per bug #6551
  from Adam Buraczewski.
  http://git.postgresql.org/pg/commitdiff/88a4cb30a4c48a6b11fd4641855595d2678aa123

- Code review for protransform patches.  Fix loss of previous
  expression-simplification work when a transform function fires: we
  must not simply revert to untransformed input tree.  Instead build a
  dummy FuncExpr node to pass to the transform function.  This has the
  additional advantage of providing a simpler, more uniform API for
  transform functions.  Move documentation to a somewhat less buried
  spot, relocate some poorly-placed code, be more wary of null
  constants and invalid typmod values, add an opr_sanity check on
  protransform function signatures, and some other minor cosmetic
  adjustments.  Note: although this patch touches pg_proc.h, no need
  for catversion bump, because the changes are cosmetic and don't
  actually change the intended catalog contents.
  http://git.postgresql.org/pg/commitdiff/0339047bc93147c1c6f78f867ae6b0c215406235

- Refactor simplify_function et al to centralize argument
  simplification.  We were doing the recursive simplification of
  function/operator arguments in half a dozen different places, with
  rather baroque logic to ensure it didn't get done multiple times on
  some arguments.  This patch improves that by postponing argument
  simplification until after we've dealt with named parameters and
  added any needed default expressions.  Marti Raudsepp, somewhat
  hacked on by me
  http://git.postgresql.org/pg/commitdiff/81a646febe87964725647a36d839f6b4b405f3ae

- Cast some printf arguments to avoid possibly-nonportable behavior.
  Per compiler warnings on buildfarm member black_firefly.
  http://git.postgresql.org/pg/commitdiff/ed61127be483d8939e13a151773549f1517d6e67

- Fix planner's handling of outer PlaceHolderVars within subqueries.
  For some reason, in the original coding of the PlaceHolderVar
  mechanism I had supposed that PlaceHolderVars couldn't propagate
  into subqueries.  That is of course entirely possible.  When it
  happens, we need to treat an outer-level PlaceHolderVar much like an
  outer Var or Aggref, that is SS_replace_correlation_vars() needs to
  replace the PlaceHolderVar with a Param, and then when building the
  finished SubPlan we have to provide the PlaceHolderVar expression as
  an actual parameter for the SubPlan.  The handling of the contained
  expression is a bit delicate but it can be treated exactly like an
  Aggref's expression.  In addition to the missing logic in
  subselect.c, prepjointree.c was failing to search subqueries for
  PlaceHolderVars that need their relids adjusted during subquery
  pullup.  It looks like everyplace else that touches PlaceHolderVars
  got it right, though.  Per report from Mark Murawski.  In 9.1 and
  HEAD, queries affected by this oversight would fail with "ERROR:
  Upper-level PlaceHolderVar found where not expected".  But in 9.0
  and 8.4, you'd silently get possibly-wrong answers, since the value
  transmitted into the subquery wouldn't go to null when it should.
  http://git.postgresql.org/pg/commitdiff/8279eb4191c7ab9920c72ec8eec5df0e7b8c7530

- Replace empty locale name with implied value in CREATE DATABASE and
  initdb.  setlocale() accepts locale name "" as meaning "the locale
  specified by the process's environment variables".  Historically
  we've accepted that for Postgres' locale settings, too.  However,
  it's fairly unsafe to store an empty string in a new database's
  pg_database.datcollate or datctype fields, because then the
  interpretation could vary across postmaster restarts, possibly
  resulting in index corruption and other unpleasantness.  Instead, we
  should expand "" to whatever it means at the moment of calling
  CREATE DATABASE, which we can do by saving the value returned by
  setlocale().  For consistency, make initdb set up the initial lc_xxx
  parameter values the same way.  initdb was already doing the right
  thing for empty locale names, but it did not replace non-empty names
  with setlocale results.  On a platform where setlocale chooses to
  canonicalize the spellings of locale names, this would result in
  annoying inconsistency.  (It seems that popular implementations of
  setlocale don't do such canonicalization, which is a pity, but the
  POSIX spec certainly allows it to be done.)  The same risk of
  inconsistency leads me to not venture back-patching this, although
  it could certainly be seen as a longstanding bug.  Per report from
  Jeff Davis, though this is not his proposed patch.
  http://git.postgresql.org/pg/commitdiff/c7cea267de3ca05b29a57b9d113b95ef3793c8d8

- Fix COPY FROM for null marker strings that correspond to invalid
  encoding.  The COPY documentation says "COPY FROM matches the input
  against the null string before removing backslashes".  It is
  therefore reasonable to presume that null markers like E'\\0' will
  work ... and they did, until someone put the tests in the wrong
  order during microoptimization-driven rewrites.  Since then, we've
  been failing if the null marker is something that would de-escape to
  an invalidly-encoded string.  Since null markers generally need to
  be something that can't appear in the data, this represents a
  nontrivial loss of functionality; surprising nobody noticed it
  earlier.  Per report from Jeff Davis.  Backpatch to 8.4 where this
  got broken.
  http://git.postgresql.org/pg/commitdiff/e8476f46fc847060250c92ec9b310559293087fc

Peter Eisentraut pushed:

- pg_upgrade: Add new generated file to .gitignore
  http://git.postgresql.org/pg/commitdiff/1b12c3e836676e8e94ba94e15c25b100ce0debf0

- pg_dump: Remove undocumented "files" output format.  This was for
  demonstration only, and now it was creating compiler warnings from
  zlib without an obvious fix (see also
  d923125b77c5d698bb8107a533a21627582baa43), let's just remove it.
  The "directory" format is presumably similar enough anyway.
  http://git.postgresql.org/pg/commitdiff/19f45565f581ce605956c29586bfd277f6012eec

- Clean up compiler warnings from unused variables with asserts
  disabled.  For those variables only used when asserts are enabled,
  use a new macro PG_USED_FOR_ASSERTS_ONLY, which expands to
  __attribute__((unused)) when asserts are not enabled.
  http://git.postgresql.org/pg/commitdiff/0e85abd658738db9ea183c133c89c3c94ee37dae

- Add installing entab to pgindent instructions.  And minor other
  pgindent documentation tweaks.
  http://git.postgresql.org/pg/commitdiff/621eb156f159d4979193939902f49ea597972a7d

Robert Haas pushed:

- Remove stray word from sepgsql documentation.
  http://git.postgresql.org/pg/commitdiff/0b43edbca708a33a83d6842e96b837ad77f3e5bc

- Add some CHECK_FOR_INTERRUPTS() calls to the heap-sort call path.  I
  broke this in commit 337b6f5ecf05b21b5e997986884d097d60e4e3d0, which
  among other things arranged for quicksorts to CHECK_FOR_INTERRUPTS()
  slightly less frequently.  Sadly, it also arranged for heapsorts to
  CHECK_FOR_INTERRUPTS() much less frequently.  Repair.
  http://git.postgresql.org/pg/commitdiff/aefa6d163e21391626c20a508af33373d6ba387e

- Improve connectMaintenanceDatabase() error reporting.  The prior
  coding instructs the user to pick an alternative maintenance
  database, but this is overly clever, since it obscures whatever the
  real cause of the failure is.  Josh Kupershmidt
  http://git.postgresql.org/pg/commitdiff/99081c63e40ac6e33c83406b0958aa9426193fea

- Doc updates for index-only scans.  Document that routine vacuuming
  is now also important for the purpose of index-only scans; and
  mention in the section that describes the visibility map that it is
  used to implement index-only scans.  Marti Raudsepp, with some
  changes by me.
  http://git.postgresql.org/pg/commitdiff/5b9c1e6d52dfd022a074462e029bfd7f7bc4321c

- Update docs on numeric storage requirements.  Since 9.1, the minimum
  overhead is three bytes, not five.  Fujii Masao
  http://git.postgresql.org/pg/commitdiff/e83f827f5abeeca4750b40bd6d8701d91fead8dd

- Doc clarifications regarding use of varlena.  Jay Levitt, reviewed
  by Tom Lane.
  http://git.postgresql.org/pg/commitdiff/fdf719cd2868144b5bd78971217bb36bb970e15e

- Clarify that PQconninfoParse returns an array with all legal
  options.  Per discussion with Dmitriy Igrishin and Tom Lane.
  http://git.postgresql.org/pg/commitdiff/e08b4101e1daa2f4e6644330918177a10cac0aab

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

Marco Nenciarini sent in another revision of the patch to support
arrays each element of which is a foreign key.

Andres Freund sent in a patch to check that the specified tablespace
in a CREATE TABLE AS command is not pg_global.

Daniel Farina sent in another revision of the patch to allow users to
terminate their own backends.

Dimitri Fontaine sent in another revision of the patch to implement
command triggers.

Robert Haas sent in a patch to remove a dead code path in
heap_freeze_tuple().

Dimitri Fontaine sent in three more revisions of the patch to make
finer EXTENSION dependencies.

Ants Aasma sent in another revision of the patch to add timing of
buffer I/O requests.

Peter Geoghegan sent in another revision of the patch to publish
checkpoint timing and sync files summary data to pg_stat_bgwriter.

Alexander Shulgin sent in another revision of the patch to add a URI
connection string to libpq.

Peter Eisentraut sent in another revision of the patch to allow
renaming a DOMAIN constraint.

Marko Kreen sent in another revision of the patch to create a new,
more efficient tuple storage in libpq and use same in dblink.

David Fetter sent in two more revisions of a patch to allow CREATE
FOREIGN TABLE to take a LIKE clause.

Fujii Masao sent in three revisions of a patch to change
pg_controldata so that it reports the name of WAL file containing the
latest checkpoint's REDO record.

Zoltan Boszormenyi sent in another revision of the patch to enable
ECPG fetch readahead.

Joachim Wieland sent in another revision of the patch to enable
parallel pg_dump.

Jeff Davis sent in another revision of the patch to fix initdb's
behavior with fsync.

Peter Geoghegan sent in another revision of the patch to allow
pg_stat_statements to be normalized.