== PostgreSQL Weekly News - October 02 2011 ==

== PostgreSQL Weekly News - October 02 2011 ==

Early bird registration (cheap! cheap!) is available for PGDay.IT
http://blog.2ndquadrant.com/en/2011/09/pgday-it-2011-early-bird-registrations-open.html

The list of speakers for PGBR2011 is up:
http://pgbr.postgresql.org.br/2011/palestrantes.en.php

== PostgreSQL Product News ==

check_postgres 2.18.0, a script for monitoring PostgreSQL, released.
http://bucardo.org/wiki/Check_postgres

tail_n_mail 1.20.3, a PostgreSQL log watcher program, released.
http://bucardo.org/wiki/Tail_n_mail

Pyrseas 0.4.0, a toolkit for PostgreSQL version control, released on PGXN.
http://pgxn.org/dist/pyrseas/

xlogdump 0.5.0, a tool for extracting data from WAL segment files, released.
https://github.com/snaga/xlogdump

== PostgreSQL Local ==

PostgreSQL Conference Europe 2011 will be held on October 18-21 in
Amsterdam.
http://2011.pgconf.eu/

PG-Day Denver 2011 will be held on Friday, October 21st, 2011 at
the Auraria Campus near downtown Denver, Colorado.
http://pgday.consistentstate.com/

pgbr will be in Sao Paulo, Brazil November 3-4, 2011.
http://pgbr.postgresql.org.br/

PGConf.DE 2011 is the German-speaking PostgreSQL Conference and will
take place on November 11th in the Rheinisches Industriemuseum in
Oberhausen, Germany.  Call for Papers is open.
http://2011.pgconf.de/

The fifth edition of the Italian PostgreSQL Day (PGDay.IT 2011) will
be held on November 25, 2011 in Prato, Italy.
http://2011.pgday.it/

The Call for Papers for is open for FLOSS UK, which will be held in
Edinburgh from the 20th to the 22nd March 2012.  The deadline for
proposals is the 18th November 2011 and selected speakers will be
notified by the 25th November 2011.  Proposals should be submitted to
postgresql2012 AT flossuk DOT org.  More information at:
http://www.flossuk.org/Events/Spring2012


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

- Use a fresh copy of query_list when making a second plan in
  GetCachedPlan.  The code path that tried a generic plan, didn't like
  it, and then made a custom plan was mistakenly passing the same copy
  of the query_list to the planner both times.  This doesn't work too
  well for nontrivial queries, since the planner tends to scribble on
  its input.  Diagnosis and fix by Yamamoto Takashi.
  http://git.postgresql.org/pg/commitdiff/21fb95da46bce8de3e149707c680d489b8a5ffb0

- Speed up array element assignment in plpgsql by caching type
  information.  Cache assorted data in the PLpgSQL_arrayelem struct to
  avoid repetitive catalog lookups over multiple executions of the
  same statement.  Pavel Stehule
  http://git.postgresql.org/pg/commitdiff/16762b519c9421ad5f1e373b1d89b0f2f6568769

- Allow snapshot references to still work during transaction abort.
  In REPEATABLE READ (nee SERIALIZABLE) mode, an attempt to do
  GetTransactionSnapshot() between AbortTransaction and
  CleanupTransaction failed, because GetTransactionSnapshot would
  recompute the transaction snapshot (which is already wrong, given
  the isolation mode) and then re-register it in the
  TopTransactionResourceOwner, leading to an Assert because the
  TopTransactionResourceOwner should be empty of resources after
  AbortTransaction.  This is the root cause of bug #6218 from Yamamoto
  Takashi.  While changing plancache.c to avoid requesting a snapshot
  when handling a ROLLBACK masks the problem, I think this is really a
  snapmgr.c bug: it's lower-level than the resource manager mechanism
  and should not be shutting itself down before we unwind resource
  manager resources.  However, just postponing the release of the
  transaction snapshot until cleanup time didn't work because of the
  circular dependency with TopTransactionResourceOwner.  Fix by
  managing the internal reference to that snapshot manually instead of
  depending on TopTransactionResourceOwner.  This saves a few cycles
  as well as making the module layering more straightforward.
  predicate.c's dependencies on TopTransactionResourceOwner go away
  too.  I think this is a longstanding bug, but there's no evidence
  that it's more than a latent bug, so it doesn't seem worth any risk
  of back-patching.
  http://git.postgresql.org/pg/commitdiff/57eb009092684e6e1788dd0dae641ccee1668b10

- Fix window functions that sort by expressions involving aggregates.
  In commit c1d9579dd8bf3c921ca6bc2b62c40da6d25372e5, I changed things
  so that the output of the Agg node that feeds the window functions
  would not list any ungrouped Vars directly.  Formerly, for example,
  the Agg tlist might have included both "x" and "sum(x)", which is
  not really valid if "x" isn't a grouping column.  If we then had a
  window function ordering on something like "sum(x) + 1",
  prepare_sort_from_pathkeys would find no exact match for this in the
  Agg tlist, and would conclude that it must recompute the expression.
  But it would break the expression down to just the Var "x", which it
  would find in the tlist, and then rebuild the ORDER BY expression
  using a reference to the subplan's "x" output.  Now, after the
  above-referenced changes, "x" isn't in the Agg tlist if it's not a
  grouping column, so that prepare_sort_from_pathkeys fails with
  "could not find pathkey item to sort", as reported by Bricklen
  Anderson.  The fix is to not break down Aggrefs into their component
  parts, but just treat them as irreducible expressions to be sought
  in the subplan tlist.  This is definitely OK for the use with
  respect to window functions in grouping_planner, since it just built
  the tlist being used on the same basis.  AFAICT it is safe for other
  uses too; most of the other call sites couldn't encounter Aggrefs
  anyway.
  http://git.postgresql.org/pg/commitdiff/269c5dd2f46e3490da05d5dd5dad07828df281d9

- Take sepgsql regression tests out of the regular regression test
  mechanism.  Because these tests require root privileges, not to
  mention invasive changes to the security configuration of the host
  system, it's not reasonable for them to be invoked by a regular
  "make check" or "make installcheck".  Instead, dike out the
  Makefile's knowledge of the tests, and change chkselinuxenv (now
  renamed "test_sepgsql") into a script that verifies the environment
  is workable and then runs the tests.  It's expected that
  test_sepgsql will only be run manually.  While at it, do some
  cleanup in the error checking in the script, and do some
  wordsmithing in the documentation.
  http://git.postgresql.org/pg/commitdiff/cc4ff8742b99d3b20a52f529d03bbe802f4b0053

- Update and extend the EXPLAIN-related documentation.  I've made a
  significant effort at filling in the "Using EXPLAIN" section to be
  reasonably complete about mentioning everything that EXPLAIN can
  output, including the "Rows Removed" outputs that were added by
  Marko Tiikkaja's recent documentation-free patch.  I also updated
  the examples to be consistent with current behavior; several of them
  were not close to what the current code will do.  No doubt there's
  more that can be done here, but I'm out of patience for today.
  http://git.postgresql.org/pg/commitdiff/a32dd16459ae8fbc1e09607d7ed960b3dcce7dba

- Fix index matching for operators with mixed collatable/noncollatable
  inputs.  If an indexable operator for a non-collatable indexed
  datatype has a collatable right-hand input type, any OpExpr for it
  will be marked with a nonzero inputcollid (since having one
  collatable input is sufficient to make that happen).  However, an
  index on a non-collatable column certainly doesn't have any
  collation.  This caused us to fail to match such operators to their
  indexes, because indxpath.c required an exact match of index
  collation and clause collation.  It seems correct to allow a match
  when the index is collation-less regardless of the clause's
  inputcollid: an operator with both noncollatable and collatable
  inputs could perhaps depend on the collation of the collatable
  input, but it could hardly expect the index for the noncollatable
  input to have that same collation.  Per bug #6232 from Pierre
  Ducroquet.  His example is specifically about "hstore ? text" but
  the problem seems quite generic.
  http://git.postgresql.org/pg/commitdiff/cb37c291060dd13b1a8ff61fceee09efcfbc34e1

- Fix recursion into previously planned sub-query in
  examine_simple_variable.  This code was looking at the sub-Query
  tree as seen in the parent query's RangeTblEntry; but that's the
  pristine parser output, and what we need to look at is the tree as
  it stands at the completion of planning.  Otherwise we might pick up
  a Var that references a subquery that got flattened and hence has no
  RelOptInfo in the subroot.  Per report from Peter Geoghegan.
  http://git.postgresql.org/pg/commitdiff/79edb2b1dc33166b576f51a8255a7614f748d9c9

- Support GiST index support functions that want to cache data across
  calls.  pg_trgm was already doing this unofficially, but the
  implementation hadn't been thought through very well and leaked
  memory.  Restructure the core GiST code so that it actually works,
  and document it.  Ordinarily this would have required an extra
  memory context creation/destruction for each GiST index search, but
  I was able to avoid that in the normal case of a non-rescanned
  search by finessing the handling of the RBTree.  It used to have its
  own context always, but now shares a context with the scan-lifespan
  data structures, unless there is more than one rescan call.  This
  should make the added overhead unnoticeable in typical cases.
  http://git.postgresql.org/pg/commitdiff/d22a09dc70f9830fa78c1cd1a3a453e4e473d354

- Cache the result of makesign() across calls of gtrgm_penalty().
  Since gtrgm_penalty() is usually called many times in a row with the
  same "newval" (to determine which item on an index page newval fits
  into best), the makesign() calculation is repetitious.  It's
  expensive enough to make it worth caching the result, so do so.  On
  my machine this is good for more than a 40% savings in the time
  needed to build a trigram index on /usr/share/dict/words.  This is
  all per a suggestion of Heikki's.  In passing, make some
  mostly-cosmetic improvements in the caching logic in the other
  functions in this file that rely on caching info in fn_extra.
  http://git.postgresql.org/pg/commitdiff/0a5d5a49d9965aa092e75ce31a88fbf5f05c5009

- Improve generated column names for cases involving sub-SELECTs.
  We'll now use "exists" for EXISTS(SELECT ...), "array" for
  ARRAY(SELECT ...), or the sub-select's own result column name for a
  simple expression sub-select.  Previously, you usually got
  "?column?" in such cases.  Marti Raudsepp, reviewed by Kyotaro
  Horiugchi
  http://git.postgresql.org/pg/commitdiff/5ec6b7f1b87f0fa006b8e08a11cd4e99bcb67358

- Restructure error handling in reading of postgresql.conf.  This
  patch has two distinct purposes: to report multiple problems in
  postgresql.conf rather than always bailing out after the first one,
  and to change the policy for whether changes are applied when there
  are unrelated errors in postgresql.conf.  Formerly the policy was to
  apply no changes if any errors could be detected, but that had a
  significant consistency problem, because in some cases specific
  values might be seen as valid by some processes but invalid by
  others.  This meant that the latter processes would fail to adopt
  changes in other parameters even though the former processes had
  done so.  The new policy is that during SIGHUP, the file is rejected
  as a whole if there are any errors in the "name = value" syntax, or
  if any lines attempt to set nonexistent built-in parameters, or if
  any lines attempt to set custom parameters whose prefix is not
  listed in (the new value of) custom_variable_classes.  These tests
  should always give the same results in all processes, and provide
  what seems a reasonably robust defense against loading values from
  badly corrupted config files.  If these tests pass, all processes
  will apply all settings that they individually see as good, ignoring
  (but logging) any they don't.  In addition, the postmaster does not
  abandon reading a configuration file after the first syntax error,
  but continues to read the file and report syntax errors (up to a
  maximum of 100 syntax errors per file).  The postmaster will still
  refuse to start up if the configuration file contains any errors at
  startup time, but these changes allow multiple errors to be detected
  and reported before quitting.  Alexey Klyukin, reviewed by Andy
  Colson and av (Alexander ?) with some additional hacking by Tom Lane
  http://git.postgresql.org/pg/commitdiff/d56b3afc0376afe491065d9eca6440b3cc7b1346

Robert Haas pushed:

- Update obsolete comments.  This was partially fixed by
  57fdb2b0d835fe201434fc28bf5dabf83ada26d1, back in 2005, but it
  missed a couple of spots.  YAMAMOTO Takashi
  http://git.postgresql.org/pg/commitdiff/821fd903f9b7f5a8ca439b9b7cb6d2f54672582f

- heap_update() must recheck tuple after unlocking and relocking
  buffer.  Bug found by Alvaro Herrera, fix suggested by Heikki
  Linnakangas and reviewed by Tom Lane.
  http://git.postgresql.org/pg/commitdiff/624f155ffa93d1af4fb9d91fd5c0eb05da1e9e54

- sepgsql uavc comment improvements.  Robert Haas and KaiGai Kohei
  http://git.postgresql.org/pg/commitdiff/a9d845be496b779efbcf16095bf236639e9e7665

- Update comments related to the crash-safety of the visibility map.
  In hio.c, document how we avoid deadlock with respect to visibility
  map buffer locks.  In visibilitymap.c, update the LOCKING section of
  the file header comment.  Both oversights noted by Heikki
  Linnakangas.
  http://git.postgresql.org/pg/commitdiff/f70648d5a16b4b048cacd04b82079e6734449bc2

Alvaro Herrera pushed:

- Fix typo
  http://git.postgresql.org/pg/commitdiff/173499273804f137bbb4a6785d6967b9e2d7e478

- Remove dependency on error ordering in isolation tests.  We now
  report errors reported by the just-unblocked and unblocking
  transactions identically; this should fix relatively common
  buildfarm failures reported by animals that are failing the "wrong"
  session.
  http://git.postgresql.org/pg/commitdiff/bbd38af3a879604db2b455cbb2b4ec93275249fa

Bruce Momjian pushed:

- In pg_upgrade, because toast table names can be mismatched with the
  heap oid on 8.4, modify the toast name comparison test to only apply
  to old 9.0+ servers.  (The test was previously 8.4+.) Backpatch to
  9.1.X.
  http://git.postgresql.org/pg/commitdiff/5e5958428b7c6e91ffddf46bf9aafd920af57a60

- Fix pg_upgrade for EXEC_BACKEND builds (e.g. Windows) by properly
  passing the -b/binary-upgrade flag.  Backpatch to 9.1.X.
  http://git.postgresql.org/pg/commitdiff/054219c907a9fe668b2dac4134563b4f38e2b233

- In pg_upgrade, remove unnecessary local variable.
  http://git.postgresql.org/pg/commitdiff/878b74e094a70e660e5ed365a2c4e1b41460515d

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

Peter Geoghegan sent in another revision of the patch to inline
comparison operators.

Shigeru HANADA sent in another revision of the patch to display
accumulated autovacuum cost.

Noah Misch and Alvaro Herrera traded patches to test for isolation
failures.

Fujii Masao sent in two more revisions of the patch to enable making a
base backup from a hot standby.

Bruce Momjian sent in two more revisions of a patch to fix testing for
pg_upgrade.

Andreas Karlsson sent in a patch to allow for EXECUTE tab completion in
psql.

Tom Lane sent in a WIP patch to break a circular dependency in
snapshot management.

Marti Raudsepp sent in a patch to log crashed backends.

Brar Piening sent in another revision of the patch to support VS2010.

Joachim Wieland sent in another revision of the patch to enable
exporting and synchronizing snapshots.

KaiGai Kohei sent in another revision of the patch to rework DROP into
a single framework.

KaiGai Kohei sent in another revision of the patch to fix certain
leaks in VIEWs.

Bruce Momjian sent in another revision of a patch to fix pg_upgrade.

Gurjeet Singh sent in a patch to remove savepointLevel from
TransactionState.

Alvaro Herrera sent in a patch to make SLRU's truncate use callbacks.

Fujii Masao sent in a patch which prevents the creation of
restartpoints by using rm_safe_restartpoint callback when a consistent
state is not yet reached and the invalid-page table is not empty.

Kyotaro HORIGUCHI sent in another revision of the patch to add
make_greater_string().

KaiGai Kohei sent in another revision of the patch to add object
access hooks with argument support.

Jeff Davis sent in two more revisions of the patch to add range types.

Bruce Momjian sent in a patch which makes an empty string the default
for external_pid_file in postgresql.conf to make it consistent with
other defaults there.

Bruce Momjian sent in a patch to add a configuration directory setting
for pg_upgrade.

Simon Riggs sent in another revision of a patch to separate the
background writer process from the checkpointer.

Tom Lane sent in a WIP patch to remove custom variable classes for
GUCs.   There hadn't been a way to validate them anyhow, so now
arbitrary GUCs are allowed.