== PostgreSQL Weekly News - March 18 2012 ==

== PostgreSQL Weekly News - March 18 2012 ==

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

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

== PostgreSQL Product News ==

DBD::Pg 2.19.1, the Perl interface to PostgreSQL, released.
http://search.cpan.org/~turnstep/DBD-Pg-2.19.1/

Lock Tuple extension released:
http://people.apache.org/~fabien/tuplock/

PGXN Client 1.0.2 released.
http://pgxnclient.projects.postgresql.org/

PostgreDAC 2.8.0, a Delphi/C++ builder for PostgreSQL, released.
http://microolap.com/products/connectivity/postgresdac/download/

Skytools 2.1.13, a package of tools developed by Skype for replication
and failover including PgQ, a generic queuing framework and Londiste,
a row-based master-slave replication replication system, released:
http://pgfoundry.org/frs/shownotes.php?release_id=1932

Skytools 3.0, including cascaded queuing, parallel copy,
multi-database ticker, and new Londiste handler modules for custom
processing, released.
http://skytools.projects.postgresql.org/skytools-3.0/doc/skytools3.html

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

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

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.

== Reviews ==

== Applied Patches ==

Bruce Momjian pushed:

- Remove tabs in SGML files
  http://git.postgresql.org/pg/commitdiff/9a39583264e46e6d2f869f202847cd8ab6c43f81

- In pg_upgrade, add various logging improvements: add ability to
  control permissions of created files, have psql echo its queries for
  easier debugging, output four separate log files, and delete them on
  success, add -r/--retain option to keep log files after success,
  make logs file append-only, remove -g/-G/-l logging options, suggest
  tailing appropriate log file on failure, enhance -v/--verbose
  behavior
  http://git.postgresql.org/pg/commitdiff/717f6d60859cc2d1d7dfd57520531a50bd78df9c

- Uppercase pg_upgrade status output title.
  http://git.postgresql.org/pg/commitdiff/7c8cfed78a52113095e51cf92b20f6606c3141be

- In pg_upgrade, create a script to incrementally generate more
  accurate optimizer statistics so the cluster can be made available
  sooner.
  http://git.postgresql.org/pg/commitdiff/faec2815c8ea9d50da503896bb55222aabcc9741

- In pg_upgrade, move new echo quote define into include file.
  http://git.postgresql.org/pg/commitdiff/ced53c38d7a05694bc316229e2332aebd98388aa

Peter Eisentraut pushed:

- Use correct sizeof operand in qsort call.  Probably no practical
  impact, since all pointers ought to have the same size, but it was
  wrong nonetheless.  Found by Coverity.
  http://git.postgresql.org/pg/commitdiff/bad250f4f31704f05247fa4696ac2077f884ed8e

- Add comment for missing break in switch.  For clarity, following
  other sites, and to silence Coverity.
  http://git.postgresql.org/pg/commitdiff/c9f310d377d1d8f8acd2d05bf7920704d33affe5

- Add additional safety check against invalid backup label file.  It
  was already checking for invalid data after "BACKUP FROM", but would
  possibly crash if "BACKUP FROM" was missing altogether.  Found by
  Coverity
  http://git.postgresql.org/pg/commitdiff/e684ab5e1ec900bb7b73fb6f8a655e0ec54b3720

- COPY: Add an assertion.  This is for tools such as Coverity that
  don't know that the grammar enforces that the case of not having a
  relation (but instead a query) cannot happen in the FROM case.
  http://git.postgresql.org/pg/commitdiff/6f018c6dda201818251d8ead03866289b6b7e1c8

- Add missing va_end() calls.  Found by Coverity
  http://git.postgresql.org/pg/commitdiff/942b63193c722a58ed24b3305308836b47b3f028

- pg_dump: Fix some minor memory leaks.  Although we often don't care
  about freeing all memory in pg_dump, these functions already freed
  the same memory in other code paths, so we might as well do it
  consistently.  Found by Coverity
  http://git.postgresql.org/pg/commitdiff/acfaa596ccd90e161bcc09bb00e755e65d9c068c

- Improve EncodeDateTime and EncodeTimeOnly APIs.  Use an explicit
  argument to tell whether to include the time zone in the output,
  rather than using some undocumented pointer magic.
  http://git.postgresql.org/pg/commitdiff/ad4fb0d0d2f33ec0165f2a9a50a6d8cbcef4ab82

- Remove unused tzn arguments for timestamp2tm()
  http://git.postgresql.org/pg/commitdiff/531e60aec02fa14d02185e42363b2277427e2119

- Add const qualifier to tzn returned by timestamp2tm().  The tzn
  value might come from tm->tm_zone, which libc declares as const, so
  it's prudent that the upper layers know about this as well.
  http://git.postgresql.org/pg/commitdiff/eb990a2b9efff853d0c68a9cf902b1df23498540

- pg_restore: Fix memory and file descriptor leak with directory
  format.  Found by Coverity
  http://git.postgresql.org/pg/commitdiff/d8e5b3427a0f0c127eb08a3130055180ed0118e1

- backend: Fix minor memory leak in configuration file processing.
  Just for consistency with the other code paths.  Found by Coverity
  http://git.postgresql.org/pg/commitdiff/693ff85d47181fad123ef245cf9c28539cc3db92

- psql: Remove inappropriate const qualifiers Since mbvalidate() can
  alter the string it validates, having the callers claim that the
  strings they accept are const is inappropriate.
  http://git.postgresql.org/pg/commitdiff/598bb8cdbd9c2554bd8acfc51c9fed473a7d37bc

- libpq: Fix minor memory leaks.  When using connection info arrays
  with a conninfo string in the dbname slot, some memory would be
  leaked if an error occurred while processing the following array
  slots.  found by Coverity
  http://git.postgresql.org/pg/commitdiff/d4318483e151b41cd663e7b36a5539d3c5048c5a

- pg_dump: Fix crash with invalid pg_cast row.  An invalid combination
  of pg_cast.castfunc and pg_cast.castmethod would result in a
  segmentation fault.  Now it prints a warning.  Found by Coverity
  http://git.postgresql.org/pg/commitdiff/359dea2859c63e73c8f3f4d769e5cf1eda2a428d

- Add note about column privilege behavior to REVOKE reference page.
  suggested by Josh Berkus
  http://git.postgresql.org/pg/commitdiff/313d29f740ef92e25f2e7707c61697471d22ca1b

Tom Lane pushed:

- Fix SPGiST vacuum algorithm to handle concurrent tuple motion
  properly.  A leaf tuple that we need to delete could get moved as a
  consequence of an insertion happening concurrently with the VACUUM
  scan.  If it moves from a page past the current scan point to a page
  before, we'll miss it, which is not acceptable.  Hence, when we see
  a leaf-page REDIRECT that could have been made since our scan
  started, chase down the redirection pointer much as if we were doing
  a normal index search, and be sure to vacuum every page it leads to.
  This fixes the issue because, if the tuple was on page N at the
  instant we start our scan, we will surely find it as a consequence
  of chasing the redirect from page N, no matter how much it moves
  around in between.  Problem noted by Takashi Yamamoto.
  http://git.postgresql.org/pg/commitdiff/b4af1c25bbc636379efc5d2ffb9d420765705b8a

- Create a stack of pl/python "execution contexts".  This replaces the
  former global variable PLy_curr_procedure, and provides a place to
  stash per-call-level information.  In particular we create a
  per-call-level scratch memory context.  For the moment, the scratch
  context is just used to avoid leaking memory from datatype output
  function calls in PLyDict_FromTuple.  There probably will be more
  use-cases in future.  Although this is a fix for a pre-existing
  memory leakage bug, it seems sufficiently invasive to not want to
  back-patch; it feels better as part of the major rearrangement of
  plpython code that we've already done as part of 9.2.  Jan Urbański
  http://git.postgresql.org/pg/commitdiff/ed75380bdae30dc1313aef44beafad860cf246c0

- Fix minor memory leak in PLy_typeinfo_dealloc().  We forgot to free
  the per-attribute array element descriptors.  Jan Urbański
  http://git.postgresql.org/pg/commitdiff/a14fa84693659c4c4a17204406945b29fae3d9c4

- Patch some corner-case bugs in pl/python.  Dave Malcolm of Red Hat
  is working on a static code analysis tool for Python-related C code.
  It reported a number of problems in plpython, most of which were
  failures to check for NULL results from object-creation functions,
  so would only be an issue in very-low-memory situations.  Patch in
  HEAD and 9.1.  We could go further back but it's not clear that
  these issues are important enough to justify the work.  Jan Urbański
  http://git.postgresql.org/pg/commitdiff/5cd72c7a7c7bd76ab028e1dc59d90a47750acebe

- Revisit handling of UNION ALL subqueries with non-Var output
  columns.  In commit 57664ed25e5dea117158a2e663c29e60b3546e1c I tried
  to fix a bug reported by Teodor Sigaev by making non-simple-Var
  output columns distinct (by wrapping their expressions with dummy
  PlaceHolderVar nodes).  This did not work too well.  Commit
  b28ffd0fcc583c1811e5295279e7d4366c3cae6c fixed some ensuing problems
  with matching to child indexes, but per a recent report from Claus
  Stadler, constraint exclusion of UNION ALL subqueries was still
  broken, because constant-simplification didn't handle the injected
  PlaceHolderVars well either.  On reflection, the original patch was
  quite misguided: there is no reason to expect that EquivalenceClass
  child members will be distinct.  So instead of trying to make them
  so, we should ensure that we can cope with the situation when
  they're not.  Accordingly, this patch reverts the code changes in
  the above-mentioned commits (though the regression test cases they
  added stay).  Instead, I've added assorted defenses to make sure
  that duplicate Emmanuel Cecchet child members don't cause any
  problems.  Teodor's original problem ("MergeAppend child's
  targetlist doesn't match MergeAppend") is addressed more directly by
  revising prepare_sort_from_pathkeys to let the parent MergeAppend's
  sort list guide creation of each child's sort list.  In passing, get
  rid of add_sort_column; as far as I can tell, testing for duplicate
  sort keys at this stage is dead code.  Certainly it doesn't trigger
  often enough to be worth expending cycles on in ordinary queries.
  And keeping the test would've greatly complicated the new logic in
  prepare_sort_from_pathkeys, because comparing pathkey list entries
  against a previous output array requires that we not skip any
  entries in the list.  Back-patch to 9.1, like the previous patches.
  The only known issue in this area that wasn't caused by the
  ill-advised previous patches was the MergeAppend planning failure,
  which of course is not relevant before 9.1.  It's possible that we
  need some of the new defenses against duplicate child Emmanuel
  Cecchet entries in older branches, but until there's some clear
  evidence of that I'm going to refrain from back-patching further.
  http://git.postgresql.org/pg/commitdiff/dd4134ea56cb8855aad3988febc45eca28851cd8

- Improve commentary in match_pathkeys_to_index().  For a little while
  there I thought match_pathkeys_to_index() was broken because it
  wasn't trying to match index columns to pathkeys in order.  Actually
  that's correct, because GiST can support ordering operators on any
  random collection of index columns, but it sure needs a comment.
  http://git.postgresql.org/pg/commitdiff/b67ad046e6c37cbe0eebc5745323ed9864192f52

Robert Haas pushed:

- pgstattuple: Add new error case for spgist indexes.  Extracted from
  a larger patch by Jaime Casanova, reviewed by Noah Misch.  I think
  this error message could use some more extensive revision, but this
  at least makes the handling of spgist consistent with what we do for
  other types of indexes that this code doesn't know how to handle.
  http://git.postgresql.org/pg/commitdiff/97c85098de1e21825adf447df60b95a56cef7bd8

- pgstattuple: Use a BufferAccessStrategy object to avoid
  cache-trashing.  Jaime Casanova, reviewed by Noah Misch, slightly
  modified by me.
  http://git.postgresql.org/pg/commitdiff/2e46bf67114586835f4a9908f1a1f08ee8ba83a8

- sepgsql_setcon().  This is intended as infrastructure to allow
  sepgsql to cooperate with connection pooling software, by allowing
  the effective security label to be set for each new connection.
  KaiGai Kohei, reviewed by Yeb Havinga.
  http://git.postgresql.org/pg/commitdiff/523176cbf14a3414170a83dd43686c0eccdc61c6

- Copy editing of sepgsql documentation.
  http://git.postgresql.org/pg/commitdiff/d0ed9efdf713f1bd8cdb5897d3f0c825986ff3b4

- A couple more fixes for the sepgsql documentation.
  http://git.postgresql.org/pg/commitdiff/4012810a688f37ea12159f93d3ab0a1ed47ca3dc

Heikki Linnakangas pushed:

- Add comments explaining why our Itanium spinlock implementation is safe.
  http://git.postgresql.org/pg/commitdiff/aef5fe7efee5bde4abd618adbaf4c13f44ee59ab

Andrew Dunstan pushed:

- Honor inputdir and outputdir when converting regression files.  When
  converting source files, pg_regress' inputdir and outputdir options
  were ignored when computing the locations of the destination files.
  In consequence, these options were effectively unusable when the
  regression inputs need to be adjusted by pg_regress. This patch
  makes pg_regress put the converted files in the same place that
  these options specify non-converted input or results files are to be
  found. Backpatched to all live branches.
  http://git.postgresql.org/pg/commitdiff/e3fc4a97bc8ee82a78605b5ffe79bd4cf3c6213b

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

Joachim Wieland sent in two more revisions of the patch to implement
parallel pg_dump.

Jeff Davis sent in three revisions of a patch to add an initdb option
to refrain from doing an fsync.

Fujii Masao sent in another revision of the patch add xlog location to
pg_size_pretty().

Alexander Korotkov sent in a patch to fix an infelicity in GiST
indexing on points.

Pavel Stehule sent in another revision of the PL/pgsql CHECK FUNCTION
code.

Fujii Masao sent in a patch to fix a condition which could cause
walsender to spin the CPU too much.

Etsuro Fujita sent in a patch to support error handling in file_fdw.

Hans-Juergen Schoenig sent in another WIP patch to track cross-column
correlations.

Heikki Linnakangas sent in another revision of the patch to scale xlog
insertion.

Marco Nenciarini and Gabriele Bartolini traded patches to support
arrays each element of which is a foreign key.

David Fetter sent in two revisions of a patch to make CREATE FOREIGN
TABLE (... LIKE ... ) work.

Fabrízio de Royes Mello sent in two revisions of a patch to include
VALID UNTIL date in \d for roles (users and groups).

Shigeru HANADA sent in another revision of the patch to create a
PostgreSQL FDW.

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

Dimitri Fontaine sent in two more revisions of a patch to add command
triggers.

Daniel Farina sent in three revisions of a patch to make it possible
to initiate back-end termination from SQL, race-free.

Peter Eisentraut sent in a patch to enable renaming domain
constraints.

Alvaro Herrera sent in another revision of the patch to implement
foreign key locks.

Peter Eisentraut sent in a patch to fix incompatible pointer types
when pg_dump uses the current (i.e. new) zlib.

Joachim Wieland sent in a patch to fix a double free in pg_dump.